MySQL Backup and Restore

MySQL Backup and Restore

   KB 3054        Last updated on 2015-07-12  




0.00
(0 votes)

Overview

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.

Setup

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

log-bin = <path>/<basename>

If using ZCS 7.0 or later, there is an additional recommend configuration option, binlog-format, that was introduced in MySQL 5.1.1:

binlog-format = MIXED

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.

Backup

To set up a scheduled backup of the database, set up a cron job that runs mysqldump:

# su - zimbra
$ source ~/bin/zmshutil
$ zmsetvars

If using binary logging, the dump is run like the following:

$ /opt/zimbra/mysql/bin/mysqldump --user=root --password=$mysql_root_password --socket=$mysql_socket \
  --all-databases --single-transaction --master-data --flush-logs > {dump-file}.sql

If not using binary logging, the dump is run like the following:

$ /opt/zimbra/mysql/bin/mysqldump --user=root --password=$mysql_root_password --socket=$mysql_socket \
  --all-databases --single-transaction --flush-logs > {dump-file}.sql

where {dump-file} is the path to the MySQL dump file. Just like the binary log files, be sure to put the dump file on a separate disk device from the MySQL database files.

As of HELIX (version 7.x) of ZCS, there is an option to automatically run full mysqldumps as part of the nightly zmbackup:

  • Nightly backups should include a mysqldump [1] - FIXED 7.0 Beta 1
        mysql dump files are gzipped and placed in the backup target 
        directory, or /opt/zimbra/backup if none specified.

        This is controlled via the localconfig key mysql_backup_retention - 
        if 0 (or less) or unset, no backups will be performed.  
        If N > 0, N sql copies will be retained.

For example, setting mysql_backup_retention to 3 will retain three rotating versions of mysqldump, allowing for a restore of all mysql date for the last three days:

zmlocalconfig -e mysql_backup_retention=3

Recovery

If the database ever needs to be recovered, use the mysql command-line tool to load the dump file and binary log records. It is also a good idea to perform a recovery on a test server, to make sure that things are working properly.

Before attempting database recovery, make sure that the mail server is not running while you're performing the restore. Then determine the first binlog file that needs to be replayed by grepping the dump file:

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

In this example, the first binlog after the database dump is binlog.000006. Yours will probably be different. Next, determine the last binlog file that needs to be replayed. We do this by finding the last binlog file that was written:

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

and come up with 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:

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

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

Purge Binary Logs

In order to prevent the server from piling up with old binary logs, it is a good idea to have these expired automatically. For example, if you are doing a mysqldump every 7 days, it is probably safe to purge the old bin logs every 15 days (just over two intervals of the mysqldump). Add the following to /opt/zimbra/conf/my.cnf:

expire_logs_days = 15

Reference: expire_logs_days

Summary of Recommended Configuration Steps for MySQL Backups

  • Confirm enough disk space to handle mysqldump and binlog data
  • Enable automated backup retention
$ zmlocalconfig -e mysql_backup_retention=3
  • Modify MySQL configuration for
$ vi /opt/zimbra/conf/my.cnf

# add bin-logging
log-bin = /opt/zimbra/backup/mysql/binlog  # make sure directory enough space exists
binlog-format = MIXED
max_binlog_size = 134217728
expire_logs_days = 3
  • Restart mailboxd
$ zmmailboxdctl restart

New Features in ZCS 6.0

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.


Verified Against: unknown Date Created: 10/26/2009
Article ID: https://wiki.zimbra.com/index.php?title=MySQL_Backup_and_Restore Date Modified: 2015-07-12



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »

Jump to: navigation, search