MySQL 리플리케이션(replication)을 설정하는 방법

MySQL 리플리케이션(replication)을 설정하는 방법

테스트 환경

서버명	서버 아이피	역할	server-id	비고
node1	192.168.20.211	마스터	1	리플리케이션 계정 정보: repluser / replpassword
node2	192.168.20.212	슬레이브	2
node3	192.168.20.213	슬레이브	3

• 운영체제 릴리즈 정보

$ lsb_release -d
Description:	Ubuntu 22.04.2 LTS

• MySQL 버전 정보

$ /usr/local/mysql/bin/mysqld -V
/usr/local/mysql/bin/mysqld  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

1. 마스터 서버 설정

• 마스터 서버의 MySQL 구성 파일(my.cnf 또는 my.ini) 편집

vim /etc/my.cnf

[mysqld]
bind-address = 0.0.0.0
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /var/run/mysqld/mysqld.pid
socket = /tmp/mysql.sock

general_log = on
general_log_file = /usr/local/mysql/data/general.log
log-error = /usr/local/mysql/data/error.log
slow_query_log = on
slow_query_log_file = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes = /usr/local/mysql/data/not_using_indexes.log

server-id = 1
log_bin = /usr/local/mysql/data/mysql-bin.log
binlog_format = ROW
log-slave-updates

relay-log = mysql-relay-bin
relay-log-space-limit = 1G

[client]
port = 3306
socket = /tmp/mysql.sock

• bind-address: MySQL 서버가 바인딩할 IP 주소를 설정합니다. 0.0.0.0으로 설정하면 모든 IP 주소에서 접속이 가능합니다.
• port: MySQL 서버의 포트 번호를 설정합니다.
• user: MySQL 서버의 실행 사용자를 설정합니다.
• basedir: MySQL 서버의 기본 디렉토리 경로를 설정합니다.
• datadir: MySQL 서버의 데이터 디렉토리 경로를 설정합니다.
• pid-file: MySQL 서버의 프로세스 ID 파일 경로를 설정합니다.
• socket: MySQL 서버의 소켓 파일 경로를 설정합니다.
• general_log: 일반 쿼리 로그를 활성화합니다.
• general_log_file: 일반 쿼리 로그 파일의 경로를 설정합니다.
• log-error: 에러 로그 파일의 경로를 설정합니다.
• slow_query_log: 느린 쿼리 로그를 활성화합니다.
• slow_query_log_file: 느린 쿼리 로그 파일의 경로를 설정합니다.
• log-queries-not-using-indexes: 인덱스를 사용하지 않는 쿼리 로그 파일의 경로를 설정합니다.
• server-id: 마스터 서버의 고유한 식별자를 설정합니다.
• log_bin: 바이너리 로그 파일의 경로를 설정합니다.
• binlog_format: 바이너리 로그 형식을 설정합니다.
• log-slave-updates: 슬레이브 서버에서 발생하는 변경 사항도 마스터 서버의 바이너리 로그에 기록하도록 설정합니다.
• relay-log: 릴레이 로그 파일의 경로를 설정합니다.
• relay-log-space-limit: 릴레이 로그 파일의 용량 제한을 설정합니다.
• binlog-do-db: 이 옵션은 복제되는 바이너리 로그(binlog)에 포함될 데이터베이스를 지정합니다. 여기에 지정된 데이터베이스의 변경 내용만이 복제되며, 다른 데이터베이스의 변경 내용은 무시됩니다. 여러 개의 데이터베이스를 지정하려면 쉼표(,)로 구분하여 나열합니다.
• replicate-ignore-db: 이 옵션은 복제 과정에서 무시할 데이터베이스를 지정합니다. 여기에 지정된 데이터베이스의 변경 내용은 복제되지 않습니다. 여러 개의 데이터베이스를 지정하려면 쉼표(,)로 구분하여 나열합니다.
• replicate-wild-ignore-table: 이 옵션은 복제 과정에서 특정 패턴과 일치하는 테이블을 무시합니다. 와일드카드 문자인 '%'와 ''를 사용하여 패턴을 지정할 수 있습니다. 예를 들어, 'test_%'와 같이 지정하면 "test"로 시작하는 모든 테이블을 무시합니다. 여러 개의 패턴을 지정하려면 쉼표(,)로 구분하여 나열합니다.
• port: 클라이언트가 MySQL 서버에 접속할 때 사용할 포트 번호를 설정합니다.
• socket: 클라이언트가 MySQL 서버와 통신할 때 사용할 소켓 파일 경로를 설정합니다.

