基于主从架构先升级从库切换后升级原主库实现零停机原地升级主库192.168.195.141MySQL 5.7.35 → 8.0.35从库192.168.195.142MySQL 5.7.35 → 8.0.351. 环境信息项目主库 (Master)从库 (Slave)IP192.168.195.141192.168.195.142MySQL版本升级前5.7.35二进制包glibc2.125.7.35二进制包glibc2.12MySQL版本升级后8.0.35二进制包glibc2.178.0.35二进制包glibc2.17server-id12gtid_modeONON2. 升级方式说明原地升级In-Place Upgrade直接在5.7的数据目录上启动8.0MySQL自动完成数据字典升级。主从架构升级策略先升级从库142→ 验证主从切换142提升为主库再升级原主库141→ 配置为新从库可选再次切换回原角色优点无需导出导入数据升级速度快主从架构下可实现零停机升级数据目录原地升级无需额外磁盘空间缺点升级过程不可逆必须做好备份数据字典升级过程中实例不可用如果升级失败回退较复杂3. 前置条件搭建 MySQL 5.7.35 GTID 主从3.1 清理旧环境两台均执行systemctl stop mysqldrm-rf/usr/local/mysqlrm-rf/data/mysql/3306/data/*rm-f/etc/my.cnf unlink /usr/local/mysql2/dev/null3.2 安装 MySQL 5.7.35两台均执行groupaddmysqluseradd-gmysql mysqlcd/usr/local/wgethttps://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gztarxzf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gzln-smysql-5.7.35-linux-glibc2.12-x86_64 mysql /usr/local/mysql/bin/mysql--version# /usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.35, for linux-glibc2.12 (x86_64) using EditLine wrapper3.3 创建数据目录两台均执行mkdir-p/data/mysql/3306/datachownmysql.mysql /data/mysql/3306/data/3.4 编辑配置文件主库 /etc/my.cnf141[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1 gtid_mode ON enforce_gtid_consistency ON从库 /etc/my.cnf142[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2 gtid_mode ON enforce_gtid_consistency ON3.5 初始化实例两台均执行/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf--initialize--usermysql获取临时密码greptemporary password/data/mysql/3306/data/mysqld.err# 141: A temporary password is generated for rootlocalhost: aij#uemP2pJ# 142: A temporary password is generated for rootlocalhost: /5#QW7ciUM83.6 配置 systemd 服务两台均执行创建/etc/systemd/system/mysqld.service[Unit] DescriptionMySQL Server Documentationman:mysqld(8) Documentationhttp://dev.mysql.com/doc/refman/en/using-systemd.html Afternetwork.target Aftersyslog.target [Install] WantedBymulti-user.target [Service] Usermysql Groupmysql Typeforking PIDFile/data/mysql/3306/data/mysqld.pid TimeoutSec0 ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf --pid-file/data/mysql/3306/data/mysqld.pid --daemonize $MYSQLD_OPTS EnvironmentFile-/etc/sysconfig/mysql LimitNOFILE 65535 Restarton-failure RestartPreventExitStatus1 PrivateTmpfalse创建/etc/sysconfig/mysqlMYSQLD_OPTS3.7 启动实例并修改密码两台均执行systemctl daemon-reload systemctl start mysqld systemctlenablemysqld /usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -p临时密码--connect-expired-password-ealter user user() identified by Root123456;3.8 搭建 GTID 主从复制主库141创建复制用户CREATEUSERrepl%IDENTIFIEDBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;从库142建立复制CHANGE MASTERTOMASTER_HOST192.168.195.141,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_AUTO_POSITION1;STARTSLAVE;验证-- 从库执行showslavestatus\G-- Slave_IO_Running: Yes-- Slave_SQL_Running: Yes-- Auto_Position: 1-- Seconds_Behind_Master: 03.9 插入测试数据-- 主库141执行CREATEDATABASEtest_upgrade;USEtest_upgrade;CREATETABLEt1(idINTPRIMARYKEY,nameVARCHAR(20));INSERTINTOt1VALUES(1,before_upgrade),(2,mysql57),(3,gtid_test);SELECT*FROMt1;-- ---------------------- | id | name |-- ---------------------- | 1 | before_upgrade |-- | 2 | mysql57 |-- | 3 | gtid_test |-- --------------------从库验证SELECT*FROMtest_upgrade.t1;-- 数据一致主从复制正常4. 原地升级步骤4.1 下载 MySQL 8.0.35两台均执行cd/usr/local/wgethttps://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xztarxJf mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz4.2 第一步升级从库1424.2.1 停止从库复制-- 在从库142执行STOP SLAVE;4.2.2 停止MySQL服务systemctl stop mysqld4.2.3 修改配置文件编辑/etc/my.cnf新增8.0必需参数[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2 gtid_mode ON enforce_gtid_consistency ON default_authentication_plugin mysql_native_password配置变更说明新增default_authentication_plugin mysql_native_password8.0默认使用caching_sha2_password需保持与5.7兼容去掉query_cache_type/query_cache_size如果5.7配置中有8.0已移除查询缓存功能本环境5.7最小化配置中没有query_cache相关参数无需去掉4.2.4 切换软链接到8.0unlink /usr/local/mysqlln-smysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql# 验证/usr/local/mysql/bin/mysql--version# /usr/local/mysql/bin/mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)4.2.5 启动8.0自动升级数据字典systemctl start mysqld重要启动8.0时MySQL会自动检测5.7的数据目录并执行升级。这个过程在error日志中可以看到。如果表很大升级可能较慢。4.2.6 检查升级日志grep-iupgrade/data/mysql/3306/data/mysqld.err|tail-5输出2026-07-26T15:39:30.96345108:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2026-07-26T15:39:31.58149608:00 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80035 started. 2026-07-26T15:39:32.76012708:00 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80035 completed.关键信息Server upgrade from 50700 to 80035 completed表示升级成功完成。4.2.7 验证从库升级-- 版本确认SELECTVERSION();-- 8.0.35-- 数据完整性USEtest_upgrade;SELECT*FROMt1;-- ---------------------- | id | name |-- ---------------------- | 1 | before_upgrade |-- | 2 | mysql57 |-- | 3 | gtid_test |-- ---------------------- .frm文件已消除8.0使用数据字典SELECTCOUNT(*)ASfrm_countFROMinformation_schema.FILESWHEREFILE_NAMELIKE%.frm%;-- 0-- MyISAM系统表已转为InnoDBSELECTTABLE_NAME,ENGINEFROMinformation_schema.TABLESWHERETABLE_SCHEMAmysqlANDENGINEMyISAM;-- Empty set无MyISAM系统表-- GTID状态SHOWVARIABLESLIKEgtid_mode;-- ON-- 认证插件SHOWVARIABLESLIKEdefault_authentication_plugin;-- mysql_native_password4.2.8 再次重启确认systemctl restart mysqld /usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-eSELECT VERSION();# 8.0.354.3 第二步主从切换将从库142提升为主库原主库141降为从库。4.3.1 在142上停止复制并提升为主库-- 在142执行STOP SLAVE;RESET SLAVEALL;-- 确认142可以独立运行SHOWMASTERSTATUS;-- ------------------------------------------------------------------------------------------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |-- ------------------------------------------------------------------------------------------------------ | binlog.000002 | 157 | | | 9eec700e-88c4-11f1-9843-000c29048d1f:1-6 |-- ----------------------------------------------------------------------------------------------------4.3.2 在142上创建复制用户-- 在142执行CREATEUSERIFNOTEXISTSrepl%IDENTIFIEDWITHmysql_native_passwordBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;4.4 第三步升级原主库1414.4.1 停止MySQL服务systemctl stop mysqld4.4.2 修改配置文件编辑/etc/my.cnf[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1 gtid_mode ON enforce_gtid_consistency ON default_authentication_plugin mysql_native_password4.4.3 切换软链接到8.0unlink /usr/local/mysqlln-smysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql /usr/local/mysql/bin/mysql--version# /usr/local/mysql/bin/mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)4.4.4 启动8.0自动升级数据字典systemctl start mysqld4.4.5 检查升级日志grep-iupgrade/data/mysql/3306/data/mysqld.err|tail-5输出2026-07-26T15:42:16.74368608:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2026-07-26T15:42:17.35051108:00 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80035 started. 2026-07-26T15:42:18.50895208:00 5 [System] [MY-013381] [Server] Server upgrade from 50700 to 80035 completed.4.4.6 验证141升级SELECTVERSION();-- 8.0.35USEtest_upgrade;SELECT*FROMt1;-- ---------------------- | id | name |-- ---------------------- | 1 | before_upgrade |-- | 2 | mysql57 |-- | 3 | gtid_test |-- --------------------4.4.7 再次重启确认systemctl restart mysqld /usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-eSELECT VERSION();# 8.0.354.5 第四步恢复主从架构将141配置为142的从库恢复主从复制主从角色已互换142为主141为从。-- 在141执行CHANGE MASTERTOMASTER_HOST192.168.195.142,MASTER_USERrepl,MASTER_PASSWORD123456,MASTER_AUTO_POSITION1,GET_MASTER_PUBLIC_KEY1;STARTSLAVE;验证主从状态-- 在141执行showslavestatus\GSlave_IO_State: Waiting for source to send event Master_Host: 192.168.195.142 Master_Port: 3306 Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Auto_Position: 14.6 数据同步验证-- 在新主库142写入数据USEtest_upgrade;INSERTINTOt1VALUES(4,after_upgrade_80);SELECT*FROMt1;-- ------------------------ | id | name |-- ------------------------ | 1 | before_upgrade |-- | 2 | mysql57 |-- | 3 | gtid_test |-- | 4 | after_upgrade_80 |-- ------------------------ 在新从库141查询验证SELECT*FROMtest_upgrade.t1;-- ------------------------ | id | name |-- ------------------------ | 1 | before_upgrade |-- | 2 | mysql57 |-- | 3 | gtid_test |-- | 4 | after_upgrade_80 |-- ----------------------数据一致主从复制正常5. 升级后全面验证5.1 两台节点版本确认-- 141SELECTVERSION();-- 8.0.35-- 142SELECTVERSION();-- 8.0.355.2 GTID状态确认-- 141从库SHOWVARIABLESLIKEgtid_executed;-- 9eeb4935-88c4-11f1-ab8f-000c29a59955:1-3, 9eec700e-88c4-11f1-9843-000c29048d1f:1-6-- 142主库SHOWVARIABLESLIKEgtid_executed;-- 9eeb4935-88c4-11f1-ab8f-000c29a59955:1-3, 9eec700e-88c4-11f1-9843-000c29048d1f:1-6两台GTID一致。5.3 数据字典升级确认-- .frm文件已消除SELECTCOUNT(*)ASfrm_countFROMinformation_schema.FILESWHEREFILE_NAMELIKE%.frm%;-- 0-- MyISAM系统表已全部转为InnoDBSELECTTABLE_NAME,ENGINEFROMinformation_schema.TABLESWHERETABLE_SCHEMAmysqlANDENGINEMyISAM;-- Empty set5.4 认证插件确认SHOWVARIABLESLIKEdefault_authentication_plugin;-- mysql_native_passwordSELECTuser,host,pluginFROMmysql.userWHEREuserrepl;-- ------------------------------------- | user | host | plugin |-- ------------------------------------- | repl | % | mysql_native_password |-- -----------------------------------6. 兼容性注意事项6.1 认证插件MySQL 8.0默认使用caching_sha2_password5.7使用mysql_native_password。原地升级后需在my.cnf中添加default_authentication_plugin mysql_native_password否则5.7客户端可能无法连接8.0实例。6.2 query_cache 相关参数MySQL 8.0移除了查询缓存功能以下参数在8.0中不再支持query_cache_typequery_cache_sizequery_cache_limit如果5.7的my.cnf中有这些参数升级到8.0前必须去掉否则8.0无法启动。6.3 8.0新增关键字MySQL 8.0新增了RANK、DENSE_RANK、ROW_NUMBER、GROUPS等关键字。如果5.7中使用了这些名称作为表名或列名升级后需要用反引号引用-- 5.7中可以CREATETABLErank(idINT);-- 8.0中需要反引号CREATETABLErank(idINT);SELECT*FROMrank;6.4 .frm文件MySQL 8.0使用数据字典Data Dictionary替代了5.7的.frm文件。原地升级后.frm文件会被自动清理。6.5 数据字典自动升级MySQL 8.0启动时会自动检测5.7的数据目录并执行升级包括将MyISAM系统表转为InnoDB升级数据字典格式移除.frm文件升级过程在error日志中可见无需手动执行mysql_upgrade8.0中已废弃。6.6 支持的升级路径仅支持从MySQL 5.7 GA版本直接升级到8.0不支持跨大版本升级如5.6→8.0建议升级前先升级到5.7的最新小版本如5.7.35→5.7.44再升级到8.07. 升级后最终配置文件/etc/my.cnf141升级后当前为从库[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 1 gtid_mode ON enforce_gtid_consistency ON default_authentication_plugin mysql_native_password/etc/my.cnf142升级后当前为主库[client] socket /data/mysql/3306/data/mysql.sock [mysqld] basedir /usr/local/mysql datadir /data/mysql/3306/data user mysql port 3306 socket /data/mysql/3306/data/mysql.sock log_error /data/mysql/3306/data/mysqld.err log_timestamps system server-id 2 gtid_mode ON enforce_gtid_consistency ON default_authentication_plugin mysql_native_password软链接两台均相同/usr/local/mysql - mysql-8.0.35-linux-glibc2.17-x86_648. 连接信息# 141当前从库/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456# 142当前主库/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot1234569. 回退方案原地升级不可逆回退需要从备份恢复停止8.0实例恢复5.7的数据目录备份切换软链接回5.7unlink /usr/local/mysql ln -s mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql恢复5.7的my.cnf去掉default_authentication_plugin启动5.7实例重要原地升级前必须做好数据备份数据无价。10. 升级流程总结┌─────────────────────────────────────────────────────────┐ │ MySQL 5.7 → 8.0 主从架构原地升级流程 │ ├─────────────────────────────────────────────────────────┤ │ │ │ 1. 搭建5.7 GTID主从141主142从 │ │ ↓ │ │ 2. 升级从库142停5.7 → 改my.cnf → 切软链接 → 启8.0 │ │ ↓ │ │ 3. 验证142升级成功版本、数据、error日志 │ │ ↓ │ │ 4. 主从切换142提升为主库141降为从库 │ │ ↓ │ │ 5. 升级原主库141停5.7 → 改my.cnf → 切软链接 → 启8.0 │ │ ↓ │ │ 6. 验证141升级成功 │ │ ↓ │ │ 7. 141配置为142的从库恢复主从架构 │ │ ↓ │ │ 8. 数据同步验证升级完成 │ │ │ │ 结果141(从) ← 8.0.35 GTID复制 → 142(主) │ │ 两台均已从5.7.35原地升级到8.0.35 │ └─────────────────────────────────────────────────────────┘