MySQL : Backup and Recovery
This article provides a quick guide to performing backup and recovery of MySQL databases. The article only describes those methods available without needing additional licensing.
- Logical Backups
- Cold Backups
- Binary Logs : Point In Time Recovery (PITR)
Logical Backup (mysqldump)
Amongst other things, the
mysqldump command allows you to do logical backups of your database by producing the SQL statements necessary to rebuild all the schema objects. An example is shown below.
$ # All DBs $ mysqldump --user=root --password=mypassword --all-databases > all_backup.sql $ # Individual DB (or comma separated list for multiple DBs) $ mysqldump --user=root --password=mypassword mydatabase > mydatabase_backup.sql $ # Individual Table $ mysqldump --user=root --password=mypassword mydatabase mytable > mydatabase_mytable_backup.sql $ # Multiple Tables $ mysqldump --user=root --password=mypassword mydatabase mytable1 mytable2 mytable3 > mydatabase_mytables_backup.sql
The full syntax of the command can be found here.
Recovery from Logical Backup (mysql)
The logical backup created using the
mysqldump command can be applied to the database using the MySQL command line tool, as shown below.
$ # All DBs $ mysql --user=root --password=mypassword < all_backup.sql $ # Individual DB $ mysql --user=root --password=mypassword --database=mydatabase < mydatabase_backup.sql
The full syntax of the MySQL command line tool can be found here.
Cold backups are a type of physical backup as you copy the database files while the database is offline.
The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. You can use whichever method you want to copy the files (cp, scp, tar, zip etc.).
# service mysqld stop # cd /var/lib/mysql # tar -cvzf /tmp/mysql-backup.tar.gz ./* # service mysqld start
Recovery from Cold Backup
To recover the database from a cold backup, stop MySQL, restore the backup files and start MySQL again.
# service mysqld stop # cd /var/lib/mysql # tar -xvzf /tmp/mysql-backup.tar.gz # service mysqld start
Binary Logs : Point In Time Recovery (PITR)
Binary logs record all changes to the databases, which are important if you need to do a Point In Time Recovery (PITR). Without the binary logs, you can only recover the database to the point in time of a specific backup. The binary logs allow you to wind forward from that point by applying all the changes that were written to the binary logs. Unless you have a read-only system, it is likely you will need to enable the binary logs.
To enable the binary blogs, edit the "/etc/my.cnf" file, uncommenting the "log_bin" entry.
# Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. log_bin
The binary logs will be written to the "datadir" location specified in the "/etc/my.cnf" file, with a default prefix of "mysqld". If you want alter the prefix and path you can do this by specifying an explicit base name.
# Prefix set to "mydb". Stored in the default location. log_bin=mydb # Files stored in "/u01/log_bin" with the prefix "mydb". log_bin=/u01/log_bin/mydb
Restart the MySQL service for the change to take effect.
# service mysqld restart
mysqlbinlog utility converts the contents of the binary logs to text, which can be replayed against the database.
You must make sure the "[client]" section of your "/etc/my.cnf" file, or local config file, does not contain any "ssl" references. The
mysqlbinlog utility doesn't support SSL and will produce the following type of error if the client configuration includes it.
mysqlbinlog: unknown variable 'ssl-ca=/home/mysql/certs/ca-cert.pem'
If you get this, simply comment out the references while you run the utility and put them back afterwards. You do not need a server restart.
The following exmaples show how it can be used.
# Display contents. mysqlbinlog mysqld-bin.000001 | more # Apply contents directly to the database. mysqlbinlog mysqld-bin.000001 | mysql -u root -p # Push contents to a file, then apply the file. # Allows you to edit the file before applying mysqlbinlog mysqld-bin.000001 > tmpfile mysql -u root -p < tmpfile # Pull out changes for a specific database mysqlbinlog --database=hr mysqld-bin.000001 | more # Pull out changes for a specific time period mysqlbinlog --start-datetime="2014-01-1 21:00:02" --stop-datetime="2014-01-02 09:00:00" \ --database=hr mysqld-bin.000001 # Pull out changes between specific log_pos events mysqlbinlog --start-position=123456 --stop-position=234567 \ --database=hr mysqld-bin.000001
When applying changes from multiple binary log files, do them using a single connection or push them in an ordered fashion to a temp file to be applied later.
# Display contents. mysqlbinlog mysqld-bin.000001 mysqld-bin.000002 | more # Apply contents directly to the database. mysqlbinlog mysqld-bin.000001 mysqld-bin.000002 | mysql -u root -p # Push contents to a file, then apply the file. # Allows you to edit the file before applying mysqlbinlog mysqld-bin.000001 > tmpfile mysqlbinlog mysqld-bin.000002 >> tmpfile mysql -u root -p < tmpfile # Pull out changes for a specific database mysqlbinlog --database=hr mysqld-bin.000001 mysqld-bin.000002 | more
For more information see:
- mysqldump - A Database Backup Program
- mysql - The MySQL Command-Line Tool
- Point-in-Time (Incremental) Recovery Using the Binary Log
- mysqlbinlog — Utility for Processing Binary Log Files
- MySQL Documentation: MySQL Reference Manuals
- MySQL : Example Backup Schedule
Hope this helps. Regards Tim...