Replicate_do_db , do_table 동기화

쏭문·2023년 7월 11일

MariaDB Replication Filter do_db do_table replication 복제필터 혼공

MariaDB

목록 보기

2/2

Replication Filter

복제 필터를 통해 의도적으로 특정 DB나 Table만 복제하거나 건너뛸 수 있다.
각 마스터와 슬레이브 필터로 나뉘어 있는데 여기서는 슬레이브의 do_db와 do_table에 대한 테스트 내용을 정리함.

replicate_do_db

지정한 데이터베이스에 대한 복제를 수행함
replicate_do_db 시스템 변수를 사용하면 지정된 이름과 일치하는 데이터베이스에 영향을 미치는 명령문 및 트랜잭션을 적용하도록 복제 슬레이브를 구성할 수 있음
명령문 기반 복제의 경우 쿼리에서 명시적으로 언급된 테이블이 아닌 기본 데이터베이스(즉, USE에서 선택한 데이터베이스)만 고려됨.

replicate_do_table

지정한 테이블에 대한 복제를 수행함
이 옵션은 테이블과 관련된 명령문에만 영향을 주며, CREATE DATABASE , ALTER DATABASE 및 DROP DATABASE 와 같이 데이터베이스 자체와 관련된 명령문에는 영향을 미치지 않는다.

Slave에서의 복제필터 종류 및 우선순위

위에서 언급한 대표적인 dodb, do_table 2가지 외에도 여러가지 종류의 필터를 설정할 수 있다.
다만 슬레이브에서의 복제필터 옵션 중 충돌하는 옵션이 발생하면 replicate_do* 옵션이 가장 우선 시 된다.

replicate_do_db
replicate_do_table
replicate_rewrite_db
replicate_ignore_db
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table

📄 기본 구문

동적 변수로 설정이 가능함
반드시 slave 구동을 중지하고 변경해야한다.
여러개 설정 시, 쉼표로 나눠서 작성 가능. 단 my.cnf에서 수정 시 쉼표 구분을 허용하지 않으므로 한줄씩 구분하여 작성해야한다.
```
	stop slave;
	set global replicate_do_db='db_name';
	start slave;

	stop slave;
	set global replicate_do_table='db_name.table_name';
	start slave;
```

☑️ TEST 1

do_table, do_db 둘다 설정 했을 경우
설정 값
Replicate_Do_DB: employees,test1
Replicate_Do_Table: employees.titles

-> employees DB는 titles 테이블만 복제가 가능해지고, test1은 DB 전체가 복제가 가능할 것이라 예상

1-1. master에서 do_table이 아닌 데이터 delete

    MariaDB [employees]> delete from dept_emp where emp_no = '10239';
    Query OK, 1 row affected (0.039 sec)

1-2. 복제 안됨.

    MariaDB [employees]> select * from dept_emp where emp_no = '10239';
    +--------+---------+------------+------------+
	| emp_no | dept_no | from_date  | to_date    |
	+--------+---------+------------+------------+
	|  10239 | d001    | 1996-05-04 | 9999-01-01 |
	+--------+---------+------------+------------+

2-1. 다른 DB에서 테이블 생성 및 데이터 Insert 수행

	MariaDB [test1]> select * from tb_test;
	+----+-------+
	| no | name  |
	+----+-------+
	| 1  | test  |
	| 2  | test2 |
	| 3  | test3 |
	+----+-------+
	3 rows in set (0.000 sec)

2-2. 복제 안됨.

	MariaDB [test1]> select * from tb_test;
	ERROR 1146 (42S02): Table 'test1.tb_test' doesn't exist

3-1. do_table 설정 제거 후 재 테스트

-  DB에서 테이블 생성 및 데이터 Insert 수행

	MariaDB [test1]> select * from tb_test2;
	+----+-------+
	| no | name  |
	+----+-------+
	| 1  | test1 |
	+----+-------+
	1 row in set (0.000 sec)

3-2. 복제 확인 됨.

    MariaDB [test1]> select * from tb_test2;
    +----+-------+
    | no | name  |
    +----+-------+
    | 1  | test1 |
    +----+-------+
    1 row in set (0.001 sec)

💫 TEST 1번 결과
do_table에 적용 된 테이블만 복제가 된다. do_table의 우선순위가 더 높다는 걸 알 수 있었음.

☑️ TEST 2

do_db로 설정한 db가 아닌 다른 db를 use 하고 employees.dept_emp 데이터 변경 시 변경되는지
설정 값
Replicate_Do_DB: employees

