기술/AWS
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)소중한 공감 감사합니다