MySQL – Using Star Topology for MySQL Replication

MySQLmysql-replicationtopology

I have one central database with all the data in MySQL 5.1-lastest-stable.
I want to hook up multiple clients in a master-master relationship.

Question
How do I setup a star topology with 1 central server in the middle with multiple client-databases so that changes in one client get propagated first to the central server and from there to all the other client-databases?

Database info
I'm using inno-db for all the tables and I've enabled the binary-log.
Other than that I've learned how to do master-master between to databases.
All tables have primary keys primary integer autoincrement. Where the autoincrements offset and start is tuned to different client-databases never have primary key conflicts.

Why do I want this
I have client software (not a website or php) that connects to a local MySQL database on the laptop, this needs to sync to a central database, so that all folks using the program on their laptop see all the other changes that other folks make.
I do not want to connect directly against the central database because if the internet connection drops between the laptop and the central database my application dies.
In this setup the application continues, the laptop just does not get updates from other people until the connection to the central database is reestablished.

Best Answer

There is a specific reason why what you proposed is impossible to achieve with MyISAM and InnoDB.

A star topology warrants a Master being the center of the universe, not the slave. MySQL Replication was not designed to have a slave read from multiple masters simultaneously. It can only read from one master at a time. The CHANGE MASTER TO command connects a slave to one, and only one, master.

According to the book Understanding MySQL Internals, page 219 paragraph 2 under the subheading "Multi-Master" says the following:

MySQL Replication was not originally written with multi-master support in mind. A slave is natively capable of replicating only one master. A fairly simple patch can be created to allow one slave to collect updates from multiple masters without conflict resolution. This was done at one time, but for a number of reasons did not make it into the main branch of the source tree. A more complex patch to allow some conflict resolution was planned at one point, but for a number of reasons did not make it to development. It stll may be implemented in the future.

The book High Performance MySQL: Optimization, Backups, Replication and more has a box at the top of page 364 (Chapter 8 : Replication Topologies) whose title is "MySQL Does Not Support Multimaster Replication". The box has the following paragraphs:

We use the term multimaster replication very specifically to describe a slave with more than one master. Regardless of what you may have been told, MySQL (unlike some other database servers) does not support the configuration illustrated in Figure 8-6 at present. However, we show you some ways to emulate multimaster replication later in this chapter.

Unfortunately, many people use this term casually to describe any setup where there is more than one master in the entire topology, such as the "tree" topology we show later in this chapter.Other people use it to describe what we call master-master replication, where the servers are mutually master and slave.

These terminology problems cause a lot of confusion and even arguments, so we think it's best to be careful with names. Just imagine how hard it will be to communicate if MySQL adds support for a slave with two masters! What term will you use to describe that if you haven't reserved "multimaster replication" fro the purpose?

While the emulation techniques listed pages 373-375 under the subheading "Emulating multimaster replication" is theorectically possible (using the BLACKHOLE storage engine) and have been implemented successfully by others to emulate only two masters, it is still could never support your particular proposed topology.

I had addressed this question before. In fact, the answer I gave there is successfully done all the time. This is why insurance salesmen can bring a laptop to a person's house and collect insurance data on a person applying for insurance. The salesman would eventually connect to a central computer to download a new client's application. In turn, the central computer can download the latest actuary info so as to pro-rate what a policy would cost the applicant. It works on the same premise for connecting a laptop to a central computer, one laptop at a time.

Related Topic