master에서 test1 DB를 USE 후 employees DB 데이터 삭제

    MariaDB [test1]> delete from employees.employee_name where emp_no ='10001';
    Query OK, 1 row affected (0.298 sec)

slave 에서 데이터 조회

    MariaDB [(none)]> select * from employees.employee_name where emp_no ='10001';
    +--------+------------+-----------+
    | emp_no | first_name | last_name |
    +--------+------------+-----------+
    |  10001 | Georgi     | Facello   |
    +--------+------------+-----------+
    1 row in set (0.001 sec)

employees DB USE 후 delete 시

    MariaDB [employees]> delete from employees.employee_name where emp_no ='10002';
    Query OK, 1 row affected (0.044 sec)

슬레이브 확인 시 복제가 잘 되어짐을 확인.

        MariaDB [employees]> select * from employee_name where emp_no='10002';
        Empty set (0.000 sec)

💫 TEST 2 결과
2_default DB에의 명령문만 복제가 되어짐을 확인 할 수 있었음.

☑️ TEST 3

do_db에 설정한 db외에 다른 db의 데이터 변경 시 relay log는 어떤 형태로 남게 되는지.
설정 값
Replicate_Do_DB: employees

test1.sss_test에 insert 구문 적용

    MariaDB [test1]> insert into sss_test values ('', 1, 'now', now());
    Query OK, 1 row affected, 1 warning (0.044 sec)
    
    MariaDB [test1]> select * from sss_test;
    +-------+------+-----------+---------------------+
    | song2 | test | time_date | time_date_2         |
    +-------+------+-----------+---------------------+
    |     1 |    0 | NULL      | NULL                |
    |     2 |   28 | test_day  | 2022-06-29 15:47:30 |
    |     3 |    1 | now       | 2022-07-01 12:03:20 |
    +-------+------+-----------+---------------------+
    3 rows in set (0.000 sec)

binlog 확인

