How to Take Automatic MySQL Database Backup Using Shell Script and Cron Job

Write a script to take auto backup of MySQL database using Cronjob. First of all create a seperate sql backup directory in home folder to keep all your sql backup file. Then create the “mysqlbackup.sh” script file in “/usr/local/sbin/” folder.

$ mkdir /home/sqlbackup

Now, create “Script” folder in your home directory and create your “mysqlbackup.sh” script file in this folder.

$ sudo vi /usr/local/sbin/mysqlbackup.sh

#!/bin/bash
datum=`/bin/date +%Y%m%d-%H`
/usr/bin/mysqladmin --user=root --password=yourmysqlpassword stop-slave
/usr/bin/mysqldump --user=root --password=yourmysqlpassword --lock-all-tables \
     --all-databases > /home/sqlbackup/backup-${datum}.sql
/usr/bin/mysqladmin --user=root --password=yourmysqlpassword start-slave
for file in "$( /usr/bin/find /home/sqlbackup -type f -mtime +2 )"
do
 /bin/rm -f $file
done
exit 0

Note: Change username and password accoding to your database.

Code Explanation: “-mtime +2” means files that are more than 2 days old If you want to keep backup file older than 2 days then you have to change it’s value. Otherwise it will delete the backup files older than 2 days. Give execute permittion to this script.

$ sudo chmod +x /usr/local/sbin/mysqlbackup.sh

Final setp is set the cronjob. This cronjob will run every 3 hours so it will backup 4 times in a day. You can chage the time by editing the cronjob. For more information about cronjob see manual.

0 */3 * * * /usr/local/sbin/mysqlbackup.sh &> /dev/null

You can setup a FTP connection to copy this backup file to a remote server for more security.