MySQL 主从复制在 9.0 版本中仍然无法过滤 binlog 事件，例如 DROP 和 TRUNCATE 操作，一旦误操作，将造成严重损失。本文介绍使用 ProxySQL 来解决这一缺陷的方案，通过配置查询规则，在执行 DROP 和 TRUNCATE 操作前临时关闭 binlog 记录，从而实现过滤 binlog 事件，避免从库执行这些操作，保障数据安全。

🤔 ProxySQL 是一款 MySQL 代理工具，可以拦截和修改 SQL 语句，并根据配置进行路由和过滤。

💡 针对 MySQL 主从复制无法过滤 DROP 和 TRUNCATE 事件的问题，ProxySQL 提供了查询规则功能，可以匹配特定 SQL 语句并进行修改。

💪 通过在执行 DROP 和 TRUNCATE 操作前添加 `SET sql_log_bin=0` 语句，可以临时关闭 binlog 记录，确保这些操作不会被复制到从库，从而避免误操作带来的数据损失。

🚀 ProxySQL 能够自动在这些操作前禁用 binlog 记录，操作后重新启用，无需手动干预，对应用程序透明，无需修改应用代码。

🎯 使用 ProxySQL 能够有效地解决 MySQL 主从复制无法过滤 DROP 和 TRUNCATE 事件的问题，保障数据安全，提高数据库管理效率。

原创 贺春旸 2024-09-23 07:15 广东

一旦手滑误操作，直接P0，将是毁灭性的伤害……

作者介绍

贺春旸，dbaplus社群金牌专家，凡普金科和爱钱进DBA团队负责人，《MySQL管理之道：性能调优、高可用与监控》第一&二版、《MySQL运维进阶指南》作者，曾任职于中国移动飞信、安卓机锋网。五次荣获dbaplus年度MVP，致力于MariaDB、MongoDB等开源技术的研究，主要负责数据库性能调优、监控和架构设计。

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

DBA、运维或者研发一旦手滑误操作，直接P0，将是毁灭性的伤害。那么，我们就需要借助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 repositorybaseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasevergpgcheck=1gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_keyEOF# yum install proxysql -y# systemctl start proxysql

2、配置ProxySQL规则

## 连接到ProxySQL管理界面# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
-- 配置MySQL服务器 - 写主节点VIP地址INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',6666);
-- 配置用户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; SET sql_log_bin=1;', 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; 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;

3、这个配置的工作原理如下

我们定义了一个主服务器组：主库（hostgroup 1）

我们创建了一个DBA、运维、Java研发使用的用户名 rd，默认连接到主库。

我们设置了两个查询规则：

规则1和2：匹配DROP和TRUNCATE语句，在它们前面添加SET sql_log_bin=0，这可以确保只有特定的DROP或TRUNCATE操作不被记录到binlog，且在执行完后再开启binlog。

例如，如果原始查询是DROP TABLE t6;  重写后的查询将变为：

SET sql_log_bin=0; DROP TABLE t6; SET sql_log_bin=1;

4、使用这种方法的优势

它只影响DROP和TRUNCATE操作，其他操作不受影响。

它自动在这些操作前禁用binlog记录，操作后重新启用，无需手动干预。

这些更改对应用程序是透明的，不需要修改应用代码。

三、使用

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

# mysql -u rd -p123456 -h 127.0.0.1 -P6033

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

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

跳转微信打开