MySQL Replication – Master Master Setup with Single Master Write

MySQLperconareplication

I am looking to improve our fault tolerance for our persistent store. After reading several online docs about how to set-up master <> master replication, I'd still like to ask the experts about the order of set-up, to ensure I don't miss anything important.

Our setup will be a master <> master, however, only one master will be written to at a time. This will be controlled via "floating ip" which can be set to point at the secondary master, if the primary master fails.

  1. With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?

  2. Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?

  3. Do I need to do an export from master1 and import to master2, before I start replication, or can I start master 2 to pick up all transactions over the last several months?

  4. Just to be clear, I need to setup a "replicator" user (with replication perms) on both master1 and master2?

— Update 8.15.2014 —

This is the guide I followed for my intial setup

To answer question #2, I did the following:

Notice that I have a performance_schema table, this is because I am using Percona MySQL. Per Rolando's edit, the following is not needed if you want to replicate ALL databases, however I am leaving here for posterity to show others how they can specify multiple databases, if they do not want to export all.

binlog_do_db = information_schema
binlog_do_db = mysql
binlog_do_db = performance_schema
binlog_do_db = example_blog
binlog_do_db = example_core
binlog_do_db = example_log

To answer question #3, I ended up creating a snapshot of Master1 by issuing the following command:

mysqldump -h localhost -p -u root --opt --all-databases --single-transaction --master-data  > /tmp/example_snapshot_`date +%Y_%m_%d__%H_%M_%S`.sql

Notice the --master-data param, according to mysql docs, this allows us to create snapshot without have to create a new session and deal with table locking. However it does add an extra line the resulting .sql file:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=107;

I removed this line b/c I wanted to affect this change manually, on each master.

Happy to report that replication appears to be working. Thanks Rolando!

Best Answer

With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?

Yes, you are right. I have setup Master-Master setups for years with one Write Master. I never touched the auto_increment options. Never had an incident.

Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?

Yes, it is by default. Although binlog-do-db exists, I would not recommend it since you are doing Master-Master. If you have additional slaves, it is best to setup filtering on the slave. That way, you have all binlog events to point-in-time recovery and other incidentals. Such incidentals might include Star Topologies (which feature Distribution Masters, a Master that does not house any data, only binlogs Such binlogs could be filter with binlog_do_db to reduce binlog traffic to slaves).

Do I need to do an export from master1 and import to master2, before I start replication ?

You do need to to export from master1 and import to master2.

can I start master 2 to pick up all transactions over the last several months?

The only way you can do that is if you had every binlog since you loaded master1 aznd never erased any of the binlogs. Most people rotate their binlogs, making this impossible.

I need to setup a "replicator" user (with replication perms) on both master1 and master2?

Yes

EPILOGUE

I have setup ucarp over the years as the failover mechanism for the floating IP. Most use Linux Heartbeat, Pacemaker, and others. So, you are in the ballpark of what needs to be done.

Related Question