Home User Manual Discussion Forum Search

One-Way Replication Setup

MySQL comes with its own Replication capabilities. Make sure to use Statement Based Replication, and NOT Row Based Replication.

Note: The recommended steps below include setting up auto-increment variables to avoid data collision. If you already use random primary keys instead, see One-Way Replication with Random Primary Keys.

  1. Add Servers and Designate a Slave Monitor.

  2. 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.

  3. On the Slave, move everything out of the mysql\data folder and into a mysql\backup folder except for the following:
    • Files beginning with "ib" (e.g. iblog_file0, ib_logfile1, ibdata1)
    • The database that will be used in step 4
    • The mysql\data\mysql folder (these are the grant tables)

  4. 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.

  5. On the Slave and Master, Set Auto Increment / Off Set Variables in the my.ini file.

  6. Make sure the master and slave are synched to the same date and time.

  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 = 'od1234';


    Set "MASTER_HOST" to either the IP address or computer name of the Master.

  9. Run the following queries on the Master to grant permission for the Slave to read data. 

    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 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'.


  1. (Optional) How to test one-way replication between the Master and Slave.  
    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;
  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

 

Open Dental Software 1-503-363-5432