This is a task I perform fairly regularly, but not regularly enough to remember exactly which permissions are needed, so I invariably end up having to look up the MySQL reference manual.
The task is to set up a scheduled backup of a MySQL server using mysqldump and cron. The backup should contain not only a copy of all databases on the server but also the state of the binary logs at the time, ie. using the –master-data option. I find binary logging + regular full snapshots of the MySQL server to be a great and simple backup strategy that allows not only for restoring the entire server to a known good state (from the snapshot backup) but also restoring to any point in time using the binary logs.
For example, let’s say you run a daily backup at midnight. At 13:26 a friendly Web developer (definitely not you) made a typo in an SQL query and wiped out a whole column full of very important data.
With snapshots + binary log, you can:
1. Restore the snapshot from midnight
2. Use the mysqlbinlog command to extract all the data-affecting queries that were run on the server between the time the backup ran and the time when the erroneous query was made. You can do this by starting your search from the log position recorded at the top of your backup file, and ending with the position just before the bad query. Use mysqlbinlog and grep to find the latter position. Pipe all this extracted data into another sql file.
3. Import this sql file into your MySQL server.
4. You now have your server restored to the exact state it was in immediately prior to the nefarious query.
Sounds good? Here’s how to set up this backup scheme:
1. Set up a MySQL user with permission to run the backups, nothing more:
mysql> GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD, SUPER, REPLICATION CLIENT ON *.* TO ‘backup’@’localhost’;
mysql> FLUSH PRIVILEGES;
2. Set up a cron job: [root@myserver ~]# crontab -e
# MySQL backup
0 0 * * * mysqldump -u backup -A –master-data -v | gzip > /backups/current/MySQL/backup.sql.gz
Change the path to taste. Or if you prefer to store 7 days worth of backups, you could do something like:
0 0 * * * mysqldump -u backup -A –master-data -v | gzip > /backups/current/MySQL/backup-`date +\%a`.sql.gz
This will name your backups with the day of the week in the filename, eg. backup-Mon.sql.gz. See ‘man date’ for formatting options.
Esc, :w and :q to save your crontab and exit.
An explanation of the options to mysqldump:
-u: The MySQL user name to use when connecting to the server.
–master-data: Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.
-A: Dump all tables in all databases.
-v: Verbose mode. Print more information about what the program does.
MySQL dump files are plain text which compress really well, so I feed the data into gzip before writing to disk.
It is worth noting that in order for mysqldump to take a consistent snapshot using –master-data, –lock-all-tables is automatically turned on: “Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump.”
If you have a large amount of data, the backup may take a considerable amount of time, during which any INSERT/UPDATE/DELETE operations on your databases will be blocked. The queries will be shown in the process list as “Waiting for release of readlock” and will execute when your backup has completed, if the connection has not yet timed out. To avoid this problem, I typically recommend to my customers to run a separate MySQL server specifically as a disaster recovery and/or backup system. This can be on a separate server, a small VM, or even on the same server as the primary but bound to a different port. Set the backup server to replicate from the master using MySQL’s built-in replication system then schedule your backups to run on the backup server instead of the master. This way you will avoid any disruption to your live system and still get clean snapshots. While your backup is running the replication slave process will have to wait for the table locks, but as soon as the backup is finished replication will automatically resume so your slave catches up with the master again.