ScriptBits, Little Bits Of Code You Cannot Live Without

Recently I was looking to come up with possible solutions for a true multi-master mysql replication environment.   Our production databases had been separated for performance issues, but there was still a need to query across these db's for inhouse reporting issues.  The "solution" in place was our MySQL 4.1 db's replicating to a slave, then a bunch of scripts running nightly/weekly/monthly that went out to another slave and get databases from that replication chain.  (For those not following, a mysql slave can only have 1 master.)  We looked at MySQL proxy but quickly discarded it.  We also looked at the federated engine in a traditional sense, which would look like:

Master1--->>-----Slave1-------src---<<-------Federated1------>>----src----Slave2----<<-----Master2

In this example each master replicates to their slave, then the federated box is set up with just the schema, the federated engine, and a connection to each slave.  So, all databases exist on the ferated box schematically, but are really just pointers to their source data on the slave.  Querries seemed to be a little slow and network high so we weren't too pleased with this option.  After some more exmperiments and googling we stumbled across a solution where the federated box is the slave.  Like so:

Master1--->>--FedSlave1----src-->>----Merged1----<<---src-----FedSlave2-----<<----Master2

Here we have standard replication happening, but when the FedSlave box runs it's sql, its actually run against the db on the Merged box.   So, databases from seperate replication chains are both local to the merged box and al querries run against it only.   The set up for this is relatively easy.

• Get a snapshot of your master, including its master_status.  If nothing is writing to it, don't worry about the master status
• Restore the snapshot on the Merged box.
• Dump the database schema of the db on the Merged box

#mysqldump -u dbuser -p --no-data --opt db1 > db1_schema.sql

• Before you restore this schema dump on the FedSlave, you have to tell the FedSlave about the Merged box using the CREATE SERVER command.

mysql> CREATE SERVER db1 FOREIGN DATA WRAPPER mysql  OPTIONS\
(USER 'Remote', PASSWORD 'mypwd',  HOST 'Merged1_IP', DATABASE 'db1');

  **where db1 is the name of the database.  If you are doing more than one on each FedSlave, you will need a CREATE SERVER statement for each db.

• Now that the FedSlave knows about the Merged box, edit the ENGINE in the dump we did above, and add a connection string.
• ENGINE=MyISAM needs to become ENGINE=FEDERATED CONNECTION='db1'.  Use sed:

[root@fedslave1 /]#sed -e "s/ENGINE=MyISAM/ENGINE=FEDERATED\
CONNECTION='db1'/g" db1_schema.sql > db1_restore.sql  

**I run that in a script and 'db1' is actually a variable I set.  You'll have to deal with the single quotes if you don't

• Now restore.

[root@fedslave1 /]mysql -p db1 < db1_restore.sql

• Last, issue your change master

mysql>CHANGE MASTER to MASTER_HOST='master1';
mysql>slave start

***if you need to supply relay_log and pos, do so.

Tags: federated, multi-master, MySQL, replication

This entry was posted on Wednesday, July 1st, 2009 at 16:52 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.