Mysql 리플리케이션

1. MySQL 리플리케이션이란

1-1) 리플리케이션의 정의

MySQL 리플리케이션이란 하나의 데이터 베이스 서버(Master)에 있는 데이터를 하나이상의 다른 데이터베이스 서버(Slave)에

복제하는 것을 의미합니다. 마스터 서버가 죽는 경우 슬레이브 서버를 통해서 데이터를 이용할 수 있습니다.

주로 데이터를 실시간으로 백업하거나, 데이터 서버의 부하분산을 하고자 할 때 많이 사용됩니다.

2. MySQL 리플리케이션 구성

※ 테스트 환경 : CentOS 7.5 MySQL 8.0

마스터 슬레이브 동일한 환경에서 테스트 진행했습니다.

2-1) master 서버 설정

마스터 서버의 my.cnf 파일을 수정해줍니다.

# vi /etc/my.cnf

[mysqld]

log-bin=mysql-bin // 로그 파일명

max_binlog_size=100M // 로그 파일 크기

expire_logs_days=7 // 로그 보존 주기

server_id = 2 // 서버 식별자

binlog_do_db=test1 // 리플리케이션 DB명

binlog_do_db=test2

* 서버 식별자(server_id)의 경우 리플리케이션을 구성할 master 와 slave 들 간의 server_id가 중복되어서는 안됩니다.

Mysql 8.0 에서는 server_id 의 기본값이 1로 설정되어있습니다.

* master 에서는 log-bin 설정이 필요한데 바이너리 로그파일의 경로나 파일명을 지정하는 설정입니다. 바이너리 로그는 DB 에

가해지는 변경사항 기록이 별도로 저장되는 파일을 의미하는데, master에서만 생성하도록 하는게 일반적이고 slave는 바이너리

로그를 받아와서 릴레이 로그로 저장하게 됩니다.

* binlog_do_db 의 경우 해당 부분을 생략 시에는 전체 DB 를 리플리케이션을 진행한다는 의미이며, 전체가 아닌 여러개의 DB

를 리플리케이션 할 경우에는 binlog_do_db=db 명 으로 하나씩 생성해주면 됩니다.

설정 변경 후 mysql 를 재 시작해 줍니다.

2-2) 사용자 권한 추가

Mysql를 외부에서 접근할 수 있는 권한을 추가해줍니다.

Mysql 8.0 의 경우 사용자의 기본 플러그인이 caching_sha2_password로 설정되어있습니다. 이 경우 SHA-256 해시를 통해서

구현하기 때문에 클라이언트 쪽에서도 키를 받아서 진행해야 합니다. 따라서 기존에 5.7 에서 사용하던 플러그인인

mysql_native_password 를 통해 외부에서도 접근하도록 설정해놓았습니다.

Mysql> CREATE USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘비밀번호’;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select host,user,plugin from user; // 플러그인 확인

+-----------+------------------+-----------------------+

| host | user | plugin |

+-----------+------------------+-----------------------+

| % | root | mysql_native_password |

| localhost | mysql.infoschema | mysql_native_password |

| localhost | mysql.session | mysql_native_password |

| localhost | mysql.sys | mysql_native_password |

| localhost | root | caching_sha2_password |

+-----------+------------------+-----------------------+

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '비밀번호';

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

2-3) Master 서버 데이터 백업

master 와 slave 를 동일한 상태로 만들어야 하기 때문에 덤프를 뜰 때 데이터가 추가가 되지 않도록 설정해주어야 합니다.

[Master]

mysql> flush tables with read lock; // 데이터를 더이상 쓰지 못하게 lock 걸어놓습니다.

Query OK, 0 rows affected (0.00 sec)

lock 을 걸어놓은 이후에 mysql 덤프를 진행합니다.

# mysqldump -u root -p --all-databases > all.sql

Enter password:

# ll

-rw-r--r-- 1 root root 936139 7월 26 09:21 all.sql

덤프 뜬 이후에 mysql 에 접속하여 mysql 의 bin 로그 번호와 pos값을 확인합니다.

[Master]

mysql> show master status;

+-----------------------+-----------+-------------------+-----------------------+-------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------------+-----------+-------------------+-----------------------+-------------------------+

| binlog.000014 | 438 | | | |

+-----------------------+-----------+-------------------+-----------------------+-------------------------+

1 row in set (0.00 sec)

mysql> unlock tables; // DB쓰기를 해제합니다.

Query OK, 0 rows affected (0.00 sec)

2-4) Slave 서버 데이터 복사

슬레이브 서버에서도 /etc/my.cnf 변경해줍니다.

# vi /etc/my.cnf

[mysqld]

server_id = 3

replicate-do-db=test1 // 리플리케이션 DB명 (생략 시 전체 DB를 리플리케이션합니다.)

slave-skip-errors=1062 // 에러코드 발생시 오류 SKIP

* slave-skip-errors 의 경우 해당 에러코드가 발생하면 오류를 스킵한다는 내용입니다.

1062에러코드의 경우 같은 키값을 가진 두개가 들어가면 에러가 발생하면서 리플리케이션을 중단되게 됩니다.

해당 오류의 경우 db 설계상 같은 키값을 가진 값이 두개가 들어갈 필요가 없기 때문에 에러메시지를 무시하도록 설정해두는

것이 좋습니다.

FTP 나 rsync, scp 를 이용해서 마스터 서버에 있는 all.sql 파일을 슬레이브 서버로 가져옵니다.

슬레이브 서버에서 all.sql 파일을 밀어넣어 준 뒤에 mysql를 재시작합니다.

