Patrick Garman

27 Jun 2013

Backup All MySQL Databases Automatically via Cronjob

So recently I needed to have a method that will automatically backup all the databases in a MySQL server from a cron job, and thought I would share my a couple options in case you were trying to do the same thing.
In my own setup I have a dedicated Linode for nothing but MySQL. On this server I created a user that's sole purpose is for backups and the MySQL server has a user that has nothing but SELECT access on the databases so it cannot make any changes. The Linux user will have a cron job run nightly which will dump all the databases except a select few from the MySQL server and the cronjob will not need to include a username or password because they have been added to a .my.cnf file in the users home directory.

Setup your Users

If you are not sure how to create users in Linux or MySQL, Google will be your friend. If you are looking for an easy way to manage your MySQL servers - instead of using server resources for phpMyAdmin I've had good luck with Navicat.
In MySQL you need to create a user that has global privileges for "Lock Tables" and "Select" - if you use Navicat you would select the two checkboxes in the "Server Privileges" tab. To be a bit more secure you can also specify the host you are backing up your database from as well on the user, that way the user can only connect from that host. If you are connecting from the same server then enter "localhost" into the host field.

Once you have the MySQL user setup, you should create a file named .my.cnf (the first dot is important) and use the sample below to save your MySQL username and password. By creating this file and having your username and password included you will not need to enter a username and password to connect to MySQL anymore.
Sample .my.cnf file

[client]
user = myusername
password = mypassword

Choose your Command

These commands will run through an entire database and backup all of the databases except for Database, informationschema, or performanceschema. You can add or remove databases from this filter just by following the format in the command.
These commands work by first querying a list of databases, and then looping through them to create a separate backup for each database as opposed to a single file. It may just be me but I'd rather have separate backups of each database instead of one big one.

With GZIP

SQL files are plain text, and because of this you can save a lot of space using GZIP. As an example a 12MB SQL file GZIP'd with the default options has typically been compressed to around 1MB in size for me.

Single rsyncable file

_________________________________________________________________________________

mysql -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema' | while read dbname; do mysqldump --complete-insert "$dbname" | gzip --rsyncable > ~/backups/"$dbname".sql.gz; done

_________________________________________________________________________________

Multiple files with timestamps
_________________________________________________________________________________

mysql -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema' | while read dbname; do mysqldump --complete-insert "$dbname" | gzip > ~/backups/"$dbname"-`date +%Y-%m-%d-%T`.sql.gz; done

_________________________________________________________________________________

Without GZIP

If you are not concerned with disk space you can remove the GZIP commands and just use plain .SQL files.
Single rsyncable file
_________________________________________________________________________________

mysql -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema' | while read dbname; do mysqldump --complete-insert "$dbname" > ~/backups/"$dbname".sql; done

_________________________________________________________________________________

Multiple files with timestamps
_________________________________________________________________________________

mysql -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema' | while read dbname; do mysqldump --complete-insert "$dbname" > ~/backups/"$dbname"-`date +%Y-%m-%d-%T`.sql; done

_________________________________________________________________________________

Setup your Cronjobs

To reliably and automatically run this backup on your chosen schedule you will need to setup a cronjob. Again, Google will be your friend if you want to learn more about cronjobs but if you need help there are cronjob code generators that will let you paste your command and choose a schedule. I wouldn't suggest running this backup every minute of every day, nightly is a good starting point.

Edit your scheduled cronjobs

crontab -e

Sample cronjob

_________________________________________________________________________________

0    0   *   *   *   mysql -e 'show databases' | grep -Ev 'Database|information_schema|performance_schema' | while read dbname; do mysqldump --complete-insert "$dbname" | gzip --rsyncable > ~/backups/"$dbname".sql.gz; done

_________________________________________________________________________________

All content copyright Patrick Garman © 2014 • All rights reserved.

Thanks to Patrick Garman