wiki:MariaDB 복제

MariaDB 복제

복제는 마스터(primary) 서버의 바이너리 로그 파일을 슬레이브(replica) 서버에 전달해서 이루어진다. 이 때 마스터 서버의 바이너리 로그 포맷은 ROW로 지정하는 것이 가장 안전하다.

1. 복제 설정

마스터 서버가 옛날 버전이어야(슬레이브 서버가 최신 버전) 제대로 동작한다.

1.1. 마스터 서버 설정

MariaDB 설정 파일에 다음과 같이 바이너리 로그를 설정하고 server-id를 지정한 다음 MariaDB 서버를 재시작한다.

# incremental 백업을 하거나 복제를 하려면 바이너리 로그를 저장해야 한다.
# 로그 파일 기본이름은 명시적으로 지정해주는 것을 권장한다. 생략하면 호스트 이름을
# 사용하는데, 호스트 이름이 바뀔 경우에는 따라서 변경되기 때문이다.
log-bin=mysqld-bin
# ROW 포맷이 가장 안전
binlog_format=row
# 복제를 하지 않으려면 server-id=0으로 지정하거나
# 디폴트 값이 0이므로 server-id를 지정하지 않으면 됨.
# 10.2부터는 디폴트 값이 1로 바뀌고 0은 더이상 유효하지 않음.
server-id=1
# InnoDB 트랜잭션 복제를 하기 위해서 마스터에서
# innodb_flush_log_at_trx_commit=1과 sync_binlog=1을 지정.
# innodb_flush_log_at_trx_commit의 디폴트 값이 1이므로 그냥 생략해도 무방
sync_binlog=1
# 바이너리 로그 포맷을 ROW로 지정했기 때문에 함수를 만들 때 실수해도
# 복제하는 데 문제가 없다. 따라서 NON DETERMINISTIC 함수도 만들 수 있게끔
# log_bin_trust_function_creators를 1로 지정한다. 디폴트는 0.
log_bin_trust_function_creators=1

1.2. 마스터에 복제 계정 만들기

REPLICATION SLAVE 권한을 가진 MariaDB 계정을 마스터 서버에 추가한다. 이 계정의 비밀번호를 master.info 파일에 암호화하지 않고 저장하므로 일반 계정을 사용하지 않고 복제를 위한 별도의 계정을 만드는 것이 좋다.

MariaDB> CREATE USER 'repl'@'61.78.63.109' IDENTIFIED BY 'qhrwp!21';
MariaDB> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'61.78.63.109';

1.3. 슬레이브 서버 설정

MariaDB 설정 파일에 다음과 같이 릴레이 로그를 설정하고 server-id를 마스터나 다른 슬레이브와 다르게 지정한 다음 MariaDB 서버를 재시작한다.

# 복제를 하지 않으려면 server-id=0으로 지정하거나
# 디폴트 값이 0이므로 server-id를 지정하지 않으면 됨.
# 10.2부터는 디폴트 값이 1로 바뀌고 0은 더이상 유효하지 않음.
server-id=2
# 로그 파일 기본이름은 명시적으로 지정해주는 것을 권장한다. 생략하면 호스트 이름을
# 사용하는데, 호스트 이름이 바뀔 경우에는 따라서 변경되기 때문이다.
relay-log=mysqld-relay-bin
# 슬레이브 서버를 읽기 전용으로 만듬. SUPER 권한을 가진 계정과 복제 슬레이브
# 쓰레드만 데이터를 변경할 수 있다. 실수로 어플리케이션이 마스터 서버 대신
# 슬레이브 서버의 데이터를 수정하는 사고를 막을 수 있다.
read_only
# 슬레이브 서버를 수정할 일이 없으면 바이너리 로그 파일을 저장할 필요 없음.
disable-log-bin
# 마스터 서버에서 SHOW SLAVE HOSTS 명령을 내렸을 때 보이는 슬레이브 서버 이름
# OS의 이름과 달라도 무방하다.
# 지정하지 않으면 SHOW SLAVE HOSTS 명령에서 Host 컬럼에 아무 것도 나오지 않는다.
report-host=venus
# 슬레이브에서 에러가 발생했을 때 복제를 계속할 것인지 설정. 디폴트는 OFF.
;slave_skip_errors=all

low-priority-updates=1과 delay-key-write=ALL 옵션을 추가하면 슬레이브 서버의 속도(아마도 읽기 속도) 향상에 도움이 될 수 있다고 하는데, 그럴 필요까지 있는지는 모르겠다.

1.4. 바이너리 로그 좌표 포함한 마스터 백업

