There are a number of tutorials out there for setting up Replication in MySQL. However, I couldn’t find one that fully addressed setting up selective master-slave replication in MySQL.

By selective, I am referring to only having one or a few databases that are replicated from the master database to the slave database. Any other databases on the master server are not copied/replicated.

Master-slave replication for a MySQL database refers to having a secondary MySQL server where any changes made to the main database are replicated (copied) to the secondary MySQL database. It becomes a copy of the main database. This secondary database can be used as a “hot” backup database, or used to run queries against that you don’t want to run on the live database, or used to allow for backups to be made without affecting performance of the live database.

Tutorials out there now

There is a great tutorial for setting up Master Slave Replication for all databases. It is well documented.

Another tutorial that is quite good and is almost complete (with a few typos) is One database set up for master-slave replication.

Then there is the ancient HowToForge MySQL Replication tutorial. It is thorough, but is very out of date.

Each of these tutorials is missing one or more items, or is not clear on some steps, and you can run into issues when setting it up.

Gotchas

There are 4 gotchas when setting up MySQL Master-Slave replication:

• 1. Using the setting “replicate-do-db” on the slave instance can cause issues with not all queries being replicated. Instead, I recommend using “replicate-wild-do-table” so that all queries (regardless of construct) will be replicated in all scenarios.

• 2. Only using one “binlog-do-db” line for multiple databases will cause replication to fail. Instead, if replicating multiple databases, have multiple “binlog-do-db” lines, one for each database.

• 3. Don’t put master settings in the my.cnf configuration file. These settings won’t work in MySQL 5.5 or higher.

• 4. Using a second window / session correctly for the initial database dump file creation to prevent locking from expiring. Get this wrong and your slave instance will be corrupt.

Steps to set up MySQL master slave replication

ON THE MASTER DATABASE SERVER:

1. The first step is to set up the master database for replication. This can be done while the database server is running. You would edit the my.cnf file on the master database:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=DATABASE_NAME1
binlog-do-db=DATABASE_NAME2
server-id=1

where “DATABASE_NAME1″ and “DATABASE_NAME2″ are the names of the databases you plan to replicate.

2. Next, restart MySQL on the master server.

3. Then, on the master database, log into MySQL on the command line (mysql -p) and issue the following SQL queries:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

4. When you are ready to create the database dump of the master database(s), you will run the following commands. You *must* stay logged into this session, and do *not* quit or even issue another command:

USE DATABASE_NAME1;
FLUSH TABLES WITH READ LOCK;

If you have more than one database to replicate, you will need a separate window/session for each database that you stayed logged into. You would repeat these commands in each window for each database, staying logged in after issuing the commands.

5. In a second ssh window, you will log into MySQL again and run the following query and record the values:

SHOW MASTER STATUS;

It should look something like this (You want to record the File value and Position value):

mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------------+------------------+
| File             | Position  | Binlog_Do_DB       | Binlog_Ignore_DB |
+------------------+-----------+--------------------+------------------+
| mysql-bin.000013 | 250789445 | DB_NAME1,DB_NAME2  |                  |
+------------------+-----------+--------------------+------------------+
1 row in set (0.00 sec)

6. The next step is to dump the database(s) from the master server:

mysqldump -p --opt DATABASE_NAME1 >DATABASE_NAME1.sql
mysqldump -p --opt DATABASE_NAME2 >DATABASE_NAME2.sql

You will then want to transfer these files to the slave server, as you’ll use them to seed the slave databases later on.

7. Once you have the dump files, and you recorded the values of the master status, you can unlock the database by going back to the first window (and other window(s) for each database) that is still logged into MySQL and running:

UNLOCK TABLES;
quit;

ON THE SLAVE DATABASE SERVER:

1. Log into MySQL (mysql -p) and set up your databases:

CREATE DATABASE DATABASE_NAME1;
CREATE DATABASE DATABASE_NAME2;
quit;

2. In the my.cnf file on the slave server, add the following lines:

server-id=2
relay-log=/var/log/mysql/mysql-relay-bin.log
replicate-wild-do-table=DATABASE_NAME1.%
replicate-wild-do-table=DATABASE_NAME2.%

This will match any type of query run against the databases, and ensure they are fully replicated to the slave server.

3. Restart MySQL on the slave server.

4. Log back into MySQL (mysql -p) on the slave server and run the following queries. Make sure you stay logged into this session. You’ll need the values you recorded from the master database, as well as IP addresses and usernames/passwords:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='1.2.3.4', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.XX', MASTER_LOG_POS=XX;

Where “1.2.3.4” is the IP of the master server, and “slave_user” and “password” are the username and password of the MySQL user for replication. The “XX” values in the query are those you recorded in step 5 on the master server.

5. In a second window, import the databases:

mysql -p DATABASE_NAME1

6. Back in the first window (that is still logged into MySQL), you may now run the following MySQL queries:

START SLAVE;
quit;

That’s it. The slave server is now running, and should be replicating with the master server.

How to verify the slave server is in sync with the master

This is not an easy thing to do manually. There is no simple command that tells you everything is in sync.

Fortunately, the good folks at Percona have a toolkit that makes this easy to verify. You install it on the master server (simple perl Makefile.PL, make, and make install).

Once installed, you use a separate database (that should also be replicated) to track the sync status. I called ours “percona” and set it up on the master with:

create database percona;
CREATE TABLE checksums (
db             char(64)     NOT NULL,
tbl            char(64)     NOT NULL,
chunk          int          NOT NULL,
chunk_time     float            NULL,
chunk_index    varchar(200)     NULL,
lower_boundary text             NULL,
upper_boundary text             NULL,
this_crc       char(40)     NOT NULL,
this_cnt       int          NOT NULL,
master_crc     char(40)         NULL,
master_cnt     int              NULL,
ts             timestamp    NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

I set this table up to be replicated along with our other databases.

Then, you simply run a command on the master server (via ssh, cron, etc…) to verify all is in sync:

pt-table-checksum --user=XXX --password=YYY --databases DATABASE_NAME1 --nocheck-replication-filters

This will give you an output and indicate if there are any differences between the databases. The column you are most concerned with is “DIFF”, which should be all zeroes if everything is in sync. You may see a number greater than zero in the “ERRORS” column. Many times this can be ignored depending on the details of the error.

—-

Hopefully this helps you set up selective master-slave replication in MySQL without running into corruption or missing queries / data on the slave instance. I’ve found this process to work quite well for the replication set ups we have put in place. I’m sure there are many other ways to skin the cat, but the “gotchas” I listed above are items to consider no matter which plan you implement.