MySQL Replication

Sixhustle·2020년 10월 5일
0

RealMySQL

목록 보기
4/5

Docker를 이용해 Primary/Secondary MySQL 서버를 띄우고, DB Replication실습을 진행합니다.

Replication이란

2대 이상의 MySQL서버가 동일한 데이터를 담도록 실시간으로 동기화하는 기술입니다.

일반적으로 데이터를 변경(INSERT/UPDATE를 처리)하는 Primary서버데이터를 읽기만 하는 Secondary서버로 나뉜다.
Primary 서버는 반드시 1대이고, Secondary 서버는 1대 이상으로 구성될 수 있습니다.

Primary(master)

오직 1대의 Primary서버만 데이터의 일관성을 보장하기 위해 데이터 생성/변경/삭제할 수 있습니다.
Primary서버는 바이너리 로그를 활성화하여 실행되는 모든 Query를 바이너리 로그에 기록하고, Secondary서버가 변경 내역을 요청하면, 바이너리 로그를 넘겨줍니다.
Primary 프로세스 중 Binlog dump 스레드가 이 일을 전담하고, Secondary가 10대라면, Binlog dump스레드는 10개가 표시됩니다.

Secondary(slave)

바이너리 로그를 받아 올 Primary 서버의 정보(IP/Port/계정)를 가지고 있으면 Secondary가 되고, Primary서버와 데이터를 동일한 상태로 유지하기 위해 대부분 읽기 전용(Read only)으로 설정합니다.
Secondary서버는 변경 내역을 Relay log에 기록하고, 재실행함으로써 Secondary의 데이터를 Primary와 동일하게 유지합니다.

SQL기반 vs Row기반 복제

SQL기반의 복제를 데이터의 변경을 많이 유발하는 Query여도 SQL문장 하나만 Secondary로 전달되므로 네트워크 트래픽을 많이 유발하진 않습니다. 하지만 정상적으로 작동하려면, REPEATABLE-READ 이상의 트랜잭션 격리 수준을 사용해야 하며, 그로 인해 InnoDB 테이블에서는 레코드 간의 간격을 잠그는 Gap lock/Next Key lock이 필요해집니다.
Row기반 복제는 Primary/Secondary간 네트워크 트래픽을 많이 발생시킬 수 있지만, READ-COMMITTED 트랜잭션 격리 수준에서도 작동할 수 있으면, InnoDB 테이블에서의 잠금의 경합은 줄어듭니다.


MySQL Replication 실습

.cnf 설정

.cnf파일은 MySQL서버가 시작될 때, 읽는 설정 파일입니다. 여러 개의 디렉터리를 탐색하면서 처음으로 발견된 my.cnf 파일을 사용하게 됩니다.

.cnf 파일 위치 찾기

아래는 Docker로 생성한 MySQL서버에서 .cnf파일을 찾는 명령어 입니다.
아래의 우선순위대로 .cnf 파일을 읽고있습니다.
1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. ~/.my.cnf

root@e89fe0b90e8a:/# mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Primary .cnf 설정

# path: /docker/mysql-replication/primary/my.cnf
[mysqld]
server-id                       = 1				
log-bin                         = binary_log		
binlog_cach_size                = 5M
max_binlog_size                 = 512M
expire_logs_days                = 14
ParameterDescription
log-bin바이너리 로그의 파일명 입니다.
max_binlog_size바이너리 로그 파일의 최대 크기 입니다.
expire_logs_days바이너리 로그를 보관할 날짜 수 입니다.
binlog_cache_size바이너리 로그는 메모리의 임시 공간에 버퍼링했다가 디스크로 기록됩니다. 이 때 버퍼링용 메모리의 크기를 설정하는 변수입니다.

Secondary .cnf 설정

# path: /docker/mysql-replication/secondary/my.cnf
[mysqld]
server-id 			= 2
relay-log 			= relay_log
relay_log_purge 	= TRUE
read_only
ParameterDescription
relay-logCHANGE MASTER 명령을 실행하면 Secondary MySQL은 기본 경로에 relay log를 생성하게 됩니다. 이 때, 명시적으로 path를 변경해주는 옵션입니다.
relay_log_purgeTRUE 또는 1로 설정하면, 필요하지 않은 오래된 릴레이 로그를 자동으로 삭제합니다.
read_onlySecondary MySQL 서버는 읽기 전용으로 만드는 것이 일반적입니다. 이 때 읽기 전용으로 설정하는 변수입니다.

