MySQL Backup and Restore: Difference between revisions

mNo edit summary
mNo edit summary
Line 12: Line 12:
$ zmstorectl restart
$ zmstorectl restart
</pre>
</pre>
and check the binary log directory to make sure the logs are being written.  Now set up a cron job that runs <tt>mysqldump</tt> on a scheduled basis:
<pre>
/opt/zimbra/mysql/bin/mysqldump --all-databases --single-transaction --master-data  --flush-logs > <dump-file>.sql
</pre>
Be sure to put the dump file on a separate disk device from the MySQL database files.

Revision as of 17:55, 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 configure 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

Be sure to put the dump file on a separate disk device from the MySQL database files.

Jump to: navigation, search