MySQL主从复制与读写分离实战一、主从复制原理MySQL主从复制是实现高可用性和读写分离的基础通过将主库的数据同步到从库实现数据备份和负载均衡。1.1 复制架构┌─────────────────┐ 二进制日志 ┌─────────────────┐ │ Master │ ────────────────── │ Slave 1 │ │ (写操作) │ │ (读操作) │ └─────────────────┘ └─────────────────┘ │ │ ┌─────────────────┐ └────────────────── │ Slave 2 │ │ (读操作) │ └─────────────────┘1.2 复制流程Master将数据变更写入二进制日志binlogSlaveI/O线程读取Master的binlog写入中继日志relay logSlaveSQL线程执行中继日志中的SQL语句二、主从复制配置2.1 Master 配置[mysqld] server-id 1 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW binlog_do_db example_db binlog_ignore_db mysql expire_logs_days 7 max_binlog_size 500M2.2 创建复制用户CREATE USER repl% IDENTIFIED BY password; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;2.3 获取Master状态FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;记录File和Position的值------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------------------------------------------------ | mysql-bin.000001 | 154 | example_db | mysql | ------------------------------------------------------------2.4 Slave 配置[mysqld] server-id 2 relay_log /var/log/mysql/relay-bin.log log_slave_updates 1 read_only 12.5 启动复制CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;2.6 检查复制状态SHOW SLAVE STATUS\G关键状态字段Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0三、读写分离实现3.1 使用 MySQL Proxyfunction read_query(packet) if packet:byte() proxy.COM_QUERY then local query packet:sub(2) if string.upper(query):match(^SELECT) then proxy.queries:append(1, packet, {resultset_is_needed true}) return proxy.PROXY_SEND_QUERY else proxy.backends[1].state proxy.BACKEND_STATE_CURRENT proxy.queries:append(1, packet, {resultset_is_needed true}) return proxy.PROXY_SEND_QUERY end end end function read_result(inj) return proxy.PROXY_SEND_RESULT end3.2 使用 Spring JDBC 路由public class RoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.getDataSourceType(); } } public class DataSourceContextHolder { private static final ThreadLocalString contextHolder new ThreadLocal(); public static void setDataSourceType(String type) { contextHolder.set(type); } public static String getDataSourceType() { return contextHolder.get(); } public static void clearDataSourceType() { contextHolder.remove(); } }3.3 AOP 切面配置Aspect Component public class DataSourceAspect { Pointcut(execution(* com.example.service.*.select*(..))) public void readPointcut() {} Pointcut(execution(* com.example.service.*.insert*(..)) || execution(* com.example.service.*.update*(..)) || execution(* com.example.service.*.delete*(..))) public void writePointcut() {} Before(readPointcut()) public void setReadDataSource() { DataSourceContextHolder.setDataSourceType(slave); } Before(writePointcut()) public void setWriteDataSource() { DataSourceContextHolder.setDataSourceType(master); } After(readPointcut() || writePointcut()) public void clearDataSource() { DataSourceContextHolder.clearDataSourceType(); } }3.4 配置文件spring: datasource: master: url: jdbc:mysql://master:3306/example_db username: admin password: password driver-class-name: com.mysql.cj.jdbc.Driver slave: url: jdbc:mysql://slave:3306/example_db username: readonly password: password driver-class-name: com.mysql.cj.jdbc.Driver四、主从复制优化4.1 并行复制[mysqld] slave-parallel-type LOGICAL_CLOCK slave-parallel-workers 4 master_info_repository TABLE relay_log_info_repository TABLE4.2 半同步复制Master 配置[mysqld] plugin-load-add semisync_master.so loose-semi_sync_master_enabled 1 loose-semi_sync_master_timeout 1000Slave 配置[mysqld] plugin-load-add semisync_slave.so loose-semi_sync_slave_enabled 14.3 GTID 复制Master 配置[mysqld] gtid_mode ON enforce_gtid_consistency ONSlave 配置CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_AUTO_POSITION 1;五、故障处理5.1 延迟问题排查-- 查看延迟情况 SHOW SLAVE STATUS\G -- 查看复制线程状态 SELECT * FROM performance_schema.replication_applier_status\G -- 分析慢查询 EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;5.2 主从数据不一致-- 检查数据一致性 CHECKSUM TABLE table_name; -- 使用 pt-table-checksum 检查 pt-table-checksum hmaster,Dexample_db -- 使用 pt-table-sync 修复 pt-table-sync --execute hmaster,Dexample_db hslave5.3 主库故障切换-- 在从库上提升为主库 STOP SLAVE; RESET MASTER; -- 修改其他从库指向新主库 CHANGE MASTER TO MASTER_HOSTnew_master_ip, MASTER_AUTO_POSITION 1;六、监控与告警6.1 Prometheus 监控scrape_configs: - job_name: mysql static_configs: - targets: [master:9104, slave1:9104, slave2:9104]6.2 告警规则groups: - name: mysql_replication_alerts rules: - alert: ReplicationLag expr: mysql_slave_status_seconds_behind_master 60 for: 5m labels: severity: warning annotations: summary: 复制延迟超过60秒 description: 从库 {{ $labels.instance }} 延迟: {{ $value }}秒 - alert: ReplicationStopped expr: mysql_slave_status_slave_io_running 0 or mysql_slave_status_slave_sql_running 0 for: 1m labels: severity: critical annotations: summary: 复制停止 description: 从库 {{ $labels.instance }} 复制线程停止七、最佳实践总结7.1 架构建议场景推荐架构说明读多写少1主N从N建议3-5个高可用MHA/PXC自动故障转移大数据量分库分表 主从结合ShardingSphere7.2 配置建议binlog_format: 使用 ROW 格式保证数据一致性expire_logs_days: 根据业务需求设置建议7-30天read_only: 从库设置为只读防止误写log_slave_updates: 级联复制时开启7.3 监控指标Seconds_Behind_Master: 复制延迟Slave_IO_Running/Slave_SQL_Running: 复制线程状态binlog_size: 二进制日志大小Threads_connected: 连接数通过合理配置主从复制和读写分离可以显著提升MySQL的可用性和性能。
MySQL主从复制与读写分离实战
MySQL主从复制与读写分离实战一、主从复制原理MySQL主从复制是实现高可用性和读写分离的基础通过将主库的数据同步到从库实现数据备份和负载均衡。1.1 复制架构┌─────────────────┐ 二进制日志 ┌─────────────────┐ │ Master │ ────────────────── │ Slave 1 │ │ (写操作) │ │ (读操作) │ └─────────────────┘ └─────────────────┘ │ │ ┌─────────────────┐ └────────────────── │ Slave 2 │ │ (读操作) │ └─────────────────┘1.2 复制流程Master将数据变更写入二进制日志binlogSlaveI/O线程读取Master的binlog写入中继日志relay logSlaveSQL线程执行中继日志中的SQL语句二、主从复制配置2.1 Master 配置[mysqld] server-id 1 log_bin /var/log/mysql/mysql-bin.log binlog_format ROW binlog_do_db example_db binlog_ignore_db mysql expire_logs_days 7 max_binlog_size 500M2.2 创建复制用户CREATE USER repl% IDENTIFIED BY password; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;2.3 获取Master状态FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;记录File和Position的值------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------------------------------------------------ | mysql-bin.000001 | 154 | example_db | mysql | ------------------------------------------------------------2.4 Slave 配置[mysqld] server-id 2 relay_log /var/log/mysql/relay-bin.log log_slave_updates 1 read_only 12.5 启动复制CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;2.6 检查复制状态SHOW SLAVE STATUS\G关键状态字段Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0三、读写分离实现3.1 使用 MySQL Proxyfunction read_query(packet) if packet:byte() proxy.COM_QUERY then local query packet:sub(2) if string.upper(query):match(^SELECT) then proxy.queries:append(1, packet, {resultset_is_needed true}) return proxy.PROXY_SEND_QUERY else proxy.backends[1].state proxy.BACKEND_STATE_CURRENT proxy.queries:append(1, packet, {resultset_is_needed true}) return proxy.PROXY_SEND_QUERY end end end function read_result(inj) return proxy.PROXY_SEND_RESULT end3.2 使用 Spring JDBC 路由public class RoutingDataSource extends AbstractRoutingDataSource { Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.getDataSourceType(); } } public class DataSourceContextHolder { private static final ThreadLocalString contextHolder new ThreadLocal(); public static void setDataSourceType(String type) { contextHolder.set(type); } public static String getDataSourceType() { return contextHolder.get(); } public static void clearDataSourceType() { contextHolder.remove(); } }3.3 AOP 切面配置Aspect Component public class DataSourceAspect { Pointcut(execution(* com.example.service.*.select*(..))) public void readPointcut() {} Pointcut(execution(* com.example.service.*.insert*(..)) || execution(* com.example.service.*.update*(..)) || execution(* com.example.service.*.delete*(..))) public void writePointcut() {} Before(readPointcut()) public void setReadDataSource() { DataSourceContextHolder.setDataSourceType(slave); } Before(writePointcut()) public void setWriteDataSource() { DataSourceContextHolder.setDataSourceType(master); } After(readPointcut() || writePointcut()) public void clearDataSource() { DataSourceContextHolder.clearDataSourceType(); } }3.4 配置文件spring: datasource: master: url: jdbc:mysql://master:3306/example_db username: admin password: password driver-class-name: com.mysql.cj.jdbc.Driver slave: url: jdbc:mysql://slave:3306/example_db username: readonly password: password driver-class-name: com.mysql.cj.jdbc.Driver四、主从复制优化4.1 并行复制[mysqld] slave-parallel-type LOGICAL_CLOCK slave-parallel-workers 4 master_info_repository TABLE relay_log_info_repository TABLE4.2 半同步复制Master 配置[mysqld] plugin-load-add semisync_master.so loose-semi_sync_master_enabled 1 loose-semi_sync_master_timeout 1000Slave 配置[mysqld] plugin-load-add semisync_slave.so loose-semi_sync_slave_enabled 14.3 GTID 复制Master 配置[mysqld] gtid_mode ON enforce_gtid_consistency ONSlave 配置CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_AUTO_POSITION 1;五、故障处理5.1 延迟问题排查-- 查看延迟情况 SHOW SLAVE STATUS\G -- 查看复制线程状态 SELECT * FROM performance_schema.replication_applier_status\G -- 分析慢查询 EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;5.2 主从数据不一致-- 检查数据一致性 CHECKSUM TABLE table_name; -- 使用 pt-table-checksum 检查 pt-table-checksum hmaster,Dexample_db -- 使用 pt-table-sync 修复 pt-table-sync --execute hmaster,Dexample_db hslave5.3 主库故障切换-- 在从库上提升为主库 STOP SLAVE; RESET MASTER; -- 修改其他从库指向新主库 CHANGE MASTER TO MASTER_HOSTnew_master_ip, MASTER_AUTO_POSITION 1;六、监控与告警6.1 Prometheus 监控scrape_configs: - job_name: mysql static_configs: - targets: [master:9104, slave1:9104, slave2:9104]6.2 告警规则groups: - name: mysql_replication_alerts rules: - alert: ReplicationLag expr: mysql_slave_status_seconds_behind_master 60 for: 5m labels: severity: warning annotations: summary: 复制延迟超过60秒 description: 从库 {{ $labels.instance }} 延迟: {{ $value }}秒 - alert: ReplicationStopped expr: mysql_slave_status_slave_io_running 0 or mysql_slave_status_slave_sql_running 0 for: 1m labels: severity: critical annotations: summary: 复制停止 description: 从库 {{ $labels.instance }} 复制线程停止七、最佳实践总结7.1 架构建议场景推荐架构说明读多写少1主N从N建议3-5个高可用MHA/PXC自动故障转移大数据量分库分表 主从结合ShardingSphere7.2 配置建议binlog_format: 使用 ROW 格式保证数据一致性expire_logs_days: 根据业务需求设置建议7-30天read_only: 从库设置为只读防止误写log_slave_updates: 级联复制时开启7.3 监控指标Seconds_Behind_Master: 复制延迟Slave_IO_Running/Slave_SQL_Running: 复制线程状态binlog_size: 二进制日志大小Threads_connected: 连接数通过合理配置主从复制和读写分离可以显著提升MySQL的可用性和性能。
