본문 바로가기
리눅스

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

변군이글루 2020. 9. 4. 15:17
반응형

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
### my.cnf
[mysqld]
bind_address=0.0.0.0
port=3306
user=mysql

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid_file=/usr/local/mysql/data/mysqld.pid
socket=/tmp/mysql.sock

character_set_server=utf8

### general log
general_log=ON
general_log-file=/usr/local/mysql/data/general.log

### error log
log_error_verbosity=1
log_error=/usr/local/mysql/data/error.log

### slow query log
slow_launch_time=5
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow-query.log

skip_name_resolve=ON

server_id=1

log_bin=/usr/local/mysql/data/mysql-bin
binlog_format=ROW
log_slave_updates=ON

sync_binlog=1

relay_log=/usr/local/mysql/data/relay-log
relay_log_index=/usr/local/mysql/data/relay-log.index
relay_log_purge=ON

expire_logs_days=7

[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

또는

/usr/local/mysql/bin/mysqld --validate-config

2. 슬레이브 서버 설정

  • 슬레이브 서버의 MySQL 구성 파일(my.cnf 또는 my.ini) 편집
vim /etc/my.cnf
### my.cnf
[mysqld]
bind_address=0.0.0.0
port=3306
user=mysql

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid_file=/usr/local/mysql/data/mysqld.pid
socket=/tmp/mysql.sock

character_set_server=utf8

### general log
general_log=ON
general_log-file=/usr/local/mysql/data/general.log

### error log
log_error_verbosity=1
log_error=/usr/local/mysql/data/error.log

### slow query log
slow_launch_time=5
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow-query.log

skip_name_resolve=ON

server_id=2

log_bin=/usr/local/mysql/data/mysql-bin
binlog_format=ROW

sync_binlog=1

read_only=1
relay_log=/usr/local/mysql/data/relay-log
relay_log_index=/usr/local/mysql/data/relay-log.index
relay_log_purge=ON

expire_logs_days=7

[client]
port=3306
socket=/tmp/mysql.sock
  • read_only: 슬레이브 서버를 읽기 전용으로 설정합니다.
  • slave-skip-errors: 이 옵션은 슬레이브에서 복제 중 발생하는 특정 에러를 무시하도록 설정합니다. "all" 값을 지정하면 모든 에러를 무시합니다. 에러 코드를 지정하여 특정 에러만 무시할 수도 있습니다.
  • my.cnf(/etc/my.cnf) 구문 오류 검사
./bin/mysqld --validate-config
728x90

3. 마스터에서 디비 덤프 파일 생성

mysql -hlocalhost -uroot -p'mysqlrootpassword1!' -e "FLUSH TABLES WITH READ LOCK;"
mysqldump -uroot -p'mysqlrootpw1!' --all-databases > alldatabases-backup.sql
mysql -hlocalhost -uroot -p'mysqlrootpassword1!' -e "UNLOCK TABLES;"

4. 마스터에서 슬레이브로 데이터 복사

scp alldatabases-backup.sql root@192.168.20.212:~
scp alldatabases-backup.sql root@192.168.20.213:~

5. 슬레이브에서 디비 덤프 복구

mysql -uroot -p'mysqlrootpassword1!' < alldatabases-backup.sql

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

마스터 서버에서 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;

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

  • 슬레이브 서버에서 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;

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

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

 

728x90
반응형
저작자표시

'리눅스' 카테고리의 다른 글

SSH 세션을 유지하는 방법  (0) 2020.09.14
CentOS 7에서 사용하지 않는 오래된 커널 패키지를 정리하는 방법(사용하지 않는 커널 삭제)  (0) 2020.09.08
unzip 명령어  (0) 2020.09.04
rdate 명령어  (1) 2020.09.04
CentOS 7에서 yum update를 실행할 때 kernel 패키지를 제외하는 방법  (0) 2020.09.04

'리눅스' Related Articles

티스토리툴바