MariaDB 레플리케이션 설정

1 개요[ | ]

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

2 사전작업[ | ]

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 마스터 설정[ | ]

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 같이 보기[ | ]

7 참고[ | ]

문서 댓글 ({{ doc_comments.length }})
{{ comment.name }} {{ comment.created | snstime }}