IDC MySQL(MASTER)와 RDS MySQL(Replication) 연결하기

IDC MySQL(MASTER)와 RDS MySQL(Replication) 연결하기

IDC에 있는 MySQL과 RDS의 MySQL을 동기화하여 마이그레이션이 실시간으로 이루어지도록 한다.

마스터 서버에서 작업

1) Replication 계정 생성

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'password';

 

2) my.cnf 설정 변경

[mysqld]
log-bin=mysql-bin
server-id=1

 

3) 마스터 status 확인

[mysql] show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000005
        Position: 327
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

리플리케이션 서버에서 작업

1) 마스터 서버 설정 프로시저 실행

모든 IP 허용일 경우
mysql> call mysql.rds_set_external_master(
  'endpoint',
  3306,
  'repl_user',
  'SomePassW0rd',
  'mysql-bin-changelog.0777',
  120,
  0);

특정 IP나 도메인만 허용할 경우
mysql> call mysql.rds_set_external_master(
  'endpoint',
  3306,
  'repl_user'@'domain.com',
  'SomePassW0rd',
  'mysql-bin-changelog.0777',
  120,
  0);

 

2) 에러 쿼리를 스킵하도록 설정 (설정 안 할 경우 쿼리 오류가 생기면 동기화가 중지됨)

mysql> CALL mysql.rds_skip_repl_error;

 

3) 리플리케이션 시작 프로시저 및 중지 프로시저

mysql> CALL mysql.rds_start_replication;
mysql> CALL mysql.rds_stop_replication;

 

4) 바이너리 로그 기간 지정 프로시저 MySQL은 최대 7일(168시간)

call mysql.rds_set_configuration('binlog retention hours', 24);

 

5) show slave status\G; 로 확인

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: Master_Host IP
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 124091627
                Relay_Log_File: relaylog.000004
                 Relay_Log_Pos: 776
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_replication_status,mysql.plugin,mysql.rds_history,innodb_memcache.config_options
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 124091627
               Relay_Log_Space: 1670
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            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: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.00 sec)

 

MariaDB의 경우 아래 작업을 추가 진행

1) MariaDB 10.2 버전 이상일 경우 리플리케이션 시작 프로시저를 실행해도 동기화가 실행되지 않음

MariaDB [(none)]> CALL mysql.rds_start_replication;
Query OK, 4 rows affected (0.04 sec)

MariaDB [(none)]> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: No

MariaDB [(none)]> select * from mysql.rds_replication_status;
+----+---------------------+------------------+------------+---------------------+---------------+-------------+
| id | action_timestamp    | called_by_user   | action     | mysql_version       | master_host   | master_port |
+----+---------------------+------------------+------------+---------------------+---------------+-------------+
|  1 | 2019-08-20 11:47:02 | root@10.0.10.124 | stop slave | 10.3.13-MariaDB-log | Master_Host IP |        3306 |
+----+---------------------+------------------+------------+---------------------+---------------+-------------+

 

2) 액션을 시작으로 업데이트하여 리플리케이션을 시작하고 시간이 약간 지난 후 slave 상태 값을 확인한다.

MariaDB [(none)]> update mysql.rds_replication_status set action='start slave';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> select * from mysql.rds_replication_status;
+----+---------------------+------------------+-------------+---------------------+---------------+-------------+
| id | action_timestamp    | called_by_user   | action      | mysql_version       | master_host   | master_port |
+----+---------------------+------------------+-------------+---------------------+---------------+-------------+
|  1 | 2019-08-20 12:00:05 | root@10.0.10.124 | start slave | 10.3.13-MariaDB-log | Master_Host IP |        3306 |
+----+---------------------+------------------+-------------+---------------------+---------------+-------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: Master_Host IP
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 124091391
                Relay_Log_File: relaylog.000004
                 Relay_Log_Pos: 540
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_replication_status,mysql.plugin,mysql.rds_history,innodb_memcache.config_options
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 124091391
               Relay_Log_Space: 1434
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            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: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.01 sec)

 

RDS Instance Read Only로 변경하기

1) RDS 파라미터에서 read_only 파라미터의 값을 1로 변경하기

 

2) 설정 확인

MariaDB [(none)]> SHOW global variables like "%read_only%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
3 rows in set (0.01 sec)