MySQL Master-Master Replication of ALL databases. How?
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
Solution 1:
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
Solution 2:
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.