# mysql -u root -p < all.sql

Enter password:

# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

2-5) master와 slave 연동

slave 서버에서 master 로 연동하는 쿼리를 작성해줍니다.

[Slave]

mysql> change master to

-> master_host='MASTERIP주소',

-> master_user='root',

-> master_password='비밀번호',

-> master_log_file='binlog.000014',

-> master_log_pos=438;

Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

쿼리 옵션 설명

* master_host=hostname : 리플리케이션을 진행하는 마스터의 IP 주소 혹은 hostname

* master_user=master_user : 슬레이브가 서버에 접속할 때 인증하는 유저 정보.

* master_password=password : 서버에 접속할 때 인증하는 사용자 패스워드

* master_port=port_number : 마스터 서버의 포트번호

* master_log_file=log_file_number : bin-log 파일 번호

* master_log_pos=log_pos_number : position 값

2-6) 연동 확인

[ Master 서버 ]

mysql> show master status\G;

*************************** 1. row ***************************

File: binlog.000014

Position: 438

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

[ Slave 서버 ]

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: MASTERIP주소

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000014 //마스터 서버의 bin-log 파일과 pos값이 같은지 확인

Read_Master_Log_Pos: 438

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 319

Relay_Master_Log_File: binlog.000014

Slave_IO_Running: Yes // Slave_IO_Running과 Slave_SQL_Running이 YES이어야 함.

Slave_SQL_Running: Yes

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: 438

Relay_Log_Space: 527

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 // 슬레이브 서버의 쿼리가 지연되는지 나타내는 값으로 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: 2

Master_UUID: f584a8f6-925a-11e8-a871-fa163e882fd8

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave 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:

1 row in set (0.00 sec)

[ 확인 테스트 ]

마스터 서버에서 데이터베이스 1개 생성 후 슬레이브쪽에서 확인

[Master 서버]

mysql> create database hello;

Query OK, 1 row affected (0.09 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| hello |

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

+--------------------+

6 rows in set (0.00 sec)

[Slave 서버]

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| hello |

| information_schema |

| mysql |

| performance_schema |

| sys |

| test1 |

+--------------------+

6 rows in set (0.09 sec)

3. 무중단 리플리케이션 작업 방법

장애 발생 시에 Master 서버와 Slave 서버 간에 DB 씽크가 맞지 않아 다시 리플리케이션을 걸어주어야 할 경우가 있습니다.

이 때 마스터 서버쪽에서 mysql 를 중단시키지 않고 작업을 진행할 수 있습니다.

테스트 환경 : CentOS 7.5 MySQL 5.7

3-1) Slave 서버 중지

mysql> stop slave;

3-2) Master 서버 데이터 백업

이때는 무중단으로 진행하기때문에 위의 상황과 다르게 lock 를 걸지 않고 진행합니다.

# mysqldump -u root -p --master-data=2 --all-databases > all.sql

-–master-data 옵션은 바이너리 로그 파일 이름과 위치(pos)값을 덤프 뜰 때 작성해줍니다. 1 로 설정될 경우 Change master

to 구문이 실제 실행 가능한 형태로 포함되고, 2 로 설정되면 SQL 코멘트 형태로 참조할 수 있습니다.

3-3) slave 데이터 복사, 바이너리 로그 파일과 pos 확인

master 쪽에서 덤프뜬 것을 Slave 로 FTP 나 rsync, scp 를 이용해서 all.sql 파일을 받아옵니다.

# mysql -u root -p < all.sql

Enter password:

바이너리 로그 파일과 pos 값을 찾아야 하는데, dump 뜰 때의 –-master-data 옵션을 통해 쿼리를 참조해놓았으므로, 바이너리

파일과 pos 값을 확인할 수 있습니다.

[Slave]

# head -n 30 all.sql

-- MySQL dump 10.13 Distrib 5.7.14, for Linux (x86_64)

--

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.7.14-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Position to start replication or point-in-time recovery from

--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

--

-- Current Database: `mysql`

--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql`;

3-4) Master서버 pos 값 확인

마스터 서버쪽에서 pos값을 확인해야 합니다.

[Master]

mysql> show binlog events in 'mysql-bin.000003' from 154 limit 3;

+-----------------------+-----+-----------------------+-------------+-------------+--------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+-----------------------+-----+-----------------------+-------------+-------------+--------------------------------------+

| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000003 | 219 | Query | 1 | 296 | BEGIN |

| mysql-bin.000003 | 296 | Table_map | 1 | 362 | table_id: 143 (wordpress.wp_options) |

+-----------------------+-----+-----------------------+-------------+-------------+--------------------------------------+

3 rows in set (0.00 sec)

덤프뜰 때의 pos 값을 기준으로 행해진 작업 3개를 본다는 의미이며, 그 다음의 pos 값을 기억해야 합니다.

3-5) Slave서버 설정 변경

slave 서버에서 master 로 연동하는 쿼리를 작성해줄 때 바로 다음번호의 pos 값을 넣어준 뒤에 slave를 시작해줍니다.

[Slave]

# mysql -u root -p

Enter password:

mysql> change master to master_log_file='mysql-bin.000003', master_log_pos=219;

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

3-6) 연동 확인

[ Master 서버 ]

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 261018

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

[ Slave 서버 ]

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: MASTERIP주소

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 261018

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 261119

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 261018

Relay_Log_Space: 261326

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: 1749f485-8e93-11e8-8c6c-fa163e97235a

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave 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:

1 row in set (0.00 sec)