一、MySQL主从同步基础概念1. 主从同步作用将一台MySQL数据库的数据变更操作自动同步到一台或多台MySQL从库拆分两类服务器角色Master主服务器负责接收客户端读写访问记录所有数据变更Slave从服务器自动拉取主库变更日志重放SQL实现数据同步2. 主从同步核心原理Master端开启binlog二进制日志所有增删改、建库建表等写操作都会完整记录在binlog中查询SELECT操作不会写入binlog。Slave端双线程工作机制Slave_IO线程IO线程远程连接Master读取主库binlog日志内容写入本机relay-log中继日志Slave_SQL线程SQL线程读取本机relay-log中继日志依次执行日志内SQL语句最终和主库数据保持一致注意主从两个数据库一定要先保持一致最好做一次完全备份3. 主流主从同步架构模式一主一从单主库搭配单从库基础实验架构一主多从单主库多台从库分担读请求常用于备份极为重要的数据链式复制级联复制主→从A→从B从A同时充当下游从B的主库此配置更加麻烦需允许从A主机线程中的sql语句也要写入binlog文件。此方式应用较少互为主从双主两台服务器互相作为对方的主、从适合双活扩展场景4. 通用搭建总步骤Master主服务器操作流程修改配置文件开启binlog二进制日志、配置唯一server_id创建专门用于主从同步的用户授予REPLICATION SLAVE权限全库备份现有数据传输给从库保证搭建前主从基础数据完全一致Slave从服务器操作流程修改配置文件设置唯一server_id从库可不开启binlog导入主库备份文件完成数据还原实现主从结构前保证服务器数据结构的统一执行CHANGE MASTER TO语句绑定主库连接信息搭建主从关系启动START SLAVE同步进程使用SHOW SLAVE STATUS \G校验同步状态二、实操1一主一从搭建环境规划主服务器Master192.168.8.100从服务器Slave1192.168.8.1011. Master主机完整操作1安装并启动MySQL服务[rootmaster ~]# dnf -y install mysql-server mysql[rootmaster ~]# systemctl start mysqld[rootmaster ~]# systemctl enable mysqld2修改配置开启binlog、设置server_id[rootmaster ~]# vim /etc/my.cnf.d/mysql-server.cnf[mysqld]server-id100log_binmaster重启服务并校验日志文件生成[rootmaster ~]# systemctl restart mysqld[rootmaster ~]# ls /var/lib/mysql/master.*/var/lib/mysql/master.000001 /var/lib/mysql/master.index3修改root初始密码[rootmaster ~]# mysqladmin -uroot password 123qqq...A4master主服务器创建同步用户并授权用户授权用户slave1密码为slavepwd这个用户用于从服务器连接主服务器同步数据使用mysql_native_password插件验证该用户的密码-**REPLICATION SLAVE**表示使用户拥有向主服务器复制的权限mysqlCREATEUSERslave1%IDENTIFIEDwithmysql_native_passwordBYslavepwd;mysqlGRANTREPLICATIONSLAVEON*.*TOslave1%;该用户只需要在主服务器创建从服务器不需要5查看主库binlog日志点位搭建从库必须参数mysqlSHOWMASTERSTATUS;----------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|----------------------------------------------------------------------------|master.000002|984||||---------------------------------------------------------记录File日志文件名、Position偏移量后续从库绑定主库需要。6全库备份并传输至从库备份master主机上的数据并拷贝到从服务器[rootmaster ~]# mysqldump -hlocalhost -uroot -p123qqq...A -A ab1.sql[rootmaster ~]# scp ab1.sql 192.168.8.101:/root2. Slave1主机完整操作1安装MySQL配置唯一server_id[rootslave1 ~]# dnf -y install mysql-server mysql[rootslave1 ~]# vim /etc/my.cnf.d/mysql-server.cnf[mysqld]server_id101[rootslave1 ~]# systemctl restart mysqld2设置root密码[rootslave1 ~]# mysqladmin -uroot password 123qqq...A3还原主库备份数据[rootslave1 ~]# mysql -uroot -p123qqq...A /root/ab1.sql4slave1绑定主库连接信息启动同步slave1指定主服务器信息#指定主服务器信息#MASTER_HOST 指定主服务器的IP地址#MASTER_USER 指定主服务器授权用户#MASTER_PASSWORD 指定授权用户的密码#MASTER_LOG_FILE 指定主服务器binlog日志文件(到master上查看)#MASTER_LOG_POS 指定主服务器binlog日志偏移量(去master上查看)进行配置mysqlCHANGE MASTERTO-MASTER_HOST192.168.8.100,-MASTER_USERslave1,-MASTER_PASSWORDslavepwd,-MASTER_LOG_FILEmaster.000002,-MASTER_LOG_POS984;-- 启动SLAVE进程mysqlSTARTSLAVE;-- 查看主从同步状态mysqlSHOWSLAVESTATUS\G校验标准Slave_IO_Running: Yes、Slave_SQL_Running: Yes两项全为Yes代表同步正常。若某一项写错直接针对该项修改即可。假设IP地址写错了直接修改对应内容即可mysql CHANGE MASTER TO MASTER_HOST“192.168.8.100”‘(但注意需要先stop slave再进行修改修改完再start slave)3. 一主一从同步验证Master创建数据库mysqlCREATEDATABASEsre;Slave查询确认库同步生成mysqlSHOWDATABASES;--------------------|Database|--------------------|information_schema||mysql||performance_schema||sre||sys|--------------------Master创建表、插入数据mysqlCREATETABLEsre.t1(idINT,nameCHAR(10));mysqlINSERTINTOsre.t1VALUES(1,Sam);mysqlINSERTINTOsre.t1VALUES(2,Jack);Slave查询校验数据同步mysqlSELECT*FROMsre.t1;------------|id|name|------------|1|Sam||2|Jack|------三、实操2一主多从搭建其实一主多从配置和一主一从完全一致只是有以下几个差异点所有节点server_id不能重复从库越多主库推送日志压力越大多从可做流量分层隔离提升读负载与容灾能力。环境新增从库Slave2192.168.8.1021. Master操作重新全量备份一主多从新增从库前需要重新备份当前全量数据同步给新从库[rootmaster ~]# mysqldump -uroot -p123qqq...A -A ab2.sql[rootmaster ~]# scp ab2.sql 192.168.8.102:/root再次查看主库最新binlog点位mysqlSHOWMASTERSTATUS;----------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|----------------------------------------------------------------------------|master.000001|1599||||---------------------------------------------------------2. Slave2主机完整操作不必重复创建授权用户之前创建的授权用户可被多个从主机使用1安装MySQL配置独立server_id[rootslave2 ~]# dnf -y install mysql-server mysql[rootslave2 ~]# vim /etc/my.cnf.d/mysql-server.cnf[mysqld]server_id102[rootslave2 ~]# systemctl restart mysqld[rootslave2 ~]# systemctl enable mysqld2导入主库最新全量备份[rootslave2 ~]# mysql /root/ab2.sql3绑定主库、启动同步mysqlCHANGE MASTERTO-MASTER_HOST192.168.8.100,-MASTER_USERslave1,-MASTER_PASSWORDslavepwd,-MASTER_LOG_FILEmaster.000001,-MASTER_LOG_POS1599;mysqlSTARTSLAVE;mysqlSHOWSLAVESTATUS\G3. 一主多从同步验证Master新增表并写入数据mysqlCREATETABLEsre.t2(idINT,nameCHAR(10),maleENUM(male,female));mysqlINSERTINTOsre.t2VALUES(1,Sam,male);mysqlINSERTINTOsre.t2VALUES(2,Janner,female);Slave1校验数据mysqlUSEsre;mysqlSHOWTABLES;mysqlSELECT*FROMsre.t2;----------------------|id|name|male|----------------------|1|Sam|male||2|Janner|female|----------------------Slave2校验数据和Slave1数据完全一致mysqlUSEsre;mysqlSHOWTABLES;mysqlSELECT*FROMsre.t2;----------------------|id|name|male|----------------------|1|Sam|male||2|Janner|female|----------------------四、MySQL三大主从复制模式复制模式定义主库执行写操作后何时给客户端返回执行结果核心区分等待从库同步的逻辑。1. 异步复制MySQL默认模式流程主库执行写入→写入binlog→立即返回客户端不会等待任何从库同步完成后台异步推送日志给从库。优点主库写入性能极高无等待延迟缺点主从存在数据延迟若主库宕机未同步至从库的数据会永久丢失数据一致性无保障2. 全同步复制流程主库执行写入→推送binlog至所有从库等待全部从库完成relay-log重放、确认同步完成后才返回客户端结果。优点主从数据完全一致无丢失风险缺点写入性能大幅下降从库数量越多、网络越差主库等待耗时越长3. 半同步复制流程主库执行写入等待至少一台从库接收binlog日志并确认后就返回客户端无需等待全部从库。优点兼顾性能与数据安全至少一台从库保存完整变更日志缺点若唯一确认的从库故障会导致等待时间变长五、读写分离技术MaxScale代理使用模板机克隆实验虚拟机配置如下信息配置IP地址信息这里不再重复演示master和slave2继续沿用上方主从关系1. MaxScale基础介绍开发厂商MariaDBMySQL同源兄弟公司核心作用数据库中间件代理自动实现写请求转发主库、读请求转发从库完成读写分离安装包maxscale-24.02.1-1.rhel.8.x86_64.rpm点击自行下载主从进行读写分离之后分别提供不同的服务主提供写服务如createinsertdelete等从提供读服务如select2. MaxScale完整部署流程1MaxScale主机安装软件[rootmaxscale ~]# dnf -y localinstall maxscale-24.02.1-1.rhel.8.x86_64.rpm2备份并修改核心配置文件[rootmaxscale ~]# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak[rootmaxscale ~]# vim /etc/maxscale.cnf配置文件分段说明需要修改配置的部分都加了注释... 12 [maxscale] 13 threadsauto ... #指定要代理的数据库服务器[server2]部分需要自己手工定义 21 [server1] 22 typeserver 23 address192.168.8.100 #指定主服务器地址 24 port3306 25 [server2] 26 typeserver 27 address192.168.8.102 #指定从服务器地址 28 port3306 ... #指定监控用户maxscalemon用于登录后端服务器检查服务器的运行状态和主从状态 47 [MariaDB-Monitor] 48 typemonitor 49 modulemariadbmon 50 serversserver1,server2 #上边的定义的主机 51 usermaxscalemon #指定监控用户 52 password123qqq...A · #指定监控用户的密码 53 monitor_interval2s ... 86 #[Read-Only-Service] #只读服务不需要这段全部注释 87 #typeservice 88 #routerreadconnroute 89 #serversserver1 90 #userservice_user 91 #passwordservice_pw 92 #router_optionsslave ... #定义读写分离服务器配置 99 [Read-Write-Service] 100 typeservice 101 routerreadwritesplit 102 serversserver1,server2 #指定读写分离服务器 103 usermaxscalerouter #指定路由用户 104 password123qqq...A #指定路由用户密码 ... #只读服务配置信息加上注释 118 #[Read-Only-Listener] 119 #typelistener 120 #serviceRead-Only-Service 121 #protocolmariadbprotocol 122 #port4008 ... #读写分离配置信息默认端口号为4006 124 [Read-Write-Listener] 125 typelistener 126 serviceRead-Write-Service 127 protocolmariadbprotocol 128 port4006监控用户maxscalemon后台定时巡检所有后端数据库检测节点是否存活、主从角色、同步是否正常给读写路由提供状态判断依据不处理业务 SQL路由用户maxscalerouter代理业务客户端转发所有业务 SQL将写请求路由到主库、读请求分发到从库实际执行数据增删改查操作3后端主、从库创建两类专用账号根据/etc/maxscale.cnf配置要求需要在master主机和slave主机授权用户maxscalemon用户密码为123qqq…Amaxscalerouter用户密码为123qqq…A创建监控用户maxscalemon用于登录后端服务器检查服务器的状态创建路由用户maxscalerouter,检测客户端的用户名和密码在后端数据库中是否存在REPLICATION SLAVE该权限能够同步数据查看从服务器上slave的状态REPLICATION CLIENT:该权限可以获取数据库服务的状态(数据库服务是否允许主从是否正常服务器是否存活)监控用户maxscalemon监控账号权限需求REPLICATION SLAVE、REPLICATION CLIENT用于检测主从同步状态、服务在线状态Master主机执行因为之前做过主从同步从库会自动同步账号因此无需在从库重复操作mysqlCREATEUSERmaxscalemon%IDENTIFIEDWITHmysql_native_passwordBY123qqq...A;mysqlGRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TOmaxscalemon%;路由用户maxscalerouter路由校验账号权限需求仅需要查询mysql系统库校验客户端账号密码是否存在mysqlCREATEUSERmaxscalerouter%IDENTIFIEDWITHmysql_native_passwordBY123qqq...A;mysqlGRANTSELECTONmysql.*TOmaxscalerouter%;补充若主从同步未生效Slave2需要手动创建上述两个账号并授权。4启动MaxScale服务[rootmaxscale ~]# systemctl restart maxscale[rootmaxscale ~]# systemctl enable maxscale[rootmaxscale ~]# ss -mtulp | grep :40063. 读写分离功能测试1- master主机授权测试用户[rootmaster~]# mysql -uroot -p123qqq...AmysqlCREATEUSERsam%IDENTIFIEDWITHmysql_native_passwordBY123qqq...A;mysqlGRANTALLON.TOsam%;2- maxscale充当客户端访问读写分离服务器#安装mysql连接命令[rootmaxscale ~]# dnf -y install mysql#注意使用P指定端口[rootmaxscale ~]# mysql -h192.168.8.99 -P4006 -usam -p123qqq...AmysqlCREATE DATABASE study;#创建study库mysqlCREATE TABLE study.t1(id INT,name VARCHAR(20));#创建表mysqlINSERT INTO study.t1 VALUES(1,tom);#插入数据分别在Master、Slave2查询数据同步存在。3验证查看数据master主机验证查看数据[rootmaster~]# mysql -uroot -p123qqq...AmysqlSELECT*FROMstudy.t1;------------|id|name|------------|1|tom|------------slave主机验证查看数据[rootslave2 ~]# mysql -uroot -p123qqq...AmysqlSELECT * FROM study.t1;------------|id|name|------------|1|tom|------------4读操作分流验证核心读写分离效果做了读写分离所以如果配置成功我们在从服务器插入jerry的数据主服务器肯定没有jerry此时我们在maxscale主机查询能看到在从主机新插入的jerry数据就能说明我们查看的数据来自于从服务器。直接登录Slave2手动插入一条数据仅从库本地存在不会同步回主库mysqlINSERTINTOstudy.t1VALUES(2,jerry);通过MaxScale代理执行查询能查到Slave独有的jerry数据证明SELECT读请求转发至从库mysqlSELECT*FROMstudy.t1;-------------|id|name|-------------|1|tom||2|jerry|-------------六、全文核心总结MySQL主从同步依靠binlogrelay-log双日志、IOSQL双线程实现数据复制必须保证全库备份初始化主从数据、所有实例server_id唯一。一主一从、一主多从是最常用生产架构搭建核心命令CHANGE MASTER TO、START SLAVE、SHOW SLAVE STATUS \G。异步、半同步、全同步三种复制模式性能与数据安全性成反比默认异步复制。MaxScale作为MariaDB官方中间件监听4006端口自动完成读写分离写流量走主库、读流量分发从库部署需要监控账号与路由校验账号两套专用权限。读写分离验证关键手段手动单独写入从库独有数据通过代理查询能读取即代表读请求分流从库分离生效。