—master-data를 지정하면 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문을 백업 파일에 기록하므로 슬레이브를 셋업하는 용도로 쓸 때 편하다. 슬레이브를 셋업하는 용도로 쓰지 않을 거라면 —master-data 대신에 -master-data=2를 지정해야 한다.

mysqldump --master-data --flush-logs --delete-master-logs --all-databases --events > fullbackup.sql
  • —master-data[=value]
    • 바이너리 로그가 활성화되어 있어야 한다.
    • 마스터에서 백업받은 것을 가지고 슬레이브를 셋업하기 위해서 사용한다. 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문을 백업 파일에 기록한다.
    • 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
    • value 값의 디폴트는 1이다. 따라서 1일 때는 값을 생략할 수 있다.
    • value가 2이라면 CHANGE MASTER TO 문이 주석으로 삽입된다. 주석으로 삽입하기 때문에 실제 영향은 없다.
    • —single-transaction을 명시적으로 적어주지 않는다면 —lock-all-tables 옵션이 자동으로 지정된다.
    • —lock-tables 옵션을 자동으로 끈다.
  • —single-transaction
    • 쓰기를 막지 않고도 일관성있게 데이터베이스를 백업할 수 있다.
    • InnoDB 같은 트랜잭션을 지원하는 테이블에만 유용하다. MyISAM 같은 트랜잭션을 지원하지 않는 테이블에서는 논리적인 일관성이 보장되지 않는다.
    • —lock-tables과 같이 쓸 수 없다.
    • MySQL Cluster 테이블에는 사용할 수 없다. (확인 필요)
  • —lock-all-tables, -x
    • 백업을 하는 동안 모든 데이터베이스를 읽기만 가능하도록 잠근다(READ LOCK).
    • 이 옵션을 설정하면 —single-transaction과 —lock-tables 옵션은 자동으로 꺼진다.
    • 데이터베이스의 일관성은 보장되지만 백업이 진행되는 동안 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
    • full 백업 이후의 변경 사항을 복구(point-in-time 복구, roll-forward)하기 위해서라도 —lock-all-tables 명령 대신 —master-data=2 옵션을 주는 것이 좋다.
  • —lock-tables, -l
    • 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock).
    • READ LOCAL LOCK이므로 진행 중인 insert는 허용한다.
    • 여러 데이터베이스를 백업할 때는 주의해야 한다. —lock-tables은 각 데이터베이스를 각각 잠근다. 그래서 데이터베이스간에 논리적인 일관성이 보장되지 않는다.
    • 백업이 진행되는 동안 특정 데이터베이스에 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
    • InnoDB나 BDB 같은 트랜잭션을 지원하는 테이블에는 —single-transaction이 더 나은 옵션이다. 테이블을 전혀 잠글 필요가 없기 때문이다.
  • —flush-logs, -F
    • 백업을 시작하기 전에 로그를 파일에 저장한다(flush).
    • 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
    • —all-databases 옵션과 같이 사용하면 각 데이터베이스가 백업될 때마다 각각 그 로그를 저장(flush)한다는 점에 주의한다.
    • 그러나 —lock-all-tables, —master-data 옵션이 사용되면 모든 데이터베이스를 잠근 순간에 한꺼번에 로그를 저장(flush)한다.
    • 따라서 백업과 로그 저장을 정확히 같은 시각에 하려면 —flush-logs를 —lock-all-tables, —master-data 옵션과 같이 사용해야 한다.
  • —delete-master-logs
    • 바이너리 로그가 활성화되어 있어야 한다.
    • 백업 후에 PURGE BINARY LOGS 명령을 내려 바이너리 로그를 지운다.
    • —master-data 옵션을 지정하면 자동으로 활성화된다고 나오지만, 실제로는 그러지 않았다.(5.0.77, 10.3.27, 10.5.16에서 확인)
    • 슬레이브 서버가 연결되지 않은 상태에서 이 옵션을 지정하면 슬레이브 서버가 미처 바이너리 로그 내용을 동기화하기 전에 지워버리기 때문에 복제가 깨진다. 슬레이브 서버가 연결된 상태라면 슬레이브 서버가 로그 파일을 읽는 중에는 지우지 않고 에러를 내기 때문에 안전하다.
  • —events
    • 명시적으로 적어줘야 mysql.events 테이블을 백업한다. —all-databases 옵션을 줘도 mysql.events 테이블 스키마만 백업하고 데이터는 다음과 같은 경고를 내면서 백업하지 않는다. Warning: Skipping the data of table mysql.event. Specify the —events option explicitly.
    • EVENT 권한이 필요하다.
  • —dump-slave[=value]
    • 5.5.3 버전 이후부터 사용할 수 있다.
    • 슬레이브에서 백업받은 것을 가지고 또다른 슬레이브를 셋업하기 위해서 사용한다. 마스터의 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문을 백업 파일에 기록한다.
    • 이 옵션을 사용하면 —master-data 옵션을 무시한다.
    • 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다. 그리고 binary log가 활성화되어 있어야 한다.
    • value 값의 디폴트는 1이다. 따라서 1일 때는 값을 생략할 수 있다.
    • value가 2이라면 CHANGE MASTER TO 문이 주석으로 삽입된다. 주석으로 삽입하기 때문에 실제 영향은 없다.
    • —single-transaction을 명시적으로 적어주지 않는다면 —lock-all-tables 옵션이 자동으로 지정된다.
    • —lock-tables 옵션을 자동으로 끈다.
  • —quick
    • 디폴트 값이므로 따로 지정해주지 않아도 된다. 큰 테이블을 백업할 때 유용하다. —skip-quick 옵션을 사용하면 테이블 데이터를 모두 메모리에 불러온 다음에 백업 파일에 쓰기 시작한다.

