Actions: | Security

Navigation: Home | Services | Tools | Articles | Other

Setting up MySQL replication

We want replication because it gives us:

Getting started with replication of MySQL databases is fairly straightforward, despite the reputation for complexity and the number of questions on mailing lists/IRC. It's also copiously documented in the MySQL manual; perhaps the problem is that there is too much information there.

  1. Setup the master. Ensure that it is writing binary logs, has a server-id and has an account for the replication slave
  2. Setup the slave. Ensure that it has a server-id and has the details about the master and the replication account
  3. Lock the tables on the master
  4. Dump the data, recording the binary log file and position
  5. Unlock the master
  6. Load the dump into the slave
  7. Start slaving using 'Change Master'

I will use 'Master$' to indicate a root shell prompt on the Master server and 'Slave$' to indicate a root shell prompt on the slave; the following lines at the same indentation without the prompt will be output.

Configure the master

Edit your my.cnf:

log-bin = /var/lib/mysql/<hostname>-bin.log
server-id = 1

Don't forget to either restart the server or use 'SET GLOBAL' to ensure that these variables are set in your running instance.

Create a user:

Master$ mysql> CREATE USER 'repl'@'' IDENTIFIED BY 'slavepass';
Master$ mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'';

Configure the slave

Edit you my.cnf:

server-id = 2
master-host =
master-user = repl
master-password = slavepass

Do not restart the slave's mysqld or it will attempt to start slaving and you're not ready for that yet.

Lock the Master

You'll need to lock the master to ensure that no client is able to update any table while you are taking the dump of the initial data so that you can be sure of a consistent data set to start with.


This session needs to be maintained until the mysqldump is complete.

Dump the data

The critical option for mysqldump(1) is --master-data which records the coordinates for the binary log (i.e. the log file and the position in that file). You'll need this for your 'CHANGE MASTER' step later on.

You can manually get the coordinates with:


There are a number of other possible options that you might want to look at for mysqldump, but I'm usually covered by:

Master$  mysqldump -p --all-databases --master-data --opt --events --routines --log-error=/ux1/dump-error.log  | gzip > /var/lib/mysql/mysql.sql.gz

Unlock the Master

All you need to do here is close the session where you locked the master.

Load the dump into the Slave

Slave$ gunzip /var/lib/mysql/mysql.sql.gz | mysql -u root -p

Start slaving

In order to actually start slaving you need to tell the slave where the binary log is and from what position to run the log.:

Slave$ mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;


In order to verify that slaving is occuring properly, you will want to check the 'Seconds_Behind_Master' variable from SHOW SLAVE STATUS on the slave.

A good further test to convince yourself might be to create a new table or database on the server and see whether it appears on the slave.

Further considerations

Multi-Master Replication

If you want more than one master in your setup, you have to avoid the problem of conflicting auto_increment values, but it's not terribly difficult. The easiest way to get this going for an existing database is:


server-id                       = 63
auto_increment_offset           = 1
auto_increment_increment        = 10
master-host                     =


server-id                       = 64
auto_increment_offset           = 2
auto_increment_increment        = 10
master-host                     =

A couple of extra hints:

sql-mode                        = NO_ENGINE_SUBSTITUTION
default-storage-engine          = InnoDB
innodb                          = FORCE
innodb-file-per-table           = 1

Reinitializing replication for a large database

If your database is large, then dumping it to SQL and loading it into a slave might not be a viable method because it just takes too long. One of the nifty tools provided by Percona is the innobackupex script. While they might not be satisfied "with the architecture, code quality and maintainability, or functionality of innobackupex", it has worked to meet my needs on several critical occasions.

Here's one incantation that created a mysql datadir ready to be started as a replication slave:

innobackupex --defaults-extra-file=/root/mysql \
             --parallel=8 \
             --no-timestamp \
             --rsync \
             /var/backup &&
innobackupex --use-memory=16G \
             --apply-log /var/backup &&
rsync -av --partial --progress /var/backup/*

Percona on mysql replication slave setup.