17

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
Improve this question
2
  • 1
    Create the DB on the machine you are directing writes to. Commented May 12, 2011 at 7:49
  • what is your full config? I try theses answer below, it didn't work :-( Commented Mar 31, 2015 at 8:18

4 Answers 4

Reset to default
20
+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
Improve this answer
8
  • Are you able to add databases to either master and have them automatically replicated to the other? Commented May 17, 2011 at 15:58
  • yes they did.. i just setup which ones to ignore Commented May 17, 2011 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? Commented May 18, 2011 at 15:29
  • 3
    yes yes yes yes yes :) Commented May 18, 2011 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. Commented May 20, 2011 at 15:11
4

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.

Improve this answer
2
  • 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... Commented May 18, 2011 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. Commented May 20, 2011 at 15:12
0

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/

Improve this answer
3
  • 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/… Commented May 19, 2011 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. Commented May 20, 2011 at 1:54
  • @all: Oops.. Sorry. I could swear i read it complete, but no must have skimmed instead... Commented May 20, 2011 at 9:38
-1

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.

Improve this answer
1
  • That's exactly what I'm asking here. I'm unable to find a definitive answer on this so far. Commented May 12, 2011 at 15:12

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.