当前位置：首页 > 数据库 > Mysql > 详细页面

Mariadb之复制过滤器

时间：2020-07-06来源：www.pcxitongcheng.com作者：电脑系统城

　　mariadb的主从复制集群，默认情况下是把主库上的所有库进行复制，只要在主库上产生写操作，从库基于主库的二进制日志做重放，从而实现把主库的上的库表复制到从库；复制过滤器指的是我们仅复制一个或几个数据库相关的数据，而非所有；过滤器的作用就是来定义我们要复制那些库，那些表，这种定义过滤器的方式叫白名单机制；除了这种告诉服务器我们要复制的库表的，当然我们也可以告诉服务器我们不需要复制的库或表，或者需要忽略的库表的机制叫黑名单；在定义我们需要复制的库或者表，我们可以在主库上定义，也可以在从库上定义；不同的是在主库上定义，我们只能定义需要复制的那些库，忽略那些库；其实在主库上定义过滤器，就是告诉主库那些库的操作不记录二进制日志，当然在主库上不记录二进制，也就无法实现复制；这种在主库上定义那些库记录二进制，那些库不记录二进制，这种方式不是很推荐，原因在于如果主库宕机，做恢复操作时，我们不能利用二进制日志来把数据全部恢复；通常情况下我们在从库上定义复制那些库或者忽略那些库，这里需要注意一点，我们要么使用白名单机制，要么使用黑名单机制，不建议混合使用；

　　在主库上配置只允许某些库记录二进制日志，用binlog_do_db变量来指定，如下

　　提示：以上配置表示只记录和first_db库相关写操作的事件到二进制日志中；

　　重启主库，然后在主库上创建其他库，看看是否还会同步到从库呢？

　　提示：可以看到我们重启主库后，在主库上新建mydb库，在从库上并没有看到mydb从主库上复制过来；

　　在主库上操作first_db库，看看是否及时同步到从库呢？

　　提示：可以看到在主库上操作first_db中的表，在从库上是能够及时的看到；

　　配置在主库上忽略某些库，剩余所有库都记录二进制日志

　　提示：以上配置表示忽略first_db库上的二进制日志记录，剩余其他库都要做二进制日志记录；

　　测试：重启主库，在主库上操作first_db库，看看是否能够及时同步到从库？

　　提示：可以看到我们在主库上操作first_db库中的student表，没有被同步到从库的；

　　查看主库是binlog否记录了我们刚才的插入操作的语句

　　提示：在主库上看二进制日志，并没有记录我们刚才的插如语句相关的操作；

　　在主库上操作其他库看看是否能够被同步到从库呢？

　　提示：可以看到我们在主库上创建新的库表是能够及时同步到从库；

　　在从库上配置只复制first_db相关写操作事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95 [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表的操作相关事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86 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开头的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84 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指定；使用方式和上面白名单机制的使用方式一样；如果需要永久生效，请把以上变量写到配置文件中重启服务即可生效；

Mariadb之复制过滤器

相关信息

热门系统总排行

装机必备

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载