복제 필터를 통해 의도적으로 특정 DB나 Table만 복제하거나 건너뛸 수 있다.
각 마스터와 슬레이브 필터로 나뉘어 있는데 여기서는 슬레이브의 do_db와 do_table에 대한 테스트 내용을 정리함.
replicate_do_db
replicate_do_table
Slave에서의 복제필터 종류 및 우선순위
위에서 언급한 대표적인 dodb, do_table 2가지 외에도 여러가지 종류의 필터를 설정할 수 있다.
다만 슬레이브에서의 복제필터 옵션 중 충돌하는 옵션이 발생하면 replicate_do* 옵션이 가장 우선 시 된다.
📄 기본 구문
동적 변수로 설정이 가능함
반드시 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;
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의 우선순위가 더 높다는 걸 알 수 있었음.
do_db로 설정한 db가 아닌 다른 db를 use 하고 employees.dept_emp 데이터 변경 시 변경되는지
설정 값 Replicate_Do_DB: employees
MariaDB [test1]> delete from employees.employee_name where emp_no ='10001';
Query OK, 1 row affected (0.298 sec)
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)
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에의 명령문만 복제가 되어짐을 확인 할 수 있었음.
do_db에 설정한 db외에 다른 db의 데이터 변경 시 relay log는 어떤 형태로 남게 되는지.
설정 값 Replicate_Do_DB: employees
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)
(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 확인
#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에 기록된 로그를 동일하게 받아온다. 슬레이브에서 설정 된 복제필터기 때문에 로그를 받아 온 후에 복제본에서 적용되는 옵션이 있는지를 평가하는 단계를 거치게 된다.
do_db는 employees, do_table는 test1 테이블값을 적용했을 경우
설정 값 Replicate_Do_DB: employees Replicate_Do_Table: test1.sss_test
→ employees db 데이터 전부 복제되고 test1 특정 테이블만 변경될거라고 생각함.
-- 마스터
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)
-- 마스터
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 결과
둘 다 반영되지 않음.
do_db는 employees로 설정 후 master에서 test1 DB로 USE 후 쿼리 수행(binlog_format : MIXED)
설정 값 Replicate_Do_DB: employees
→ 명령문 기반 복제의 경우 쿼리에서 명시적으로 언급된 테이블이 아닌 기본 데이터베이스(즉, USE에서 선택한 데이터베이스)만 고려됩니다. 에 대한 내용을 테스트 해봄.
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)
MariaDB [(none)]> select * from employees.employee_name where emp_no ='999999';
Empty set (0.000 sec)
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)
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 유무
- 테이블 레벨 복제 옵션 유무
~~ 이후로 테이블 레벨 검증 판단을 진행.
→ 이런 검증단계가 완료되어져야 명령문이 정상적으로 실행되면서 복제가 이뤄지게됨.
만약, ROW 형식 명령어가 적용되는 경우에도 기본 데이터베이스 지정 유무에 영향을 미칠까?
MariaDB [employees]> insert into repltest_2 values ('1', 'one', '');
Query OK, 1 row affected, 1 warning (0.044 sec)
#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
MariaDB [employees]> use BUDGET;
MariaDB [test1]> insert into employees.repltest_2 values ('2', 'two', '');
Query OK, 1 row affected, 1 warning (0.034 sec)
MariaDB [(none)]> select * from employees.repltest_2;
+------+------+------+
| no | name | test |
+------+------+------+
| 1 | one | 1 |
| 2 | two | 2 |
+------+------+------+
2 rows in set (0.001 sec)
#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 결과
변경 사항에 영향을 받는 데이터베이스를 기준으로 검증을 시도하여 복제가 정상적으로 이뤄진다는 것을 확인 할 수 있었음.
Binlog format별 복제 필터 테스트 진행
[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.
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로 설정되면 복제 필터가 동작하지 않음.
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)
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)
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이 함께 적용되면 두 옵션이 전부 정상적으로 동작한다.
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 관련되서 많이 헤맸다.
사실 위 내용이 그냥 단순히 이름만 보고 잘 생각해보면 또 금방 이해 될만하면서도 또 직접 해보면 내가 생각한대로 흘러가지가 않지...
단순무식한 나로써 여러가지 환경을 생각해내기란 경험해보지 않고 어려운 부분인데 선임분이 많은 도움을 주셔서 여러가지 테스트도 해볼 수 있었던 것 같다.
물론 정답은 없고 틀린 부분이 있을 수도 있으니 나중에 또 기회되면 다시 정리해야지.