Setup replication on MySQL for DR.
By using replication feature of MySQL, all CCS data of the primary site is replicated to the databases in the secondary site.
Here, as an example, settings of standard replication of MySQL is introduced. With the following settings, updated data in each site will be replicated each other.
For details of replication functions, refer to the MySQL website.
In this example, we assume that the following databases will be created for CCS applications in each site.
cimdb logdb crmdb cadb rfsdb
Primary site:
<my.cnf> #Common server-id=1001 auto_increment_increment=2 auto_increment_offset=1 innodb_flush_log_at_trx_commit=0 sync_binlog=0 slave_parallel_type=LOGICAL_CLOCK slave-parallel-workers=20 #Master Server Settings expire_logs_days=3 log-bin=mysql-bin #Slave Server Settings relay_log_info_repository = TABLE master_info_repository = TABLE relay_log_recovery = ON slave-skip-errors=1032,1062,1146,1051
You can check the master status with the following command.
mysql> show master status;
Secondary Site:
<my.cnf> #Common server-id=1002 auto_increment_increment=2 auto_increment_offset=2 innodb_flush_log_at_trx_commit=0 sync_binlog=0 slave_parallel_type=LOGICAL_CLOCK slave-parallel-workers=20 #Master Server Settings expire_logs_days=3 log-bin=mysql-bin #Slave Server Settings relay_log_info_repository = TABLE master_info_repository = TABLE relay_log_recovery = ON slave-skip-errors=1032,1062,1146,1051
You can check the master status with the following command.
mysql> show master status;
Start Replication Secondary site:
Login MySQL and execute the folloiwng commands.
mysql> CHANGE MASTER TO -> MASTER_HOST='Primary MySQL's address', -> MASTER_USER='database user for replication', -> MASTER_PASSWORD='database user's password', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.XXXXXX', -> MASTER_LOG_POS=Current position of Master log file, -> MASTER_CONNECT_RETRY=10 -> for channel 'Primary MySQL's address';
Example:
mysql> CHANGE MASTER TO -> MASTER_HOST='172.19.0.10', -> MASTER_USER='repl', -> MASTER_PASSWORD='replpassword', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=3220, -> MASTER_CONNECT_RETRY=10 -> for channel '172.19.0.10';
You can check status with the followin MySQL commands
mysql> show slave status\G;
If the slave process hasn’t started,yet start it.
mysql> start slave;
The settings for replication from the primary site (Master) to the secondary site (Slave) is completed.
Start Replication Primary site:(Optional)
In the case the replication from the secondary site (Master) to primary site (Slave) is needed.
Primary site:
Login MySQL and execute the folloiwng commands.
mysql> CHANGE MASTER TO -> MASTER_HOST='Secondary MySQL's address', -> MASTER_USER='database user for replication', -> MASTER_PASSWORD='database user's password', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.xxxxxxx', -> MASTER_LOG_POS=Current position of Secondary Master log file, -> MASTER_CONNECT_RETRY=10 -> for channel 'Secondary MySQL's address';
Example:
mysql> CHANGE MASTER TO -> MASTER_HOST='172.19.2.22', -> MASTER_USER='repl', -> MASTER_PASSWORD='replpassword', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=3220, -> MASTER_CONNECT_RETRY=10 -> for channel '172.19.2.22';
You can check status with the following MySQL command.
mysql> show slave status\G;
If the slave process hasn’t started,yet start it.
mysql> start slave;