Add additional DB's to MySQL Master-Slave replication

I have successfully configured a Master-Slave MySQL replication topology, and am replicating several databases (about a dozen) successfully from the master, to the slave.

However, I will need to add additional DB's to the replication as time goes on. I created a new DB for testing, added another Binlog_Do_DB=DBNAME to the my.cnf file, and restarted MySQL on both servers to no avail.

I hoped that creating the DB on the master server was enough for it to create itself on the slave server

Do I have to go through the whole locking the tables and doing a mysqldump on the master server, then restoring on slave server again? Can I dump/restore just the new DB, or do I have to dump/restore all of the DB's again?

I cannot find a very clear and concise guide on how to accomplish this without locking the tables, which isn't really ideal to do during normal business hours.
LVL 3
Tom-J-LaelAsked:
Who is Participating?
 
Tom-J-LaelAuthor Commented:
I set it up the hard way likely through combination of command line, editing the my.cnf file
0
 
jrm213jrm213Commented:
Not that is much help for you, but I work with two MySQL servers that are set up for replication Master->Slave and when I create a new Database through MySQL Workbench when connected to the master, that database gets created on the slave.

How did you set up your replication?
0
 
Tom-J-LaelAuthor Commented:
Is the mysql workbench free?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jrm213jrm213Commented:
Hi Yes, MySQL Workbench doesn't cost anything but I believe you now need a free Oracle Account to download it: http://dev.mysql.com/downloads/tools/ 

My understanding of MySQL replication is that it is achieved through replaying the actions in the logs from the master database on each slave database. So it should always be a perfect replication of the Master. I was actually a little confused when I saw that you set up replication for only specific databases on the Master, which is why I was wondering how you had set up the replication.
0
 
Tomas Helgi JohannssonCommented:
Hi!

As well as adding the line
binlog_do_db = dbxx

to the masters my.cnf file you will also need to add the
database to the slave my.cnf like this

 replicate_do_db = dbxx

The recommended way is to do a mysqldump master and restore it to the slave and then
add it to my.cnf and restart the server.

Regards,
    Tomas Helgi
0
 
Tom-J-LaelAuthor Commented:
I sort of gave up on this. I did set up the replication successfully using mysql workbench, but chose to replicate all dbs, and subsequently created databases would then successfully replicate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.