Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

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.
0
Tom-J-Lael
Asked:
Tom-J-Lael
  • 3
  • 2
1 Solution
 
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:
I set it up the hard way likely through combination of command line, editing the my.cnf file
0
 
Tom-J-LaelAuthor Commented:
Is the mysql workbench free?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now