MySQL Backup and Restore: Difference between revisions

No edit summary
mNo edit summary
Line 1: Line 1:
Sites that need an extra level of redundancy for their MySQL data can use the [http://dev.mysql.com/doc/refman/5.0/en/binary-log.html MySQL Binary Log] and [http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html mysqldump].  <tt>mysqldump</tt> is used to dump all the data in the database to a SQL script file.  Data changes that occur later than the dumpfile are written to the binary log.  If the database ever needs to be recovered, you can use the [http://dev.mysql.com/doc/refman/5.0/en/mysql.html mysql] command-line tool to load the data from the dump file and binary logs.
Sites that need an extra level of redundancy for their MySQL data can use the [http://dev.mysql.com/doc/refman/5.0/en/binary-log.html MySQL Binary Log] and [http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html mysqldump].  <tt>mysqldump</tt> is used to dump all the data in the database to a SQL script file.  Data changes that occur later than the dumpfile are written to the binary log.  If the database ever needs to be recovered, you can use the [http://dev.mysql.com/doc/refman/5.0/en/mysql.html mysql] command-line tool to load the data from the dump file and binary logs.


To configure binary logging, edit <tt>/opt/zimbra/conf/my.cnf</tt> and add the following line:
To enable binary logging, edit <tt>/opt/zimbra/conf/my.cnf</tt> and add the following line:


<pre>
<pre>

Revision as of 19:08, 26 October 2009

Sites that need an extra level of redundancy for their MySQL data can use the MySQL Binary Log and mysqldump. mysqldump is used to dump all the data in the database to a SQL script file. Data changes that occur later than the dumpfile are written to the binary log. If the database ever needs to be recovered, you can use the mysql command-line tool to load the data from the dump file and binary logs.

To enable binary logging, edit /opt/zimbra/conf/my.cnf and add the following line:

log-bin = <path>/<basename>

where <path> is the path to the directory that contains the binlogs and <basename> is the prefix of the binary log filenames. Binary log files should be placed on a separate disk device from the MySQL database files, so that they can be used for recovery if the database disk fails. After enabling the binary log, restart the mail server and MySQL with

$ zmstorectl restart

and check the binary log directory to make sure the logs are being written. Now set up a cron job that runs mysqldump on a scheduled basis:

/opt/zimbra/mysql/bin/mysqldump --all-databases --single-transaction --master-data  --flush-logs > <dump-file>.sql

where <dump-file> is the path to the MySQL dump file. Be sure to put the dump file on a separate disk device from the MySQL database files. Each time

If the database ever needs to be recovered, use the mysql command-line tool to load the dump file and binary log records. Make sure that the mail server is not running while you're performing the restore.

First, determine the first binlog file that needs to be replayed:

$ grep "CHANGE MASTER" dump.sql | head -1
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000006', MASTER_LOG_POS=106;

Next, determine the last binlog file that needs to be replayed:

$ ls /opt/zimbra/binlog/binlog.0* | sort | tail -1
/opt/zimbra/binlog/binlog.000009

Flush the logs so that the binlog gets rotated, and the restore operations get written to binlog.000010:

$ mysqladmin flush-logs

Now restore the database dump:

$ mysql --user=root --password=XXX < dump.sql

Once the dump is restored, replay the binlog files 6-9 to restore the latest changes:

$ mysqlbinlog /opt/zimbra/binlog/binlog.000006 | mysql --user=root --password=XXX
$ mysqlbinlog /opt/zimbra/binlog/binlog.000007 | mysql --user=root --password=XXX
$ mysqlbinlog /opt/zimbra/binlog/binlog.000008 | mysql --user=root --password=XXX
$ mysqlbinlog /opt/zimbra/binlog/binlog.000009 | mysql --user=root --password=XXX

Please see the MySQL documentation for details about mysqldump and binary logs:

Also, note that the backup and restore utilities in ZCS 6.0 have options for backing up only the database and not the content of the messages and index. This may be an option for large sites that have reliable storage and are unable to do full mailbox backups. Please see [enhancement request 35278] for more details. As always, there are tradeoffs and risk of data loss, so please contact Zimbra Technical Support for more details.

Jump to: navigation, search