Introduction

• This is a tutorial for setting up a master-slave replication of the OpenMRS database.

• This tutorial uses the AMPATH Medical Record System (AMRS) implementation of OpenMRS as an example.

  • Change names accordingly to suit your use.

Notes

• The host name of our Master Server is called AMRSPROD (as set by an alias in the host file).

• The host name of our Slave Server is called AMRSRESEARCH.

• It is recommended to use the same name for the replicated OpenMRS database on the Master as for the slave.

  • Our replicated database is called 'amrs' on both AMRSPROD and AMRSRESEARCH.

References

• MySQL Reference Manual Chapter on Replication

  • (I used this when setting up AMRS replication.)

• Conference 2008 Replication Tutorial

  • (I used MySQL Replication Tutorial Presentation (pdf) from here when setting up AMRS replication.)

• MySQL Conference 2009 Replication Tutorial

  Tutorial

  TUTORIAL

1. Ensure that each mysql have bind the host ip adddress

2. Create a replication user on the master database.

3. :

   Code Block
   #: mysql -u root -p #: mysql> GRANT REPLICATION SLAVE on *.* TO 'rep_user'@'amrsresearch' IDENTIFIED BY 'this-is-the-password'; #:

4. Stop the mysqld on the master server (AMRSPROD).

5. Edit my.cnf (or my.ini in Windows) on the master server (AMRSPROD):

6. :

   Code Block

   #: [mysqld] #: ## Replication of AMRS database: #: #: # An ID number to give the master (required): #: server_id=1 #: #: # The location and name of the binlog log files (required): #: log-bin="E:/MySQL Data/binlog/amrs-bin" #: #: # This will limit replication to only the amrs database: #: binlog-do-db=amrs #: #: # This keeps only two weeks of binlog files: #: expire_logs_days=14 #: #: # Set to '1' to write to binlog for every SQL statement instead of every transaction #: # (Safest option in case of server crash): #: sync_binlog=1 #:

7. Restart mysqld on master (AMRSPROD).

8. Obtain the master replication information (AMRSPROD).

9. :

   Code Block
   mysql -u root -p

   • Lock tables so no one can write to them.

10. :

    Code Block
    mysql> FLUSH TABLES WITH READ LOCK;

    • Then the replication information:

11. :

    Code Block
    #: mysql > SHOW MASTER STATUS; #: +----------------+----------+--------------+------------------+ #: | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | #: +----------------+----------+--------------+------------------+ #: | amrs-bin.00001 | 97 | amrs | | #: +----------------+----------+--------------+------------------+ #:

12. Create the backup of the master (AMRSPROD).

13. *Either stop mysqld on master and manually copy the data files (not covered here) or...

    • Create a mysqldump file (takes longer than copying data files):

14. :

    Code Block
    mysqldump -u root -p -q -e --single-transaction -r"amrs_backup.sql" amrs

15. Restore the backup onto the slave (AMRSRESEARCH)

    • If you chose the mysqldump route, then run this on the slave:

16. :

    Code Block
    #: mysql -u root -p #: mysql> source amrs_backup.sql

    • Depending on the size of the database, it may be a good time for lunch.

17. Grant the OpenMRS webapp_user for the slave server (AMRSRESEARCH) read-only access to all database tables except those that will not be replicated.

18. Stop the mysqld on the slave server (AMRSRESEARCH)

19. Edit my.cnf (or my.ini in Windows) on the slave server (AMRSRESEARCH):

20. :

    Code Block

    #: [mysqld] #: ## Replication for AMRS: #: # ID for slave server must be different than master (required): #: server-id=3 #: #: # Ignore these tables to run AMRS on slave server: #: replicate-ignore-table=amrs.global_property #: replicate-ignore-table=amrs.scheduler_task_config #: replicate-ignore-table=amrs.scheduler_task_config_property #: #: # Ignore these tables to run reports using AMRS on slave server: #: replicate-ignore-table=amrs.cohort #: replicate-ignore-table=amrs.cohort_member #: replicate-ignore-table=amrs.report_object #: replicate-ignore-table=amrs.report_schema_xml #: #: # Ignore any module tables you want on slave but not on master: #: replicate-ignore-table=amrs.versionedfileupload_versioned_file #: replicate-ignore-table=amrs.reporttemplate_report_template #:

21. Restart the mysqld on the slave (AMRSRESEARCH).

22. Configure the slave (AMRSRESEARCH):

23. :

    Code Block
    #: mysql -u root -p #: mysql> CHANGE MASTER TO master_host='amrsprod', #: -> master_user='rep_user', #: -> master_password='this-is-the-password', #: -> master_log_file = “amrs-bin-00001�, #: -> master_log_pos = 97; #:

    #: info| IMPORTANT: Change the master_log_file and master_log_pos to the values copied from the master (AMRSPROD).

24. Start the slave process.

25. :

    Code Block
    mysql> START SLAVE;

26. Check the slave status.

27. :

    Code Block
    mysql> SHOW SLAVE STATUS\G

28. On master (AMRSPROD) unlock tables so they can be written.

29. :

    Code Block
    mysql> UNLOCK TABLES;