MariaDB replication 구성

- DB Version : mariadb-10.5.9-linux

- OS Version : Centos 7.9

- DB 2 node (Master 1대- Slave 1대)

Mariadb 설치 파일 다운로드

https://downloads.mariadb.org/

Download MariaDB Server - MariaDB.org

 

- 설치 정보

MariaDB 버전	10.5.9
설치경로: Engine(basedir)	/usr/local/mariadb
Data(datadir)	/data/mariadb/DATA
Error log	/data/mariadb/ADMIN/error.log
Binary LOG	/data/mariadb/BINLOG
RELAY LOG	/data/mariadb/RELAYLOG
InnoDB LOG	/data/mariadb/IBLOG
socket	/data/mariadb/TMP/mysql.sock
config	/etc/my.cnf

- Mariadb 설치

mariadb 설치과정(각 node별 동일하게 설치)

https://itexit.tistory.com/6

 

- node별 사전 설정(my.cnf)

- 각 node(master-slave)별 아래 내용 추가
# vi /etc/my.cnf

- Master Node에만 추가
[mysqld]
server_id=101

- Slave 1 Node에만 추가
[mysqld]
server_id=102

- 각 node 서비스 재기동
# service mysqld restart

 

- MariaDB Replication 설정

Master server
# ----------------------------------------------------------------
# Slave에서 접속하여 Binlog를 땡겨갈 수 있도록 복제를 위한 계정 생성
# ----------------------------------------------------------------
root@localhost:mysql > create user 'repl_user'@'192.168.10.%' identified by 'xxxxx';
Query OK, 0 rows affected (0.009 sec)

root@localhost:mysql > grant replication slave, replication client, BINLOG MONITOR, SLAVE MONITOR, REPLICATION SLAVE ADMIN on *.* to 'repl_user'@'192.168.10.%';
Query OK, 0 rows affected (0.007 sec)

root@localhost:mysql > grant super, reload on *.* to 'repl_user'@'192.168.10.%';
Query OK, 0 rows affected (0.005 sec)

root@localhost:mysql > flush privileges;
Query OK, 0 rows affected (0.006 sec)

# 변경이 없도록 Lock 을 걸자
root@localhost:mysql 08:27:55>FLUSH TABLES WITH READ LOCK;
# 나중에 끝나면,  unlock tables; 해줘야 한다.
# 초기 셋업이니 궂이 안해도 된다.
 
# binlog 파일명과 Position을 구한다.
root@localhost:mysql >show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| binary_log.000001 |     10   |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
 
# 그리고 GTID를 구한다.
root@localhost:mysql >SELECT BINLOG_GTID_POS("binary_log.000001", 10);
+--------------------------------------------+
| BINLOG_GTID_POS("binary_log.000001", 10  ) |
+--------------------------------------------+
| 1-101-50                                   |
+--------------------------------------------+
1 row in set (0.001 sec)
 
root@localhost:mysql 08:29:54>
 
# 위의 값들은 Slave 설정 시 사용할 것이다.

 

 

Slave server
#master 노드 정보 입력
root@localhost:mysql >CHANGE MASTER TO
    ->   MASTER_HOST='192.168.10.101',   //master ip
    ->   MASTER_USER='repl_user',       // replication 계정 명  
    ->   MASTER_PASSWORD='xxxxx',    // replication 계정 패스워드     
    ->   MASTER_PORT=3306,           // mariadb port
    ->   master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.024 sec)

root@localhost:(none) 14:34:45>set global gtid_slave_pos='1-101-50';   //master 서버의 gtid_binlog_pos 입력
Query OK, 0 rows affected (0.015 sec)

root@localhost:(none) 14:34:53>start slave;
Query OK, 0 rows affected (0.022 sec)


# 동기화 확인
root@localhost:(none) 14:34:56>show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.10.101
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: binary_log.00001
           Read_Master_Log_Pos: 4048
                Relay_Log_File: relay_log.000002
                 Relay_Log_Pos: 686
         Relay_Master_Log_File: binary_log.00001
              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: 4048
               Relay_Log_Space: 989
               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: 101
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 1-101-50
       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.000 sec)

- my.cnf database 복제 설정 종류

# 복제는 Database, table 2가지가 존재
# 설정은 Slave에서 함
 
l  all database
l  selected database
 
# all database의 경우
replicate-do-db                     =

# a,b는 복제가 안된다.
replicate-ignore-db                 = a
replicate-ignore-db                 = b
 
# x 만 복제를 한다.
replicate-do-db                     = x
 

# 모든 테이블 무조건 복제
replicate_do_table                  =

# 두개의 테이블은 복제를 하지 않는다.
replicate-ignore-table              = "imsi.master_value"
replicate-ignore-table              = "imsi.master_info"

- replication 확인

참조 :
- real mariadb 책
- https://mariadb.com/kb/en/documentation/
- https://mariadb.com/docs/