2

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!

1

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.

  • It is safer to set up auto_increment_offset and auto_increment_increment to not have identical ids on both servers. Its all ok, while works good. But in case of problems it may help a lot, especially if failover to other master is automatic. – Kazimieras Aliulis Aug 14 '14 at 10:05
  • Normal replication on mysql is nowhere automatic. You will have to create users for replication. You will have to copy data, that would be a consistent snapshot at specific time. You will have set up position in binlog, from which to replicate. And later you will have to monitor replication, that it would not stop on some error or would not lag too much, because of many writes. – Kazimieras Aliulis Aug 14 '14 at 10:11
  • Hey Mike. I owe you an apology for a typo. My answer to point #2 should have said: I would recommend not it since you are doing Master-Master. Yous should not have to do that. If you set up Distribution Masters, then yes you should for DBs you want passed on to other slaves. See my post dba.stackexchange.com/questions/3106/… about Replication using Distribution Masters. I am going to fix my statement. – RolandoMySQLDBA Aug 15 '14 at 22:04
  • I fixed my statement !!! Sorry again. – RolandoMySQLDBA Aug 15 '14 at 22:07
  • Doh, already puppetized those changes :( . Ok I'll back out. Rather just replicate all databases in case new ones are added in the future, without having to add to a puppet config, and re-run puppet on masters. Thanks again. – Mike Purcell Aug 15 '14 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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