ROW 기반 로깅 수행.

	(INSERT's on tables with a composite primary key that 
has an AUTO_INCREMENT column that 
isn't the first column of the composite key.)
MariaDB [test1]> desc sss_test;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| song2       | int(11)     | NO   | PRI | NULL    | auto_increment |
| test        | int(10)     | YES  |     | NULL    |                |
| time_date   | varchar(10) | YES  |     | NULL    |                |
| time_date_2 | datetime    | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

#220701 12:03:20 server id 106  end_log_pos 247345347 CRC32 0x2fb90e9a  Annotate_rows:
#Q> insert into sss_test values ('', 1, 'now', now())
#220701 12:03:20 server id 106  end_log_pos 247345406 CRC32 0xb60f5f3d  Table_map: `test1`.`sss_test` mapped to number 62
# at 247345406
#220701 12:03:20 server id 106  end_log_pos 247345457 CRC32 0xcf51c6fb  Write_rows: table id 62 flags: STMT_END_F

BINLOG '
+GO+YhNqAAAAOwAAAP4wvg4AAD4AAAAAAAEABkJVREdFVAAIc3NzX3Rlc3QABAMDDxIDHgAADj1f
D7Y=
+GO+YhdqAAAAMwAAADExvg4AAD4AAAAAAAEABP/wAwAAAAEAAAADbm93ma1CwNT7xlHP
'/*!*/;
### INSERT INTO `test1`.`sss_test`
### SET
###   @1=3
###   @2=1
###   @3='now'
###   @4='2022-07-01 12:03:20'
# Number of rows: 1

Slave에서 table 조회

→ 복제 되지 않음.

Relay log 확인

test1 DB에서의 변경 사항 log 확인 되어짐.

#220701 12:03:20 server id 106  end_log_pos 247345406 CRC32 0xb60f5f3d  Table_map: `test1`.`sss_test` mapped to number 62
# at 785
#220701 12:03:20 server id 106  end_log_pos 247345457 CRC32 0xcf51c6fb  Write_rows: table id 62 flags: STMT_END_F

BINLOG '
+GO+YhNqAAAAOwAAAP4wvg4AAD4AAAAAAAEABkJVREdFVAAIc3NzX3Rlc3QABAMDDxIDHgAADj1f
D7Y=
+GO+YhdqAAAAMwAAADExvg4AAD4AAAAAAAEABP/wAwAAAAEAAAADbm93ma1CwNT7xlHP
'/*!*/;
### INSERT INTO `test1`.`sss_test`
### SET
###   @1=3
###   @2=1
###   @3='now'
###   @4='2022-07-01 12:03:20'
# Number of rows: 1

💫 test 3 결과
do_db로 설정 된 db가 아니더라도 Relay log에는 Binlog에 기록된 로그를 동일하게 받아온다. 슬레이브에서 설정 된 복제필터기 때문에 로그를 받아 온 후에 복제본에서 적용되는 옵션이 있는지를 평가하는 단계를 거치게 된다.

☑️ TEST 4

do_db는 employees, do_table는 test1 테이블값을 적용했을 경우
설정 값
Replicate_Do_DB: employees
Replicate_Do_Table: test1.sss_test
→ employees db 데이터 전부 복제되고 test1 특정 테이블만 변경될거라고 생각함.

do_table 데이터 삭제 쿼리 수행 → 복제 안됨

	
-- 마스터
MariaDB [test1]> delete from sss_test where A=1234;
Query OK, 1 row affected (0.029 sec)

MariaDB [test1]>
MariaDB [test1]>
MariaDB [test1]> select * from sss_test;
+------+
| A    |
+------+
|    0 |
+------+

-- 슬레이브 
MariaDB [(none)]> select * from test1.sss_test;
+------+
| A    |
+------+
|    0 |
| 1234 |
+------+
2 rows in set (0.000 sec)

do_db 데이터 삭제 쿼리 수행 → 복제 안됨

	-- 마스터 
MariaDB [employees]> delete from titles where emp_no ='92539';
Query OK, 1 row affected (0.318 sec)

-- 슬레이브
MariaDB [(none)]> select * from employees.titles where emp_no='92539';
+--------+-------+------------+------------+
| emp_no | title | from_date  | to_date    |
+--------+-------+------------+------------+
|  92539 | Staff | 1985-03-09 | 1985-05-05 |
+--------+-------+------------+------------+
1 row in set (0.001 sec)

💫 TEST 4 결과
둘 다 반영되지 않음.

☑️ TEST 5

do_db는 employees로 설정 후 master에서 test1 DB로 USE 후 쿼리 수행(binlog_format : MIXED)
설정 값
Replicate_Do_DB: employees
→ 명령문 기반 복제의 경우 쿼리에서 명시적으로 언급된 테이블이 아닌 기본 데이터베이스(즉, USE에서 선택한 데이터베이스)만 고려됩니다. 에 대한 내용을 테스트 해봄.

master에서 테이블 생성 test1 use 후 employees db에 Insert 수행

MariaDB [(none)]> use test1;
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 [test1]> insert into employees.employee_name values (999999, 'kim', 'songlee');
Query OK, 1 row affected (0.047 sec)

MariaDB [test1]> select * from employees.employee_name where emp_no ='999999';
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 999999 | kim        | songlee   |
+--------+------------+-----------+
1 row in set (0.000 sec)

slave에서 복제여부 확인. → 복제 안됨.

MariaDB [(none)]> select * from employees.employee_name where emp_no ='999999';
Empty set (0.000 sec)

default DB 변경 후 복제 여부 확인

MariaDB [test1]> use employees;
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 [employees]>
MariaDB [employees]>
MariaDB [employees]> insert into employees.employee_name values (888888, 'kim', 'songlee');
Query OK, 1 row affected (0.290 sec)

MariaDB [employees]> select * from employees.employee_name where emp_no ='888888';
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 888888 | kim        | songlee   |
+--------+------------+-----------+
1 row in set (0.001 sec)

slave에서 복제여부 확인. → 복제 정상.

MariaDB [(none)]> select * from employees.employee_name where emp_no ='888888';
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 888888 | kim        | songlee   |
+--------+------------+-----------+
1 row in set (0.001 sec)

💫 test 5 결과
필터링 규칙에 의해 MIXED 포맷에서의 Statement 구문은 기본 데이터베이스만 고려하여 복제필터가 적용되어진다는 것을 알 수 있음.

참고 사항
1. 로깅형식 사용여부

Statement : 기본 데이터 베이스를 기준으로 검증을 시도함.

ROW : 변경 사항에 영향을 받는 데이터베이스를 기준으로 검증을 시도함.

replication_do_db 유무

replication_ignore_db 유무

테이블 레벨 복제 옵션 유무
~~ 이후로 테이블 레벨 검증 판단을 진행.
→ 이런 검증단계가 완료되어져야 명령문이 정상적으로 실행되면서 복제가 이뤄지게됨.

☑️ TEST 5-1

만약, ROW 형식 명령어가 적용되는 경우에도 기본 데이터베이스 지정 유무에 영향을 미칠까?

auto_increment를 사용하는 table에 데이터 insert

    MariaDB [employees]> insert into repltest_2 values ('1', 'one', '');
    Query OK, 1 row affected, 1 warning (0.044 sec)

slave에서 복제 여부 확인. → 복제 정상.
- log 확인

#220705 15:15:40 server id 106  end_log_pos 247350366 CRC32 0x3fb3ae84  Table_map: `employees`.`repltest_2` mapped to number 67
# at 2023
#220705 15:15:40 server id 106  end_log_pos 247350410 CRC32 0xa077d1a2  Write_rows: table id 67 flags: STMT_END_F

BINLOG '
DNfDYhNqAAAAQAAAAF5Evg4AAEMAAAAAAAEACWVtcGxveWVlcwAKcmVwbHRlc3RfMgAD/g8DBP4M
eAADhK6zPw==
DNfDYhdqAAAALAAAAIpEvg4AAEMAAAAAAAEAA//4ATEDb25lAQAAAKLRd6A=
'/*!*/;
### INSERT INTO `employees`.`repltest_2`
### SET
###   @1='1'
###   @2='one'
###   @3=1
# Number of rows: 1

기본 데이터베이스 변경하고 row 기반 명령문 수행.

    MariaDB [employees]> use BUDGET;
    
    MariaDB [test1]> insert into employees.repltest_2 values ('2', 'two', '');
    Query OK, 1 row affected, 1 warning (0.034 sec)

slave에서 복제 여부 확인. → 복제 정상.

    MariaDB [(none)]> select * from employees.repltest_2;
    +------+------+------+
    | no   | name | test |
    +------+------+------+
    | 1    | one  |    1 |
    | 2    | two  |    2 |
    +------+------+------+
    2 rows in set (0.001 sec)

log 확인

#220705 15:19:30 server id 106  end_log_pos 247350626 CRC32 0x0b0d45f2  Table_map: `employees`.`repltest_2` mapped to number 67
# at 2204
#220705 15:19:30 server id 106  end_log_pos 247350670 CRC32 0x4073ebbf  Write_rows: table id 67 flags: STMT_END_F

BINLOG '
8tfDYhNqAAAAQAAAAGJFvg4AAEMAAAAAAAEACWVtcGxveWVlcwAKcmVwbHRlc3RfMgAD/g8DBP4M
eAAD8kUNCw==
8tfDYhdqAAAALAAAAI5Fvg4AAEMAAAAAAAEAA//4ATIDdHdvAgAAAL/rc0A=
'/*!*/;
### INSERT INTO `employees`.`repltest_2`
### SET
###   @1='2'
###   @2='two'
###   @3=2
# Number of rows: 1

💫 test 5-1 결과
변경 사항에 영향을 받는 데이터베이스를 기준으로 검증을 시도하여 복제가 정상적으로 이뤄진다는 것을 확인 할 수 있었음.

☑️ TEST 6

Binlog format별 복제 필터 테스트 진행

[Statement]

binlog 형식을 statement로 변경

    MariaDB [(none)]> show variables like '%format%';
    +----------------------------+-------------------+
    | Variable_name              | Value             |
    +----------------------------+-------------------+
    | binlog_format              | STATEMENT         |

명령문 수행 시, 경고 발생.

    MariaDB [employees]> update employees set first_name = 
'Kim' where emp_no='10001';
    ERROR 1665 (HY000): Cannot execute statement: 
impossible to write to binary log since BINLOG_FORMAT = 
STATEMENT and at least one table uses a storage engine 
limited to row-based logging. InnoDB is limited to row-
logging when transaction isolation level is 
READ COMMITTED or READ UNCOMMITTED.

binlog 형식을 row로 변경

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

명령문 수행 시, 정상 반영 확인.

    MariaDB [employees]> update employees set first_name = 'Kim' where emp_no='10001';
    Query OK, 1 row affected (0.045 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

💫 test 6 결과
포맷이 STATEMENT로 설정되면 복제 필터가 동작하지 않음.

☑️ TEST 7

do_db, ignore_table 설정 시 복제 여부
설정 값
Replicate_Do_DB: employees
Replicate_Ignore_Table: employees.titles
→ 우선 같은DB(employees) 기준으로 테스트 진행.

1) 마스터에서 DELETE 쿼리 수행


MariaDB [employees]> delete from employees.titles where emp_no = '434994';
Query OK, 2 rows affected (0.296 sec)

2) 슬레이브에 쿼리 복제 안됨. → Ignore_table 옵션이 잘 적용 되어짐.

