Mysql – Add database to existing replication which uses binlog_do_db

MySQLmysql-5.5replication

We have two MySQL 5.5 servers running on Ubuntu 12.04.

We are successfully replicating 3 databases from server01 to server02, but need to add a 4th (new) database.

Our my.cnf on the master has:

binlog_do_db = database1
binlog_do_db = database2
binlog_do_db = database3

How do I add the 4th database to replication? Everything I've read has indicated we'll need to restart MySQL, is there any way around this? Or do we just need to schedule it in?

This is the plan we have so far, but I was hoping for a method which didn't require a restart. Am I overcomplicating this?

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

Edit my.cnf to include the new database:

binlog_do_db = database4

Restart MySQL

  service mysql restart

Backup the database for the initial copy to the slave server

USE database4;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

In a new shell

mysqldump -u root -ppassword --opt database4 > database4.sql
scp database4.sql root@server02:/home

In the old shell

UNLOCK TABLES; 
QUIT;

Change my.cnf on the slave server

replicate-do-db = database4

Restart MySQL

service mysql restart

Restore the dumped DB

mysql -u root -ppassword database4 < database4.sql

Best Answer

Making changes to binlog-do-db will require a restart I'm afraid.

Personally I would remove all the binlog-db-db from the Masters my.cnf, and just use replicate-do-db on the slave to filter what you want to be processed.

In this way you can addd more databases to the master in future without needing to restart.

If I understand correctly, you are trying to copy the new Database (database4) onto a slave that already has 3 databases replicating (these are all on the same MySQL Database).

The problem then is that replication will need to be paused whilst you do this.

The process I would use would be:

1) MASTER - restart the master with new binlog-do-db | remove all binlog-do-db in order to start populating the binary log with database4's data

2) SLAVE - Check the slave Database's replication is up to date (SHOW SLAVE STATUS > Seconds_Behind_master). If not wait till it is

3) MASTER - FLUSH TABLES WITH READ LOCK; To stop new data entering the master

4) SLAVE - Check replication is fully caught up and no data is replicating (read_master_log_pos and exec_master_log_pos should be the same and not changing (in show slave status;))

5) SLAVE - STOP SLAVE; to stop any data replicating into the database once you unlock the master shortly.

6) Run MySQLDUMP with --single-transaction option

7) Once MySQLDUMP has started running unlock the master Database UNLOCK TABLES; In this way your systems can continue reading and writing to the master, hopefully keeping downtime to a minimum

n.b. that MySQLDUMP may lock the database4 schema while it works depending on your system

8) Once MySQLDUMP has completed, import it into the slave Database

9) check the newly imported database4 looks correct

10) Restart the slave Database, adding replicate-do-db=database4 to the my.cnf file as you go

Once it comes back on, it should continue replicating from where it left off, but including database4.

Related Question