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