Solved

Add additional DB's to MySQL Master-Slave replication

Posted on 2014-04-29
6
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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 25

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql database, schema and table creation 13 114
Creating Functions in phpMyAdmin 8 60
show child records separated by commas 12 50
Ubuntu don’t allow SU command in terminal 7 124
Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

739 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