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.
    – Martin
    May 12, 2011 at 7:49
  • what is your full config? I try theses answer below, it didn't work :-(
    – zx1986
    Mar 31, 2015 at 8:18

4 Answers 4

Reset to default
19
+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?
    – Andrew Ensley
    May 17, 2011 at 15:58
  • yes they did.. i just setup which ones to ignore
    – Mike
    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?
    – Andrew Ensley
    May 18, 2011 at 15:29
  • 3
    yes yes yes yes yes :)
    – Mike
    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.
    – Andrew Ensley
    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...
    – Andrew Ensley
    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.
    – Andrew Ensley
    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/…
    – Andrew Ensley
    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.
    – RolandoMySQLDBA
    May 20, 2011 at 1:54
  • @all: Oops.. Sorry. I could swear i read it complete, but no must have skimmed instead...
    – Software Mechanic
    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.
    – Andrew Ensley
    May 12, 2011 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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