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 one-way replication configuration, see One-Way Replication.

Instructions

MySQL comes with its own replication capabilities.

1. Add servers and install the OpenDentalReplicationService to each Replica. See Replication Setup.
   You must have the Open Dental Replication Service when running Daisy Chain Replication. The service will notify all users and IT staff if replication crashes via Alerts. We are not responsible for the damage done when databases continue to be used after an error crashes replication.
2. Stop the MySQL service on all replication servers (net stop mysql). On some operating systems, 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.
3. On all replication servers, wipe out all loose files in the mysql data directory which do not reside in a subfolder. If all tables in all databases are MyISAM and the database management system in use is MySQL 5.5, then all of the "ib" files can be moved to a loose files folder. If all tables in all databases are MyISAM and the database management system in use is MySQL 5.6 or MariaDB 10.5 or any InnoDB tables exist in any database, the "ib" files should not be removed.
4. Copy the information that corresponds with your database management system.
   • If all tables in all databases are MyISAM and the database management system in use is MySQL 5.5, make an identical database copy on all replication servers.
   • If all tables in all databases are MyISAM and the database management system in use is MySQL 5.6 or MariaDB 10.5 or any InnoDB tables exist in any database, make an identical data directory copy on all replication servers.
   The replicated database or data directory must be exactly the same on all replication servers before replication begins the first time.
5. On all replications servers, set auto increment / offset variables in the my.ini file. Do this before going to the next step. See Replication Auto Increment.
6. Make sure all the servers are synced to the same date and time. If locations are in different time zones, data will be synced but may not show at all locations.
7. 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.
8. Set each replication server's Primary (Master). Go to the command line and navigate to the appropriate bin folder of the MySQL service (typically C:\Program Files\MySQL\MySQL Server 5.5\bin or C:\Program Files\MariaDB 10.5\bin) and type the following: mysql -u root opendental where opendental is the name of the database. If you have a MySQL user password, add -p to the command.

   CHANGE MASTER TO

   MASTER_HOST = 'MASTER_OF_THIS_SERVER',

   MASTER_USER = 'repl',

   MASTER_PASSWORD = 'ENTER PASSWORD HERE';

   • Set MASTER_HOST to either the IP address or computer name of the replication Primary (Master) of this server. We recommend IP address to reduce possible DNS issues.
   • Set MASTER_PASSWORD to a password of your choosing.
9. For each replication server, run the following queries to grant permission for any replication Replica (Slave) to read data.

   GRANT CREATE TEMPORARY TABLES ON opendental.* TO'repl' IDENTIFIED BY 'ENTER PASSWORD HERE';

   GRANT SUPER ON *.* TO'repl' IDENTIFIED BY 'ENTER PASSWORD HERE';

   GRANT REPLICATION SLAVE ON *.* TO'repl' IDENTIFIED BY 'ENTER PASSWORD HERE';

   • The opendental.* should be the name of your mysql database and must be lowercase. The .* means everything within the specified database.
10. Run START SLAVE; in the mysql command interface for each replication server in the chain.
11. Run 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.
    
12. 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.
    • Ensure replication is working across each of the servers by performing the following:
      1. Create an appointment on Server 1 and verify the appointment shows up on ALL other servers.
      2. Make a change to the appointment on Server 2 and verify that the change reflects on ALL other servers.
      3. Repeat Step 2 until you have made a change from each server and verified the changes reflected on all other servers.
    • It is important to make a change from EVERY server and make sure those changes are reflected on each server.