Bash script to automatically back-up and compress a MySQL database and delete older backups in Linux servers

spiros · 1 · 1394

spiros

  • Administrator
  • Hero Member
  • *****
    • Posts: 823554
    • Gender:Male
  • point d’amour
Script to automatically back-up and compress a MySQL database and delete older backups in Linux servers

(Tested on CENTOS 5.8 and CENTOS 7.5 with Cpanel and WHM)

Create a file that looks like this:

Code: [Select]
#!/bin/bash

mysqldump -u USER_NAME -pUSER_PASS DATABASE_NAME  | gzip -v >/home/backups/MY_DATABASE_NAME-$(date +%Y-%m-%d-%H.%M.%S).sql.gz
find /home/backups/ -type f -mtime +7 -exec rm {} \;

(You can find the file attached with proper Linux line endings)

Change
USER_NAME = database user name
USER_PASS = database user pass
DATABASE_NAME = name of your database
MY_DATABASE_NAME = the name you want your file name of the back-up file to start with

The script deletes backups older than 7 days. If you want to change that modify the +7 value.

Make sure when you edit your file that you have Unix line endings. Some text editors like Notepad++ let you see and change this. Give it an .sh extension.

Upload it to your server's /home directory (you can use WinSCP to do that if you have root access or ask your host to do it).

Create a backups subfolder and make it writable and chown it to the name of the database owner.

Chown the file to the name of the user who owns the database and make it executable by the owner (with WinSCP, right click on file, select Properties and edit as in the attached image). You can use WinSCP to chown the file by creating a custom command.

Create a cron job in your Cpanel (Cpanel > Cron Jobs). I would make it run daily sometime in the night, say 4am:

So it would look like

0 4 * * * /home/backup.sh

You should get an e-mail (on your CPanel e-mail) with Cron Daemon as sender, mentioning the percentage of compression if the script was executed OK, or with an error message. Check your spam if not.

For a comprehensive backup script, you can try also
AutoMySQLBackup | SourceForge.net
« Last Edit: 16 May, 2018, 21:55:46 by spiros »


 

Search Tools