Daisy Chain Replication: Add Server (random primary keys)

The setup steps below are for adding a new server to daisy chain replication when using random primary keys.

Also see: Replication, Daisy Chain Replication.

On the New Replication Server

  1. On the new server (location) install the MySQL application if it is not already installed.
    1. Download the Trial version.
    2. Right click on the downloaded file and Run as administrator.
    3. Click Update MySQL to check the appropriate boxes.
    4. Make any changes if needed to data paths.
    5. Click Install. The installer will begin to install the MySQL Server.
    6. Follow the directions carefully. We recommend using the default selections.
    7. When you reach the MySQL Server Setup window, uncheck the Launch the MySQL Instance Configuration Wizardbox then click Finish.
  2. Enable Replication on the new server by setting up the my.ini file and saving it (C:\Program Files (x86)\MySQL\MySQL Server 5.5).

    Add the following lines to the my.ini file:

    log-bin=mysql-bin

    server-id=3

    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.

  3. Restart the MySQL service. On some operating systems this will require you go into the system services in Control Panel, Administrative Tools, Services and start the MySQL service manually.

Adding the New Server to the Daisy Chain

Make sure to complete each numbered step before continuing on to the next step or replication will fail. For the steps that say on all replication servers... you must perform the step on all replication servers (master and slaves) before continuing on to the next step.

  1. Pick a database from one of the replication servers to act as the master. We will refer to the server where this database is located as the master and all other replication servers as slave in the text below.
  2. On the master server where the database is located, log in to Open Dental. In the main menu, click Setup, Advanced Setup, Replication. Click Add to add a server and enter its primary keys and slave monitor. Follow steps 3 and 4 on Random Primary Keys. When complete, this database is ready to be replicated to others servers.
  3. On all replication servers (except the new server):
    1. Launch the command line window, 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.
    2. Run the following SQL statement: STOP SLAVE; (this stops the replication thread within MySQL).
    3. Stop the MySQL service.
    4. Backup and remove all 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).
  4. On all slave servers (not the master or new server): Backup and remove the current replicated database (typically namedopendental) from the data directory. You must fully complete the above steps on all replication servers before proceeding or replication will fail.
  5. Copy the database folder on the master to the data directory on all slaves and the new server.
  6. On all replication servers (master, slaves, and new server): Repeat steps 6 - 10 from Daisy Chain Replication Setup. At this point the new server becomes a slave.