Tips for Rebuilding MySQL Replication

MySQL databaseLet’s keep this post smart. It’s intended to help you rebuild a crashed MySQL replication. We assume that bin-log has been enabled at the master side.

Basically what you need to do is to retrival a snapshot of your master database. It covers three types of information, a snapshot of your current mysql database, and master log file name and its running position when this snapshot created.

You can achieve these information on a running DB like this:

Firstly, frozen your database, in order to disable any writes/updates on the databases. It affects your service for sure. Run two sql queries on a terminal:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.53 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| binlog-db.000004 |  88689   |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

Keep this session open.  Note that when this session closes, READ LOCK will vanish, and your database will keep changing again.

Then open another terminal to create a copy of your current database files. There are three common ways to do that depending on your system environment. Either of these methods is okay.

1, Use mysqldump to copy all the data.

The disadvantage for mysqldump is that it uses very long time for dumpping large databases. It’s really unacceptable on productive servers.

shell$ mysqldump --database db1 db2 > fullbackup-timestamp.sql

2, Use a LVM snapshot

It only takes some seconds to create a LVM snapshot. When your snapshot created, you can copy the snapshot to the slave server.
As all the database files are exactly the same as master DB, you need to make sure the configuration files are similar as well.

3, Use XFS’ frozen feature to do that

SYSTEM xfs_freeze -f /var/lib/mysql;
SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh;
SYSTEM xfs_freeze -u /var/lib/mysql;

After the snapshot createed, you either leave the first session, or submit this query directly:

mysql> UNLOCK TABLES;

Finally you need to restore these databases from mysql dump file or the snapshot at slave side. When recovering a large database, time make lots of sense. it might take you one or more days to finish a recovery.
To speed up a mysql recovery, there’re always some valuable advises from wise man (The wise man here is … my friend). for example you can disable binlog when importing large sql files.

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SOURCE /PATH/TO/MYSQL-BACKUP.SQL

When the files are importing, you need to make sure there are enough disk space avaiable, otherwise, it would crashed again. After importing all data files at slave side,  you need to tell the slave at which point it should read binlog from master:

mysql> SLAVE STOP;

mysql> SLAVE RESET;

mysql> CHANGE MASTER TO MASTER_HOST='pub.admon.org',MASTER_LOG_FILE='binlog-db.000004',MASTER_LOG_POS=88689;

myql> SLAVE START;

If all your recovery work is a remote operation, using Screen is highly suggested.

Share Button

3 thoughts on “Tips for Rebuilding MySQL Replication

  1. Nop – either of the three methods is ok. (I’ve updated this post, so that it’s more specific.)

Leave a comment

Your email address will not be published. Required fields are marked *