One-Way Replication (random primary keys)

The setup steps below are for setting up one-way Replication using random primary keys. The recommended method is to instead use auto-increment variables to avoid data collision. See One-Way Replication

Make sure to use Statement Based Replication, and NOT Row Based Replication.

  1. Ensure random primary keys have already been enabled. See Random Primary Keys. If random primary keys are not enabled, instead see One-Way Replication.
  2. Install the OpenDentalReplicationService to each Slave. See Open Dental Replication Service.
  3. Stop the MySQL Service on both the Master and Slave (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 the Master or Slave before step 5, then replication on the Slave will fail.
  4. On all replication servers, wipe out all loose files in the mysql data directory which do not reside in a subfolder. If any InnoDB tables exist in any database, the "ib" files should not be removed. If all tables in all databases are MyISAM, then all of the "ib" files can be moved to a loose files folder.
  5. Make an identical database copy from Master to Slave. The replicated database must be exactly the same before replication begins the first time. If the one-way replication server is being added to an existing daisy chain, the database copy must be a snapshot of the database from the point in time that the daisy chain was setup (before any SQL statements were ever replicated). If you do not have this older copy of the database, reset the daisy chain immediately before copying the database to the one-way server.
  6. Enable Replication by setting up the my.ini file on both the Master and the Slave (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. ex) relay-log=SlaveComputer-relay-bin.

  7. Start the MySQL service on the Master and Slave (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. On the Slave, point the Slave to the Master by running a command similar to the following (run the following MySQL queries): Go to 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 = 'SERVER_MASTER',

    MASTER_USER = 'repl',

    MASTER_PASSWORD = 'ENTER PASSWORD HERE';


    Set MASTER_HOST to either the IP address or computer name of the Master. Set MASTER_PASSWORD to a password of your choosing.
  9. Run the following queries on the Master to grant permission for the Slave to read data. This step is easy to get wrong.

    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 the START SLAVE; in the mysql command interface on the Slave.
  11. Run the SHOW SLAVE STATUS \G; in the mysql command interface on the Slave. Make sure that the Slave IO Running and Slave SQL Running columns both say YES.
  12. How to test one-way replication between the Master and Slave (not really necessary).
    • Launch Open Dental on each of the servers using "LocalHost" and the replicated database
    • Ensure replication is working across as intended by performing the following:
      1. On the Master Server, create an appointment and verify the appointment shows up on the report server.
      2. On the Master Server, delete the appointment and verify that it is removed on the report server.

Master IP 192.168.0.1

database=databasename
Slave 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