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:
#!/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)
ChangeUSER_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