1.5. 슬레이브에 복원

$ mysql -u root < fullbackup.sql

—master-data 옵션을 지정해서 백업 파일을 만들었기 때문에 이미 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문은 실행되었을테니, 나머지만 지정해주면 된다.

MariaDB> CHANGE MASTER TO MASTER_HOST='mysql.bookcube.com', MASTER_USER='repl', MASTER_PASSWORD='qhrwp!21';
MariaDB> START SLAVE;

—master-data 옵션이 아니라 —master-data=2 옵션으로 백업을 했다면 바이너리 로그 좌표(파일명과 위치)가 백업 파일 앞 부분에 주석으로 포함되어 있다. 그 내용에 연결에 필요한 정보를 추가해서 다음과 같이 실행하면 된다. 처음 복제하는 것이기 때문에 STOP SLAVE를 먼저 해줄 필요는 없다.

MariaDB> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.001105', MASTER_LOG_POS=107;
MariaDB> CHANGE MASTER TO MASTER_HOST='mysql.bookcube.com', MASTER_USER='repl', MASTER_PASSWORD='qhrwp!21';
MariaDB> START SLAVE;

참고로 복제 진행 중에 CHANGE MASTER TO의 옵션 값을 바꿔야 한다면, 먼저 stop slave 명령으로 복제 스레드를 중지해야 한다. 예를 들어 복제 계정의 비밀번호가 바뀌었다면 다음과 같이 하면 된다.

MariaDB> STOP SLAVE; -- if replication was running
MariaDB> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
MariaDB> START SLAVE; -- if you want to restart replication

1.6. 복제 확인

슬레이브 서버가 제대로 작동하는지 확인하고 싶다면 SHOW SLAVE STATUS 명령을 내리면 된다. Slave_IO_Running 과 Slave_SQL_Running 컬럼 값이 둘다 Yes라면 제대로 동작하고 있는 것이다. MariaDB 클라이언트를 사용한다면 \G 파라미터를 붙여 사용하면 훨씬 보기 편하다.

MariaDB> show slave status\G 

마스터 서버에 비해서 슬레이브 서버가 얼마나 늦는지 보려면 Seconds_Behind_Master 컬럼을 보면 된다.

혹은 다음과 같이 SHOW PROCESSLIST 명령을 내려서 마스터와 연결이 잘 되고 있는지 확인하면 된다. State 컬럼 값이 아래와 달리 'Connecting to master'로 나온다면 문제가 있는 것이다.

MariaDB> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  124 | Waiting for master to send event                                            | NULL             |
|  2 | system user |           | NULL | Connect |   80 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

1.7. 특정 데이터베이스 복제 제외

예를 들어 mysql 데이터베이스는 복제에서 제외하고 싶다면 다음과 같은 옵션을 사용한다.

replicate-wild-ignore-table=mysql.%

특정 데이터베이스를 복제에서 제외하려고 마스터 서버에서 binlog-do-db나 binlog-ignore-db 옵션을 사용하는 것은 피해야 한다.

슬레이브 서버에서 replicate-do-db나 replicate-ignore-db 옵션을 사용하는 것도 피하는 것이 좋다. 예를 들어 replicate-ignore-db=sales 라고 지정했다고 하자.

USE prices;
UPDATE sales.january SET amount=amount+1000;

