Automatically backup MySQL database

I needed to backup a couple of MySQL databaes automatically. Here is what I did to accomplish this.

I created a folder called mysqlbackup. I then created a script called “mysqldbbackup.sh” and put this script file in the mysqlbackup folder.

It contains “rm /mysqlbackup/DBNAME_backup.sql
mysqldump -u USERNAME -pPASSWORD DBNAME > /mysqlbackup/DBNAME_backup.sql

I used the “rm” command to remove the last database backup before the new backup runs. For one of my old blogs on mysqldump command click here.

You have to make the script executable. To do this use the following command

chmod +x  /mysqlbackup/mysqldbbackup.sh

Now test your script to make sure it works. CD to the mysqlbackup directory and run the script using by typing “mysqldbbackup.sh

Ok after it works you need to now schedule it. Use cron to schedule it. I set it to run ever Tuseday at 3am. Use this command to set it via cron  “0 3 * * 2 /mysqlbackup/mysqldbbackup.sh

For more on cron visit ABOUT CRONJOBS . For more about Linux Shell scripst visit LINUX BASH SHELL SCRIPT

Leave a reply