MySQL 主从复制与读写分离

本文基于MySQL 8.0.36 MyCat2从原理讲解、环境准备、主从复制搭建、读写分离配置、验证测试全流程讲解补充关键知识点适合零基础快速上手。一、核心原理必懂1.1 主从复制原理主从复制是Master主库实时将数据变更同步到Slave从库的机制是读写分离的基础。核心组件Binary Logbinlog主库记录所有数据修改的二进制日志。I/O Thread从库拉取主库 binlog写入中继日志Relay Log。SQL Thread从库重放中继日志保持数据与主库一致。复制类型基于语句执行相同 SQL默认、效率高。基于行同步数据行变更精准。混合模式自动切换生产推荐。1.2 读写分离原理读写分离是写操作只走主库读操作负载到从库降低主库压力。实现方式代码层业务代码判断 SQL 路由性能好、改造成本高。中间件层MyCat、MySQL Proxy、Amoeba透明无代码侵入生产主流。核心价值高并发读请求分摊到多从库。高可用主库故障从库可切换。灾备从库实时备份数据。二、实验环境规划表格角色IP 地址操作系统服务端口Master主库192.168.10.101openEuler 24.03MySQL 8.0.363306Slave1从库 1192.168.10.102openEuler 24.03MySQL 8.0.363306Slave2从库 2192.168.10.103openEuler 24.03MySQL 8.0.363306MyCat中间件192.168.10.104openEuler 24.03MyCat2 JDK88066测试客户端192.168.10.105openEuler 24.03MySQL 客户端-前置统一操作所有节点执行关闭防火墙与 SELinuxbash运行systemctl stop firewalld systemctl disable firewalld sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config setenforce 0时间同步主从必须时间一致bash运行yum -y install ntpdate ntpdate ntp1.aliyun.com安装基础依赖bash运行dnf -y install gcc vim wget net-tools lrzsz tar libaio numactl openssl ncurses-compat-libs三、MySQL 主从复制搭建详细步骤3.1 所有数据库节点安装 MySQL 8.0.36创建 mysql 用户bash运行useradd -M -s /sbin/nologin mysql解压并配置 MySQLbash运行tar xJf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql mkdir -p /usr/local/mysql/data chown -R mysql:mysql /usr/local/mysql/data初始化 MySQL记录临时密码bash运行cd /usr/local/mysql/bin ./mysqld --initialize --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data配置 my.cnfbash运行vim /etc/my.cnfini[client] socket/usr/local/mysql/data/mysql.sock [mysqld] socket/usr/local/mysql/data/mysql.sock bind-address 0.0.0.0 port 3306 basedir/usr/local/mysql datadir/usr/local/mysql/data default-storage-engineINNODB character-set-serverutf8 max_connections2048 skip-name-resolve max_allowed_packet16M配置环境变量bash运行echo export PATH$PATH:/usr/local/mysql/bin /etc/profile source /etc/profile配置 systemd 服务bash运行cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld chmod x /etc/rc.d/init.d/mysqld vim /lib/systemd/system/mysqld.serviceini[Unit] Descriptionmysqld Afternetwork.target [Service] Typeforking ExecStart/etc/rc.d/init.d/mysqld start ExecReload/etc/rc.d/init.d/mysqld restart ExecStop/etc/rc.d/init.d/mysqld stop PrivateTmptrue [Install] WantedBymulti-user.target启动并设置 root 密码bash运行systemctl daemon-reload systemctl enable mysqld systemctl start mysqld # 登录后修改密码 mysql -uroot -p临时密码 ALTER USER rootlocalhost IDENTIFIED BY pwd123; FLUSH PRIVILEGES;3.2 配置 Master 主库192.168.10.101修改 my.cnf 开启 binlogbash运行vim /etc/my.cnf在 [mysqld] 下添加iniserver-id1 # 唯一ID集群内不重复 log-bin/usr/local/mysql/data/mysql-bin # 开启binlog binlog_format MIXED # 混合复制模式重启 MySQLbash运行systemctl restart mysqld创建从库复制授权账号bash运行mysql -uroot -ppwd123sql-- 创建复制用户 CREATE USER myslave% IDENTIFIED BY 123456; -- 授予复制权限 GRANT REPLICATION SLAVE ON *.* TO myslave%; -- 兼容旧认证插件 ALTER USER myslave% IDENTIFIED WITH mysql_native_password BY 123456; FLUSH PRIVILEGES; -- 查看主库binlog状态记录File和Position SHOW MASTER STATUS;关键记录mysql-bin.000001、Position1573.3 配置 Slave1 从库192.168.10.102修改 my.cnfbash运行vim /etc/my.cnfiniserver-id2 # 唯一不能和主/其他从重复重启 MySQLbash运行systemctl restart mysqld关联主库并启动同步bash运行mysql -uroot -ppwd123sqlCHANGE MASTER TO master_host192.168.10.101, master_usermyslave, master_password123456, master_log_filemysql-bin.000001, master_log_pos157; -- 启动从库同步 START SLAVE;3.4 配置 Slave2 从库192.168.10.103步骤同 Slave1仅修改server-id3其余命令完全一致。3.5 验证主从同步从库检查状态sqlSHOW SLAVE STATUS\G必须满足Slave_IO_Running: YesSlave_SQL_Running: Yes2.主库创建数据库测试sqlCREATE DATABASE db_test;从库查看sqlSHOW DATABASES;能看到db_test即主从复制成功。四、MyCat2 读写分离搭建详细步骤MyCat 是数据库中间件负责 SQL 路由写→主库读→从库。4.1 MyCat 节点104安装 JDK8bash运行dnf install jdk-8u171-linux-x64.rpm -y java -version # 验证安装4.2 安装 MyCat2解压 MyCatbash运行unzip mycat2-install-template-1.20.zip -d /usr/local/ ll /usr/local/mycat 里面包含binconflib存放函数和logs配置环境变量bash运行echo export PATH$PATH:/usr/local/mycat/bin /etc/profile source /etc/profile添加依赖包bash运行cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib cp mysql-connector-java-8.0.18.jar /usr/local/mycat/lib添加执行权限bash运行chmod -R x /usr/local/mycat/bin mycat -h # 验证安装4.3 主库创建 MyCat 连接账号101 执行sqlCREATE USER mycat% IDENTIFIED BY pwd123; GRANT ALL ON *.* TO mycat%; ALTER USER mycat% IDENTIFIED WITH mysql_native_password BY pwd123; FLUSH PRIVILEGES;4.4 配置 MyCat 默认数据源(104)bash运行vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json修改关键配置jsonurl:jdbc:mysql://192.168.10.101:3306/mysql?useSSLfalse, user:mycat, password:pwd1234.5 启动 MyCat 并添加主从数据源启动 MyCatbash运行mycat start ss -tnl | grep 8066 # 验证8066端口监听 cat /usr/local/mycat/conf/users/root.user.json 可以进行查看一下这里放的是客户端的相关内容存放登录用户和密码客户端使用这个用户和密码才可以跟mycat连接并可以访问mysql。因为客户端需要跟mycat连接才可以访问mysql登录 MyCat客户端 / MyCat 节点均可bash运行在105客户端上安装mysql 客户端版 命令dnf -y install mysql 然后在105做测试这个·root和密码是mycat提供的就是上面第15步相关文件提供的内容 mysql -uroot -p123456 -P8066 -h 192.168.10.104 show databases;看的只有3个库此时读写分离并为实现只搭建了框架 然后把该老师给的资料中的文档中的内容一个个复制进去先复制前3条记得查看ip密码什么之类的是否正确 这一步告诉mycat谁是主谁是从如何在哪里读在哪里写这里的用户是在101上创建授权的用户因为mycat连接mysql的然后客户端通过mycat连接客户端添加主库数据源sql/* mycat:createDataSource{ name:master, url:jdbc:mysql://192.168.10.101:3306/?useSSLfalse, instanceType:WRITE, user:mycat, password:pwd123 } */;添加从库 1 数据源sql/* mycat:createDataSource{ name:slave1, url:jdbc:mysql://192.168.10.102:3306/?useSSLfalse, instanceType:READ, user:mycat, password:pwd123 } */;添加从库 2 数据源sql/* mycat:createDataSource{ name:slave2, url:jdbc:mysql://192.168.10.103:3306/?useSSLfalse, instanceType:READ, user:mycat, password:pwd123 } */;查看数据源sql/* mycat:showDataSources{} */\G4.6 创建 MyCat 集群sql/*! mycat:createCluster{ name:cls01, masters:[master], replicas:[slave1,slave2] } */;4.7 配置读写分离与轮询策略(104)修改集群配置bash运行vim /usr/local/mycat/conf/clusters/cls01.cluster.jsonjsonreadBalanceType:BALANCE_ALL_READ, balance:1修改负载均衡为轮询bash运行vim /usr/local/mycat/conf/server.jsonjsondefaultLoadBalance:BalanceRoundRobin重启 MyCatbash运行mycat restart netstat -anpt | grep 8066 (两个,其中一个是远程的ip4.8 绑定数据库到集群客户端创建测试库bash运行mysql -uroot -p123456 -P8066 -h192.168.10.104 CREATE DATABASE test;MyCat 节点修改 schema 配置bash运行vim /usr/local/mycat/conf/schemas/test.schema.json添加jsontargetName:cls01,重启 MyCatbash运行mycat restart五、读写分离验证最直观测试5.1 停止从库同步制造数据差异bash运行# Slave1执行 mysql -uroot -ppwd123 STOP SLAVE; # Slave2执行 mysql -uroot -ppwd123 STOP SLAVE;5.2 分别插入不同数据主库101sqlUSE test; CREATE TABLE zang(id INT,name VARCHAR(10)); INSERT INTO zang VALUES(1,master);Slave1102sqlUSE test; INSERT INTO zang VALUES(2,slave1);Slave2103sqlUSE test; INSERT INTO zang VALUES(3,slave2);5.3 客户端测试读轮询bash运行mysql -uroot -p123456 -P8066 -h192.168.10.104 USE test; SELECT * FROM zang;第一次查询slave1数据第二次查询slave2数据第三次查询slave1数据→读请求轮询分发到从库5.4 测试写路由到主库sqlINSERT INTO zang VALUES(4,write_test);仅主库 101能查到该数据从库 102/103 无此数据→写请求只路由到主库六、关键补充知识点6.1 主从复制常见问题server-id 重复从库无法连接必须唯一。防火墙 / 端口未开3306 端口不通I/O 线程失败。密码认证插件MySQL8 默认caching_sha2_password需改为mysql_native_password。主从数据不一致从库手动修改数据导致 SQL 线程中断。6.2 MyCat 核心概念8066 端口MyCat 数据端口客户端连接用。数据源定义主 / 从库连接信息。集群绑定主从指定读写路由。轮询策略BalanceRoundRobin读请求均匀分发。6.3 生产环境优化主库只写配置高 CPU / 内存开启 binlog。从库只读配置多实例分担读压力。MyCat集群部署避免单点故障。监控用 PrometheusGrafana 监控主从延迟、MyCat 状态。6.4 读写分离适用场景读多写少电商、博客、后台管理系统。高并发需要水平扩展读能力。数据灾备主从同步保障数据安全。七、总结主从复制是基础依赖 binlogI/OSQL 线程实现数据同步。读写分离是优化MyCat 中间件实现透明路由无代码侵入。完整流程环境准备→MySQL 安装→主从配置→MyCat 安装→数据源 / 集群配置→验证。生产中需关注主从延迟、高可用、监控告警保障数据库稳定运行。八先主从复制再读写分离顺序不可颠倒。主库负责写从库负责读大幅降低主库压力。MyCat 作为中间件对应用透明无需修改业务代码。生产环境需开启主从监控、心跳检测、故障自动切换。