MariaDB replication 설정과 모니터링

8 min readSep 7, 2024

운영 서버 중 하나가 MariaDB를 사용중이다. 얼마 전 장애가 발생해서 빠진 데이터 채워 넣는 작업하다가 update query를 잘못 날렸다. 리눅스 명령어로 치면 rm -rf 를 친 셈이다.

slave db의 transaction log를 통해서 복구할 수 있지 않을까 하고, slave db 상태를 보니, master와 동기화 실패한지 6개월이 넘었더군.

소잃고 외양간 고치기를 했다.

다시 Replication 설정을 다시하고, 상태 모니터링도 자동시켜본다.

master server의 myconf.cnf 수정

server-id = 1
log-bin = mysql-bin
binlog-format = ROW

2. replicaton user 생성

CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';
FLUSH PRIVILEGES;

3. master lock 하고 binary log 파일과 position 확인

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

그럼 결과가 아래처럼 나온다.
mysql-bin.000026,96339751

4. master db 백업하고, unlock table

MYSQL_PWD=<db passwd> mysqldump -u root --single-transaction --all-databases --master-data=2 --routines --events --triggers > masterdump.sql


UNLOCK TABLES;

5. Slave DB 준비 — myconf.cnf

server-id = 2
relay-log = mysql-relay-bin

6. 백업한 DB를 slave에 import

mysql < masterdump.sql

7. slave db 시작

CHANGE MASTER TO
   MASTER_HOST='master_ip',
   MASTER_USER='repl_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=123;


START SLAVE;

SHOW SLAVE STATUS\G

이때 문제가 발생하면 재시도

STOP SLAVE;

CREATE USER 'replicator'@'%' IDENTIFIED BY 'some_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

SET GLOBAL sql_slave_skip_counter = 1;

START SLAVE;

SHOW SLAVE STATUS\G

그럼 slave 결과가 이렇게 나온다.

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: xx.xx.xx.xx
                   Master_User: replicator
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000026
           Read_Master_Log_Pos: 96339934
                Relay_Log_File: mysql-relay-bin.000003
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000026
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
           Exec_Master_Log_Pos: 96339934
               Relay_Log_Space: 1346
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

여기서 Slave_SQL_Running_State 상태가 “has read all relay log” 라고 뜨면, master 와 전부 동기화 되었다는 뜻이다.

Replication 설정했어도, 모니터링을 해야 한다. 이것도 자동화 시켜본다.

내가 주로 사용하는 healthchecks.io 를 사용하겠다.

먼저 slave db 상태를 체크하는 script를 만든다.

#!/bin/bash
# check_mariadb_slave.sh
# MariaDB connection details
MYSQL_USER="your_username"
MYSQL_PASSWORD="your_password"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"

# Healthchecks.io ping URL
# Replace with your actual Healthchecks.io ping URL
HEALTHCHECKS_URL="https://hc-ping.com/your-uuid-here"

# Function to send ping to Healthchecks.io
send_ping() {
    local status=$1
    curl -fsS -m 10 --retry 5 -o /dev/null "${HEALTHCHECKS_URL}${status}"
}

# Check slave status
slave_status=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" -P"$MYSQL_PORT" -e "SHOW SLAVE STATUS\G")

# Extract relevant information
slave_io_running=$(echo "$slave_status" | grep "Slave_IO_Running:" | awk '{print $2}')
slave_sql_running=$(echo "$slave_status" | grep "Slave_SQL_Running:" | awk '{print $2}')
seconds_behind_master=$(echo "$slave_status" | grep "Seconds_Behind_Master:" | awk '{print $2}')

# Check if slave is running and not too far behind
if [[ "$slave_io_running" == "Yes" && "$slave_sql_running" == "Yes" ]]; then
    if [[ "$seconds_behind_master" != "NULL" && "$seconds_behind_master" -le 300 ]]; then
        echo "Slave is running and up-to-date (${seconds_behind_master} seconds behind)"
        send_ping ""  # Success
    else
        echo "WARNING: Slave is running but too far behind (${seconds_behind_master} seconds)"
        send_ping "/warn"  # Warning
    fi
else
    echo "ERROR: Slave is not running properly"
    echo "Slave_IO_Running: $slave_io_running"
    echo "Slave_SQL_Running: $slave_sql_running"
    send_ping "/fail"  # Failure
fi

그 다음 매 분마다 slave 상태 모니터링하도록 crontab 에 등록.

sudo crontab -e


0 * * * * /opt/check_mariadb_slave.sh && curl -fsS -m 10 --retry 5 -o /dev/null https://hc-ping.com/your-uuid-here

결과는 실패시 telegram으로 알람 뜨도록 하고 해서 끝.

MariaDB replication 설정과 모니터링

Written by Sungyong