In this guide, you will learn how to create a shell script that will backup all of your MySQL databases. You will also learn how to setup a scheduled task to run the backup script daily.
Step 1: Create the Backup Script
Create a folder to store your backup script in. I suggest
/scripts for this example:
sudo mkdir /scripts
Create a file called
mysql-backup.sh inside the scripts folder:
sudo vim /scripts/mysql-backup.sh
Now add the following code to the file and save it:
You will notice 5 configurable options at the beginning of this script. The main ones you will need to edit are the
PASSWORD. This should be changed to a MySQL user and password that has permissions to list and backup databases.
Once you have modified the options, make the script executable with the following command:
sudo chmod +x mysql-backup.sh
You can now test the backup script by running:
Step 2: Create the Crontab Scheduled Task
Now we will schedule the backup script to be run daily. We will do this by adding a call to the mysql-backup.sh script to the
Run the following command to open the
root crontab file:
sudo crontab -e
Now add to the last line of the file the following:
@daily sh /scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1
Save the file and then wait for the script to run. You can check for errors in
/var/log/mysql-backup.log. Once you are happy it is working, you can remove the
>> /var/log/mysql-backup.log 2>&1 from the crontab file.
Output of successful backup with GZIP enabled:
That’s it. You should now have a daily backup of all your MySQL databases. The default options of the script will create unlimited compressed backups. You should change the
DAYS_TO_KEEP option if you want to save disk space. If you have any questions or you have a better way of backing up MySQL databases, leave a comment below.
If you want to know how to backup multiple websites, read: How to Schedule a Backup of All Websites Hosted on Ubuntu 16.04.