In last month's article, we discussed performing full backups and incremental backups. This blog entry will focus more on how to actually accomplish these backups with MySQL.
Full backups with MySQL are quite straightforward. The mysqldump utility has many flags that can be used to control what is output. For our example, we will include absolutely everything so that the backup can be transferred to a new server with no additional work required (i.e. for emergency provisioning).
mysqldump -u [user] -p[password] --all-databases --single-transaction --triggers --routines > /path/to/backup.sql
To break down what this is doing, the flag commands do the following:
- -u [user] - This is the user to perform the backup (typically root)
- -p[password] - This is the password for the user being used; if this is being run manually, leave out the password and just leave it as -p to be prompted for the password.
- --all-databases - Export all databases at the same time. The default is to specify the database to back up.
- --single-transaction - Creates a point-in-time snapshot of the data. Any uncommitted transactions are not included in this backup, so data consistency is guaranteed.
- --triggers - Include all defined triggers in the export.
- --routines - Include all defined stored procedures in the export.
Once complete, the file at /path/to/backup.sql will contain all of the backed up data. To instead compress the backup using gzip, the following alternate can be used:
mysqldump -u .... --routines | gzip > /path/to/backup.sql.gz
Incremental backups will utilize the above full backup process to perform the backup, but there is a configuration change and two additional flags to the dump command that are required. The changes to the data that will be used to restore are kept in what are called binary logs. This stores the queries that were modified data when the transaction is committed.
To enable binary logging, add the following to my.cnf in the mysqld section and restart MySQL:
where [prefix] is a name that will denote the binary logs (typically "binlog"). This will enable binary logging for all databases.
With binary logging enabled, you can now back up the files you prefixed with the log-bin parameter which contain the progressive transactions. The final step is to perform a full backup that gets you a point in time to move forward with the incremental backups should restoration become necessary. This will be the full mysqldump command from above with the addition of the following flags: --flush-logs and --master-data.
The --flush-logs command will tell MySQL to flush the binary logs to disk before starting the backup, and the --master-data flag works with the binary logs to maintain a single point in time for the backup and logs to be in sync. This allows the binary logs created after the backup was started to be applied to a restore from the full backup that was performed.