mysql主从复制和读写分离部署手记

我前段时间部署mysql的主从复制和读写分离机制遇到一些情况在AI的指导下最终顺利克服以下是一些记录。1、数据库版本问题主库和从库的版本需要一致。如果不一致可以接受的情况是主低从高主库版本比从库低。比如我这个项目里主库是mysql8.0.38而从库是mysql8.0.43。如果反过来主高从低恐怕会发生不忍言之事。2、proxysql利用proxysql来实现读写分离主库写入从库读取。在我们项目中proxysql部署在从库服务器的docker容器里。使用proxysql的好处是代码不用修改只需要改一下数据库连接串。原本直接指向mysql现在连接串指向proxysql。proxysql会拦截sql分流到主库或从库。1spring boot项目中的连接串spring:datasource:driver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://192.168.10.249:6033/testdb?characterEncodingutf-8useSSLfalseserverTimezoneHongkongallowPublicKeyRetrievaltrueusername:workpassword:123456# 你的数据库密码这个192.168.10.249:6033就是proxysql的地址。2proxysql配置文件admin_variables{admin_credentialsadmin:adminmysql_ifaces0.0.0.0:6032}mysql_variables{threads8max_connections4096default_query_delay0default_query_timeout36000000have_compresstruepoll_timeout2000interfaces0.0.0.0:6033default_schemainformation_schemastacksize1048576server_version8.0.38monitor_usernamemonitormonitor_passwordmonitormonitor_history60000monitor_connect_interval2000monitor_ping_interval1000monitor_replication_lag_interval1000monitor_replication_lag_fail_count5}mysql_servers({hostname172.16.10.121, port3306, hostgroup_id0, weight1000, max_connections1000},{hostname172.17.0.1, port3306, hostgroup_id1, weight1000, max_connections1000, max_replication_lag90},{hostname172.16.10.121, port3306, hostgroup_id1, weight1, max_connections1000})mysql_users({usernamework, password123456, default_hostgroup0, active1})mysql_query_rules({rule_id1, active1, match_pattern^SELECT.*FOR UPDATE$, destination_hostgroup0, apply1},{rule_id2, active1, match_pattern^SELECT, destination_hostgroup1, apply1})其中172.16.10.121是主库172.17.0.1是从库。解读如果该从库的同步延迟Replication Lag超过了 90 秒ProxySQL 会选择临时“抛弃”它不再分配任何读流量给它。 由于 ProxySQL 配置了读写分离的主机组Hostgroup当从库被“抛弃”后流量自然就会被迫全部涌向主库。这是一种防御机制避免从库宕机影响整个应用。但如果是主库宕机呢不知道。{hostname172.17.0.1, port3306, hostgroup_id1, weight1000, max_connections1000, max_replication_lag90}3、主从复制的级别默认是mysql实例级别在主库添加用户、创建库最终都会同步到从库。由于从库只用于读取那么只在从库创建比如创建一个视图和账号给第三方。这是可以的隐患是如果某一天主库也创建了相同名字的东东就会引起冲突。所以最好一律在主库创建。4、看懂 SHOW SLAVE STATUS\G;在从库上看看从库状态。seconds_behind_master是从库同步的滞后时间。我刚设置主从复制时这个数字飙升到几十万。原因是有几个分区表索引设置得不对导致从库同步时严重滞后。后来暂停主从复制重建主库索引。经过一段挣扎时间终于恢复正常。正常情况下这个数字就是几十然后回复为0。具体方法是登录mysql后输入SHOW SLAVE STATUS\G;然后回车。5、设置从库只读在从库上执行SET PERSIST super_read_onlyON;检查是否生效SHOW VARIABLES LIKE%read_only%;6、防止重放冲突如果从库突然断电后重启有可能出现将本已同步到从库的数据重复插入。为防止此类错误需要设置relay_log_recovery ON具体方法为修改mysql-server.cnf将relay_log_recovery ON 写到文件末尾或修改原来的OFF值。[worklocalhost ~]$sudocp/etc/my.cnf.d/mysql-server.cnf /etc/my.cnf.d/mysql-server.cnf.20261030[sudo]work 的密码[worklocalhost ~]$vi/etc/my.cnf.d/mysql-server.cnf[worklocalhost ~]$sudovi/etc/my.cnf.d/mysql-server.cnf[worklocalhost ~]$sudosystemctl restart mysqld[worklocalhost ~]$ mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis24Server version:8.0.38 Source distribution Copyright(c)2000,2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysqlSHOW VARIABLES LIKErelay_log_recovery;---------------------------|Variable_name|Value|---------------------------|relay_log_recovery|ON|---------------------------1rowinset(0.01sec)relay_log_recovery ON 的副作用是有可能会导致从库丢失数据。但这仅仅是理论上的。relay_log_recovery ON意思是数据库启动后会丢掉本机上没有标记为完成的同步操作文件不信任它向主库重新要一份。因此可以有效避免重放冲突。但如果从库断电的时间够长主库当时的日志已经没有了那从库就没有这部分数据了。不过日志默认保留30天应该不会有这么长。