Primary/Secondary MySQL 서버 띄우기

Docker run Primary MySQL

$ docker run --name primary -v ~/docker/mysql-replication/primary:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=p -d mysql:latest

Docker run Secondary MySQL

Primary와의 차이점은 links 옵션을 통해 Primary/Secondary Container간 통신이 가능하도록 설정했습니다.

$ docker run --name secondary -v ~/docker/mysql-replication/secondary/my.cnf:/etc/my.cnf --link primary -e MYSQL_ROOT_PASSWORD=s -d mysql:latest

MySQL서버 및 MySQL 접속

# Host
user@iMac-Pro > docker exec -it secondary bash

# MySQL Server
root@84ca898f86d3:/# mysql -u root -p
Enter password:

# MySQL
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Primary MySQL 서버

Primary MySQL 상태 확인

아래는 Primary Server로써 "현재 binlog.000002파일에 기록되고 있으며, 현재까지 기록된 위치는 156이다."를 나타냅니다.
Position은 실제 파일의 바이트 수를 의미합니다.

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

복제 계정 준비

Secondary MySQL이 Primary서버에 접속해 바이너리 로그를 가져오기 위한 계정을 복제용 계정이라고 합니다.

복제용 계정은 Primary 서버에 미리 준비돼 있어야하고, 반드시 REPLICATION SLAVE권한을 가지고 있어야 합니다.

mysql> CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'replication_pass';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Query OK, 0 rows affected (0.01 sec)

계정 확인

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| p                | %         |
| replication_user | %         |
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

데이터 생성

CREATE DATABASE practice;

CREATE TABLE IF NOT EXISTS `practice`.`replication`
(
    `id`          bigint(20) NOT NULL AUTO_INCREMENT,
    `name`     varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

USE practice;

INSERT INTO replication(name) VALUES('sixhustle');

Primary MySQL의 데이터 Dump

Primary 서버에 존재하는 데이터(테이블 포함)를 Secondary 서버에 적재해야 합니다. mysqldump를 이용해 데이터를 복사해보겠습니다.

root@d970ee289929:~# mysqldump -u root -p --opt --single-transaction --hex-blob --master-data=2 --routines --triggers --all-databases > primary-dump.sql;
Enter password:

Primary > Host 파일 복사

Host에서 Primary 서버에 저장된 primary-dump.sql파일을 Host로 복사합니다.

docker cp primary:/primary-dump.sql .

Secondary MySQL 서버

Host > Secondary 파일 복사

Host로 복제된 primary-dump.sql파일을 Secondary 서버로 복사합니다.

docker cp primary-dump.sql secondary:/.

Secondary MySQL에 데이터 적재

Secondary 서버에 접속해서 MySQL에 데이터를 적재합니다.

root@e89fe0b90e8a:~# mysql -u root -p < primary-dump.sql
Enter password:

Replication 준비

복사한 primary-dump.sql파일의 22번 째쯤에 존재하는CHANGE MASTER ... 명령어를 복사합니다.

vi ./primary-dump.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1565; 

Secondary

아래의 3가지 명령어 CHANGE ..., START SLAVE, SHOW SLAVE STATUS를 실행합니다.
마지막 명령에서 Last_Errno, Last_IO_Errno 필드에 아무 값이 없다면 성공적으로 Replication이 구성된 것입니다.

mysql> CHANGE MASTER TO 
	MASTER_HOST='primary', 
	MASTER_USER='replication_user', MASTER_PASSWORD='replication_pass', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1565;
Query OK, 0 rows affected, 2 warnings (0.17 sec)

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: %
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 2510
               Relay_Log_File: e89fe0b90e8a-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2510
              Relay_Log_Space: 156
              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: NULL
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: 0
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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:
1 row in set (0.00 sec)

Trouble Shooting

primary/secondary 간 network통신

해당 설정은 어떻게 하는지 몰라서, local에서 primary/secondary .cnf파일을 만들고, secondary container에 primary container를 link하여 해결했습니다. Docker MySQL Replication 참조

caching_sha2_password

mysql 5.8부터는 password auth방식이 caching_sha2_passowrd라고 합니다. 복제 계정 생성시에 IDENFITIFED WITH mysql_native_password 설정하여 해결했습니다.
아래는 에러 내용입니다.

error connecting to master 'replication_user@mysql-primary:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

References

0개의 댓글