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 named mysqldump located in the media folder. Bear in mind that the folder you create should be accessible to all users on your Linux system.
sudo mkdir /media/mysqldump
2. Script.
Now let’s create a small script, mysqldump.sh, in the recently created folder.
sudo gedit /media/mysqldump/mysqldump.sh
In the opened editor, type this command with the correct username and password. This user should have enough permissions to back up all your MySQL databases. As a result, you will have one file with all your MySQL databases and the current date in its name.
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, add this command to your script. Just change 10 to the desired number. The command below deletes backups 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 every day, run crontab:
sudo crontab -e
You will see the crontab window, where you should configure the time to 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 every day 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 version 2.9.0, you can set up the MySQL script to be executed before the backup plan runs. To configure this in the CLI, execute this command:
./cbb editBackupPlan -n "Plan name goes here" -preAction yes -preActionPath "/media/mysqldump/mysqldump.sh"
In the GUI and in the web interface, there is a step in the backup plan wizard where you can specify the path to the script.
4. Backup Plan.
Now you can create a backup plan and configure it to back up all files in the /media/mysqldump folder with the recurring option Everyday at 1.20 PM.
Contact Us
- Tech questions
- Sales questions: sales@msp360.com