项目05(Mysql升级07Mysql5.7.32升级到Mysql8.0.22)

背景

《承接上文,项目05(Mysql升级06Mysql5.6.51升级到Mysql5.7.32)》,

写在前面

需要(考虑)检查和测试的层面很多，不限于以下内容。

参考文档
https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
link

推荐阅读以上链接，因为对应以下问题，有详细的建议。
官方文档：不得存在以下问题：
0.
不得有使用过时数据类型或功能的表。

不支持就地升级到MySQL 8.0，如果表包含在预5.6.4格式老时间列（TIME， DATETIME，和 TIMESTAMP列不为小数秒精度支持）。如果您的表仍然使用旧的时间列格式，请REPAIR TABLE在尝试就地升级到MySQL 8.0之前使用对其进行升级。欲了解更多信息，请参见 服务器的变化，在 MySQL的5.7参考手册。

不能有孤立.frm文件。

触发器不能有遗漏或空定义者或无效的创作环境（由表示 character_set_client， collation_connection，Database Collation属性显示的 SHOW TRIGGERS或 INFORMATION_SCHEMA TRIGGERS表）。任何此类触发器都必须转储并还原以解决此问题。

要检查这些问题，请执行以下命令：

mysqlcheck -u root -p --all-databases --check-upgrade
如果mysqlcheck报告任何错误，请更正问题。
1.
得有使用不具有本机分区支持的存储引擎的分区表。要标识此类表，请执行以下查询：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
AND CREATE_OPTIONS LIKE '%partitioned%';	   

查询所报告的任何表都必须更改为使用 InnoDB或不分区。要将表存储引擎更改为InnoDB，请执行以下语句：

ALTER TABLE table_name ENGINE = INNODB;
有关将MyISAM 表转换为的信息InnoDB，请参见 第15.6.1.5节“将表从MyISAM转换为InnoDB”。

要使分区表成为非分区表，请执行以下语句：

ALTER TABLE table_name REMOVE PARTITIONING;
2.
在MySQL 8.0中可能保留了一些以前未保留的关键字。
3.
查询报告的任何表都必须删除或重命名（使用RENAME TABLE）。这也可能需要更改使用受影响表的应用程序。
MySQL 5.7mysql系统数据库中的表不得 与MySQL 8.0数据字典使用的表同名。要使用这些名称标识表，请执行以下查询：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
4.
不得有外键约束名称超过64个字符的表。使用此查询来标识约束名称太长的表：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
  对于约束名称超过64个字符的表，请删除该约束并将其添加回不超过64个字符的约束名称（请使用ALTER TABLE）。
5.
sql_mode系统变量 不得定义过时的SQL模式 。尝试使用过时的SQL模式会阻止MySQL 8.0的启动。使用过时的SQL模式的应用程序应该进行修改以避免它们。
sql_mode:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-server-changes  
6.
不得有明确定义的列名超过64个字符的视图（MySQL 5.7中允许使用列名最多为255个字符的视图）。为避免升级错误，应在升级之前更改此类视图。当前，识别列名称超过64个字符的视图的唯一方法是使用来检查视图定义SHOW CREATE VIEW。您也可以通过查询INFORMATION_SCHEMA.VIEWS表来检查视图定义 。
7.
表或存储过程中不得包含长度超过255个字符或1020个字节的单个 ENUM或SET列元素。在MySQL 8.0之前，ENUM或SET列元素的最大组合长度为64K。在MySQL 8.0中，单个元素ENUM或 SET列元素的最大字符长度为255个字符，最大字节长度为1020个字节。（1020字节限制支持多字节字符集）。在升级到MySQL 8.0之前，请修改超出新限制的任何元素ENUM或 SET列元素。否则，将导致升级失败并显示错误。
8.
升级到MySQL 8.0.13或更高版本之前，共享InnoDB表空间中不得存在任何表分区，共享 表空间应包括系统表空间和常规表空间。通过查询来标识共享表空间中的表分区 INFORMATION_SCHEMA：

如果从MySQL 5.7升级，请运行以下查询：

SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
  WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
如果从较早的MySQL 8.0版本升级，请运行以下查询：

SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES
  WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
