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
Andrew Ensley
  • 912
  • 2
  • 16
  • 30

4 Answers4

19

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
Mike
  • 21,910
  • 7
  • 55
  • 79
  • Are you able to add databases to either master and have them automatically replicated to the other? – Andrew Ensley 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 Ensley May 18 '11 at 15:29
  • 3
    yes yes yes yes yes :) – Mike May 18 '11 at 16:44
  • Ok, I think I'm starting to catch on to how this will work. I have another question though about log-slave-updates. Reading the docs here: ( http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates ), it seems like that would cause infinite looping of an update between the two servers. It appears to only need to be used when replicating from server A to server B and server B to server C. Is there another purpose for log-slave-updates or am I missing something? – Andrew Ensley May 18 '11 at 21:16
  • 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 '11 at 15:11
  • @Mike Good Bounty Hunting and a +1 from me !!! – RolandoMySQLDBA May 20 '11 at 15:24
  • Is it absolutely necessary to have these options? : binlog-ignore-db=information_schema binlog-ignore-db=mysql because I want to have two absolutely identical masters, I want the usernames and rights to also be replicated, and as far as I know these are stored in the "mysql" db. As I have tens of mysql users, in case I would have to do a fail-over to the secondary master, it would be completely useless if it just has the databases but no users. Thanks, –  Dec 03 '11 at 04:33
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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 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 '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 Ensley May 20 '11 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/

Software Mechanic
  • 221
  • 1
  • 9
  • This is handled in the `auto-increment-increment`, and `auto-increment-offset` settings in my configuration files. See this page: http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html#sysvar_auto_increment_increment – Andrew Ensley 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 01:54
  • @all: Oops.. Sorry. I could swear i read it complete, but no must have skimmed instead... – Software Mechanic May 20 '11 at 09: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.

chocripple
  • 2,039
  • 14
  • 9