Solved

Add additional DB's to MySQL Master-Slave replication

Posted on 2014-04-29
6
673 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 24

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now