AllGoodBits.org

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'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
Master$ mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

Configure the slave

Edit you my.cnf:

server-id = 2
master-host = master.mydomain.com
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.

Master$ mysql> FLUSH TABLES WITH READ LOCK;

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:

Master$ SHOW MASTER STATUS

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;

Verify

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

• Binary Log expiry.

  Eventually, and probably sooner than you expect if your database server sees any significant level of activity, your binary logs will begin to take up a huge amount of space. However, your slave is presumably keeping up with the replication fairly well so you probably don't need to keep your binary logs for long. Mysqld will expire them for you with:

  expire_logs_days=n
  

• Statement-based replication vs Row-based vs Mixed

  Statement based replication was the default initially and it caused problems with autoincrement fields and UUID() statements (in particular). Vaguely recent versions of mysql offer Row Based Replication that is guaranteed safe, and is how other RDBMS products work. Mixed format replication provides the efficiency/compact expressiveness of SBR and the integrity of RBR. binlog_format=MIXED is probably the best approach; for further reading: http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html

• If you're using innodb_file_per_table=1, you can slave several databases to different destinations.

• If you want to slave databases from different masters, you'll need to run more than one instance of mysql on the slave, because CHANGE MASTER will only take ONE master.

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:

• Ensure that the database contains replication slave privileges, e.g.: GRANT REPLICATION SLAVE on *.* to ....
• Shut down mysqld
• Copy the filesystem directory that is mysql's datadir, e.g.: rsync -aPvz serverA:/var/lib/mysql/* serverB:/var/lib/mysql/
• Remove the <datadir>/master.info file so that replication config is drawn from my.cnf
• Modify my.cnf so that each server starts its auto_increment counter from a unique value (unique among the set of your mysql masters), e.g.: auto_increment_offset = X where X is unique
• Modify my.cnf so that each server increments by the same amount: e.g.: auto_increment_increment = N where N is at least the number of masters in your set
• Modify my.cnf so that each server has a unique id, e.g.: server-id = NN
• Modify my.cnf so that each server has the replication credentials and knows its master

ServerA:

server-id                       = 63
auto_increment_offset           = 1
auto_increment_increment        = 10
master-host                     = ServerB.example.com

ServerB:

server-id                       = 64
auto_increment_offset           = 2
auto_increment_increment        = 10
master-host                     = ServerA.example.com

A couple of extra hints:

• Use the same version of mysql and the same version of the underlying OS/distribution everywhere.
• Use 1 file per table
• Use the InnoDB storage engine everywhere

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/* newslave.example.com:/var/lib/mysql/

Percona on mysql replication slave setup.