Add additional database to MySql Slave replication

By | Server Admin | No Comments

1) MASTER – restart the master with new binlog-do-db | remove all binlog-do-db in order to start populating the binary log with database 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

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

9) check the newly imported database looks correct

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