MySQL Automated Backup On Linux

1. Preparations.

First of all, let’s create a source folder for your backup plan where you will put the dump file of MySQL databases. In this article, it will be a folder with the name mysqldump which will be located in the media folder. Just bear in mind that the folder you create should be reachable by any users on your Linux.

sudo mkdir /media/mysqldump

2. Script.

Now let’s make a little script mysqldump.sh in recently created folder.

sudo gedit /media/mysqldump/mysqldump.sh

In the opened editor type this command with a correct username and a password. This user should have enough permissions to backup all your MySQL databases. As a result you will have one file with the current data in its name with all your MySQL databases.

mysqldump -u USER_NAME -p 'PASSWORD' --all-databases > /media/mysqldump/backupall_'/usr/bin/date +\%Y\%m\%d\%H\%M'

If you would like to keep a certain number of versions you should add this command to your script. Just change 10 to the desired number. The command below deletes the backup which is older than 10 days.

find /media/mysqldump -mtime +10 -type f -delete

Then save the changes and close the editor.

3A. Crontab.

To schedule this script to run everyday run crontab

sudo crontab -e

You will see the crontab window where you should configure the time when you will dump all your MySQL databases. Press "i" (for “insert”) to begin editing the file and type the command below. In this example the dump will run everyday at 12:55 PM.

55 12 * * * /media/mysqldump/mysqldump.sh

Press ESC and type :wq to save the changes and quit.

3B. Pre-backup Action Script.

Starting from Backup for Linux v 2.9.0 you can set up the MySQL script to be executed before the backup plan is run. To configure this in CLI execute this command:

./cbb editBackupPlan -n "Plan name goes here" -preAction yes -preActionPath "/media/mysqldump/mysqldump.sh"

In GUI and in the web interface there is a step in the backup plan wizard where you can specify the path to the script - the procedure is obvious.

4. Backup Plan.

Now you can create a backup plan and configure it to backup all files in the folder /media/mysqldump with the recurring option Everyday at 1.20 PM

Tech questions: support@msp360.com
Sales questions: sales@msp360.com