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.
With a single write master <> master setup, I don't need to worry about setting the
Does MySQL replicate all databases by default, or do I need to explicitly identify them via
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?
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
--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
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!