이런 SQL문을 cross-database 업데이트라고 하는 것 같은데, STATEMENT 포맷일 때는 현재 데이터베이스가 무엇인가에 따라 복제 여부를 결정하지 실제 어느 데이터베이스가 변경되었는지에 따라 복제 여부를 결정하지 않는다.

즉, 위와 같은 SQL 문을 실행하면 sales 데이터베이스가 replicate-ignore-db에 포함되어 있어도 복제가 된다. 현재 데이터베이스가 sales 가 아닌 prices 데이터베이스이기 때문이다.

반면에 ROW 포맷일 때는 현재 데이터베이스가 무엇이든 간에 실제 작업 대상이 replicate-ignore-db에 포함되어 있다면 복제가 되지 않는다. 그러나, 바이너리 로그 포맷을 ROW로 지정해도 mysql 데이터베이스의 변경이나 DDL 문 등은 STATEMENT 포맷으로 저장이 되므로 현재 데이터베이스의 영향을 받을 수 있다.(실제로 현재 데이터베이스의 영향을 받는지 확인해보진 못했다.)

그래서 안전하게 replicate-wild-ignore-table 옵션을 사용하는 것이 좋다.

2. 바이너리 로그 삭제

마스터 서버의 바이너리 로그는 지워주지 않으면 계속 쌓인다.

바이너리 로그 파일을 지운 후 full 백업을 새로 받거나, 바이너리 로그 파일을 지우기 전에 full 백업 이후의 모든 바이너리 로그를 백업받아야 한다.

2.1. 바이너리 로그 파일 수동 삭제

현재 바이너리 로그 파일을 본다.

MariaDB> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000334 | 534806337 |
+------------------+-----------+
| mysql-bin.000335 |       335 |
+------------------+-----------+

남겨놓을 바이너리 로그 파일을 지정해 다음 명령을 내리면 지정한 바이너리 로그 파일보다 이전 바이너리 로그 파일은 삭제한다.

MariaDB> PURGE BINARY LOGS TO 'mysql-bin.000335';

이 명령은 복제가 진행 중일 때도 안전하다. 슬레이브 서버가 로그 파일을 읽는 중에는 로그 파일을 지우지 않고 에러를 내기 때문이다. 다만, 슬레이브 서버가 연결되지 않은 상태에서 이 명령을 내리면 복제가 깨진다.

복제 중일 때 안전하게 바이너리 로그를 지우려면 모든 슬레이브 서버에서 SHOW SLAVE STATUS 명령을 내려서 마스터의 로그 파일을 잘 읽고 있는지 확인해야 한다.

MariaDB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event

              Master_Log_File: mysqld-bin.001116
          Read_Master_Log_Pos: 107

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2.2. 바이너리 로그 파일 자동 삭제

expire_logs_days 값을 지정해서 자동으로 바이너리 로그를 지울 수도 있다. 디폴트는 0으로 바이너리 로그를 지우지 않는 것이다.

expire_logs_days=32

으로 지정하면 32일이 지난 바이너리 로그는 지운다.

다만 슬레이브 서버가 연결되지 않은 상태에서 바이너리 로그가 지워지면 복제가 깨진다.

full 백업 이후의 변경 사항을 복구(point-in-time 복구, roll-forward)하기 위해 이렇게 지우기보다는 crontab 등으로 바이너리 로그를 백업한 후 지우는 것이 좋다.

2.3. 바이너리 로그를 백업하지 않고 삭제했을 때

바이너리 로그 파일을 백업받지 않고 삭제했을 때는 반드시 full 백업을 해야 한다. 증분 백업 데이터가 지워졌기 때문이다.

mysqldump --master-data=2 --flush-logs --delete-master-logs --all-databases --events > fullbackup.sql
  • 마스터 서버를 복원할 목적으로 Full 백업을 하는 것이기 때문에 —master-data 대신에 —master-data=2 를 사용해야 한다.
  • —delete-master-logs 옵션은 PURGE BINARY LOGS 명령을 내리기 때문에 슬레이브 서버가 연결되지 않은 상태에서는 복제가 깨진다.

2.4. 더이상 복제하지 않을 때 바이너리 로그 삭제

다음은 마스터 서버에서만 가능한 명령인데, 모든 바이너리 로그 파일을 지우고 비어있는 000001 번 바이너리 로그 파일을 만든다. 복제가 진행중일 때는 하면 안된다.

RESET MASTER

3. 릴레이 로그 삭제

슬레이브 서버의 릴레이 로그는 자동으로 지워지기 때문에 보통은 신경쓸 필요 없다. 다만 더 이상 복제하지 않을 때 필요없는 릴레이 로그를 삭제하려면 다음과 같이 한다.

  1. 복제 스레드를 중지한다
    MariaDB> stop slave;
    
  2. master.info와 relay-log.info 파일과 릴레이 로그 파일을 지운다.
    MariaDB> reset slave all;
    