MariaDB [(none)]> select * from employees.titles where emp_no = '434994';
+--------+--------------+------------+------------+
| emp_no | title        | from_date  | to_date    |
+--------+--------------+------------+------------+
| 434994 | Staff        | 1992-12-07 | 1997-12-07 |
| 434994 | Senior Staff | 1997-12-07 | 9999-01-01 |
+--------+--------------+------------+------------+
2 rows in set (0.001 sec)

3) 다른 table에 insert 구문 수행.

MariaDB [employees]> insert into salaries values ('123456', 'dba', now(), now());
Query OK, 1 row affected, 3 warnings (0.043 sec)

MariaDB [employees]> select * from salaries where emp_no ='123456';
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
| 123456 |      0 | 2022-07-06 | 2022-07-06 |
+--------+--------+------------+------------+
1 row in set (0.001 sec)

4) 슬레이브에서 복제가 잘 되어짐을 확인. do_db 옵션으로 정상 반영되어진 것 같음.

MariaDB [employees]> select * from salaries where emp_no ='123456';
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
| 123456 |      0 | 2022-07-06 | 2022-07-06 |
+--------+--------+------------+------------+
1 row in set (0.001 sec)

5) 혹시 몰라서 다른 db에서의 insert 작업도 진행해봄.

MariaDB [test1]> insert into tb_test2 values('3', 'test3');
Query OK, 1 row affected (0.262 sec)

