Daisy Chain Replication: Adding a New Server
The steps below explain how to add a new server when using Daisy Chain Replication.
- 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.
- Shutdown all workstations.
- On the new server (location) install the MySQL application if it is not already installed.
a. Download the Trial version.
b. Right click on the downloaded file and Run as adminsitrator.
c. Click Update MySQL to check the appropriate boxes.
d. Make any changes if needed to data paths.
e. Click Install. The installer will begin to install the MySQL Server.
Follow the directions carefully. We recommend using the default selections.
g. When you reach the MySQL Server Setup window, uncheck the 'Launch the MySQL Instance Configuration Wizard' box then click Finish.
- On all existing servers:
a. Run the following SQL statement: STOP SLAVE; (This stops the replication thread within MySQL.)
b. Stop the MySQL service.
c. Open the my.ini file and update the auto_increment_increment value to reflect the new total number of servers.
d. 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).
e. 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.
f. Copy the database folder on the master to the data directory on all slaves.
- On the new server, edit the my.ini file to include the following lines.
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).
- Repeat steps 6 - 10 from Daisy Chain Replication Setup.
- 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;
- On all replication servers: If you blocked the MySQL service network port in step 1, unblock it, making sure to unblock incoming and outgoing.