Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Background: I have two MySQL 5.1 servers setup in Master-Master row-based-replication (RBR) following this excellent guide. I want all databases replicated, and will be adding new databases regularly.

Goal: I would like to be able to add new databases to replication by just adding the DB to one of the servers; without having to stop both slaves, change the configuration files, restart the MySQL servers, and start the slaves again.

Question: From what I've read, I think I can do this by simply omitting any binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db settings in each server's configuration, but I can't be sure. MySQL's docs on how Database- and Table-level replication options are evaluated make me think there might be no way to accomplish this at all.

Relevant portions of my /etc/mysql/my.cnf files are copied below. Am I on the right track? Is what I want even possible?

Master 1:

[mysqld]
binlog-format = row
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = <master2_ip>
master-user = slave_user
master-password = <password>
master-connect-retry = 60

log-bin = /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 14
max_binlog_size = 2048M

Master 2:

[mysqld]
binlog-format = row
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = <master1_ip>
master-user = slave_user
master-password = <password>
master-connect-retry = 60

log-bin = /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 14
max_binlog_size = 2048M
share|improve this question
1  
Create the DB on the machine you are directing writes to. –  Martin May 12 '11 at 7:49

4 Answers 4

up vote 9 down vote accepted
+100

This is how i did it for both masters

log-bin = mysqld-bin
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
log-slave-updates
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
relay-log=mysqld-relay-bin
share|improve this answer
    
Are you able to add databases to either master and have them automatically replicated to the other? –  Andrew May 17 '11 at 15:58
    
yes they did.. i just setup which ones to ignore –  Mike May 17 '11 at 16:07
1  
Two questions: I just need to make sure we're both saying the same thing. #1 Are you able to add a database to one of the two masters and it's created on the other automatically, and then all data inserted into the new database is replicated without changing configuration or restarting the servers? #2 Are you using statement- or row-based replication? I need all changes on all databases replicated; even those run without a "use" statement beforehand. Will these settings work well with row-based replication? –  Andrew May 18 '11 at 15:29
3  
yes yes yes yes yes :) –  Mike May 18 '11 at 16:44
1  
Ok, I just set up my servers using these settings last night. I skipped the log-slave-updates setting because I saw no need for it. Everything works perfectly. I'm able to add and drop databases and the changes are replicated. Thank you! Bounty awarded. –  Andrew May 20 '11 at 15:11

With regard to making databases, there are still bug reports on using CREATE DATABASE with row-based replication.

This report is closed, but the bug appeared again in MySQL 5.1.47
This report is based on MySQL Cluster (NDB storage engine)
This report is based on replicate-wild-ignore-table still replicating.

Row-based replication causes binary logs to grow at an extraordinary rate which can spike network traffic just shipping binary log data over to the relay logs of the slave.

@Mike says he got database creation to work and replicate just fine. I do not doubt that at all. What I am is a little leary of MySQL (eh Oracle) not having gotten all the kinks out of row-based replication once the database is instantiated.

You may want to switch to row_format MIXED if you still want row-based entries in the binary logs. Internally, the binary log format tends to float between statement and fixed anyway: ( See http://bugs.mysql.com/bug.php?id=40146 ). More bugs reports are closed using mixed and shying away from row-based ( http://bugs.mysql.com/bug.php?id=39701 ) but still problems persist intermittently.

FINAL NOTE

For your own sanity, please revert back to statement-based replication and thus make CREATE DATABASE (which is an SQL Statement) stable and consistent for subsequent SQL against the database created. In fact, the most recent bug report shows it is best to use statement-based replication, regardless what the documentation says about database-level and table-level options.

share|improve this answer
    
Thanks for all that info. My only problem with statement-based-replication is that changes specifying the database (i.e. "INSERT INTO db1.table1 (col1) VALUES ('hi');") won't work unless the database is the current database in use. I suppose it may be time to switch to statement-based-replication and clean up some code... –  Andrew May 18 '11 at 20:39
    
Turns out it wasn't that hard to fix the necessary code. I'm using statement-based-replication now. Thanks for the advice. –  Andrew May 20 '11 at 15:12

Hmm...You will also have to figure out a way to handle/differentiate auto-incremented keys and fields. This looks interesting..http://mysql-mmm.org/

share|improve this answer
    
This is handled in the auto-increment-increment, and auto-increment-offset settings in my configuration files. See this page: dev.mysql.com/doc/refman/5.1/en/… –  Andrew May 19 '11 at 15:24
    
I think you misunderstand. @Andrew is talking about adding new databases, not adding new database servers. If @Andrew was introducing new database servers, then there might by key collisions when redistributing the data amongst 3 or more DB servers, which your answer would address. +1 for yourt effort, but please read all questions carefully. I have been burned the same way as well. –  RolandoMySQLDBA May 20 '11 at 1:54
    
@all: Oops.. Sorry. I could swear i read it complete, but no must have skimmed instead... –  Anand Jeyahar May 20 '11 at 9:38

cmiiw i thought you should be able to create database on master1 and master2 without change the conf or stopped the slave, but make sure the master - master replication running. because you don't define which database need to replicate, it's mean all database will be replicate if you don't define it on my.cnf.

share|improve this answer
    
That's exactly what I'm asking here. I'm unable to find a definitive answer on this so far. –  Andrew May 12 '11 at 15:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.