Data Replication of OpenMRS Database

Contents

[edit]

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.
[edit]

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.
[edit]

References

[edit]

Tutorial

  1. Ensure that each mysql have bind the host ip adddress
  2. Create a replication user on the master database. 
     mysql -u root -p

     mysql> GRANT REPLICATION SLAVE on *.* TO 'rep_user'@'amrsresearch' IDENTIFIED BY 'this-is-the-password';

    

    

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

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

     
     [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

     

    

  5.  Restart mysqld on master (AMRSPROD).

  6.  Obtain the master replication information (AMRSPROD).

    mysql -u root -p
    

    

    •  Lock tables so no one can write to them.

    

     
    mysql> FLUSH TABLES WITH READ LOCK;
    

    

    •  Then the replication information:

    

     mysql > SHOW MASTER STATUS;

     +----------------+----------+--------------+------------------+

     | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |

     +----------------+----------+--------------+------------------+

     | amrs-bin.00001 | 97       | amrs         |                  |

     +----------------+----------+--------------+------------------+

    

    

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

    •  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):

    

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

    

  8.  Restore the backup onto the slave (AMRSRESEARCH)

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

    

     
     mysql -u root -p

     mysql> source amrs_backup.sql

    

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

    

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

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

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

     [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

    

    

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

  13.  Configure the slave (AMRSRESEARCH):

     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;

    

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

    

  14.  Start the slave process.

    mysql> START SLAVE;
    

    

  15.  Check the slave status.

    mysql> SHOW SLAVE STATUS\G
    

    

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

    mysql> UNLOCK TABLES;
    

    






Retrieved from "http://openmrs.org/wiki/Data_Replication_of_OpenMRS_Database"