• my.cnf(/etc/my.cnf) 구문 오류 검사

./bin/mysqld --validate-config

2. 슬레이브 서버 설정

• 슬레이브 서버의 MySQL 구성 파일(my.cnf 또는 my.ini) 편집

vim /etc/my.cnf

[mysqld]
bind-address = 0.0.0.0
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /var/run/mysqld/mysqld.pid
socket = /tmp/mysql.sock

general_log = on
general_log_file = /usr/local/mysql/data/general.log
log-error = /usr/local/mysql/data/error.log
slow_query_log = on
slow_query_log_file = /usr/local/mysql/data/slow.log
log-queries-not-using-indexes = /usr/local/mysql/data/not_using_indexes.log

server-id = 2
read_only = 1
relay-log = mysql-relay-bin
relay-log-space-limit = 1G

[client]
port = 3306
socket = /tmp/mysql.sock

• read_only: 슬레이브 서버를 읽기 전용으로 설정합니다.
• slave-skip-errors: 이 옵션은 슬레이브에서 복제 중 발생하는 특정 에러를 무시하도록 설정합니다. "all" 값을 지정하면 모든 에러를 무시합니다. 에러 코드를 지정하여 특정 에러만 무시할 수도 있습니다.

• my.cnf(/etc/my.cnf) 구문 오류 검사

./bin/mysqld --validate-config

 

3. 마스터에서 슬레이브로 데이터 복제 설정

• 마스터 서버에서 MySQL에 로그인합니다.

REPLICATION 계정 생성

CREATE USER 'repluser'@'%' IDENTIFIED BY 'replpassword';

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

FLUSH PRIVILEGES;

• GRANT REPLICATION SLAVE 문을 사용하여 슬레이브 서버에 대한 복제 권한을 부여합니다.

use mysql;

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY 'replpassword';

• SHOW MASTER STATUS 문을 실행하여 마스터 서버의 바이너리 로그 파일 및 위치를 확인합니다.

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000250 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

UNLOCK TABLES;

4. 슬레이브에서 마스터로 연결 설정

• 슬레이브 서버에서 MySQL에 로그인합니다.
• CHANGE MASTER TO 문을 사용하여 마스터 서버에 대한 연결 설정을 구성합니다. 마스터 서버의 바이너리 로그 파일 및 위치, 복제 계정 정보를 지정합니다.

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='master_binlog_file', MASTER_LOG_POS=master_binlog_position;

CHANGE MASTER TO MASTER_HOST='192.168.20.211', MASTER_USER='repluser', MASTER_PASSWORD='replpassword', MASTER_LOG_FILE='mysql-bin.000250', MASTER_LOG_POS=157;

• START SLAVE 문을 실행하여 슬레이브 서버가 마스터 서버로부터 데이터 복제를 시작하도록 합니다.

FLUSH PRIVILEGES;

START SLAVE;

5. 리플리케이션 상태 확인

• 슬레이브 서버에서 SHOW SLAVE STATUS\G 문을 실행하여 리플리케이션 상태를 확인합니다.
  Slave_IO_Running과 Slave_SQL_Running이 Yes로 표시되어야 합니다.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.20.211
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000250
          Read_Master_Log_Pos: 157
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000250
             Slave_IO_Running: Connecting
            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: 157
              Relay_Log_Space: 157
              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: 2061
                Last_IO_Error: Error connecting to source 'repluser@192.168.20.211:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
               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: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 230716 21:19:50
     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, 1 warning (0.00 sec)

 

위의 단계를 따라 MySQL 리플리케이션을 구성할 수 있습니다. 이후 마스터 서버에서 발생하는 데이터 변경은 슬레이브 서버로 자동으로 복제되어 동기화됩니다. 리플리케이션은 데이터의 백업, 확장성, 고가용성 등을 위해 유용한 기능입니다.