Home User Manual Discussion Forum Search

Daisy Chain Replication Setup (using Random Primary Keys)

The setup steps below are for setting up daisy chain replication using random primary keys. The recommended method is to instead use auto-increment variables to avoid data collision (see Daisy Chain Replication Setup).

Daisy chain Replication configuration is very complex and should only be attempted by users who are experienced with replication.  Incorrect setup can result in corrupt data, computer crashes, or other serious consequences.  For a simpler configuration, see One-Way Replication Setup. Also see Adding a New Server to Daisy Chain Replication.

Notes:

  • It is recommended to use a folder synchronization software for the Open Dental A to Z folders when using replication. See Online Backups.
  • If replication is currently setup and you are trying to re-create the replication setup, you must first stop the existing slave service by running the "SLAVE STOP;" while the MySQL service is started.
  • For instructions when updating, see Replication - Updating Open Dental Versions.

Set Up Instructions
MySQL comes with its own replication capabilities.  Make sure to use Statement Based Replication, and NOT Row Based Replication.

  1. Enable Random Primary Keys, set each replication server key range, and designate the Slave Monitor.  See Random Primary Keys / Slave Monitor.

    You must have a Slave Monitor when running Daisy Chain Replication. The Slave Monitor will immediately notify all users and IT staff if replication crashes.  We cannot stress this enoughWe are not responsible for the damage done when databases continue to be used after an error crashes replication. 

  2. Shutdown all workstations.

  3. Backup the entire 'mysql\data\' directory. This must be done before deleting any 'loose' files (step 5).

  4. Stop the MySQL service on all replication servers. ("net stop mysql"). On some operating systems, such as Windows Vista, this will require you go into the system services in Control Panel, Administrative Tools, Services and stop the MySQL service manually. If the MySQL service is started on any replication server before step 5, then replication will fail.

  5. On all replication servers, wipe out all loose files in the mysql data directory which do not reside in a subfolder, except for any files beginning with "ib" (e.g. iblog_file0, ib_logfile1, ibdata1).

  6. Make an identical database copy on all replication servers.  The replicated database must be exactly the same on all replication servers before replication begins the first time.

  7. Enable Replication by setting up the my.ini file on all replication servers involved before going to the next step (C:\Program Files (x86)\MySQL\MySQL Server 5.5).

    my.ini: Add the following lines to the "my.ini" file:
    log-bin=mysql-bin
    server-id=2
    log-slave-updates
    replicate-do-db=opendental
    relay-log=server-relay-bin
    skip-name-resolve

    Change the "server-id" as needed. The server ID must be unique for each replication server.
    Change the "replicate-do-db" to the name of the database to replicate.
    Change the "relay-log" value to include the name of the server you are on. ex) relay-log=ComputerName-relay-bin


  8. Start the MySQL service on all replication servers ("net start mysql"). On some operating systems, such as Windows Vista, this will require you go into the system services in Control Panel, Administrative Tools, Services and start the MySQL service manually.

  9. Set each replication server's master server by running a command similar to the following (run the following MySQL queries):
    Go the command line and change the directory to "C:\Program Files\MySQL\MySQL Server 5.5\bin" and type the following: "mysql -u root opendental" where opendental is the name of the database.

    CHANGE MASTER TO
    MASTER_HOST = 'MASTER_OF_THIS_SERVER',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'od1234';


    Set "MASTER_HOST" to either the IP address or computer name of the replication master of this server. We recommend IP address to reduce possible DNS issues.

  10. For each replication server, run the following queries to grant permission for any replication slave to read data.

    GRANT CREATE TEMPORARY TABLES ON opendental.* TO 'repl' IDENTIFIED BY 'od1234';
    GRANT SUPER ON *.* TO 'repl' IDENTIFIED BY 'od1234';
    GRANT REPLICATION SLAVE ON *.* TO 'repl' IDENTIFIED BY 'od1234'
    ;

    The "opendental.*" should be the name of your mysql database and must be lowercase. The ".*" means everything within the specified database.

  11. Run the "START SLAVE;" in the mysql command interface for each replication server in the chain.

  12. Run the "SHOW SLAVE STATUS\G;" in the mysql command interface for each replication server in the chain.
    Make sure that the Slave IO Running and Slave SQL Running columns both say 'YES'.



  13. How to test replication between two replication servers. Only test two replication servers at a time. We recommend testing the first replication server in the chain with the last replication server in the chain. If the test fails, make several smaller tests between consecutive replication servers.
    1. From server1, run the following SQL to create a junk temporary table: CREATE TABLE tmp123 (Col1 VARCHAR(1));
    2. From server2, verify that table tmp123 was created by running the following SQL: SELECT * FROM tmp123;
    3. From server2, run the following SQL to create a junk temporary table: CREATE TABLE tmp456 (Col1 VARCHAR(1));
    4. From server1, verify that table tmp456 was created by running the following SQL: SELECT * FROM tmp456;
    5. From server1, delete table tmp456 by running the following SQL: DROP TABLE tmp456;
    6. From server2, verify that table tmp456 was dropped by running the following SQL and verifying that tmp456 does NOT show up: SHOW TABLES LIKE 'tmp456%';
    7. From server2, delete table tmp123 by running the following SQL: DROP TABLE tmp123;
    8. From server1, verify that table tmp123 was dropped by running the following SQL and verifying that tmp123 does NOT show up: SHOW TABLES LIKE 'tmp123%';
  Server_1 IP 192.168.0.1
database=databasename
Server_2 IP 192.168.0.2
database=databasename
Check the log files to make sure the files were created and time stamps are recent.  C:\mysql\data

C:\mysql\data>dir

02/22/2008 01:25 PM 57 relay-log.info

02/22/2008 09:12 PM 235 server-relay-bin.000043

02/22/2008 09:12 PM 26 server-relay-bin.index

02/22/2008 01:22 PM 17,642 server.err

02/22/2008 01:22 PM 5 server.pid

C:\mysql\data>dir

02/22/2008 01:25 PM 57 relay-log.info

02/22/2008 09:12 PM 235 server-relay-bin.000043

02/22/2008 09:12 PM 26 server-relay-bin.index

02/22/2008 01:22 PM 17,642 server.err

02/22/2008 01:22 PM 5 server.pid


 

Open Dental Software 1-503-363-5432