MariaDB Replication(복제) 설정

OS : CeontOS7

Mariadb version : 10.2.13

 

############### Master 설정 ###############

1. /etc/my.cnf.d/replication.cnf 또는 my.cnf 파일에 다음과 같이 설정

[mysqld] server-id               = 1 log_bin                 = /var/log/mysql/mariadb-bin log_bin_index           = /var/log/mysql/mariadb-bin.index expire_logs_days        = 10 max_binlog_size         = 100M

* 여기서 /var/log/mysql/은 소유자가 mysql이어야 합니다.

 

2. Mariadb 재시작

service mariadb restart

 

3. MariaDB에 접속하여 Replication 사용자를 생성

MariaDB [mysql]>CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password'; MariaDB [mysql]>GRANT REPLICATION SLAVE ON *.* TO replication_user;

4. MASTER정보를 조회

*운영중인 서버라면 DB에 락을 걸어준다 >FLUSH TABLES WITH READ LOCK;

- MASTER정보를 조회 MariaDB [mysql]>SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000003 |     33 |              |                  | +--------------------+----------+--------------+------------------+

* SHOW MASTER STATUS시 file 및 Position 정보는 Slave 설정시 필요하므로 별도로 메모하세요.

SMALL

############### Slave 설정 ###############

1. /etc/my.cnf.d/replication.cnf 또는 my.cnf 파일에 다음과 같이 설정

[mysqld] server-id               = 2 log_bin                 = /var/log/mysql/mariadb-bin log_bin_index           = /var/log/mysql/mariadb-bin.index expire_logs_days        = 10 max_binlog_size         = 100M relay_log               = /var/log/mysql/relay-bin relay_log_index         = /var/log/mysql/relay-bin.index relay_log_info_file     = /var/log/mysql/relay-bin.info log_slave_updates replicate-ignore-db     = test replicate-ignore-db     = information_schema replicate-ignore-db     = mysql

* replicate-ignore-db는 복제하지 않을 데이터베이스를 지정합니다.

 

2. Mariadb 재시작

service mariadb restart

 

3. Mariadb Master 정보 등록

MariaDB [mysql]>CHANGE MASTER TO MASTER_HOST='Master IP', MASTER_USER='replication 계정', MASTER_PASSWORD='password', MASTER_PORT=portNumber, MASTER_LOG_FILE='Master File', MASTER_LOG_POS=Master Position, MASTER_CONNECT_RETRY=10; MariaDB [mysql]>FLUSH PRIVILEGES;

* MASTER_LOG_FILE과 MASTER_LOG_POS는 Master 정보(Master에서 조회한 정보)를 입력합니다.

 

4. Slave 적용 확인

MariaDB [mysql]> SHOW SLAVE STATUS\G;  *************************** 1. row ***************************                Slave_IO_State: Connecting to master                   Master_Host: Master IP                   Master_User: replication 계정                    Master_Port: Port                 Connect_Retry: 10               Master_Log_File: mysql-bin.00003           Read_Master_Log_Pos: 33                Relay_Log_File: relay-bin.00003                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-bin.00003              Slave_IO_Running: Yes              Slave_SQL_Running: Yes               Replicate_Do_DB:            Replicate_Ignore_DB: test,information_schema,mysql            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: 35278476               Relay_Log_Space: 256               Until_Condition: None                Until_Log_File:                  Until_Log_Pos: 0            Master_SSL_Allowed:              Master_SSL_CA_File:             Master_SSL_CA_Path:                Master_SSL_Cert:              Master_SSL_Cipher:                 Master_SSL_Key:          Seconds_Behind_Master:   Master_SSL_Verify_Server_Cert:                   Last_IO_Errno:                  Last_IO_Error:                 Last_SQL_Errno:                  Last_SQL_Error:    Replicate_Ignore_Server_Ids:               Master_Server_Id:                  Master_SSL_Crl:             Master_SSL_Crlpath:                     Using_Gtid:                    Gtid_IO_Pos:        Replicate_Do_Domain_Ids:    Replicate_Ignore_Domain_Ids:                  Parallel_Mode:                      SQL_Delay:             SQL_Remaining_Delay:        Slave_SQL_Running_State:  1 row in set (0.00 sec) ERROR: No query specified

* Slave_IO_Running: Yes, Slave_SQL_Running: Yes 로 표시되면 정상 동작입니다.

 

5. Master 락 해제

MariaDB [mysql]>UNLOCK TABLES;