MariaDB [test1]> select * from tb_test2;
+----+-------+
| no | name  |
+----+-------+
| 1  | test1 |
| 2  | test2 |
| 3  | test3 |
+----+-------+
3 rows in set (0.001 sec)

6) 슬레이브에 정상적으로 복제가 되어지지 않음을 확인.

MariaDB [test1]> select * from tb_test2;
+----+-------+
| no | name  |
+----+-------+
| 1  | test1 |
+----+-------+
1 row in set (0.000 sec)

☑️ TEST 7-1

do_db, ignore_table 설정 시 복제 여부
설정 값
Replicate_Do_DB: employees
Replicate_Ignore_Table: test1.tb_test2
→ 이번엔 다른 DB 기준으로 테스트 진행.

1) 마스터에서 insert 쿼리 수행

MariaDB [BUDGET]> insert into tb_test2 values ('2', 'test2');
Query OK, 1 row affected (0.032 sec)

2) 슬레이브에 쿼리 복제 안됨. → Ignore_table 설정 옵션이 적용 되는 것 같음.

MariaDB [BUDGET]> select * from tb_test2;
+----+-------+
| no | name |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.000 sec)

다른 DB일 경우 employees DB 복제 유무 확인.

1) 마스터에서 DELETE 쿼리 수행

MariaDB [employees]> delete from employees.titles where emp_no = '434995';
Query OK, 2 rows affected (0.042 sec)

2) 슬레이브에 쿼리 복제 됨. → do_db 설정 옵션이 정상임을 확인.

MariaDB [employees]> select * from employees.titles where emp_no = '434995';
Empty set (0.001 sec)

💫 test 7 결과
do_db와 ignore_table이 함께 적용되면 두 옵션이 전부 정상적으로 동작한다.

☑️ TEST 8

do_db, ignore_db 각 상반 된 옵션 적용
설정 값
Replicate_Do_DB: employees
Replicate_Ignore_DB: employees

1) employees.tb_trans 테이블 삭제

MariaDB [employees]> drop table tb_trans;
Query OK, 0 rows affected (0.082 sec)

2) 슬레이브에 쿼리 복제 되어짐.

MariaDB [employees]> select * from tb_trans;
ERROR 1146 (42S02): Table 'employees.tb_trans' doesn't exist

💫 test 8 결과
각 충돌되는 옵션값에는 replicate_do_* 옵션이 우선시 적용 되어짐을 확인. Replicate_Ignore_DB 옵션이 무시되어진다.**

✔️ The replicate_ignore_db system variable is effectively ignored if the replicate_do_db
system variable is set, so those two system variables should not be set together

✔️ 참고 링크 > https://mariadb.com/kb/en/replication-filters/#replicate_ignore_db

끝

처음 DBA되고 공부하면서 Replication 관련되서 많이 헤맸다.
사실 위 내용이 그냥 단순히 이름만 보고 잘 생각해보면 또 금방 이해 될만하면서도 또 직접 해보면 내가 생각한대로 흘러가지가 않지...
단순무식한 나로써 여러가지 환경을 생각해내기란 경험해보지 않고 어려운 부분인데 선임분이 많은 도움을 주셔서 여러가지 테스트도 해볼 수 있었던 것 같다.
물론 정답은 없고 틀린 부분이 있을 수도 있으니 나중에 또 기회되면 다시 정리해야지.

쏭문

이전 포스트