使用以下命令将表分区从共享表空间移至每个表文件表空间 ALTER TABLE ... REORGANIZE PARTITION：

ALTER TABLE table_name REORGANIZE PARTITION partition_name
  INTO (partition_definition TABLESPACE=innodb_file_per_table);
9. 
MySQL 8.0.12或更低版本中不得有使用ASC或 DESC限定符的GROUP BY子句查询和存储程序定义。否则，升级到MySQL 8.0.13或更高版本可能会失败，就像复制到MySQL 8.0.13或更高版本的副本服务器一样。有关更多详细信息，请参见 SQL更改。
10.
您的MySQL 5.7安装不得使用MySQL 8.0不支持的功能。此处的任何更改都必须是特定于安装的，但以下示例说明了要查找的内容：
11.
一些服务器启动选项和系统变量已在MySQL 8.0中删除。请参见 MySQL 8.0中已删除的功能和 第1.4节“在MySQL 8.0中已添加，已弃用或已删除的服务器和状态变量及选项”。如果使用其中任何一种，则升级需要更改配置。

示例：由于数据字典提供有关数据库对象的信息，因此服务器不再检查数据目录中的目录名称以查找数据库。因此，该--ignore-db-dir选项是多余的，已被删除。要解决此问题，请--ignore-db-dir从启动配置中删除所有实例 。另外，在升级到MySQL 8.0之前，请删除或移动命名的数据目录子目录。（或者，让8.0服务器将这些目录作为数据库添加到数据字典中，然后使用删除每个数据库 DROP DATABASE。）
12.
如果打算lower_case_table_names 在升级时将设置更改 为1，请在升级前确保方案和表名均为小写。否则，由于架构或表名字母大小写不匹配，可能会发生故障。您可以使用以下查询来检查包含大写字符的架构和表名称：

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);
从MySQL 8.0.19开始，如果 lower_case_table_names=1，升级过程会检查表名和模式名，以确保所有字符均为小写。如果发现表或架构名称包含大写字符，则升级过程将失败并显示错误。

笔记
lower_case_table_names 不建议在升级时 更改 设置。
13.
如果由于上述任何问题升级到MySQL 8.0失败，则服务器会将所有更改都还原到数据目录。在这种情况下，请删除所有重做日志文件，然后在现有数据目录上重新启动MySQL 5.7服务器以解决错误。重做日志文件（ib_logfile*）默认位于MySQL数据目录中。修复错误之后，请执行缓慢关机（通过设置 innodb_fast_shutdown=0），然后再次尝试升级。

推荐使用mysql shell检查

安装

1.
https://mirrors.cloud.tencent.com/mysql/downloads/MySQL-Shell/  （下载地址）
2.
tar xvf mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-shell-8.0.23-linux-glibc2.12-x86-64bit/ mysql-shell8.0.23
mv mysql-shell8.0.23/ /usr/local/
3.vim /etc/profile
PATH=$PATH:/usr/local/mysql5.7.32/bin:/usr/local/mysql-shell8.0.23/bin
source /etc/profile
4.mysqlsh --version
mysqlsh   Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))
(低版本)mysql-shell 1.0.11 mysqlsh root@localhost -S /usr/local/mysql5.7.29/mysql.sock   不好用

使用

1.
mysqlsh  
\q  退出
2.
（需要先连接 \connect root@(/tmp/mysql.sock)）
提示符，MySQL  localhost  JS > #表示已链接，MySQL  JS >表示未连接
3.
MySQL  localhost  JS > util.checkForServerUpgrade()
MySQL Shell 8.0
(或)
mysqlsh root@localhost:3306 -e "util.checkForServerUpgrade();"
(或)MySQL  JS > util.checkForServerUpgrade("root@localhost:3306")
(或)util.checkForServerUpgrade({"targetVersion":"8.0.11"})[指定版本检查]
本次检查的结果为：
Errors:   0
Warnings: 18
Notices:  1
No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

根据每项的提示修改如下：
1.
   [mysqld]
    default_authentication_plugin=mysql_native_password
2.根据提示(可选)
把utf8设置为utf8mb4

逻辑升级

命令介绍详见之前文章

