mariadb 데이터베이스를 Master / Slave로 이중화하는 방법에 대하여 알아 보도록 하자. OS는 Rocky 9.6 을 사용하였다.
1 Master Node
1.1 mariadb-server 설치
[root@localhost ~]# dnf install mariadb-server
Last metadata expiration check: 0:30:03 ago on Thu 17 Jul 2025 02:59:37 PM KST.
Dependencies resolved.
…
1.2 /etc/my.cnf.d/mariadb-server.cnf 설정
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
# for mariadb replication
server_id=1 # master ID is 1, slave ID is 2
log-bin=mysql-bin
binlog-format=row # 파일 저장 방식
expire_logs_days=30 # 파일 보존 기간
...
1.3 mariadb 재시작하여 config 값을 적용
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.5 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; preset: disabled)
Active: active (running) since Thu 2025-07-17 15:32:08 KST; 42s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 6867 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS)
Process: 6889 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 6997 ExecStartPost=/usr/libexec/mariadb-check-upgrade (code=exited, status=0/SUCCESS)
Main PID: 6976 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 20 (limit: 48887)
Memory: 81.5M
CPU: 1.168s
CGroup: /system.slice/mariadb.service
└─6976 /usr/libexec/mariadbd --basedir=/usr
1.4 Slave Node와 통신 할 계정 생성
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.27-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'stephen'@'%' IDENTIFIED BY 'eogks123!!@@';
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'eogks123!!@@';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 771
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)
ERROR: No query specified
1.5 설정 파일 확인
[root@localhost ~]# ls -al /var/lib/mysql/
total 122936
drwxr-xr-x. 4 mysql mysql 4096 Jul 17 15:22 .
drwxr-xr-x. 22 root root 4096 Jul 17 15:14 ..
-rw-rw----. 1 mysql mysql 24576 Jul 17 15:25 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Jul 17 15:22 aria_log_control
-rw-rw----. 1 mysql mysql 972 Jul 17 15:22 ib_buffer_pool
-rw-rw----. 1 mysql mysql 12582912 Jul 17 15:22 ibdata1
-rw-rw----. 1 mysql mysql 100663296 Jul 17 15:22 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 Jul 17 15:22 ibtmp1
-rw-rw----. 1 mysql mysql 0 Jul 17 15:22 multi-master.info
drwx------. 2 mysql mysql 4096 Jul 17 15:22 mysql
-rw-rw----. 1 mysql mysql 771 Jul 17 15:25 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Jul 17 15:22 mysql-bin.index
srwxrwxrwx. 1 mysql mysql 0 Jul 17 15:22 mysql.sock
-rw-rw----. 1 mysql mysql 16 Jul 17 15:22 mysql_upgrade_info
drwx------. 2 mysql mysql 20 Jul 17 15:22 performance_schema
2. Slave Node
Slave node를 reboot 할때, 매번 master log file 설정을 재 적용해야한다. (자동화 필요)
2.1 mariadb-server 설치
[root@localhost ~]# dnf install mariadb-server
Last metadata expiration check: 0:30:03 ago on Thu 17 Jul 2025 02:59:37 PM KST.
Dependencies resolved.
…
2.2 /etc/my.cnf.d/mariadb-server.cnf 설정
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
#
server_id=2
relay-log=mysql-relay-bin
relay_log_purge=1
log-bin=mysql-bin
binlog-format=row
expire_logs_days=30
2.3 mariadb 재시작하여 config 값을 적용
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.5 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; preset: disabled)
Active: active (running) since Thu 2025-07-17 15:32:08 KST; 42s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 6867 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS)
Process: 6889 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 6997 ExecStartPost=/usr/libexec/mariadb-check-upgrade (code=exited, status=0/SUCCESS)
Main PID: 6976 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 20 (limit: 48887)
Memory: 81.5M
CPU: 1.168s
CGroup: /system.slice/mariadb.service
└─6976 /usr/libexec/mariadbd --basedir=/usr
2.4 mariadb 데이터베이스 설정
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.27-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '10.0.4.4', MASTER_USER='replication_user', MASTER_PASSWORD = 'eogks123!!@@';
Query OK, 0 rows affected (0.015 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=771;
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.4.4
Master_User: stephen
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 771
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 771
Relay_Log_Space: 864
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
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: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
이를 통하여 mariadb의 Master / Slave 설정을 마무리 하였다
'Linux & Development' 카테고리의 다른 글
| MariaDB Galera Cluster 설치 방법 (0) | 2026.03.03 |
|---|---|
| WireGuard VPN 설정하기 (Ubunut24.04) (0) | 2025.12.31 |
| nodejs version 업그레이드 (1) | 2024.06.15 |
| Linux chrony client 설정 (0) | 2024.04.18 |
| git pull error 해결 방법 (0) | 2024.04.15 |