mariadb 的主从复制集群, 默认情况下是把主库上的所有库进行复制, 只要在主库上产生写操作, 从库基于主库的二进制日志做重放, 从而实现把主库的上的库表复制到从库; 复制过滤器指的是我们仅复制一个或几个数据库相关的数据, 而非所有; 过滤器的作用就是来定义我们要复制那些库, 那些表, 这种定义过滤器的方式叫白名单机制; 除了这种告诉服务器我们要复制的库表的, 当然我们也可以告诉服务器我们不需要复制的库或表, 或者需要忽略的库表的机制叫黑名单; 在定义我们需要复制的库或者表, 我们可以在主库上定义, 也可以在从库上定义; 不同的是在主库上定义, 我们只能定义需要复制的那些库, 忽略那些库; 其实在主库上定义过滤器, 就是告诉主库那些库的操作不记录二进制日志, 当然在主库上不记录二进制, 也就无法实现复制; 这种在主库上定义那些库记录二进制, 那些库不记录二进制, 这种方式不是很推荐, 原因在于如果主库宕机, 做恢复操作时, 我们不能利用二进制日志来把数据全部恢复; 通常情况下我们在从库上定义复制那些库或者忽略那些库, 这里需要注意一点, 我们要么使用白名单机制, 要么使用黑名单机制, 不建议混合使用;
在主库上配置只允许某些库记录二进制日志, 用 binlog_do_db 变量来指定, 如下
提示: 以上配置表示只记录和 first_db 库相关写操作的事件到二进制日志中;
重启主库, 然后在主库上创建其他库, 看看是否还会同步到从库呢?
提示: 可以看到我们重启主库后, 在主库上新建 mydb 库, 在从库上并没有看到 mydb 从主库上复制过来;
在主库上操作 first_db 库, 看看是否及时同步到从库呢?
提示: 可以看到在主库上操作 first_db 中的表, 在从库上是能够及时的看到;
配置在主库上忽略某些库, 剩余所有库都记录二进制日志
提示: 以上配置表示忽略 first_db 库上的二进制日志记录, 剩余其他库都要做二进制日志记录;
测试: 重启主库, 在主库上操作 first_db 库, 看看是否能够及时同步到从库?
提示: 可以看到我们在主库上操作 first_db 库中的 student 表, 没有被同步到从库的;
查看主库是 binlog 否记录了我们刚才的插入操作的语句
提示: 在主库上看二进制日志, 并没有记录我们刚才的插如语句相关的操作;
在主库上操作其他库看看是否能够被同步到从库呢?
提示: 可以看到我们在主库上创建新的库表是能够及时同步到从库;
在从库上配置只复制 first_db 相关写操作事件
- [root@docker-node03 ~]# MySQL
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 22
- Server version: 5.5.65-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)]> show global variables like 'replicate%';
- +----------------------------------+-----------+
- | Variable_name | Value |
- +----------------------------------+-----------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | |
- | replicate_do_table | |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | |
- | replicate_wild_ignore_table | |
- +----------------------------------+-----------+
- 8 rows in set (0.00 sec)
- MariaDB [(none)]> set @@global.replicate_do_db=first_db;
- ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
- MariaDB [(none)]> stop slave;
- Query OK, 0 rows affected (0.01 sec)
- MariaDB [(none)]> set @@global.replicate_do_db=first_db;
- Query OK, 0 rows affected (0.01 sec)
- MariaDB [(none)]> show global variables like 'replicate%';
- +----------------------------------+-----------+
- | Variable_name | Value |
- +----------------------------------+-----------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | first_db |
- | replicate_do_table | |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | |
- | replicate_wild_ignore_table | |
- +----------------------------------+-----------+
- 8 rows in set (0.00 sec)
- MariaDB [(none)]> start slave;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.22
- Master_User: rpluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000017
- Read_Master_Log_Pos: 245
- Relay_Log_File: relay-log.000035
- Relay_Log_Pos: 529
- Relay_Master_Log_File: MySQL-bin.000017
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB: first_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: 245
- Relay_Log_Space: 1101
- 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
- 1 row in set (0.00 sec)
- MariaDB [(none)]>
提示: 以上是在从节点上直接在线修改 replicate_do_db 变量来实现复制过滤功能; 如果需要永久生效, 那么把该配置写到配置文件中即可; 以上配置表示只复制 first_db 相关写操作的事件; 在线修改变量的方式, 需要先停止 slave, 修改完成后在启动; 从 show slave status 中, 我们就能够清楚的 replicate_do_db 的值为 first_db;
测试: 在主库上对其他库操作, 看看是否能够同步到从节点?
提示: 可以看到我们在主库删除 mydb2 中的 test 表后, 并没有同步到从库;
测试: 操作 first_db 中的表, 看看是否能同步?
提示: 可以看到我们在 first_db 库中的 student 表中插入了一条新数据, 在从库是能够及时的同步;
配置从节点只复制 first_db 库中的 student 表的操作相关事件
- MariaDB [first_db]> stop slave;
- Query OK, 0 rows affected (0.01 sec)
- MariaDB [first_db]> show global variables like 'replicate%';
- +----------------------------------+-----------+
- | Variable_name | Value |
- +----------------------------------+-----------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | first_db |
- | replicate_do_table | |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | |
- | replicate_wild_ignore_table | |
- +----------------------------------+-----------+
- 8 rows in set (0.00 sec)
- MariaDB [first_db]> set @@global.replicate_do_table=first_db.student;
- ERROR 1232 (42000): Incorrect argument type to variable 'replicate_do_table'
- MariaDB [first_db]> set @@global.replicate_do_table='first_db.student';
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [first_db]> show global variables like 'replicate%';
- +----------------------------------+------------------+
- | Variable_name | Value |
- +----------------------------------+------------------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | first_db |
- | replicate_do_table | first_db.student |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | |
- | replicate_wild_ignore_table | |
- +----------------------------------+------------------+
- 8 rows in set (0.00 sec)
- MariaDB [first_db]> start slave;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [first_db]> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.22
- Master_User: rpluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000017
- Read_Master_Log_Pos: 1198
- Relay_Log_File: relay-log.000050
- Relay_Log_Pos: 529
- Relay_Master_Log_File: MySQL-bin.000017
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB: first_db
- Replicate_Ignore_DB:
- Replicate_Do_Table: first_db.student
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 1198
- Relay_Log_Space: 1101
- 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
- 1 row in set (0.00 sec)
- MariaDB [first_db]>
提示: replicate_do_table 这个变量在线修改时需要指明那个库中的那个表, 并用引号引起来, 否则会提示我们参数类型不正确;
测试: 在主库的 first_db 库中新建一张表, 看看是否能够被同步到从库?
提示: 可以看到我们在主库的 fisrt_db 中新建一张 test 表, 并没有同步到从库中去, 这说明 replicate_do_table 的优先级要高于 replicate_do_db;
在主库的 first_db 库中对 student 表做插入操作, 看看是否能够被同步到从库?
提示: 可以看到操作 first_db 中的 student 是能够及时同步到从库;
设置从节点只复制以 student 开头的表
- MariaDB [first_db]> stop slave;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [first_db]> show global variables like 'replicate%';
- +----------------------------------+------------------+
- | Variable_name | Value |
- +----------------------------------+------------------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | first_db |
- | replicate_do_table | first_db.student |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | |
- | replicate_wild_ignore_table | |
- +----------------------------------+------------------+
- 8 rows in set (0.00 sec)
- MariaDB [first_db]> set @@global.replicate_wild_do_table='first_db.student%';
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [first_db]> show global variables like 'replicate%';
- +----------------------------------+-------------------+
- | Variable_name | Value |
- +----------------------------------+-------------------+
- | replicate_annotate_row_events | OFF |
- | replicate_do_db | first_db |
- | replicate_do_table | first_db.student |
- | replicate_events_marked_for_skip | replicate |
- | replicate_ignore_db | |
- | replicate_ignore_table | |
- | replicate_wild_do_table | first_db.student% |
- | replicate_wild_ignore_table | |
- +----------------------------------+-------------------+
- 8 rows in set (0.01 sec)
- MariaDB [first_db]> start slave;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [first_db]> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.22
- Master_User: rpluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000017
- Read_Master_Log_Pos: 1198
- Relay_Log_File: relay-log.000051
- Relay_Log_Pos: 529
- Relay_Master_Log_File: MySQL-bin.000017
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB: first_db
- Replicate_Ignore_DB:
- Replicate_Do_Table: first_db.student
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table: first_db.student%
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 1198
- Relay_Log_Space: 1101
- 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
- 1 row in set (0.00 sec)
- MariaDB [first_db]>
测试: 在主库 first_db 库中操作非 student 开头的表, 看看是否能够被复制到从库呢?
提示: 可以看到在主库的 first_db 库中新增 test2 表, 并没有被同步到从库;
测试: 在主库新增 student_test 表, 看看是否被同步到从库?
提示: 可以看到在主库上新建 student_test 表被同步到从库中去了;
以上就是 mariadb/MySQL 数据库主从复制中的复制过滤器的使用; 以上演示部分使用白名单机制进行演示的, 黑名单和白名单配置方式相同, 只不过黑名单表示忽略指定的库或者表, 其他剩下的库和表都要进行复制, 而白名单是指定的库和表都要复制, 其他剩余的库或表都不被复制; 在从节点使用黑名单机制忽略库使用 replicate_ignore_db 变量指定; 忽略某些表使用 replicate_ignore_table 变量来指定; 忽略以某类表使用 replicate_ignore_wild_do_table 指定; 使用方式和上面白名单机制的使用方式一样; 如果需要永久生效, 请把以上变量写到配置文件中重启服务即可生效;
来源: https://www.cnblogs.com/qiuhom-1874/p/13252853.html