"MariaDB 레플리케이션 설정"의 두 판 사이의 차이

2017년 9월 26일 (화) 00:15 판

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

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 을 기억해두자.

4 슬레이브 설정

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
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만 추가함

→ 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)

5 같이 보기

6 참고

@@ 13번째 줄: / 13번째 줄: @@
 ==사전작업==
 * master1, slave1, slave2에 [[MariaDB 설치]]
-==DB 데이터 이전==
-<source lang='console'>
-root@master1:~# mysqldump -uroot -p패스워드 --all-databases > all.sql
-</source>
-<source lang='console'>
-root@slave1:~# mysql -uroot -p패스워드 < all.sql
-</source>
-<source lang='console'>
-root@slave2:~# mysql -uroot -p패스워드 < all.sql
-</source>
 ==마스터 설정==