Re-Initialize a MySQL slave (replication)

If your MySQL slave is broken/out of sync or you just want to import the master data here are the steps you should take to jumpstart your MySQL replication.

On the slave

mysql> SLAVE REST;
mysql> exit

On the master

root# mysqldump --routines --master-data --single-transaction  --skip-add-locks --skip-lock-tables --default-character-set=utf8 database > db-backup-`date -I`.sql
Now push this dump to the slave server.

This one is just if you don't have the user or want to change the password.
mysql> grant replication slave on *.* to 'replication'@ identified by 'slave';

On the slave

mysql> drop schema database;
mysql> create schema database;
root# mysql database < dbdump-date.sql
We did the reset so we have to update user information too
and look for:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes



Let’s review these parameters and see their effect:

  • -u or --user: This is the user which initiates the dump. Depending on other parameters, the user may need to have quite a few privileges, such as SELECT, RELOAD, FILE, REPLICATION CLIENT etc. Since I do not usually allow for remote root access into mysql, I create a temporary user solely for the purpose of the dump (many times it’s a one-time action), for the specific machine from which the dump is run, and provide this user with all necessary permissions.
  • --routines: It is really an annoyance to have to remember this flag. In contrast to –triggers, which is by default TRUE, the --routines parameter is by default FALSE, which means if you forget it – you don’t get the stored functions and procedures in your schema.
  • --master-data: I always enable binary logs on the MySQL nodes I work on. While binary logs may lead to more IO operations (writing binary logs make for more disk writes, obviously, but also disable some InnoDB optimizations), may consume more disk space (once I’ve worked with a company which had such a burst of traffic, that the binary logs to completely filled their disk in less than one day). If binary logs are enabled, the --master-data parameter allows for easy replication setup: the dump includes the CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=... statement, so no need to do stuff like SHOW MASTER STATUS on the dumped node. Optionally, you can set --master-data=2 to have the statement commented.
  • --single-transaction --skip-add-locks --skip-lock-tables: When working with transactional-only storage engines (InnoDB is the most popular choice, but new engines are coming: Falcon, PBXT, Transactional-Maria, SolidDB and more), these parameters allow for a non-interruptive backup, which does not place read locks on all tables. It is possible to keep on reading and writing to the database while mysqldump is running with single transaction. Running in this mode does have its penalty: more IO operations (due to MVCC’s duplication of data while many transactions access the same data for Read/Write). The server is likely to perform more slowly during the dump time.
  • --default-character-set=utf8: I’ve seen so many MySQL installations in which world-wide textual data was stored in the Latin1 charset than I can remember. Many developers, who are testing using standard English data, are not even aware of the issues arrising from changing the data later on to utf8. But even those who are, are usually unaware of the necessity to configure the character set on a per connection basis, or for their specific clients (JDBC or PHP connectors, etc). mysqldump is no different, and if you have non-latin text in your tables, always remember to set this option.

2 responses to this post.

  1. Posted by justin on October 11, 2012 at 8:55 AM

    Helped me out a lot. Thanks for explaining the parameters as well. Too many people leave out the reasons for setting such parameters.


  2. Thank you, this was very useful. The –master-data option was new to me, and this helped me quickly get things up and running after both master and slave servers were patched but MySQL didn’t start up on the slave until enough time had passed that the master’s log had cycled.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: