Skip to content

MySQL Replication

Replication 이란?

Replication은 3.23.15부터 지원되기 시작한 기능으로 ‘복제’라는 사전적 의미에 맞게 마스터의 MySQL 서버의 데이터를 여러 대의 슬레이브 MySQL 서버의 데이터와 동기화 시켜주는 기능이다.
주로, MySQL의 데이터를 실시간으로 백업하거나, 데이터 서버의 부하분산을 하고자 할 때 많이 사용된다.

Dual-Master Replication을 구축하기 위해, 먼저 Master-Slave로 구성된 Replication 상태를 만들어야 한다.

How to Set Up Replication

MASTER 와 SLAVE 설치

MySQL을 master 와 slave 서버에 설치한다.
안정성을 위해 두 서버의 버전을 맞춰주는 것이 좋다. Replication 기능은 3.23.15부터 지원되기 시작하였으나
3.23.32부터 안정화되었다고 알려져 있으므로, 그 이상 혹은 최신 버전의 MySQL 을 설치하길 권장한다. mysql 의
같은 버전을 사용함은 물론, /etc/my.cnf 파일도 동일한 설정을 사용하고, 리플리케이션 부분만 조정한다.

MASTER 계정생성

slave 서버에서 master 서버에 접속할 수 있도록, master 서버에 계정을 만든다. 사용자를 추가해 주어야
한다는 말이다. 이 계정에 REPLICATION SLAVE 권한을 주어야 한다. replication에만 사용할 계정이라면
추가적인 권한은 주지 않아도 된다.
slave 서버에서master 서버에 접속할 계정과 패스워드에 권한을 부여하는 명령은 다음과 같다.

master mysql > GRANT REPLICATION SLAVE ON*.*
-> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

여기서 user_name은 중복되지 않는 이름이면 되며, user_host 는 slave로 만들 서버의 주소 혹은 도메인 네임을 적어준다. 이 주소의 slave 유저만 master 서버로 접속할 수 있다.
4.0.2 이전 버전의 MySQL에서는, REPLICATION SLAVE 권한이 없으므로, 다음과 같이 FILE 권한으로 대신한다.

master mysql > GRANT FILE ON*.*
-> TO 'user_name'@'user_host' IDENTIFIED BY 'user_password';

MASTER 데이터 SLAVE 에 복사

master 서버의 mysql data 디렉토리의 이미지 전체를 백업 받아, slave 서버의 데이터베이스에 복사한 후, 데이터 디렉토리에서 압축을 푼다.

HOT 백업

master mysql > FLUSH TABLES WITH READ LOCK;
master shell > tar cvf /tmp/mysql-snapshot.tar ~mysql
master mysql > UNLOCK TABLES;

slave shell > tar xvf /tmp/mysql-snapshot.tar

mysqldump 이용 백업

master Shell > mysqldump –u root –p 'password' -B db_name > dump_file.sql

MASTER 환경설정

Master 와 Slave 의 데이터 베이스 환경을 설정한다.
우선 master 서버를 설정하도록 한다.

master shell> vi /etc/my.cnf

master 서버는 디폴트로 구성이 되어 있을 것이므로, [service:mysqld] 섹션에 log-bin이 있는 지 확인한다.

[service:mysqld]
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

SLAVE 환경설정

다음은 slave 서버의 환경설정이다.

slave shell> vi /etc/my.cnf

[service:mysqld] 섹션으로 가서 server-id를 master 서버의 server-id와 다르게 설정한다.
본 문서에서는 2로 설정하도록 하겠다. slave 서버를 여러 대로 구축하고자 할 때에 각 slave 서버의 server-id는 각각 달라야 한다는 것에 주의하자. 2^32-1까지 가능하다.

[service:mysqld]
server-id = 2
master-host = xxx.xxx.xxx.xxx(user_host)
master-port = 3306
master-user = user_name
master-password = user_password

master 서버의 데이터를 백업 받았다면, slave 서버를 시작하기 전에 slave 서버의 데이터 디렉토리에
master 서버의 데이터를 복사해 둔다. mysqldump를 사용했다면, 다음으로 가서 먼저, slave 서버를 스타트한다.

SLAVE 서버 스타트

slave 서버를 스타트한다.

slave shell > /etc/init.d/mysqld start

SLAVE 덤프라일 LOAD

mysqldump를 사용해 백업 파일을 만들었다면, slave 서버에 덤프 파일을 로드시킨다.

slave shell > mysql &#8211;u root &#8211;p < dump_file.sql

MASTER 계정 설정

slave 서버에서 master-host, master-user, master-password 등의 설정을 다음과 같이 바꿀 수도 있다. 물론 /etc/my.cnf에서 설정하지 않았을 경우에도 쓸 수 있다.

slave mysql >  CHANGE MASTER TO 
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;

각 옵션의 최대 길이는 다음과 같다.

MASTER_HOST  60
MASTER_USER 16
MASTER_PASSWORD 32
MASTER_LOG_FILE 255

SLAVE 쓰레드 스타트

slave 쓰레드를 스타트한다.

slave mysql > START SLAVE;

SUCCESS CERTIFICATION

mysql/data/slave.err을 확인하여 다음과 같은 메시지가 있으면 성공적으로 설정된 것이다.

Slave I/O thread: connected to master 'user_name@user_host:3306',  replication started in log 'FIRST' at position 4

How to Set Up Dual-Master Replication

우선 이후에서는 지금까지 master 라고 칭했던 서버를 mysql1 서버라고 하고, slave라 칭했던 서버를
mysql2 서버라 하겠다. 듀얼 마스터 리플리케이션을 구축할 두 대의 서버에는 동일 버전의 최신 MySQL이 설치되어 있으며,
Master-Slave 리플리케이션이 구축된 상태에 있다고 간주한다.

이미 앞에서 리플리케이션 구축에 대해 자세히 설명하였으므로, 과정에 대해서만 기술하기로 하겠다.

SLAVE STOP

mysql2 서버로 이동한 후, mysql2 서버의 mysql 구동을 멈춘다.

mysql2 shell > /etc/init.d/mysqld stop

SLAVE LOG DELETE

mysql2 서버의 –bin log를 삭제한다.

SLAVE RESTER

mysql2 서버의 mysql을 구동시킨다.

mysql2 shell > /etc/init.d/mysqld start

GRANT REPLICATION SLAVE

d. mysql2 서버에서 GRANT REPLICATION SLAVE명령을 실행한다.
Dual-Master란 것이 서로가 서로의 master이자 slave가 되는 것이므로, 이전의 설치에서 slave였던
mysql2가 mysql1 서버의 유저를 slave 유저로 갖게 된다.

mysql2 mysql > GRANT REPLICATION SLAVE ON*.*
-> TO 'users_name'@'users_host' IDENTIFIED BY 'users_password';

MASTER SETUP

이제 mysql1 서버로 이동하여, 설정을 계속한다.
우선, mysql1 서버의 mysql 구동을 멈춘다.

mysql1 shell > /etc/init.d/mysqld stop

MASTER CONFIGURATION

mysql1 서버의 /etc/my.cnf 파일을 수정한다.
[service:mysqld] 섹션으로 가서 mysql2 서버를 마스터로 간주하도록 정보를 추가한다.

[service:mysqld]
server-id = 1 <= 그대로 두고, 아래 내용을 추가한다.
master-host = users_host
master-port = 3306
master-user = users_name
master-password = users_password

MASTER START

mysql1 서버의 mysql을 구동시킨다.

mysql1 shell > /etc/init.d/mysqld start

SUCCESS CERTIFICATION

mysql/data/mysql1.err을 확인하여 다음과 같은 메시지가 있으면 성공적으로 설정된 것이다.

Slave I/O thread: connected to master 'ccotti@222.112.137.172:3306',  replication started in log 'FIRST' at position 4

지금까지 별다른 문제없이 설치를 진행하였다면, 각 서버의 mysql 모니터에서 데이터를 입력하고, 두 서버가 서로 연동이 되는 것을 확인할 수 있을 것이다.

장애복구

위의 설정에서 두 대의 서버 중 한 대가 장애를 일으키는 경우 한 서버를 리부팅한다고 가정할 때, 별도의 설정이 없다면 기존의 MySQL 리플리케이션 구성에서는 두 서버 간의 동기화가 원활히 일어나지 않았다.
그런 경우 다음을 순서대로 진행하며, 장애를 복구할 수 있다.
우선 mysql1 서버를 재시작해야 한다고 가정하자.
1. mysql1의 mysql/data/ 의 mysql1-bin.*를 지운다.

2. mysql1의 mysqld를 시작한다.

mysql1 shell > /etc/init.d/mysqld start

3. mysql2의 mysql 모니터에서 다음 명령어를 실행한다.

mysql2 mysql > slave stop;
mysql2 mysql > slave reset;
mysql2 mysql > slave start;

참고

● master와 slave 데이터 일치 방법

  • master mysql을 정지시키고 대상 파일들을 백업(복사)
  • master mysql을 구동
    -> 이 후 변경사항들이 bin-log에 기록됨
  • slave에 백업한 DB 파일들을 복사 후 구동
    -> master의 bin-log를 참고하여 데이터 일치됨
    ※ 이 때, 복사한 파일의 소유자(mysql인지?) 확인 철저
    ※ my.cnf 설정에서 특정 DB를 선택한 경우 master와 slave 모두 동일하게 설정해야 함
    (한 쪽은 설정하지 않고 한 쪽은 설정한 경우 오동작)
    ※ my.cnf 주의사항 : mysql_safe 실행 시 DB_DIR 옵션에 따라 불러오는 위치 달라짐
    ● slave에서 LOAD DATA FROM MASTER’ 명령을
    사용하기 위해서는 replication 계정에 다음은 권한 추가 필요
  • SUPER, RELOAD, SELECT 권한을 replication 계정에 부여
  • LOAD DATA FROM MASTER 실행시 다음과 같은 로그가 쌓임
    080321 14:05:20 [ERROR] Delete link points outside datafile at 358148
    080321 14:05:20 [Note] Retrying repair of: './cacti/data_input_data' with keycache
    080321 14:05:20 [ERROR] Delete link points outside datafile at 358148
    080321 14:05:20 [ERROR] Delete link points outside datafile at 360276
    080321 14:05:20 [ERROR] Delete link points outside datafile at 35108
    080321 14:05:20 [Note] Retrying repair of: './cacti/data_local' with keycache
    080321 14:05:20 [ERROR] Delete link points outside datafile at 35108
    080321 14:05:20 [ERROR] Delete link points outside datafile at 286812
    080321 14:05:20 [Note] Retrying repair of: './cacti/data_template_data' with keycache
    080321 14:05:20 [ERROR] Delete link points outside datafile at 286812
    080321 14:05:20 [ERROR] Couldn't fix table with quick recovery: Found wrong number of deleted records
    080321 14:05:20 [ERROR] Run recovery again without -q
    ....

● 다음 명령을 통해 mysql의 내부cache를 clear시키고 쓰기 방지 가능
※ mysql 기본 테이블인 MyISAM 테이블을 사용할 경우

  • mysql> FLUSH TABLES WITH READ LOCK;
    ● 쓰기 방지 해제 명령
  • mysql> UNLOCK TABLES;
    ● slave의 mysql을 replication 미적용하고 구동 방법
  • /usr/local/bin/mysqld_safe –skip-slave-start &
    ● slave 동작 구동 방법
  • mysql> start slave;
    ※ slave 설정 미인식 등의 문제 발생 시
    mysql> change master to 명령을 사용하여 설정
    ● replication 정상동작 확인
  • mysql> show processlist 상세한 내용 확인
    mysql> show processlist;
    +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
    | 10 | root | localhost | NULL | Query | 0 | NULL | show processlist |
    | 11 | system user | | NULL | Connect | 12 | Waiting for master to send event | NULL |
    | 12 | system user | | NULL | Connect | 31 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
    +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
    3 rows in set (0.00 sec)

    mysql>
  • mysql> show slave status 상세한 내용 확인
    mysql> show slave_status;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave_status' at line 1
    mysql> show slave status;
    +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
    +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    | Waiting for master to send event | 211.239.127.186 | slave | 3306 | 60 | mysql-bin.000002 | 4836800 | nw2nms-relay-bin.000002 | 233387 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 4836800 | 233387 | None | | 0 | No | | | | | | 0 |
    +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    1 row in set (0.00 sec)

    mysql>
  • MASTER 서버에서 show master status 의 상세내용 확인
    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 | 4836800 | | |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    mysql>
  • error 로그 확인
    080321 14:06:01 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 4603648, relay log './nw2nms-relay-bin.000001' position: 4
    080321 14:06:01 [Note] Slave I/O thread: connected to master 'slave@211.239.127.186:3306', replication started in log 'mysql-bin.000002' at position 4603648
Published inLinux