One-Way Replication Setup

These instructions are for setting up simple one-way Replication and need to be followed very closely.

This configuration is useful for offices that have many workstations, or who experience slowness on the main server when running custom reports or complex backup scripts.  The Slave pulls data from the Master.  No changes are ever made directly to the Slave database because any users of EZ2000 Plus Dental connecting to the slave database should be trained to only run reports or make backups, not to do any data entry.  Data is never sent from the Slave to the Master because there is no replication process in that direction.  If the Slave becomes corrupt, simply wipe it clean and start again.  One-way replication is also a good way to familiarize yourself with replication administration.

The Master, the Slave, and any client machines which connect to the Master or Slave, must be on the same version of EZ2000 Plus Dental.

Random Primary Keys
Random primary keys do NOT need to be turned on in one-way replication.  No changes are ever made on the Slave, and no data is moved from the Slave to the Master, so there is no possibility of conflicting data. Therefore, no additional setup is required in Open Dental.  See Replication.

Instructions

1. Stop the MySQL Service on both the Master and Slave. ("net stop 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 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.
2. On the Slave, wipe out all loose 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).
3. Make an identical database copy from Master to Slave.  The replicated database must be exactly the same before replication begins the first time.
4. Enable Replication on the Slave by setting up the my.ini file.
   Option 1:  For MySQL 5.0, add the following lines to the "my.ini" file:
   log-bin=mysql-bin
   server-id=2
   log-slave-updates
   master-connect-retry=30
   replicate-do-db=opendental
   relay-log=server-relay-bin
   
   Alter the "server-id" and "replicate-do-db" as needed. 
   The "replicate-do-db" must be the name of the database to replicate.
   Replace 'server' within the "relay-log" value to the name of the server. ex) relay-log=SlaveComputer-relay-bin
   
   Option 2: For MySQL 5.5, 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
   
   Alter the "server-id" and "replicate-do-db" as needed.
   The "replicate-do-db" must be the name of the database to replicate.
   Replace 'server' within the "relay-log" value to the name of the server. ex) relay-log=SlaveComputer-relay-bin
5. 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.
6. 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 FilesMySQLMySQL Server 5.0in" 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 = 'od1234';
   
   Set "MASTER_HOST" to either the IP address or computer name of the Master.
7. 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 'od1234';
   GRANT SUPER ON *.* TO
   'repl' IDENTIFIED BY 'od1234';
   GRANT REPLICATION SLAVE ON *.* TO
   'repl' IDENTIFIED BY 'od1234';
   
   The "opendental.*" should be the name of your mysql database, and the ".*" means everything within the specified database.
8. Run the "START SLAVE;" in the mysql command interface on the Slave.
   
   
9. Run the "SHOW SLAVE STATUS;" in the mysql command interface on the Slave.
   Make sure that the Slave IO Running and Slave SQL Running columns both say 'YES'.

10. How to test one-way replication between the Master and Slave.  (not really necessary)  
    1. From the Master, run the following SQL to create a junk temporary table: CREATE TABLE tmp123 (Col1 VARCHAR(1));
    2. From the Slave, verify that table tmp123 was created by running the following SQL: SELECT * FROM tmp123;
    3. From the Master, delete table tmp123 by running the following SQL: DROP TABLE tmp123;
    4. From the Slave, verify that table tmp123 was dropped by running the following SQL and verifying that tmp123 does NOT show up: SHOW TABLES LIKE 'tmp123%';
    5. From the Slave, run the following SQL to create a junk temporary table: CREATE TABLE tmp456 (Col1 VARCHAR(1));
    6. From the Master, verify that table tmp456 was not replicated by running the following SQL and verifying that tmp456 does NOT show up: SHOW TABLES LIKE 'tmp456%';
    7. From the Slave, delete table tmp456 by running the following SQL: DROP TABLE tmp456;