[MYSQL] Replication (Master-Slave 동기화)

Replication 이란?

-. Replication은 '복제'라는 의미에 맞게 MySQL의 Master 서버의 데이터를 n개의 Slave 서버와 동기화 시켜주는 기능이다.

MySQL의 데이터를 실시간으로 백업하거나, DB서버의 부하를 분산하기 위하여 사용한다.

 

테스트 환경

-. 같은 서버에 다른 OS유저로 MySQL 2개를 기동한 상태

1. Master 서버

MySQL 8.0.27

Ip: 192.168.56.101

 

2. Slave 서버

Mysql 8.0.27

Ip: 192.168.56.101

 

---

1. Master 설정

1.1. Replication용 계정 생성 및 replication 권한 부여

create user 'repl_user'@'%' identified by 'mysql'; GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

 

1.2 my.cnf 설정

[mysqld] max_allowed_packet=1000M server-id=1 log-bin=mysql-bin binlog_format=ROW max_binlog_size=500M sync_binlog=1 expire_logs_days=7 ##binlog_do_db=repl_test

 

 

Parameter	Explain
max_allow_packet	패킷 값은 최대 1GB 까지 설정가능하다. 최대로 설정해두어도 되지만 너무 크게 하면 메모리 문제가 생길 수있다.
server-id	마스터 서버 아이디로 고유값이여야한다.
log-bin	바이너리 로그 경로 따로 설정하지 않으면, datadir 경로로 지정된다.
binlog_format	Statement-based Type – MySQL 3.23 이후로 도입된 방식으로 실행된 SQL을 그대로 Binary Log에 기록하는 방식 - Binary Log 사이즈는 작으나, SQL 실행 시점에 따라 적용되는 결과가 달라질 수 있음 (Time Function, UUID, User Defined Function) Row-based Type – MySQL 5.1부터 도입된 방식으로 변경된 행 자체를 BASE64로 Encoding하여 Binary Log에 기록하는 방식 - Master 에서 실행된 SQL이 Slave 에서 재 실행되지 않으나, 변경된 행이 많은 경우 Binary Log 사이즈가 비약적으로 커질 수 있음 Mixed Type (Statement + Row) – 기본적으로 Statement-Based Type으로 기록되나, 필요에 따라 Row-base Type으로 Binary Log에 기록되는 방식
max_bindlog_size	바이너리 로그 최대 사이즈
sync_binlog	1 -> 동기화 사용
expire_logs_days	보관 기간
binlog_do_db	입력하지 않으면 전체  database를 복제하고 입력하면 특정 db만 복제합니다.

 

1.3 mysql 재기동

 

2. Slave 설정

2.1 my.cnf 설정

[mysqld] max_allowed_packet=1000M server-id=2 log-bin=mysql-bin binlog_format=ROW max_binlog_size=500M sync_binlog=1 expire_logs_days=7 read_only=1

 

read_only

읽기 전용 DB 설정

 

2.2 mysql 재기동

 

3. Replication 적용[Master DB와 replication DB 완전 동기화 시키기]

 

3.1 Master DB 백업 

3.1.1 Master log reset reset master; 3.1.2 Master table lock flush tables with read lock; 3.1.3 마스터 상태 확인 show master status; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      156 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 3.1.4 DB 백업 mysqldump -u root -p --all-databases  > mysql1_data.sql 3.1.5 백업완료 후 테이블 락 해제 unlock tables;

 

3.2 Slave DB 복원

3.2.1 Slave DB에 백업 데이터 복원 mysql -u root -p --socket=/home/mysql2/mysql2_data/mysql.sock < mysql1_data.sql 3.2.2 Slave - Master 연결 reset slave; change master to master_host='192.168.56.101', master_user='repl_user', master_password='mysql', master_log_file='mysql-bin.000001', master_log_pos=156; 3.2.3 Replication 시작 start replica;  -. 8.0 이전 버전은 start slave 3.2.4 Replication 중지 stop replica;

 

3.3 Replication 동작 확인

show slave status\G; -. Master DB에 replication slave 권한이 재대로 부여되지 않으면 Salve_IO_Running=connection 으로 출력되며, 접속 에러가 난다.

 

 

---

동기화 검증

1. Master 서버에서 insert 작업

create table repl_test.test_tab94(c1 varchar(110), c2 varchar(110), c3 varchar(110), c4 varchar(110), c5 varchar(110),c6 date); DELIMITER $$ $$ CREATE PROCEDURE repl_test.insert_data_test_tab94() BEGIN DECLARE NUM1 INT DEFAULT 1; WHILE(NUM1 < 10000) do insert into repl_test.test_tab94 SELECT SUBSTR(MD5(RAND()),1,100) c1, SUBSTR(MD5(RAND()),1,100) c2, SUBSTR(MD5(RAND()),1,100) c3 ,SUBSTR(MD5(RAND()),1,100) c4 ,SUBSTR(MD5(RAND()),1,100) c5, sysdate(); set NUM1 = NUM1 + 1; COMMIT; end while; END; $$ DELIMITER ; call repl_test.insert_data_test_tab94();

 

Master server

Slave server

call repl_test.insert_data_test_tab94(); mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |    10002 | +----------+ 1 row in set (0.08 sec)

mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |        3 | +----------+ 1 row in set (0.13 sec) mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |     7932 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |     8219 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |     9769 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |    10002 | +----------+ 1 row in set (0.05 sec) mysql> select count(*) from repl_test.test_tab94; +----------+ | count(*) | +----------+ |    10002 | +----------+ 1 row in set (0.05 sec)

2. Master , Slave status

mysql> show master status\G; *************************** 1. row ***************************                         File: mysql-bin.000002                  Position: 4852130       Binlog_Do_DB:  Binlog_Ignore_DB:  Executed_Gtid_Set:

mysql> show slave status\G; *************************** 1. row ***************************              Slave_IO_State: Waiting for source to send event                   Master_Host: 192.168.56.101                   Master_User: repl_user                    Master_Port: 3306                Connect_Retry: 60                aster_Log_File: mysql-bin.000002     ead_Master_Log_Pos: 4852130               Relay_Log_File: rac1-relay-bin.000003               Relay_Log_Pos: 4852345  Relay_Master_Log_File: mysql-bin.000002          Slave_IO_Running: Yes       Slave_SQL_Running: Yes            Replicate_Do_DB:          eplicate_Ignore_DB:         Replicate_Do_Table:      eplicate_Ignore_Table:    Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                       Last_Errno: 0                       Last_Error:                    Skip_Counter: 0 Exec_Master_Log_Pos: 4852130         Relay_Log_Space: 4854745              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_UUID: 0a1692aa-5219-11ed-8df9-080027126ad9        Master_Info_File: mysql.slave_master_info                   SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates      Master_Retry_Count: 86400                   Master_Bind:  Last_IO_Error_Timestamp:  Last_SQL_Error_Timestamp:              Master_SSL_Crl:       Master_SSL_Crlpath:         Retrieved_Gtid_Set:          Executed_Gtid_Set:                  Auto_Position: 0   Replicate_Rewrite_DB:                Channel_Name:        Master_TLS_Version:   Master_public_key_path:     Get_master_public_key: 0       Network_Namespace:

 

# 참고

※ max_allowed_packet

 

MySQL max_allowed_packet

※ binlog_format

MySQL Replication 구성 시 Binlog format 결정하기 - MIXED vs ROW !!

 

[MySQL] Binary log 정리

 

 

Mysql 8.0 replication 설정 :: TRANDENT