MySQL in star topology

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 !!!


This sounds like a job of a special type of MySQL Replication Topology

The topology I have in mind is called "the Distribution Master," which comes from pages 368-370 of the book High Performance MySQL : Optimizations, Backups, Replication and more under the subheading "Master, Distribution Master, and Slaves."

You will need the following

1 DB Master Server
1 DB Distribution Master (known as a /dev/null Slave, or Blackhole Slave)
Whatever Number of 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 DISTRIBUTION MASTER

  1. Install MySQL 5.1.x
  2. Make sure /etc/my.cnf
          server-id=2
          log-bin=mysql-bin
          expire-logs-days=14
          default-storage-engine=BLACKHOLE
          skip-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;
  6. GRANT SELECT,REPLICATION SLAVE ON . TO replicator@'%' IDENTIFIED BY 'repl-password';
  7. For every table that is not in information_schema database and not in the mysql database, convert each table to the BLACKHOLE storage engine like this: ALTER TABLE tblname ENGINE=BLACKHOLE;
  8. RESET MASTER; (Clear Binary Logs From DB Distribution Master)

SETUP OF DB SLAVES

  1. Install MySQL 5.1.x
  2. Make sure /etc/my.cnf
          server-id=3
          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;

The purpose of ths setup is to have the DB Master only process SQL meant for the DB Master only. The SQL to be replicated to the DB Slaves are handled by the DB Distribution Master. This will alleviate the DB Master processing the transmission of SQL to Slave; that becomes the responsibility of the DB Distribution Master. All Slaves read SQL changes from the DB Distribution Master rather than the DB Master.

Although I do not fully understand your application, this topology should properly support one central database and multiple read slaves without I/O bounding the central database. Your application should organization INSERTs, UPDATEs, and DELETEs to client database as long as each laptop handles a unique set of clients different and distinct from other laptops.

CAVEAT

You may have to do serious testing on the DB Slaves to make sure data doesn't vanish because of the BLACKHOLE setup. If this occurs, try removing 'default-storage-engine=BLACKHOLE' and reloading everything.

Future questions of this nature should be asked in dba.stackexchange.com

Tags:

Mysql

Configuration

Replication

Topology

Master

Recent Posts