## 更新之前的需要了解的事项
1. 了解MySQL在升级过程中会做什么操作
- 对mysql元数据库做更新
- 数据字典表
- 系统表(如权限,存储过程,触发器,函数表等)
2. 在升级之前需要对数据库做一个完整性备份
- MySQL不支持物理降级(但可以用mysqldump逻辑降级),当升级失败需要有备份进行数据恢复
3. 了解升级的支持版本及路径
- 仅支持5.7GA版本升级到8.0GA版本(GA=General Availability,表示正式发版的版本)
- 推荐5.7先升级到5.7最新版后再升级到8.0
- 不支持跨大版本的升级,如5.6升级到8.0,
- 支持小版本的跨版本升级,如5.7.12升级到5.7.29,8.0.15升级到8.0.21
4. 了解MySQL8.0与MySQL5.7不兼容或变更的点
- 数据字典的改变
- 默认密码插件的改变
- 参数配置的改变
- SQL_MODE的改变
- InnoDB系统表的改变
- SQL语法的改变
5. 了解MySQL8.0的特性
- 新特性
- 已经移出的特性
- 即将废弃的特性
6. 了解MySQL8.0的参数变更
- 新增的参数
- 已经移出的参数
- 即将废弃的参数
7. 升级测试
- 数据库升级测试
- 应用升级测试
## 兼容性检测
>使用mysqlsh提供的util.checkForServerUpgrade()工具做兼容性检测
- https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
```js
util.checkForServerUpgrade('zhenxing@10.186.61.162:3306', {"password":"zhenxing", "targetVersion":"8.0.19", "configPath":"/etc/my.cnf"})
mysqlsh -- util checkForServerUpgrade zhenxing@10.186.61.162:3306 --target-version=8.0.21 --output-format=JSON --config-path=/etc/mysql/my.cnf
// 检测的指标
"id": "oldTemporalCheck", // 是否存在5.6版本的date,datetime,timestamp类型
"id": "reservedKeywordsCheck", // 是否使用到MySQL的关键字,保留字
"id": "utf8mb3Check", // 是否使用utf8字符集,需要转为uft8mb4字符集
"id": "mysqlSchemaCheck", // 对mysql数据库表做校验
"id": "nonNativePartitioningCheck", // 是否存在非InnoDB存储引擎的分区表
"id": "foreignKeyLengthCheck", // 检测外键约束长度
"id": "maxdbFlagCheck", // 检测maxdb标识
"id": "sqlModeFlagCheck", // 检测SQL_MODE是否兼容
"id": "enumSetElementLenghtCheck", // 检测枚举类型是否超长
"id": "partitionedTablesInSharedTablespaceCheck", // 检测分区表是否在共享表空间
"id": "removedFunctionsCheck", // 检测是否有被移除的函数使用
"id": "removedSysVars", // 检测是否存在被删除的系统变量在使用
"id": "sysVarsNewDefaults", // 检测当前配置的系统变量与默认值
"id": "zeroDatesCheck", // 检测date类型对zero的处理逻辑是否合理
"id": "schemaInconsistencyCheck", // 检测数据库是否兼容
"id": "ftsTablenameCheck", // 全文索引表名检查
"id": "engineMixupCheck", // 存储引擎检查
"id": "checkTableOutput", // 检查表当前是否正常且支持升级
"id": "defaultAuthenticationPlugin", // 检查当前的默认密码校验插件
```
## 升级常见的错误
1. .frm文件与数据字典元数据信息不匹配,如.frm或ibd文件损坏,需要先使用mysqldump导入导出做修复
2. 使用mysqld --print-defaults方式校验当前使用的配置文件正确
3. 确保mysql客户端使用了正确的lib包,如libmysqlclient.so.20,libmysqlclient.so.21
## MySQL5.7.27升级到MySQL8.0.21实操步骤
1. 安装最新版的mysqlsh
```bash
yum install mysql-shell-commercial-8.0.21-1.1.el7.x86_64.rpm
```
2. 执行util.checkForServerUpgrade工具检测兼容性
```bash
mysqlsh -- util checkForServerUpgrade zhenxing@10.186.61.162:3306 --target-version=8.0.21 --output-format=JSON --config-path=/etc/mysql/my.cnf
## 检测出40个Warnings
Errors: 3
Warnings: 40
Notices: 0
## 存在使用MySQL关键字的字段名称
- ylgj_council.lshy_user.LEAD - Column name
- ylgj_council.lshy_user_test.LEAD - Column name
alter table ylgj_council.lshy_user change column `LEAD` LEAD_FLAG varchar(10) DEFAULT NULL;
alter table ylgj_council.lshy_user_test change column `LEAD` LEAD_FLAG varchar(10) DEFAULT NULL;
## 存在大量表的字段字符集为utf8
demo.g_device_action.uid - columns default character set: utf8
demo.g_device_action.domain_id - columns default character set: utf8
demo.innodb_index_stats.database_name - columns default character set: utf8
demo.innodb_index_stats.table_name - columns default character set: utf8
demo.innodb_index_stats.index_name - columns default character set: utf8
demo.innodb_index_stats.stat_name - columns default character set: utf8
demo.innodb_index_stats.stat_description - columns default character set:
alter table demo.g_device_action convert to character set utf8mb4;
alter table demo.g_device_action_base convert to character set utf8mb4;
alter table demo.innodb_index_stats convert to character set utf8mb4;
alter table demo.trade_detail convert to character set utf8mb4;
alter table percona.checksums convert to character set utf8mb4;
alter table upi360workshopdb.course_scope_country convert to character set utf8mb4;
## 参数被废弃,需要修改
innodb_undo_logs - is set and will be removed, consider using
innodb_rollback_segments instead
query_cache_size - is set and will be removed
query_cache_type - is set and will be removed
```
```bash
2020-08-25T16:00:11.339985+08:00 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-08-25T16:00:11.344311+08:00 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2020-08-25T16:00:15.221697+08:00 0 [ERROR] [MY-011685] [Repl] Plugin group_replication reported: 'The group_replication_group_name option is mandatory'
2020-08-25T16:00:15.222084+08:00 0 [ERROR] [MY-011660] [Repl] Plugin group_replication reported: 'Unable to start Group Replication on boot'
##
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
```
浙公网安备 33010602011771号