4. Global Transaction ID (GTID)

http://www.gurubee.net/lecture/4213

GTID 사용 이전 버전에서는 마스터 서버의 바이너리 로그 파일 이름과 위치를 전달하는 방법을 사용했으나 여러 슬레이브 서버를 사용할 때는 문제가 있었다.

하나의 마스터 서버에 두 개 이상의 슬레이브 서버가 연결 된 상태에서 마스터 서버에 장애 발생 시

  • 슬레이브 서버 중 하나를 마스터 서버로 승격시키고
  • 다른 슬레이브 서버를 새로 생긴 마스터 서버를 바라보게 전환
  • 이 때 장애 발생 전 슬레이브 서버 간 전송 지연등에 의해 시점 차이가 있었을 경우 장애 이후 자동 동기화 불가

글로벌 트랜잭션 ID를 사용한다면

  • 모든 서버 간 동일한 ID 값을 사용하므로
  • 마스터 서버가 바뀌더라도 무관
  • 전송 지연이 된 슬레이브 서버라도 새로 승격된 마스터 서버에서 동일한 GTID 값을 이용해 따라잡을 수 있다.

MariaDB는 10.0.2부터 Global Transaction ID (GTID)를 이용한 복제를 지원한다.

5. SLAVE인지 아닌지 확인

지금 접속한 서버가 SLAVE인지 아닌지 알아내는 방법은 show slave status 명령을 내리는 것이다. 명령의 결과가 아무 것도 나오지 않는다면 MASTER다.

프로시저 등에서 사용하려면 다음과 같은 SQL문을 사용하면 된다. 1을 리턴하면 MASTER, 0을 리턴하면 SLAVE다.

select count(1)
  from information_schema.global_status
  where variable_name = 'SLAVE_RUNNING'
    and variable_value = 'OFF';

자주 사용한다면 다음과 같이 함수를 만드는 것도 좋은 방법이다.

delimiter //
create or replace function is_master()
  returns tinyint
  comment 'MASTER인지 아닌지 리턴하는 함수. 1을 리턴하면 MASTER, 0을 리턴하면 SLAVE다. SLAVE가 중지되면 잘못된 값을 리턴한다.'
  deterministic
begin

  declare v_result tinyint;

  select count(1) into v_result
    from information_schema.global_status
    where variable_name = 'SLAVE_RUNNING'
      and variable_value = 'OFF';
      
  return v_result;
    
end//
delimiter ;

select is_master();

이 방법의 단점은 SLAVE가 잠시 중지된 상태에서는 잘못된 정보를 준다는 것이다.

stop slave;
select is_master();

start slave;
select is_master()

위와 같은 문제가 없으면서도 조금 더 간단한 방법은 read_only 글로벌 변수를 사용하는 방법이다.

SLAVE 서버의 설정에 read_only 를 추가했다면 SUPER 권한을 가진 사용자를 제외하고는 쓰기 작업이 불가능하기 때문에 SLAVE 서버에 많이 쓰는 설정이다.

다음과 같이 그 값을 읽어올 수 있다.

select @@global.read_only;

이 값이 1이라면 SLAVE, 0이라면 MASTER이다. 다만 이 방법은 SLAVE 설정에서 read_only를 지정하지 않을 수도 있기 때문에 완벽한 방법은 아니다.

마지막 방법은 @@global.hostname 변수를 이용하는 방법이다. hostname 값이 MASTER인지 SLAVE인지는 하드코딩을 할 수도 있고 다음과 같이 처리할 수도 있다.

create or replace table master_list
(
    hostname varchar(64),
    primary key (hostname)
);

delimiter //
create or replace function is_master()
  returns tinyint
  comment 'MASTER인지 아닌지 리턴하는 함수. 1을 리턴하면 MASTER, 0을 리턴하면 SLAVE다. MASTER와 SLAVE가 변경되면 master_list 테이블을 먼저 수정해야 한다.'
  deterministic
begin

  declare v_result tinyint;

  select count(1) into v_result
    from information_schema.global_variables g
    join master_list m
      on g.variable_value = m.hostname
    where variable_name='hostname';
      
  return v_result;
    
end//
delimiter ;

select is_master();

insert into master_list values ('local-hems.dasangng.co.kr');

select is_master();

처음으로

Last modified 4 weeks ago Last modified on Nov 11, 2025, 10:33:53 AM
Note: See TracWiki for help on using the wiki.