REDCap Replication

Requirements

This is the minimal set of access privileges required to preform this tasks

• Access to Mysql account with administrative privilege in to running mysql setup tasks
• Access to Azure account with asset/resource provisioning privileges
• Access to Azure account with REDCap key Vault secrets read/list privileges
• Command line access to REDCap VM or RIS compute cluster
• Access to RIS I2 storage1 allocation with read/write privileges

Part 1) Preparation for mysql replication

Confirm primary server is configured for replication

• verify primary server parameters

  • wildcard table ignore

  • binary logging is on

  • binary log expiration

  • Time zone

    Prod parameter page

• validate and if needed create a replication user on primary server

Take full backup of Primary server

From either a VM with network connectivity or the RIS compute cluster run the following commands:

RIS compute getting started Guide

command line MySQL using RIS compute

time mysqldump -h <primary sever> -u <admin user> -p --source-data=2 --single-transaction --quick <database name> > /storage1/fs1/I2/Active/rdc2_etl/REDCap/backups/redcap_<enviroment>_<database name>_<function>_(date +%F).sql

Example:

time mysqldump -h 10.24.96.139 -u i2adminv8qa -p --source-data=2 --single-transaction --quick redcap_appq32m1 > /storage1/fs1/I2/Active/rdc2_etl/REDCap/backups/redcap_qa_redcap_appq32m1_replication_(date +%F).sql

Create secondary server

Update Secondary server

Notes

Azure refence for data-in replication

Setup replication between on-prem instance and azure

Azure refence for data-in replication
Source database host: 10.39.196.157
destination database host: 10.24.96.134
Application host: 10.24.96.154
Source database: redcap_prod
destination database: redcap_prod

1. Create backup from secondary v7 on prem redcap_prod db
   time mysqldump --no-create-db --routines --quick -B redcap_prod -u -p --master-data=2 --single-transaction > /backups/dbp14m2/mysqldump_redcap_prod_$(date +%F).sql
2. Sync redcap_prod db to azure prod
   time scp /backups/dbp14m2/mysqldump_redcap_prod_2023-09-20.sql @10.24.96.154:/mnt/austore/
   time mysql -h 10.24.96.135 -u azureadmin -p -D redcap_prod < /mnt/austore/mysqldump_redcap_prod_2023-09-20.sql
3. Create replication user on secondary v7 on prem redcap_prod db
   CREATE USER 'sync_user'@'%' IDENTIFIED BY '';
   GRANT REPLICATION SLAVE ON . TO ' sync_user'@'%';
4. Set filters to only replicate recap_prod database
   Azure Portal->ICS - Redcap->i2-redcap7-migration-rg->i2-redcap-migr-mysql->Server Parammter->replicate_wild_ignore_table:
   https://portal.azure.com/#@gowustl.onmicrosoft.com/resource/subscriptions/66fc3882-1a21-4787-9351-af5aa8eb3563/resourceGroups/i2-redcap7-migration-rg/providers/Microsoft.DBforMySQL/flexibleServers/i2-redcap-migr-mysql/serverParameters
   mysql.%,information_schema.%,performance_schema.%,sys.%,redcap_appo27m1.%,redcap_appp60m1.%
5. Configure replication on azure flex server
   CALL mysql.az_replication_change_master('10.39.196.157', 'sync_user', '', 3306, 'bp14m2_mysql-bin.005876', 475494453, '');
   CALL mysql.az_replication_start;
   SHOW SLAVE STATUS\G