1.
mysqlcheck --defaults-file=/etc/my.cnf  -u root -p --all-databases --check-upgrade
2.只备份业务表，用户之后可以重建
mysqldump --socket=/tmp/mysql.sock -u root -p --add-drop-table --routines  --events --master-data=2 --single-transaction --databases  ceshi100 ceshi555 ceshi55555 ceshi666 ceshi66666 ceshi888 ceshi88888 ceshi999  --force > data-for-upgrade.sql
3.
确保数据都刷到硬盘上，更改成0
show variables like 'innodb_fast_shutdown';
set global innodb_fast_shutdown=0; 慢关闭
shutdown;
只要全部数据落盘，其他关闭方式也可以
4.
PATH=$PATH:/usr/local/mysql8.0.22/bin
which mysql
/usr/local/mysql8.0.22/bin/mysql
5.
vim /etc/my.cnf
使用的是https://imysql.cn/my-cnf-wizard.html 生成器，读者根据请自己的需要设置
6.
/usr/local/mysql8.0.22/bin/mysqld --user=mysql --basedir=/usr/local/mysql8.0.22 --datadir=/usr/local/mysql8.0.22/data  --initialize
7.(请在升级过程中，持续关注errorlog)
tailf error.log 
日志中包含 用户root@localhost的密码 -WCppwK&z1Ur
8.启动mysqld
官网使用的是：
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &

也可以：/etc/init.d/mysqld8.0.22 start
cp support-files/mysql.server /etc/init.d/mysqld8.0.22
vim /etc/init.d/mysqld8.0.22

basedir	= /usr/local/mysql8.0.22
datadir	= /usr/local/mysql8.0.22/data

补充：根据启动过程中的日志[Warning]
注释了以下：(为高版本启用参数)
#binlog_expire_logs_seconds = 604800
#innodb_undo_tablespaces = 95  默认2个
修改为transaction_isolation = READ-COMMITTED
[mysqld]
default_authentication_plugin=mysql_native_password

9.设置新密码
mysql -uroot -p  #密码在之前的errorlog里面 -WCppwK&z1Ur
ALTER USER USER() IDENTIFIED BY '123456';

10.
mysql -u root -p --force < data-for-upgrade.sql

11.(以下按照官网文档进行)
执行所有剩余的升级操作：
在MySQL 8.0.16及更高版本中，关闭服务器，然后使用--upgrade=FORCE选项执行以下其余升级任务来重新启动服务器 ：
mysqladmin -u root -p shutdown
/usr/local/mysql8.0.22/bin/mysqld_safe --user=mysql --datadir=/usr/local/mysql8.0.22/data --upgrade=FORCE &

使用重启后 --upgrade=FORCE，服务器会mysql在MySQL 5.7和MySQL 8.0之间的系统架构中进行所需的任何更改 ，以便您可以利用新的特权或功能。它还为MySQL 8.0更新了Performance SchemaINFORMATION_SCHEMA和 Performance Schema， sys并检查了所有用户模式与MySQL当前版本的不兼容性。

在MySQL 8.0.16之前，执行 mysql_upgrade来执行其余的升级任务：
mysql_upgrade -u root -p
然后关闭并重新启动MySQL服务器，以确保对系统表所做的所有更改均生效。例如：
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir/usr/local/mysql8.0.22/data &

12.
推荐重启一下数据库，应用更改
/etc/init.d/mysqld8.0.22 restart

不建议备份系统表的原因之一
gtid_mode=ON如果转储文件包含系统表， 则不建议在服务器（）上启用GTID时加载转储文件。 mysqldump为使用非事务性MyISAM存储引擎的系统表发出DML指令，并且在启用GTID时不允许这种组合。还应注意，将已启用GTID的服务器中的转储文件加载到已启用GTID的另一台服务器中，会生成不同的事务标识符。

升级完成之后注意官网文档提醒

至此Mysql5.0到8.0的升级项目已完结，感谢您的阅读

期间的数据备份恢复，针对大数据量的库也可选择xtrabackup+mysqldump的方式，不足之处是没有更改数据库的字符集，需要增加一步导入导出操作(之前utf8的库还是utf8)

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​