UPSSO WINDOWS ON-PREMISES - DISASTER RECOVERY DATABASE SETUP

This document provides instructions on how to set up DR (disaster recovery) for the UPSSO MySQL database for Windows On-Premises solution.

UPSSO DISASTER RECOVERY ARCHITECTURE

PREREQUISITES

  • UPSSO DC application should be up and running.

BACKUP DC DATABASE

  1. Login into the UPSSO DC server.
  2. Open the command prompt and execute the following commands,
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

mysqldump --all-databases --user=root --password --master-data > upsso_mysql_dump.sql 

UPSSO DR DATABASE SETUP

  1. Login into UPSSO DR server
  2. Copy the DC database dump (“upsso_mysql_dump.sql”) file into the “c:\tmp” folder.
  3. Open “MySQL 8.0 Command Line Client” from the start menu. Enter MYSQL_ROOT_PASSWORD.
  4. Execute the following commands in MySQL prompt,
source C:\tmp\upsso_mysql_dump.sql

5. Execute the following commands,

SET PERSIST server_id=2; 
SET PERSIST_ONLY gtid_mode=ON; 
SET PERSIST_ONLY enforce_gtid_consistency=true; 
RESTART;

6. Execute the following commands, (Replace <DC_MYSQL_HOST> and <DC_MYSQL_PORT> with appropriate values)

CHANGE MASTER TO MASTER_HOST='<DC_MYSQL_HOST>', MASTER_PORT=<DC_MYSQL_PORT>, MASTER_USER='replica', MASTER_PASSWORD='<MYSQL_ROOT_PASSWORD>', MASTER_AUTO_POSITION=1;

START SLAVE;

7. Execute the following command to make sure the MySQL replication is running fine,

SHOW SLAVE STATUS;

8. Make sure the replication works by adding a row in the DC database table and verify that the same is available in the DR database table.

DISASTER RECOVERY FAILOVER

This section explains how to failover UPSSO production (DC) to the DR environment and switch back to the DC environment. UPSSO DR architecture supports ACTIVE-PASSIVE setup. When DC application is up DR application should be down and vice versa.

FAILOVER TO DR ENVIRONMENT

  1. Make sure the IIS server on the DC environment is stopped.
  2. Make sure the “UPSSO MFA RADIUS SERVER” Windows Service is stopped on the DC environment.
  3. Make sure the “MySQL80” Windows Service is stopped on the DC environment.
  4. Login into the DR environment database machine.
  5. Open “MySQL 8.0 Command Line Client” from the start menu. Enter MYSQL_ROOT_PASSWORD.
  6. Stop slave by executing the following command,
STOP SLAVE;

7. Start the “UPSSO MFA RADIUS SERVER” Windows Service in the DR environment.

8. Start the IIS server in the DR environment.

SWITCHING BACK TO DC ENVIRONMENT

  1. Stop the IIS server in the DR environment.
  2. Stop the “UPSSO MFA RADIUS SERVER” Windows Service on the DR environment.
  3. Login into the UPSSO DR environment database machine.
  4. Execute following commands from the command prompt to take the backup of DR database,
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

mysqldump --all-databases --user=root --password --master-data > upsso_dr_mysql_dump.sql

5. Login into the DC environment database machine.

6. Copy the DR database dump taken above (“upsso_dr_mysql_dump.sql”) into the “c:\tmp” folder.

7. Start the “MySQL80” Windows Service

8. Open “MySQL 8.0 Command Line Client” from the start menu. Enter MYSQL_ROOT_PASSWORD.

9. Execute the following command in mysql prompt,

source C:\tmp\upsso_dr_mysql_dump.sql

10. Execute the following commands,(Replace <REPLICA_USER_PASSWORD> with MYSQL_ROOT_PASSWORD)

SET PERSIST server_id=1;
SET PERSIST_ONLY gtid_mode=ON;
SET PERSIST_ONLY enforce_gtid_consistency=true;
drop user 'replica'@'%';
create user 'replica'@'%' identified by '<REPLICA_USER_PASSWORD>';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
RESTART;

11. Login into the DR environment database machine.

12. Open “MySQL 8.0 Command Line Client” from the start menu. Enter MYSQL_ROOT_PASSWORD.

13. Start slave by executing the following command,

START SLAVE;

14. Execute the following command to make sure the MySQL replication is running fine,

SHOW SLAVE STATUS;

15. Make sure the replication works by adding a row in the DC database table and verify that the same is available in the DR database table.

16. Start the IIS server in the DC environment

17. Start the “UPSSO MFA RADIUS SERVER” Windows Service in the DC environment