How to backup MySQL database using shell and cron

03 Dec 2014 Matteo Mattei linux cron mysql backup

Given I did it dozens of times and everytime I have rewritten the code form scratch, I decided to write a simple script to backup all MySQL databases separately in order to avoid to always reinvent the wheel.

The following script must be configured with:

• the MySQL root user.
• the password of MySQL root user.
• the email address to receive the notifications in case of failures.
• the destination folder of the backups.
• the number of copies to keep before overwriting the old backup.

NOTE: given the MySQL root password is in clear is important to limit the access to the script:

chown root.root mysql_backup.sh
chmod 770 mysql_backup.sh

Then, to set a cronjob to do it automatically every day, open /etc/crontab and add the following line at the bottom:

# mysql backup
32 4  *  *  *   root    /root/mysql_backup.sh