4

Disclaimer: I've never worked with replication before, but I'm doing all my research. Not really having a bunch of in-depth knowledge about MySQL makes a lot of the documentation hard to understand.

We have a remote server, and many 'onsite' servers. This app is mission-critical, and because of the unreliable internet connections on site, the onsite machines have fully functional instances of the application. Onsite machines are behind various firewalls and generally are not able to access each other. We can set up SSH tunnels to allow the cloud DB to communicate with the onsite DBs.

Here's a simple diagram:

enter image description here

Async Multi-Master replication seems to be the best bet for us, but most of the documentation seems to imply that multi-source replication only works for slaves and/or multi-master replication only works circularly and a single master (in this case, the cloud server) can not replicate directly to multiple other masters.

Am I interpreting this incorrectly (merely unable to find documentation on how to do this), or is this the case? If it's the case, are there are 3rd party packages like Tungsten or Galera we can use? We would prefer to use as vanilla MySQL as possible, though.

Edit for clarification: Here's a my.cnf section I found describing a 2-master setup. I'd like to set N number of these on the cloud server, and just one each (the cloud server) on the onsite servers. We'd like to do everything through my.cnf and avoid using/scripting mysql shell commands.

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306
Improve this question

2 Answers 2

Reset to default
0

MULTIMASTER REPLICATION

If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.

If you have EC2, you can also use the PXC Setup Guide in EC2.

MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7

If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

MySQL High Availability

has the algorithm for doing robin robin multisource replication

  1. Setup the slave to replicate from one master. We'll call this the current master.
  2. let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client responsible for handling the switch just sleeps.
  3. Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
  4. Wait until the relay log is empty.
  5. Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
  6. Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE STATUS output.
  7. Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup replication.
  8. Restart the slave threads using START SLAVE.
  9. Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

BTW Pages 228,229 of the Book has sample code in Python

MySQL REPLICATION

Please do not put the options for connecting up replication in my.cnf.

They have been obsolete since MySQL 5.5

It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

Improve this answer
2
  • Thank you for the comprehensive answer! Will the CHANGE MASTER settings persist when the server is restarted?
    – threewordphrase
    Commented Jul 29, 2015 at 17:00
  • Yes. Those values are stored in a file called master.info. This is not a static file. It updates the log file and log position on the fly. As long as the file is present, mysqld will detect its presence and setup replication. It will start replication for you unless --skip-slave-start in embedded in my.cnf.
    – RolandoMySQLDBA
    Commented Jul 29, 2015 at 17:03
0

For anyone with a similar problem, I have ended up going with MariaDB, which supports multiple masters per node (connection_name variable when specifying CHANGE MASTER TO). MySQL supports this allegedly starting at 5.7, but we don't want to wait for that release.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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