背景几台centos7.9机器ip网段是192.168.2.*mysql8.0.27firewalld互相之间已经放通3306一、一主一从背景192.168.2.7是主192.168.2.8是从1.0 主从服务器必须时间同步-主和从yum install chrony -y systemctl enable chronyd --now1.1 修改配置文件-主vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWsystemctl restart mysqld1.2 创建用于从库复制的用户并赋予权限-主# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;注replication slave是复制必须权限replication client用于查看master状态主从账号建议使用密码插件mysql_native_password使用caching_sha2_password 有可能因多环节中有旧版本的不支持引发2061问题。检查密码插件:select user,host,plugin from mysql.user where user‘rep’;修改密码插件alter user rep‘192.168.2.%’ identified with mysql_native_password by ‘密码.’;1.3 备份全量数据便于从库同步-主# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data2 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给从节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/ # 记录日志标志点位置找CHANGE MASTER TO MASTER_LOG_FILE记录binlog文件名 和 pos head -n 30 /backup/2026-10-31/2026-10-31-15-mysql-all.sql注如CHANGE MASTER TO MASTER_LOG_FILE‘master1-bin.000002’, MASTER_LOG_POS156;记录 master1-bin.000002’ 和 1561.4 从库测试主库rep用户是否可用-从mysql -h 192.168.2.7 -urep -p # 正常登录就没问题注这个账号只有复制权限无读写权限1.5 修改配置-从vim /etc/my.cnfserver-id2read_onlyONsystemctl restart mysqld1.6 先导入主库全量备份文件-从mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql1.7 在从库设置主服务器-从change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_log_filemaster1-bin.000002, source_log_pos156;注source_host是主库这里建议写主库ip不要写hosts容易出错source_user是在主库设置的复制权限账号source_password是复制权限账号的密码source_log_file和source_log_pos是在备份文件那记录的binlog日志文件名和标志点如果使用的是caching_sha2_password,则要使用ssl加上source_ssl1mysql8.0.22之前是用change master to master_hostmaster_usermaster_passwordmaster_log_filemaster_log_pos1.8 启动从设备-从start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 从库io线程连接主库拉取主库binlog写入到从库的relay-log中继日志。Slave_SQL_Running: Yes 从库sql线程读取本地relay-log中继日志在从库回放sql两个必须同时yes才算成功\G输出转向为竖向显示旧版是start slave; show slave status\G新版依旧兼容1.9 注意事项# 配置完主从后需要修改主库信息重新配置change replication source to # 要先停止从库 stop replica; # 清除从库在主库复制过来的所有信息 reset replica all;二、 一主一从gtid好处是不需要记录binlog日志分割点可以让从库与主库自动协商。背景192.168.2.7是主192.168.2.8是从2.0 所有服务器同步时间yum install chrony -y systemctl enable chronyd --now2.1 修改配置文件-主vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWgtid_modeONenforce_gtid_consistency1systemctl restart mysqld2.2 创建用于从库复制的用户并赋予权限-主# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;2.3 备份全量数据便于从库同步-主# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data1 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给从节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/注不再需要看binlog分割点所以配置source-data12.4 从库测试主库rep用户是否可用-从mysql -h 192.168.2.7 -urep -p # 正常登录就没问题注这个账号只有复制权限无读写权限2.5 修改配置-从vim /etc/my.cnfserver-id2read_onlyONgtid_modeONenforce_gtid_consistency1systemctl restart mysqld2.6 先导入主库全量备份文件-从mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql2.7 在从库设置主服务器-从change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_auto_position1;注source_auto_position1自动与主库商量binlog日志的分割标志位2.8 启动从设备-从start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。三、双主双从双主是在一主一从的基础上配置为互为主从然后两从库配置主库的信息背景192.168.2.7是主1192.168.2.8是主2192.168.2.9是从1192.168.2.10是从23.0 所有服务器同步时间yum install chrony -y systemctl enable chronyd --now3.1 修改配置文件-主1vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWgtid_modeONenforce_gtid_consistency1auto-increment-increment2auto-increment-offset1systemctl restart mysqld3.2 修改配置文件-主2vim /etc/my.cnflog_binmaster2-binserver-id2binlog_formatROWgtid_modeONenforce_gtid_consistency1auto-increment-increment2auto-increment-offset2systemctl restart mysqld注auto-increment-incrementauto-increment-offset配置自增主键主1生成奇数主2生成偶数防止自增主键冲突。双主都同时写时必须配置。如果双主中只写其中一台另外一台作为备用可以不配置auto-increment-incrementauto-increment-offset3.3 创建用于从库复制的用户并赋予权限-主1和主2# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;3.4 双主互相测试rep用户是否可用-主1主2mysql -h 192.168.2.8 -urep -p # 正常登录就没问题 mysql -h 192.168.2.7 -urep -p # 正常登录就没问题3.5 先同步双主数据备份全量数据-主1# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data1 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给主2节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/3.6 先导入主1全量备份文件-主2mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql3.7 在主2设置主1服务器-主2change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_auto_position1;3.8 启动主2为主1从设备-主2start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。3.9 在主1设置主2服务器-主1# 校验数据完成同步后再开始 change replication source to source_host192.168.2.8, source_userrep, source_passwordrep用户密码, source_auto_position1;3.10 启动主1为主2从设备-主1start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。可以开始校验双主间是否数据同步至此双主配置成功3.11 配置从1配置文件-从1vim /etc/my.cnfserver-id3read_onlyONgtid_modeONenforce_gtid_consistency1master-info-repositoryTABLErelay-log-info-repositoryTABLEsystemctl restart mysqld注:从机把主的信息存在主信息仓库里。主信息库可以是文件也可以上表具体由–master-info-repository参数值决定。–master-info-repositoryfile时 会生成master.info 和 relay-log.info2个文件。如果–master-info-repositorytable信息就会存在mysql.slave_master_info表中。–relay-log-info-repositorytable写在mysql.slave_relay_log_info表中。3.12 配置从2配置文件-从2vim /etc/my.cnfserver-id4read_onlyONgtid_modeONenforce_gtid_consistency1master-info-repositoryTABLErelay-log-info-repositoryTABLEsystemctl restart mysqld3.13 导入数据-从1从2# 在从1从2创建目录 mkdir -p /backup/2026-10-31/ # 主1/主2发送全量备份文件给从1节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.9:/backup/2026-10-31/ # 主1/主2发送全量备份文件给从2节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.10:/backup/2026-10-31/ # 从1从2导入数据 mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql3.14 设置主库-从1# 配置主1 change replication source to source_host192.168.2.7,source_userrep,source_passwordchaoROOT1314.,source_auto_position1; #开启从服务器 start replica; # 查看 show replica status\G3.15 设置主库-从2# 配置主2 change replication source to source_host192.168.2.8,source_userrep,source_passwordchaoROOT1314.,source_auto_position1; #开启从服务器 start replica; # 查看 show replica status\G注至此双主双从已经配置完毕双主同时写入的业务有可能造成脑裂场景建议配合MGR或keepalive使用。或者只要一主写入另一主作为备库。3.16 附加从服务器多通道 --不建议不建议从库配置多channel追两主库# 配置主1 change replication source to source_host192.168.2.7,source_userrep,source_passwordchaoROOT1314.,source_auto_position1 for channel master1; # 配置主2 change replication source to source_host192.168.2.8,source_userrep,source_passwordchaoROOT1314.,source_auto_position1 for channel master2; #开启从服务器 start replica for channel master1; start replica for channel master2; # 查看master1 show replica status for channel master1 \G # 查看master2 show replica status for channel master2 \G # 同时查看 show replica status;注start replica 会同时启动使用channel3.17 附加# 查看复制同步信息 show replica status\G # 查看主库binlog状态 show master status; # 查看GTID已执行集合 select gtid_executed; # 查看复制账号权限 show grants for rep192.168.2.%;4. 附加经验4.1 mysql集群出问题排查经验# 查看状态 show replica status\Gio线程没问题sql线程出问题代表依旧可以从主库获取binlog数据不会丢失只是sql线程执行中继日志relay_log时遇到一条有问题的sql卡住了记录事务idc9b3e42f-d4de-11f1-8295-000c29f59b92:4到mysqld.log日志中查# 根据事务id在日志中查询 less /var/log/mysqld.log查到是在执行创建’mycatproxy2’192.168.2.11’卡住了只需分析为什么这条sql失败结合错误码Error_code: MY-001396大概率是因为这个用户已存在。可能原因是双主同时手动创建这个用户等sql执行中继日志时用户已存在创建失败。# 在主1主2检查这个用户是否一致 SELECT user,host,plugin,authentication_string FROM mysql.user WHERE usermycatproxy;一致则这条中继日志的sql就没有执行必要了将这个事务跳过即可如果不一致则自己判断取舍额外分析。# 一致情况下 # 关闭复制 stop replica; # 跳过事务-提交空事务 set session.gtid_nextc9b3e42f-d4de-11f1-8295-000c29f59b92:4; begin;commit; set session.gtid_nextAUTOMATIC; # 启动复制 start replica; show replica status\G恢复正常 
长河君 · 2026/11/2 11:17:53 · 阅读 1.2k mysql集群
背景几台centos7.9机器ip网段是192.168.2.*mysql8.0.27firewalld互相之间已经放通3306一、一主一从背景192.168.2.7是主192.168.2.8是从1.0 主从服务器必须时间同步-主和从yum install chrony -y systemctl enable chronyd --now1.1 修改配置文件-主vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWsystemctl restart mysqld1.2 创建用于从库复制的用户并赋予权限-主# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;注replication slave是复制必须权限replication client用于查看master状态主从账号建议使用密码插件mysql_native_password使用caching_sha2_password 有可能因多环节中有旧版本的不支持引发2061问题。检查密码插件:select user,host,plugin from mysql.user where user‘rep’;修改密码插件alter user rep‘192.168.2.%’ identified with mysql_native_password by ‘密码.’;1.3 备份全量数据便于从库同步-主# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data2 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给从节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/ # 记录日志标志点位置找CHANGE MASTER TO MASTER_LOG_FILE记录binlog文件名 和 pos head -n 30 /backup/2026-10-31/2026-10-31-15-mysql-all.sql注如CHANGE MASTER TO MASTER_LOG_FILE‘master1-bin.000002’, MASTER_LOG_POS156;记录 master1-bin.000002’ 和 1561.4 从库测试主库rep用户是否可用-从mysql -h 192.168.2.7 -urep -p # 正常登录就没问题注这个账号只有复制权限无读写权限1.5 修改配置-从vim /etc/my.cnfserver-id2read_onlyONsystemctl restart mysqld1.6 先导入主库全量备份文件-从mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql1.7 在从库设置主服务器-从change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_log_filemaster1-bin.000002, source_log_pos156;注source_host是主库这里建议写主库ip不要写hosts容易出错source_user是在主库设置的复制权限账号source_password是复制权限账号的密码source_log_file和source_log_pos是在备份文件那记录的binlog日志文件名和标志点如果使用的是caching_sha2_password,则要使用ssl加上source_ssl1mysql8.0.22之前是用change master to master_hostmaster_usermaster_passwordmaster_log_filemaster_log_pos1.8 启动从设备-从start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 从库io线程连接主库拉取主库binlog写入到从库的relay-log中继日志。Slave_SQL_Running: Yes 从库sql线程读取本地relay-log中继日志在从库回放sql两个必须同时yes才算成功\G输出转向为竖向显示旧版是start slave; show slave status\G新版依旧兼容1.9 注意事项# 配置完主从后需要修改主库信息重新配置change replication source to # 要先停止从库 stop replica; # 清除从库在主库复制过来的所有信息 reset replica all;二、 一主一从gtid好处是不需要记录binlog日志分割点可以让从库与主库自动协商。背景192.168.2.7是主192.168.2.8是从2.0 所有服务器同步时间yum install chrony -y systemctl enable chronyd --now2.1 修改配置文件-主vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWgtid_modeONenforce_gtid_consistency1systemctl restart mysqld2.2 创建用于从库复制的用户并赋予权限-主# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;2.3 备份全量数据便于从库同步-主# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data1 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给从节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/注不再需要看binlog分割点所以配置source-data12.4 从库测试主库rep用户是否可用-从mysql -h 192.168.2.7 -urep -p # 正常登录就没问题注这个账号只有复制权限无读写权限2.5 修改配置-从vim /etc/my.cnfserver-id2read_onlyONgtid_modeONenforce_gtid_consistency1systemctl restart mysqld2.6 先导入主库全量备份文件-从mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql2.7 在从库设置主服务器-从change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_auto_position1;注source_auto_position1自动与主库商量binlog日志的分割标志位2.8 启动从设备-从start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。三、双主双从双主是在一主一从的基础上配置为互为主从然后两从库配置主库的信息背景192.168.2.7是主1192.168.2.8是主2192.168.2.9是从1192.168.2.10是从23.0 所有服务器同步时间yum install chrony -y systemctl enable chronyd --now3.1 修改配置文件-主1vim /etc/my.cnflog_binmaster1-binserver-id1binlog_formatROWgtid_modeONenforce_gtid_consistency1auto-increment-increment2auto-increment-offset1systemctl restart mysqld3.2 修改配置文件-主2vim /etc/my.cnflog_binmaster2-binserver-id2binlog_formatROWgtid_modeONenforce_gtid_consistency1auto-increment-increment2auto-increment-offset2systemctl restart mysqld注auto-increment-incrementauto-increment-offset配置自增主键主1生成奇数主2生成偶数防止自增主键冲突。双主都同时写时必须配置。如果双主中只写其中一台另外一台作为备用可以不配置auto-increment-incrementauto-increment-offset3.3 创建用于从库复制的用户并赋予权限-主1和主2# 创建新用户 rep让集群192.168.2.的都可以访问 CREATE USER rep192.168.2.% IDENTIFIED WITH mysql_native_password BY 新密码.; #赋予权限 grant replication slave, replication client on *.* to rep192.168.2.%;3.4 双主互相测试rep用户是否可用-主1主2mysql -h 192.168.2.8 -urep -p # 正常登录就没问题 mysql -h 192.168.2.7 -urep -p # 正常登录就没问题3.5 先同步双主数据备份全量数据-主1# 备份 mysqldump -uroot -p --all-databases --single-transaction --source-data1 --flush-logs --column-statistics0 /backup/2026-10-31/date %F-%H-mysql-all.sql # 发送给主2节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.8:/backup/2026-10-31/3.6 先导入主1全量备份文件-主2mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql3.7 在主2设置主1服务器-主2change replication source to source_host192.168.2.7, source_userrep, source_passwordrep用户密码, source_auto_position1;3.8 启动主2为主1从设备-主2start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。3.9 在主1设置主2服务器-主1# 校验数据完成同步后再开始 change replication source to source_host192.168.2.8, source_userrep, source_passwordrep用户密码, source_auto_position1;3.10 启动主1为主2从设备-主1start replica; #查看启动状态 show replica status\G;注Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 必须同时才算成功。可以开始校验双主间是否数据同步至此双主配置成功3.11 配置从1配置文件-从1vim /etc/my.cnfserver-id3read_onlyONgtid_modeONenforce_gtid_consistency1master-info-repositoryTABLErelay-log-info-repositoryTABLEsystemctl restart mysqld注:从机把主的信息存在主信息仓库里。主信息库可以是文件也可以上表具体由–master-info-repository参数值决定。–master-info-repositoryfile时 会生成master.info 和 relay-log.info2个文件。如果–master-info-repositorytable信息就会存在mysql.slave_master_info表中。–relay-log-info-repositorytable写在mysql.slave_relay_log_info表中。3.12 配置从2配置文件-从2vim /etc/my.cnfserver-id4read_onlyONgtid_modeONenforce_gtid_consistency1master-info-repositoryTABLErelay-log-info-repositoryTABLEsystemctl restart mysqld3.13 导入数据-从1从2# 在从1从2创建目录 mkdir -p /backup/2026-10-31/ # 主1/主2发送全量备份文件给从1节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.9:/backup/2026-10-31/ # 主1/主2发送全量备份文件给从2节点 scp /backup/2026-10-31/2026-10-31-15-mysql-all.sql root192.168.2.10:/backup/2026-10-31/ # 从1从2导入数据 mysql -uroot -p /backup/2026-10-31/2026-10-31-15-mysql-all.sql3.14 设置主库-从1# 配置主1 change replication source to source_host192.168.2.7,source_userrep,source_passwordchaoROOT1314.,source_auto_position1; #开启从服务器 start replica; # 查看 show replica status\G3.15 设置主库-从2# 配置主2 change replication source to source_host192.168.2.8,source_userrep,source_passwordchaoROOT1314.,source_auto_position1; #开启从服务器 start replica; # 查看 show replica status\G注至此双主双从已经配置完毕双主同时写入的业务有可能造成脑裂场景建议配合MGR或keepalive使用。或者只要一主写入另一主作为备库。3.16 附加从服务器多通道 --不建议不建议从库配置多channel追两主库# 配置主1 change replication source to source_host192.168.2.7,source_userrep,source_passwordchaoROOT1314.,source_auto_position1 for channel master1; # 配置主2 change replication source to source_host192.168.2.8,source_userrep,source_passwordchaoROOT1314.,source_auto_position1 for channel master2; #开启从服务器 start replica for channel master1; start replica for channel master2; # 查看master1 show replica status for channel master1 \G # 查看master2 show replica status for channel master2 \G # 同时查看 show replica status;注start replica 会同时启动使用channel3.17 附加# 查看复制同步信息 show replica status\G # 查看主库binlog状态 show master status; # 查看GTID已执行集合 select gtid_executed; # 查看复制账号权限 show grants for rep192.168.2.%;4. 附加经验4.1 mysql集群出问题排查经验# 查看状态 show replica status\Gio线程没问题sql线程出问题代表依旧可以从主库获取binlog数据不会丢失只是sql线程执行中继日志relay_log时遇到一条有问题的sql卡住了记录事务idc9b3e42f-d4de-11f1-8295-000c29f59b92:4到mysqld.log日志中查# 根据事务id在日志中查询 less /var/log/mysqld.log查到是在执行创建’mycatproxy2’192.168.2.11’卡住了只需分析为什么这条sql失败结合错误码Error_code: MY-001396大概率是因为这个用户已存在。可能原因是双主同时手动创建这个用户等sql执行中继日志时用户已存在创建失败。# 在主1主2检查这个用户是否一致 SELECT user,host,plugin,authentication_string FROM mysql.user WHERE usermycatproxy;一致则这条中继日志的sql就没有执行必要了将这个事务跳过即可如果不一致则自己判断取舍额外分析。# 一致情况下 # 关闭复制 stop replica; # 跳过事务-提交空事务 set session.gtid_nextc9b3e42f-d4de-11f1-8295-000c29f59b92:4; begin;commit; set session.gtid_nextAUTOMATIC; # 启动复制 start replica; show replica status\G恢复正常