Add database to existing replication which uses binlog_do_db?

Question

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 

Answer 1

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.

Answer 2

As a comment to the answer given by IGGt, if the database is new, and not being used - i.e. has no connections to it you could dump the new database first and then drop or rename it on the master.

Assuming the slaves have not been instructed to only replicate your current database(s) the only steps you then need to do are :

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 ) Create new database (database4)

3 ) Import database from dump back into master

In this way the replication process will create the new database and populate on all your slaves for you.

This also has the benefit of reducing downtime to your other database(s) to only the time it takes to restart the master db instance.

As recommended removing all the binlog-do-db statements will mean in future you can add new databases and these will automatically be replicated on the slaves. If you do not want to replicate a database to one or more slaves you can set the replicate-ignore-db startup variable on those slaves, either before database creation or at any later point and then drop the database from those slaves.

If your version is MySQL 5.7 or later you can manage which databases are included or ignored on the slaves without restarting the slave : https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html