MariaDB 복제 구성 하기

구성 내용

Master 서버 - 읽기/쓰기

Slave 서버 - 읽기, Master DB 복제 구성

 

* 읽기/쓰기 분산없이 Slave 서버를 단순 복제 구성 할 경우, my.cnf 지정 옵션이 다름, 단순 복제의 경우 아래 링크 참조

gangnam-americano.tistory.com/12 

[MySQL] MySQL Replication 구성하기

 

1. Master 서버 설정

1) my.cnf 설정 파일 변경

[root@localhost tmp]# vi /etc/my.cnf
....
#mysqld 항목에 아래 Master Server Setting 내용 추가
[mysqld]
....
#Master Server Setting
server_id = 1                   #마스터 서버 ID
log_bin = mysql.bin             #DB 변경사항을 기록하는 바이너리 파일 지정

※ 복제 관련 상세 옵션은 아래 링크 참고

eager2rdbms.blogspot.com/2010/07/replication-2.html

Replication : Master에서의 설정과 조작 1

 

2) mysql > 복제 서비스 계정 추가, server_id 값 설정

[root@localhost tmp]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.4.17-MariaDB 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.

# 복제를 위한 mysql 계정 생성
# replication slave -> 복제 수행 권한 부여
# replication client -> show master status 실행 권한 부여
MariaDB [(none)]> grant replication slave, replication client on *.* to repl@'%' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.001 sec)

# 서버 ID 변경 - my.cnf 파일에 지정한 번호와 동일하게 세팅
MariaDB [(none)]> SET GLOBAL server_id=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.001 sec)

3) mysql > master 설정 상태 확인

# mariadb 재시작
[root@localhost tmp]# systemctl restart mariadb

[root@localhost tmp]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.17-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.

# master 설정 상태 확인 > slave 설정시 File, Position 정보가 활용되므로 기억해둔다.
MariaDB [(none)]> show master status;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000001 |      324 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

4) db 전체 백업, Slave 서버에 백업 파일 전송

# 전체 백업
[root@localhost tmp]# mysqldump -uroot -p -A > /tmp/backup_DB.sql

# 슬레이브 서버로 백업 파일 복사
[root@localhost tmp]# scp /tmp/backup_DB.sql root@192.168.60.16:/tmp/backup_DB.sql

2. 슬레이브 서버 설정

1) my.cnf  설정 파일 변경

[root@localhost /]# vi /etc/my.cnf
...
[mysqld]
....
#Slave Setting - 아래 내용 추가
server_id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin     #relay 로그 파일 지정
log_slave_updates = 1           #Slave 겸 Master로 할 경우 지정
read_only = 1                   #Read only 지정

2) mysql > server_id 값 설정

[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.17-MariaDB 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.

# 확인 결과 cnf 파일에 저정된 번호가 자동으로 적용되지 않음
MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.001 sec)

# Slave는 2번으로 지정
MariaDB [(none)]> SET GLOBAL server_id=2;
Query OK, 0 rows affected (0.000 sec)

3) Master DB 백업 파일 > Slave DB Restore(복원)

[root@localhost /]# mysql -uroot -p < /tmp/backup_DB.sql
Enter password:

4) mysql > Master DB 접속 정보 설정

[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.4.17-MariaDB 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)]>
MariaDB [(none)]>
MariaDB [(none)]> change master to master_host = '192.168.60.15'
    -> ,master_user='repl'					# master에서 생성한 복제 계정
    -> ,master_password='P@ssw0rd'		
    -> ,master_port=3306					# 통신  포트
    -> ,master_log_file='mysql.000001'  	# master 서버의 status 결과에 'File'칼럼 값 입력
    -> ,master_log_pos=324					# master 서버의 status 결과에 'Position'칼럼 값 입력	
    -> ,master_connect_retry=10;
Query OK, 0 rows affected (0.033 sec)

5) Slave 서버 mariadb 재시작

#slave 시작
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

# slave 상태 확인
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event  --> 정상
                   Master_Host: 192.168.60.15
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000001
           Read_Master_Log_Pos: 324
                Relay_Log_File: localhost-relay-bin.000002
                 Relay_Log_Pos: 551
         Relay_Master_Log_File: mysql.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: 324
               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: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified

3. Master DB 변경시 Slave 반영 테스트

1) Master 서버 테이블 인서트 수행

[root@localhost tmp]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.4.17-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)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> select * from testtable;
+------+------+
| a    | b    |
+------+------+
|    2 | aaa  |
|    3 | bbb  |
+------+------+
2 rows in set (0.000 sec)

# 테스트를 위한 데이터 입력
MariaDB [testdb]> insert into testtable (a,b) values (4,'ccc');
Query OK, 1 row affected (0.006 sec)

2) Slaver 서버 테이터 확인

[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.4.17-MariaDB 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)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.000 sec)

# 마스터에서 입력한 (4,'ccc') 항목이 제대로 들어와 있음
MariaDB [testdb]> select * from testtable;
+------+------+
| a    | b    |
+------+------+
|    2 | aaa  |
|    3 | bbb  |
|    4 | ccc  |
+------+------+
3 rows in set (0.000 sec)

MariaDB [testdb]>