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.
1 2 3 | $ mkdir /home/sqlbackup |
Now, create “Script” folder in your home directory and create your “mysqlbackup.sh” script file in this folder.
1 2 3 | $ sudo vi /usr/local/sbin/mysqlbackup.sh |
1 2 3 4 5 6 7 8 9 10 11 12 13 | #!/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.
1 2 3 | $ 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.
1 2 3 | 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.
