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.cnfPrimary .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| Parameter | Description |
|---|---|
| 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| Parameter | Description |
|---|---|
| relay-log | CHANGE MASTER 명령을 실행하면 Secondary MySQL은 기본 경로에 relay log를 생성하게 됩니다. 이 때, 명시적으로 path를 변경해주는 옵션입니다. |
| relay_log_purge | TRUE 또는 1로 설정하면, 필요하지 않은 오래된 릴레이 로그를 자동으로 삭제합니다. |
| read_only | Secondary 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:latestDocker 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:latestMySQL서버 및 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
- Real MySQL
- Docker MySQL Replication
- my.cnf File 위치
- Docker Network
