MySQL automated backup on Linux

1. Preparation beforehand

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 in recently created folder.

sudo gedit /media/mysqldump/

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.

3. 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/

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

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