MySQL 主从复制，在最新的9.0版本里，仍旧无法实现过滤binlog event事件，例如主库上执行drop和truncate操作，从库同步复制后，直接过滤掉drop和truncate操作。

DBA、运维或者研发一旦手滑误操作，直接P0，毁灭性的伤害。

而这个实用价值极高的需求，在TiDB DM里早已经实现。

那么，我们需要借助ProxySQL来解决MySQL 主从复制这一缺陷。


思想

如果主库执行drop和truncate，临时关闭binlog，在命令前面增加set sql_log_bin=0，这样主库执行完以后，binlog不会记录，从库也就不会执行。


实施

1.安装ProxySQL

# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF

# yum install proxysql -y
# systemctl start proxysql
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.


2.配置ProxySQL规则

-- 连接到ProxySQL管理界面

# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '


-- 配置MySQL服务器

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',6666);

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'127.0.0.1',6667);


-- 配置用户

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('rd','123456',1);


-- 设置查询规则来重写DROP和TRUNCATE语句

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, destination_hostgroup, apply)

VALUES (1, 1, '^DROP', 'SET sql_log_bin=0; DROP', 1, 1);


INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, destination_hostgroup, apply)

VALUES (2, 1, '^TRUNCATE', 'SET sql_log_bin=0; TRUNCATE', 1, 1);


-- 添加一个规则来重置sql_log_bin

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, destination_hostgroup, apply)

VALUES (3, 1, '^(DROP|TRUNCATE)', 'SET sql_log_bin=0; ${1}; SET sql_log_bin=1;', 1, 1);


-- 加载配置到运行时环境

LOAD MYSQL SERVERS TO RUNTIME;

LOAD MYSQL USERS TO RUNTIME;

LOAD MYSQL QUERY RULES TO RUNTIME;


-- 保存配置

SAVE MYSQL SERVERS TO DISK;

SAVE MYSQL USERS TO DISK;

SAVE MYSQL QUERY RULES TO DISK;

这个配置的工作原理如下

  1. 我们定义了两个服务器组：主库（hostgroup 1）和从库（hostgroup 2）。


  1. 我们创建了一个应用程序使用的用户 rd，默认连接到主库。


  1. 我们设置了三个查询规则：
  • 规则1和2：匹配 DROP 和 TRUNCATE 语句，在它们前面添加 SET sql_log_bin=0;。
  • 规则3：更全面的规则，它不仅在操作前禁用 binlog，还在操作后重新启用它。这可以确保只有特定的 DROP 或 TRUNCATE 操作不被记录到 binlog。
  • 所有这些规则都被设置为应用到主库（hostgroup 1）。


使用这种方法的优势

  1. 它只影响 DROP 和 TRUNCATE 操作，其他操作不受影响。
  2. 它自动在这些操作前禁用 binlog 记录，操作后重新启用，无需手动干预。
  3. 这些更改对应用程序是透明的，不需要修改应用代码。


使用

DBA、运维或者研发直接访问ProxySQL的6033端口

mysql -u rd -p123456 -h 127.0.0.1 -P6033
  • 1.

当执行drop和truncate操作时，只会在主库上执行，从库上不会执行，从而实现了过滤binlog event事件。

注：rd用户需要在后端mysql主库上创建。