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

Given the requirement to use MySQL Circular Replication against floating slaves as a means to Synchronize DBs, here is a solution:

1 DB Master
4 DB Slaves

---

SETUP OF DB MASTER

1. Install MySQL 5.1.x
   
2. Make sure /etc/my.cnf
         server-id=1
         log-bin=mysql-bin
         expire-logs-days=14
         default-storgae-engine=InnoDB
   
3. Startup MySQL
4. RESET MASTER; (Clear Binary Logs From DB Master)
5. Load in Data into Master
6. GRANT SELECT,REPLICATION SLAVE ON . TO replicator@'%' IDENTIFIED BY 'repl-password';

---

SETUP OF DB SLAVES

1. Install MySQL 5.1.x
   
2. Make sure /etc/my.cnf
         server-id=(unique server id)
         log-bin=mysql-bin
         default-storage-engine=InnoDB
   
3. Startup MySQL
   
4. CHANGE MASTER TO MASTER_HOST='IP Address of DB Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
5. START SLAVE; (let replication catch up, check SHOW SLAVE STATUS\G)
6. STOP SLAVE;
7. CHANGE MASTER TO MASTER_HOST='IP Address of DB Distribution Master', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
8. START SLAVE;

---

IMPORT PROCESS

Once every DB Slave is prepared, now the migration of Data between the DB Master and DB Slave can proceed as follows (DM for DB Master, and DS for DB Slave):

1. On DS, Run SHOW MASTER STATUS;
2. On DS, Record binary log filename and position
3. On DM, Run CHANGE MASTER TO MASTER_HOST='IP Address of DS', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='LogFile From Step2', MASTER_LOG_POS=(LogPos From Step2);
4. On DM, Run START SLAVE; (let replication catch up; port changes introduced by DS to DM)
5. On DM, STOP SLAVE;
6. On DS, record line 2 of /var/lib/mysql/master.info (log file)
7. On DS, record line 3 of /var/lib/mysql/master.info (log position)
8. On DS, Run CHANGE MASTER TO MASTER_HOST='IP Address of DM', MASTER_PORT=3306, MASTER_USER='replicator', MASTER_PASSWORD='repl-password', MASTER_LOG_FILE='LogFile From Step6', MASTER_LOG_POS=(LogPos From Step7);
9. On DS, Run START SLAVE; (let replication catch up; port changes introduced by DM to DS)
10. On DS, STOP SLAVE;

---

CAVEAT

The import process is crucial !!! You must record the exact log file and log position correctly each and every time.

Give it a Try !!!

Let me know how it goes !!!