Daisy Chain Replication: Add Server

The steps below explain how to add a new server when using daisy chain Replication. Related link: Daisy Chain Replication.

Note:
  • The server should be added after business hours because you will need to stop the MySQL service and no one can be using a database.
  • Every time you add or remove a server, you must also change the auto_increment_increment variable in the my.ini file on all servers to reflect the new total number of servers. It is best to alter the session table on all slaves to make the next id be greater than the id allocated by any of the slaves to date.


  1. Shutdown all workstations. Shutdown Workstations
  2. On the new server (location) install the MySQL application if it is not already installed.
    • Download the Trial version.
    • Right click on the downloaded file and Run as administrator.
    • Click Update MySQL to check the appropriate boxes.
    • Make any changes if needed to data paths.
    • Click Install. The installer will begin to install the MySQL Server.
    • Follow the directions carefully. We recommend using the default selections.
    • When you reach the MySQL Server Setup window, uncheck the Launch the MySQL Instance Configuration Wizard box then click Finish.
  3. On all existing servers:
    • Run the following SQL statement: STOP SLAVE;(This stops the replication thread within MySQL.)
    • Stop the MySQL service.
    • Open the my.ini file and update the auto_increment_increment value to reflect the new total number of servers.
    • 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).
    • On all slave servers (not the server with the most up-to-date data (master)): Backup and remove the current replicated database (typically named opendental) from the data directory. You must fully complete the above steps on all replication servers before proceeding to next step or replication will fail.
    • Copy the database folder on the master to the data directory on all slaves.
  4. On the new server, edit the my.ini file to include the following lines.

    log-bin=mysql-bin

    server-id=X

    log-slave-updates

    replicate-do-db=opendental

    relay-log=server-relay-bin

    skip-name-resolve

    auto_increment_increment=Y

    auto_increment_offset=Z

    Change the following values:

    • Change server-id as needed. It must be unique for each replication server.
    • Change replicate-do-db to the name of the database to replicate.
    • Change relay-log to include the name of the server (e.g. relay-log=SlaveComputer-relay-bin)
    • Change auto_increment_increment to equal the new total number of servers (e.g. 4).
    • Change auto_increment_offset to the new server's value (e.g. 4). Each server should have a unique value that doesn't exceed the auto_increment_increment value (e.g. 1, 2, 3, 4).
  5. Repeat steps 6 - 10 from Daisy Chain Replication.
  6. On all replication servers: Verify that replication is running successfully by executing the following SQL statement and checking for the Slave_IO_Running and Slave_SQL_Running columns to both say Yes: SHOW SLAVE STATUS;
  7. On all replication servers: If you blocked the MySQL service network port in step 1, unblock it, making sure to unblock incoming and outgoing.