MySQL in star topology

Question

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.

Answer 1

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.

Answer 2

It is not possibile, Mysql supports only multi master-master circular replication.

This article describes this replication very well.

Answer 3

IMPORTANT UPDATE I stand corrected. While in theory everything I've said here is sound and valid, MySQL does not actually support multi-master replication, for reasons I don't know. Other database servers do support this kind of topology, however, so if a true master-master star topology is required, changing to a different server will likewise be required.

I disagree with @lg, I think it is possible. (Sorry in advance that this "answer" lacks specifics, I don't have a MySQL database in front of me and have never done this before, but there's more here than would fit in a comment.)

In MySQL master-master replication, both servers take on the role of both master and slave; similarly, in master-master-etc circular repetition, all the servers are again both master and slave.

From another angle, MySQL is fully capable of having one master server replicate to multiple slaves -- we had this (master to 3 slaves) set up at my previous job.

Since we know a master can also be a slave, and we know that a master can have multiple slaves, and we know (from the way circular replication works) that replicated statements can be put into the binlog (for the next slave to pick up) and that a server can identify its own statements in a binlog in its own master (this is what keeps the "first" server in a circular replication scenario from replicating its own statements again and keeping them going in an infinite loop), it seems quite reasonable that you could set up a single master serving multiple slaves, which itself is a slave to multiple masters (which, again, I know is possible, because MySQL supports multi-master replication for the purpose of a single real-time backup for multiple servers).

Now, I'll admit that I can't offer specifics for how to set this up, and I'll readily acknowledge that you're not likely to get much (if any) support on this very unorthodox set-up, but here's some hints to get you started:

1. All the servers should be set up to log replicated statements (as per usual circular replication, like the article @lg linked to).
2. The "spoke" (for lack of a better term) servers should be set up in a master-master relationship with the central "hub" server.

This should be easy to test -- you say you've already got 2 set up in a master-master relationship, so now set up a third in a master-master relationship with whichever one you want to be the central "hub", then see if the replication works as you want it to (although remember step 1 -- typical master-master I believe does not put replicated statements into the binlog).