MySQL 5.7.35 升级到 8.0.35 — 平滑迁移（数据迁移方式）

基于mysqldump导出5.7数据导入到全新8.0实例实现平滑迁移源实例192.168.195.141MySQL 5.7.35端口3306目标实例192.168.195.141MySQL 8.0.35端口3307→33061. 环境信息项目源实例5.7目标实例8.0IP192.168.195.141192.168.195.141MySQL版本5.7.35二进制包glibc2.128.0.35二进制包glibc2.17端口33063307迁移期间→ 3306切换后server-id110迁移期间→ 1切换后gtid_modeONON2. 升级方式说明平滑迁移数据迁移方式在5.7实例运行的同时新建一个8.0实例通过mysqldump导出5.7的用户数据导入到8.0实例验证无误后切换应用。优点安全可控5.7实例始终可用可随时回退不依赖数据目录兼容性8.0实例是全新初始化的缺点需要双实例并行运行占用更多资源迁移期间5.7的新增数据需要增量同步大数据量时mysqldump导出导入耗时较长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 mysql# 解压二进制包cd/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: 6Y:DTTfAGHAO# 142: A temporary password is generated for rootlocalhost: fDlZ3cDg%uhF3.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: 13.9 插入测试数据-- 主库141执行CREATEDATABASEtest_gtid;USEtest_gtid;CREATETABLEt1(idINTPRIMARYKEY,nameVARCHAR(20));INSERTINTOt1VALUES(1,gtid_data);SELECT*FROMt1;-- ----------------- | id | name |-- ----------------- | 1 | gtid_data |-- ---------------4. 平滑迁移步骤4.1 下载并解压 MySQL 8.0.35cd/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.xz注意8.0.35使用glibc2.17编译5.7.35使用glibc2.12编译两者可以共存。4.2 创建8.0实例的数据目录mkdir-p/data/mysql/3307/datachownmysql.mysql /data/mysql/3307/data/4.3 编辑8.0实例配置文件创建/data/mysql/3307/my.cnf[client] socket /data/mysql/3307/data/mysql.sock [mysqld] basedir /usr/local/mysql-8.0.35-linux-glibc2.17-x86_64 datadir /data/mysql/3307/data user mysql port 3307 socket /data/mysql/3307/data/mysql.sock log_error /data/mysql/3307/data/mysqld.err log_timestamps system log-bin mysql-bin server-id 10 gtid_mode ON enforce_gtid_consistency ON default_authentication_plugin mysql_native_password关键参数说明port 3307与5.7实例3306并行运行互不冲突server-id 10与5.7实例1不同default_authentication_plugin mysql_native_password8.0新增参数保持与5.7兼容的认证方式。8.0默认使用caching_sha2_password5.7客户端可能无法连接basedir直接指向8.0的安装目录不使用软链接4.4 初始化8.0实例/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysqld --defaults-file/data/mysql/3307/my.cnf--initialize--usermysql获取临时密码greptemporary password/data/mysql/3307/data/mysqld.err# A temporary password is generated for rootlocalhost: nlFtkR/Fy7X34.5 启动8.0实例/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysqld --defaults-file/data/mysql/3307/my.cnf --pid-file/data/mysql/3307/data/mysqld.pid--daemonize修改root密码/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysql-uroot-S/data/mysql/3307/data/mysql.sock -pnlFtkR/Fy7X3--connect-expired-password-ealter user user() identified by Root123456;4.6 导出5.7用户数据重要只导出用户数据库不导出系统库mysql、information_schema、performance_schema、sys。5.7的系统表使用MyISAM引擎8.0的系统表使用InnoDB引擎直接导入会报错。/usr/local/mysql/bin/mysqldump-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456\--databasestest_gtid\--routines--triggers--events\--set-gtid-purgedOFF\--single-transaction\/tmp/57_user_data.sql参数说明--databases test_gtid只导出用户数据库排除系统库--set-gtid-purgedOFF8.0是全新实例不需要设置GTID_PURGED--single-transactionInnoDB表一致性快照导出不锁表4.7 导入数据到8.0实例# 先清空8.0的GTID信息/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysql-uroot-S/data/mysql/3307/data/mysql.sock -pRoot123456-eRESET MASTER;# 导入数据/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysql-uroot-S/data/mysql/3307/data/mysql.sock -pRoot123456/tmp/57_user_data.sql4.8 验证8.0实例数据-- 版本确认SELECTVERSION();-- 8.0.35-- 数据库列表SHOWDATABASES;-- ---------------------- | Database |-- ---------------------- | information_schema |-- | mysql |-- | performance_schema |-- | sys |-- | test_gtid |-- ---------------------- 数据验证USEtest_gtid;SELECT*FROMt1;-- ----------------- | id | name |-- ----------------- | 1 | gtid_data |-- ----------------- 认证插件确认SHOWVARIABLESLIKEdefault_authentication_plugin;-- mysql_native_password4.9 验证8.0新功能-- 窗口函数8.0新功能SELECTid,name,ROW_NUMBER()OVER(ORDERBYid)ASrnFROMtest_gtid.t1;-- --------------------- | id | name | rn |-- --------------------- | 1 | gtid_data | 1 |-- --------------------- Instant ADD Column8.0新功能不锁表加字段ALTERTABLEt1ADDCOLUMNageINTDEFAULT0,ALGORITHMINSTANT;SELECT*FROMt1;-- ----------------------- | id | name | age |-- ----------------------- | 1 | gtid_data | 0 |-- ----------------------- JSON增强功能CREATETABLEjson_test(idINTPRIMARYKEY,dataJSON);INSERTINTOjson_testVALUES(1,{name:test,value:100});SELECTid,JSON_EXTRACT(data,$.name)ASjnameFROMjson_test;-- -------------- | id | jname |-- -------------- | 1 | test |-- ------------DROPTABLEjson_test;4.10 创建复制用户-- 在8.0实例上创建复制用户为后续切换做准备CREATEUSERrepl%IDENTIFIEDWITHmysql_native_passwordBY123456;GRANTREPLICATIONSLAVEON*.*TOrepl%;4.11 切换应用停5.78.0接管3306端口# 1. 停止5.7实例/usr/local/mysql/bin/mysqladmin-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456shutdown# 2. 停止8.0实例/usr/local/mysql-8.0.35-linux-glibc2.17-x86_64/bin/mysqladmin-uroot-S/data/mysql/3307/data/mysql.sock -pRoot123456shutdown# 3. 将8.0数据目录移到3306rm-rf/data/mysql/3306/datamv/data/mysql/3307/data /data/mysql/3306/data/mysql/3306/datachown-Rmysql.mysql /data/mysql/3306/data# 4. 更新my.cnf端口改为3306basedir改为软链接cat/etc/my.cnfEOF [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 EOF# 5. 切换软链接到8.0unlink /usr/local/mysqlln-smysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql# 6. 启动8.0systemctl start mysqld4.12 最终验证-- 版本确认SELECTVERSION();-- 8.0.35-- 数据完整性USEtest_gtid;SELECT*FROMt1;-- ----------------------- | id | name | age |-- ----------------------- | 1 | gtid_data | 0 |-- ----------------------- GTID状态SHOWVARIABLESLIKEgtid_mode;-- ON-- 认证插件SHOWVARIABLESLIKEdefault_authentication_plugin;-- mysql_native_password-- 复制用户SELECTuser,host,pluginFROMmysql.userWHEREuserrepl;-- ------------------------------------- | user | host | plugin |-- ------------------------------------- | repl | % | mysql_native_password |-- -----------------------------------再次重启确认systemctl restart mysqld /usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot123456-eSELECT VERSION();# 8.0.355. 兼容性注意事项5.1 认证插件MySQL 8.0默认使用caching_sha2_password5.7使用mysql_native_password。升级后需在my.cnf中添加default_authentication_plugin mysql_native_password否则5.7客户端可能无法连接8.0实例。5.2 系统表引擎MySQL 5.7的系统表使用MyISAM引擎8.0使用InnoDB引擎。平滑迁移时不要导出系统库mysql、sys等只导出用户数据库。5.3 8.0新增关键字MySQL 8.0新增了RANK、DENSE_RANK、ROW_NUMBER、GROUPS等关键字。如果5.7中使用了这些名称作为表名或列名升级后需要用反引号引用-- 5.7中可以CREATETABLErank(idINT);-- 8.0中需要反引号CREATETABLErank(idINT);SELECT*FROMrank;5.4 query_cache 相关参数MySQL 8.0移除了查询缓存功能以下参数在8.0中不再支持query_cache_typequery_cache_sizequery_cache_limit如果5.7的my.cnf中有这些参数升级到8.0前必须去掉。5.5 .frm文件MySQL 8.0使用数据字典Data Dictionary替代了5.7的.frm文件。平滑迁移后8.0实例中不会有.frm文件。6. 升级后最终配置文件/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软链接/usr/local/mysql - mysql-8.0.35-linux-glibc2.17-x86_647. 连接信息# 升级后连接/usr/local/mysql/bin/mysql-uroot-S/data/mysql/3306/data/mysql.sock -pRoot1234568. 回退方案如果升级后发现问题可以回退到5.7停止8.0实例切换软链接回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的数据目录如果有备份启动5.7实例注意平滑迁移方式下5.7的数据目录在切换前应保留备份这是回退的关键保障。