Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add additional DB's to MySQL Master-Slave replication

Posted on 2014-04-29
6
Medium Priority
?
777 Views
Last Modified: 2014-05-19
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
Comment
Question by:Tom-J-Lael
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 40032100
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
 
LVL 3

Accepted Solution

by:
Tom-J-Lael earned 0 total points
ID: 40032244
I set it up the hard way likely through combination of command line, editing the my.cnf file
0
 
LVL 3

Author Comment

by:Tom-J-Lael
ID: 40032256
Is the mysql workbench free?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 40032342
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
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40034755
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
 
LVL 3

Author Closing Comment

by:Tom-J-Lael
ID: 40074492
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question