Page Comparison

Steps for creating Data pool:

...

datadir (should be empty)

etc

init

mkdir datadir

mkdir etc

mkdir init

2. Change directory to etc

...

Encountered Issues:

1. To re-start Docker

To check if the docker container is running, run the following command:

docker ps

<if no row returns → docker is down>

Path: /home/ubuntu/datalake/afi/scripts

Names of the container - afi, vso

Command to start docker:

docker restart <container name>

Once the docker is restarted, it is necessary to restart the sync service & enable the same (else the replication timer will not be scheduled).

The server location of service & timer is: /lib/systemd/system

sync_master_afi.service

sync_master_afi.timer

sync_master_vso.service

sync_master_vso.timer

Command to list the scheduled timers:

system$ sudo systemctl list-timers sync_master*

Command to check the status of the service & timer

systemctl status sync_master_afi.service

systemctl status sync_master_vso.service

systemctl status sync_master_afi.timer

systemctl status sync_master_vso.timer

To enable the service run following commands:

systemctl enable sync_master_afi.service

systemctl enable sync_master_vso.service

To enable the timer run the following commands:

systemctl enable sync_master_afi.timer

systemctl enable sync_master_vso.timer

2. If the datapool is out of sync with production:

Perform the following steps on Master Server that is to be replicated on the Datapool

1.Using Putty, connect to prod server. Change to root user.

sudo su

2. Restart mysql service

systemctl restart mysql

3. Login to mysql using root

mysql -u root -p

4. Run following commands:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

5. The status appears as below: Note that position number varies!

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | your_DB | |
+------------------+----------+--------------+------------------+

6. Note down the master details - file name and log position(log pos)

7. Open a duplicate session and in this new session run the following command

mysqldump -u root -p openmrs > 1openmrs.sql

8. Switch back to the mysql session and run the following commands

unlock tables;

quit;

9. Transfer the file 1openmrs.sql file to the datapool server

Perform the following steps on Master Server that is to be replicated on the Datapool (In all the steps mentioned below, it is assumed that we are replicating the AFI docker container. For VSO or any other container make the necessary changes)

1. Stop the docker that is out of sync

   1. docker stop afi

2. Remove the docker container

   1. docker rm afi

3. The file structure maintained at datapool is:

   1. Image Added

4. Go to path - /home/ubuntu/datalake

5. Take the backup of the container folder that you want to replace on your local machine

6. delete the container folder from datalake folder

7. Now create a new directory for the conatainer

   1. mkdir afi

8. Under this new folder create following 5 sub folders

   1. mkdir datadir (it should be empty)

   2. mkdir init

   3. mkdir etc

   4. mkdir scripts

   5. mkdir services

9. Change directory to etc

   1. cd etc

10. Create the file my.cnf

    1. touch my.cnf

11. Edit the my.cnf file

    1. vi my.cnf

12. To insert text press i

    1. Add the following lines in the file:

       [mysqld]

       user=mysql

       server-id=2

       log-bin=mysql-bin

       binlog-format=row

       relay-log=mysql-relay

       replicate-rewrite-db=openmrs->OpenMRS_AFI

       replicate-do-db=OpenMRS_AFI

       binlog-do-db=OpenMRS_AFI

13. Press esc, enter :wq, enter to save the file

14. Change directory to init

    1. cd ../init

15. Copy the 1openmrs.sql file

16. Edit the 1openmrs.sql file as follows:

    1. Add the following line as the first line in the file

        

       USE <name_of_the_database_in_the_slave_mysql>;

       i.e. USE OpenMRS_AFI;

17. Press esc, enter :wq, enter to save the file

18. Create 2setup_slave.sql file

    1. touch 2setup_slave.sql

19. Add following lines to the sql file:

    1. CHANGE MASTER TO MASTER_HOST='<master_ip_address>', MASTER_USER='<slave_user_created_on_the_master_mysql_server>', MASTER_PASSWORD='<slave_user_password>', MASTER_LOG_FILE='<master_log_file_name>', MASTER_LOG_POS=<master_log_pos>;

    2. in case of afi the the above lines will be

       1. CHANGE MASTER TO MASTER_HOST='13.127.240.201', MASTER_USER='dbslave', MASTER_PASSWORD='dbslave_afi', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=<as noted in step#5>;

20. Press esc, enter :wq, enter to save the file

21. Under subfolder “Scripts”, copy the files from backup taken on local machine (under same subfolder) i.e. files viz. create_container.sh, mysql.sh & dbash.sh. Provide the necessary execution rights to the files.

22. Under subfolder “Services”, create 2 files viz. service.log & debug.txt using command:

    1. touch service.log

    2. touch debug.txt

23. Use docker to create the mysql server container that will have the above configurations with the following command:

    1. docker run --name <container_name>
       -e MYSQL_ROOT_PASSWORD=<root_password>
       -e MYSQL_USER=<slave_user>
       -e MYSQL_PASSWORD=<slave_user_password>
       -e MYSQL_DATABASE=<database_name_that_will_be_populated_with_master_data>
       --mount type=bind,source=<location_to_datadir>,target=/var/lib/mysql
       --mount type=bind,source=<location_to_my.cnf>,target=/etc/my.cnf
       --mount type=bind,source=<location_to_init>,target=/docker-entrypoint-initdb.d
       -p <host_port_to_be_mapped>:3306
       -d mysql/mysql-server:5.7

    2. In case of afi, the command will be:

       1. docker run --name afi
          -e MYSQL_ROOT_PASSWORD=i10hi1c_afi
          -e MYSQL_USER=dbslave
          -e MYSQL_PASSWORD=dbslave_afi
          -e MYSQL_DATABASE=OpenMRS_AFI
          --mount type=bind,source=/home/ubuntu/datalake/afi/datadir,target=/var/lib/mysql
          --mount type=bind,source=/home/ubuntu/datalake/afi/etc/my.cnf,target=/etc/my.cnf
          --mount type=bind,source=/home/ubuntu/datalake/afi/init,target=/docker-entrypoint-initdb.d
          -p 3307:3306
          -d mysql/mysql-server:5.7 &>> /home/ubuntu/datalake/afi/services/debug.txt

24. Check with the following command whether the container has been successfully created

    1. docker logs <container_name> i.e. docker logs afi

25. Run the scripts in “Scripts” folder viz. dbash.sh & mysql.sh

26. Enable the sync master service & timer as described in Issue#1

27. Copy the shell scripts from backup (local machine) “Services” folder to “Services” folder in docker container folder i.e /home/ubuntu/datalake/afi

28. Provide necessary read/write/execute rights to the shell scripts

29. Login to Superset with admin login

30. Go to Sources → Databases.

31. Select the container newly created. Click on Edit record button.

32. Hit the “Test Connection” button to ensure that the DB connection is re-established.

33. Check if dashboard displays correct data