MariaDB 레플리케이션 설정

1 개요

MariaDB 레플레이션 설정

호스트명	역할	IP
master1	마스터 #1	135.79.246.11
slave1	슬레이브 #1	135.79.246.21
slave2	슬레이브 #2	135.79.246.22

2 사전작업

• master1, slave1, slave2에 MariaDB 설치

3 DB 데이터 이전

root@master1:~# mysqldump -uroot -p패스워드 --all-databases > all.sql

root@slave1:~# mysql -uroot -p패스워드 < all.sql

root@slave2:~# mysql -uroot -p패스워드 < all.sql

4 마스터 설정

• 설정 파일은 /etc/mysql/my.cnf 또는 /etc/mysql/mariadb.conf.d/50-server.cnf

root@master1:~# vi /etc/mysql/mariadb.conf.d/50-server.cnf

[mariadb]
log-bin
server_id=1
log-basename=master1

→ [mariadb]영역에 기입

root@master1:~# systemctl restart mariadb
root@master1:~#

root@master1:~# mysql -uroot -p
Enter password: P@ssw0rd

MariaDB [(none)]> CREATE USER 'replication_user'@'%' IDENTIFIED BY '패스워드';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO replication_user;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |    42102 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

→ master1-bin.000001 와 42102 을 기억해두자.

5 슬레이브 설정

root@slave1:~# vi /etc/mysql/mariadb.conf.d/50-server.cnf

#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
server-id=11
log-bin
log-basename=master1
report-host=slave1
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

→ server-id, log-bin, log-basename, report-host를 추가함

→ slave1은 server-id=11, slave2는 server-id=12로 정했음. 서로 겹치지만 않으면 됨

root@slave1:~# systemctl restart mariadb
root@slave1:~#

root@slave1:~# mysql -uroot -p
Enter password: P@ssw0rd

MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 11    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='135.79.246.11',
    -> MASTER_USER='replication_user',
    -> MASTER_PASSWORD='패스워드',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master1-bin.000001',
    -> MASTER_LOG_POS=42102,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

6 같이 보기

• MariaDB 레플리케이션 비정상 복구
• MariaDB
• 리플리케이션
• MariaDB 갈레라 클러스터 설치

7 참고

• https://mariadb.com/kb/en/the-mariadb-library/setting-up-replication/
• https://www.tecmint.com/mariadb-master-slave-replication-on-centos-rhel-debian/
• http://www.tutorialbook.co.kr/entry/MariaDB-Mysql-일반-Replication-복제-하기