原创 MySQL主从复制没法过滤binlog event的缺陷，ProxySQL给补齐了-天涯论坛

5ep9lzv 发表于 2024-10-5 03:44:45

原创 MySQL主从复制没法过滤binlog event的缺陷，ProxySQL给补齐了

作者介绍
贺春旸，dbaplus社群金牌专家，凡普金科和爱钱进DBA团队负责人，《MySQL管理之道：性能调优、高可用与监控》第1&二版、《MySQL运维进阶指南》作者，曾任职于中国移动飞信、安卓机锋网。五次荣获dbaplus年度MVP，致力于MariaDB、MongoDB等开源技术的科研，重点负责数据库性能调优、监控和架构设计。
MySQL主从复制，在最新的9.0版本里，仍旧没法实现过滤binlog event事件，例如主库上执行drop和truncate操作，从库同步复制后，直接过滤掉drop和truncate操作。
DBA、运维或开发一旦手滑误操作，直接P0，将是毁灭性的损伤。那样，咱们就需要借助ProxySQL来处理MySQL主从复制这一缺陷。
1、思路
倘若主库执行drop和truncate，临时关闭binlog，在命令前面增多set sql_log_bin=0，这般主库执行完以后，binlog不会记录，从库亦就不会执行。
2、实施
1、安装ProxySQL
# cat <<EOF | tee /etc/yum.repos.d/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
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记录，操作后重新启用，无需手动干涉。这些更改对应用程序是透明的，不需要修改应用代码。3、运用
DBA、运维或Java开发直接拜访ProxySQL的6033端口：
# mysql -u rd -p123456 -h 127.0.0.1 -P6033
当执行drop和truncate操作时，只会在主库上执行，从库上不会执行，从而实现了过滤binlog event事件。
注：rd用户需要在后端MySQL主库上创建。
举荐
为了和大众一块探索AI关联技术在大数据、数据资产管理、数据库、运维等行业的最佳落地区式，挖掘由此激发的软件发展和技术进步，第九届DAMS中国数据智能管理峰会将于2024年11月29日在上海举办，携手一众产学研界技术领跑单位，带来新思路、重实践、可落地的全日干货盛宴。
活动详情：
https://www.bagevent.com/event/8805002?bag_track=WAZ
<a style="color: black;">返回外链论坛： http://www.fok120.com，查看更加多</a>

责任编辑：网友投稿

wrjc1hod 发表于 2024-11-5 04:34:33

感谢楼主的分享！我学到了很多。

m5k1umn 发表于 3 天前

我深受你的启发，你的话语是我前进的动力。

wrjc1hod 发表于 3 天前

谷歌外贸网站优化技术。

页: [1]

天涯论坛's Archiver

原创 MySQL主从复制没法过滤binlog event的缺陷，ProxySQL给补齐了