REDCap Database Migration and Replication

[rwattuab]

REDCap Database Migration and Replication

Helpful links:

• mariadb-backup Overview
• Full Backup and Restore (mariadb-backup)
• MariaDB Replication Overview
• MariaDB Changing a Replica to Become the Primary

---

Note

What follows is somewhat specific to my needs at the time. I'll try to make it as generic as possible so it can be easier to adapt to other situations. I'll start out with VMs on our vSphere system. Then I'll go back and add in whatever extra steps I might have needed to change when doing this within OpenStack.

Steps taken when initially testing database replication.

• Cloned prod-redcap-database VM in vSphere.
  • Disable any cron jobs that might be running.
  • for user in $(cut -f1 -d: /etc/passwd); do echo $user; crontab -u $user -l; done
  • mv /var/spool/cron /var/spool/cron_is_disabled
• Started up clone VM without networking so I can change IPs and make firewall changes without affecting production.
  • Assigned new IP address
• Cloned prod-redcap-app VM in vSphere. So I could have a web frontend to use.
• Modified networking and firewall rules as before to reference new IPs.
  • Assigned new IP address
• Changed prod-redcap-database-clone database to allow prod-redcap-app-clone to access REDCap files and update itself.

[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > RENAME USER 'appsdomu_user'@'<Old IP Address>' TO 'appsdomu_user'@'<New IP Address>';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > RENAME USER 'rcprod_updater'@'<Old IP Address>' TO 'rcprod_updater'@'<New IP Address>';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > FLUSH PRIVILEGES;

• Decided to use mariadb-backup (fork of Percona XtraBackup) instead of mysqldump.
  • Test took about 30 minutes. Much faster and easier.
  • Backs up everything in the database.
• Created backup user in prod-redcap-database-clone

[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > CREATE USER 'backupuser'@'localhost' IDENTIFIED BY '<Password>';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'backupuser'@'localhost';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > FLUSH PRIVILEGES;

• Made backup of prod-redcap-database-clone mariadb database.

[root@prod-redcap-database-clone ~] mariadb-backup --backup --target-dir=/sourcedb/mariadb/backup/ --user=backupuser --password=<Password> --parallel=#
[root@prod-redcap-database-clone ~] mariadb-backup --prepare --target-dir=/sourcedb/mariadb/backup/

• I already had a running FreeBSD VM which I repurposed as the replica-server-vm.
• Rsync'd mariadb-backup files from prod-redcap-database-clone to replica-server-vm.

[root@prod-redcap-database-clone ~] rsync -ahP --no-p --no-o --no-g --info=progress2 -e 'ssh -p 22' /sourcedb/mariadb/backup/ rwatt@replica-server-vm:/misc/mariadb/backup/redcapdb

• Realized I needed free space on replica-server-vm, so I added another volume.
  • Moved backup data to new volume.

replica-server-vm root ~ # geom disk list
replica-server-vm root ~ # gpart show -lp
replica-server-vm root ~ # gpart create -s GPT da2
replica-server-vm root ~ # gpart add -t freebsd-zfs -b 1M -l misc da2
replica-server-vm root ~ # zpool create -o ashift=12 misc gpt/misc
replica-server-vm root ~ # zfs set atime=off misc
replica-server-vm root ~ # zpool list
replica-server-vm root ~ # zpool status
replica-server-vm root ~ # zfs list
replica-server-vm root ~ # mkdir -p /misc/mariadb/
replica-server-vm root ~ # mv /var/db/mysql/backup/ /misc/mariadb/

• Noticed that backup folder on replica-server-vm is considerably smaller than prod-redcap-database-clone.
  • 59GB vs 277GB.
  • Ran another rsync, but nothing changed.
  • Need to research, but maybe space reclamation during the process???
    • Seems like it's related to innodb table fragmentation

replica-server-vm root ~ # du -hsc /misc/mariadb/backup/* | sort -hr
replica-server-vm root ~ # du -hsc /misc/mariadb/backup/redcapdb/* | sort -hr
replica-server-vm root ~ # rm -rf /misc/mariadb/backup/*
replica-server-vm root ~ # chown -R rwatt:rwatt /misc/mariadb/
replica-server-vm root ~ # du -hs /misc/mariadb/backup

• Restored mariadb-backup files on replica-server-vm.
  • Need to completely remove previous database files on replica-server-vm.
  • The mariadb restore / copy-back balloons the database to 99GB.
  • mariadb-backup --copy-back --force-non-empty-directories --parallel=#

replica-server-vm root ~ # service mysql-server stop
replica-server-vm root ~ # rm -rf /var/db/mysql/*
replica-server-vm root ~ # mariadb-backup --copy-back --target-dir=/misc/mariadb/backup/redcapdb/
replica-server-vm root ~ # du -hs /var/db/mysql/
replica-server-vm root ~ # chown -R mysql:mysql /var/db/mysql/
replica-server-vm root ~ # service mysql-server start

• Configure replica user on prod-redcap-database-clone (or primary).
  • Replica database uses this to connect to primary.

[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > CREATE USER 'repl'@'replica-server-vm' IDENTIFIED BY '<Password>';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'replica-server-vm';
[root@prod-redcap-database-clone ~] → MariaDB-10.6.19 > FLUSH PRIVILEGES;

• Created self CA on prod-redcap-database-clone to generate certs for mariadb TLS. Replication requires it.

[root@prod-redcap-database-clone ~] mkdir /var/lib/mysql/ssl/
[root@prod-redcap-database-clone ~] openssl genrsa 2048 > /var/lib/mysql/ssl/ca-key.pem
[root@prod-redcap-database-clone ~] openssl req -new -x509 -nodes -days 365000 -key /var/lib/mysql/ssl/ca-key.pem -out /var/lib/mysql/ssl/ca.pem -subj "/C=US/ST=Alabama/L=Birmingham/O=University of Alabama at Birmingham/OU=Department of Medicine/CN=prod-redcap-database-clone"
[root@prod-redcap-database-clone ~] openssl req -nodes -days 365 -newkey rsa:2048 -keyout /var/lib/mysql/ssl/mysqlserver-key.pem -out /var/lib/mysql/ssl/mysqlserver-req.pem -subj "/C=US/ST=Alabama/L=Birmingham/O=University of Alabama at Birmingham/OU=Department of Medicine/CN=<prod-redcap-database-clone IP>"
[root@prod-redcap-database-clone ~] openssl rsa -in /var/lib/mysql/ssl/mysqlserver-key.pem -out /var/lib/mysql/ssl/mysqlserver-key.pem
[root@prod-redcap-database-clone ~] ll /var/lib/mysql/ssl/
[root@prod-redcap-database-clone ~] openssl x509 -req -in /var/lib/mysql/ssl/mysqlserver-req.pem -days 365 -CA /var/lib/mysql/ssl/ca.pem -CAkey /var/lib/mysql/ssl/ca-key.pem -set_serial 01 -out /var/lib/mysql/ssl/mysqlserver-cert.pem
[root@prod-redcap-database-clone ~] openssl verify -CAfile /var/lib/mysql/ssl/ca.pem /var/lib/mysql/ssl/mysqlserver-cert.pem

• Configure primary prod-redcap-database-clone to use the certs.
  • Added binlog modifications for prod-redcap-database-clone.

[root@prod-redcap-database-clone ~] vi /etc/my.cnf

# BINARY LOGGING #
log-bin
expire_logs_days                = 14
sync_binlog                     = 1
server_id                       = 1
log-basename                    = master1
binlog-format                   = mixed

[mysqld]
ssl_cert = /usr/local/etc/mysql/mysqlserver-cert.pem
ssl_key  = /usr/local/etc/mysql/mysqlserver-key.pem
ssl_ca   = /usr/local/etc/ssl/ca.pem

• Gather replication info from mariadb-backup's xtrabackup_binlog_info file.
  • Located on both servers under the backup folder.
  • 11.8 uses a different file mariadb_backup_binlog_info

Examples:

[root@prod-redcap-database-clone ~] cat /sourcedb/mariadb/backup/xtrabackup_binlog_info
mysql-bin.002019	7577250	0-1-1516628153

replica-server-vm root ~ # cat /misc/mariadb/backup/redcapdb/xtrabackup_binlog_info
master1-bin.000007	344	0-1-3291372

[root@replica-server-vm26 ~]# cat /misc/mariadb/redcapdb/mariadb_backup_binlog_info
replica-server-vm2026-bin.000009	203073806	0-3-26566455

• Modify my.cnf on replica database to set a different server_id from the primary database.

[root@prod-redcap-database-clone ~] vim /usr/local/etc/mysql/my.cnf

[mariadb]
# Primary's id is "1"
server_id = 2

• Configure replica database on replica-server-vm to connect to primary on prod-redcap-database-clone

[root@replica-server-vm ~] → MariaDB-11.4.9 > SET GLOBAL gtid_slave_pos = "0-1-3291372";
[root@replica-server-vm ~] → MariaDB-11.4.9 > CHANGE MASTER TO
											MASTER_HOST="prod-redcap-database-clone",
											MASTER_PORT=3306,
											MASTER_USER="repl",
											MASTER_PASSWORD="<Password>",
											MASTER_USE_GTID=slave_pos,
											MASTER_SSL=1,
											MASTER_SSL_VERIFY_SERVER_CERT=0;
[root@replica-server-vm ~] → MariaDB-11.4.9 > START REPLICA;

Note

Early on during the database unicode upgrade transition, I ran into the following error during replication:

Last_SQL_Error: Column 4 of table 'redcap_alerts_sent_log' cannot be converted from type 'varchar(573 octets)' to type 'varchar(764 octets) character set utf8mb4'

This fix in this case was to enable slave_type_conversions in MariaDB.

MariaDB Help Link: "Replication When the Primary and Replica Have Different Table Definitions"

STOP REPLICA;
SET GLOBAL slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY';
START REPLICA;

You can check the replica status (\G to make it easier to read):

root@localhost [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: <IP Address>
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: cloudrcdb01-bin.000061
           Read_Master_Log_Pos: 820559026
                Relay_Log_File: mysqld-relay-bin.000004
                 Relay_Log_Pos: 799435146
         Relay_Master_Log_File: cloudrcdb01-bin.000061
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 820559026
               Relay_Log_Space: 799435509
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 103
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-103-115635501
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 141
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 2064659
          Replicate_Rewrite_DB:
1 row in set (0.001 sec)

[rwattuab]

Promoting REDCap Database Replica to Primary

Helpful links:

• Changing a Replica to Become the Primary

Primary: MariaDB [(Primary)] FLUSH TABLES WITH READ LOCK;
Primary: MariaDB [(Primary)] SHOW MASTER STATUS;
Primary: MariaDB [(Primary)] SELECT @@global.gtid_binlog_pos;

And wait until you have the same position on the replica:

Replica: MariaDB [(Replica)] SHOW SLAVE STATUS;

The most important information to watch are Master_Log_File and Exec_Master_Log_Pos as when this matches the primary, it signals that all transactions have been committed on the replica.

Primary: MariaDB [(Primary)] SHUTDOWN;

Replica: MariaDB [(Replica)] STOP ALL SLAVES;
Replica: MariaDB [(Replica)] RESET SLAVE ALL;
Replica: MariaDB [(Replica)] SHOW MASTER STATUS;
Replica: MariaDB [(Replica)] SELECT @@global.gtid_binlog_pos;
Replica: MariaDB [(Replica)] SET @@global.read_only=0;