MySQL5.7升级版本到8.0

升级二进制包安装的MySQL

一、In-Place Upgrade（替代升级）

替代升级涉及到shutdown down旧版本的MySQL，用新版本的包替代旧版本的二进制包，用存在的数据文件目录重启MySQL，升级剩余需要升级的部分。

1.查看章节 ‘Before You Begin’

2.通过 ‘预先检查环节’，确保升级准备已完成，否则升级过程会失败

  预先准备：

1）以下问题必须没有出现
  ·必须确保没有表用到废弃的数据类型或函数，如有使用upgrade them using REPAIR TABLE
  ·必须没有单独的.frm文件，跟.ibd文件是成对存在
  ·触发器必须是正常的
为检查这些问题，可以执行这个命令：
mysqlcheck -u root -p --all-databases --check-upgrade

2）检查分区表是否使用了不支持本地分区的存储引擎
  使用这个命令检查：
     SELECT TABLE_SCHEMA, TABLE_NAME
     FROM INFORMATION_SCHEMA.TABLES
     WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
    AND CREATE_OPTIONS LIKE '%partitioned%';
  任何由上边查询出来的表，可以通过命令更改存储引擎
    ALTER TABLE table_name ENGINE = INNODB;
  让分区表不分区，通过命令：
    ALTER TABLE table_name REMOVE PARTITIONING;

3）保留字的问题
语句中使用到的保留字要用backtick[反引号]标识，才可用

4）确保在现有的mysql系统库没有8.0要用到的表名相同的表，如有要rename表名或删除
  查询命令：
    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'
);

5）必须没有表外键名称超过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);

6）必须确保升级后没有废弃的SQL mode系统参数，sql mode不能包含NO_AUTO_CREATE_USER，否则启动不了；
推荐sql mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
  
7）视图的字段名超过64个字符要整改（5.7是可以达255字符长）

8）确认没有存储过程的 ENUM或SET 字段超过255个字符

9）确保没有表分区在共享表空间内
  查询命令：
    SELECT DISTINCT NAME, SPACE, SPACE_TYPE 
    FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
    WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
  从一个比较早版本的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 table_name REORGANIZE PARTITION partition_name
    INTO (partition_definition TABLESPACE=innodb_file_per_table);

10）没有查询用到了ASC/DESC跟在group by后的从句

11）一些启动的条件被移除了在8.0

12）区分大小写，如要设置 lower_case_table_names=1，确保没有大写字符：
  查看表或schema是否有大写字符，查看命令：
    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);

假如在超出以上问题的情况下升级到8.0失败，server会恢复对数据目录所做的所有改变。在这种情况，删除redo log files并重启5.7 server用存在的数据目录来解决错误。
重启的时候会重新产生redo log文件。解决了报错在尝试重新进行升级。

3.假如你有用XA transactions，执行 XA COMMIT or XA ROLLBACK 进行解决

4.假如有加密的表空间，执行 ：ALTER INSTANCE ROTATE INNODB MASTER KEY;

5.设置innodb_fast_shutdown

mysql> SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown
mysql> SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown，推荐
在一个fast/slow shutdown模式，undo logs和data files处于一种处理不同版本的文件格式的状态，利于升级

6.关闭MySQL

# mysqladmin -u root -p shutdown --socket=/data/mysql/mysql.sock

7.解压MySQL8.0二进制包

8.启动MySQL8.0，用存在的数据目录

# mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &

当你启动MySQL8的时候，为所有数据库的对象升级元数据，并删除.frm文件（记录元数据的存储介质）

9.在上个步骤，server升级了数据字典；现在执行剩余的升级操作：

·大于MySQL8.0.16版本，启动MySQL时升级了数据字典并更新了各个系统库的表、检查各个业务库是否兼容当前8.0版本。说人话就是第8个步骤执行完就等于升级成功（如没有报错）

·对于MySQL8.0.16版本之前的版本，之前以上步骤只是升级了数据字典，启动成功之后还需执行mysql_upgrade来执行剩余的升级步骤
   mysql_upgrade -u root -p
 然后关闭并新启动MySQL，确保对系统表所做的更改生效：
   mysqladmin -u root -p shutdown
   mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &
 在第一次启动MySQL8.0时你会注意到错误日志有忽略未升级的表，如果mysql_upgrade执行成功，第二次启动的时候就没有这样的错误了

二、Logical Upgrade（逻辑升级）

1.查看信息章节，Section 2.11.1, “Before You Begin”.

 2.导出数据

mysqldump -u root -p
  --add-drop-table --routines --events
  --all-databases --force > data-for-upgrade.sql

注释：--routines and --events是导出存储过程，--all-databases会把mysql系统库也导出来
     有创建虚拟列（generated column）的要规避，避免导入出现语法错误

3.导入上个步骤的dump文件到新的MySQL实例（即MySQL8.0版本）

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

4. 执行一些剩余的升级操作

• 在MySQL8.0.16或更高版本，关闭server，再重启server带 --upgrade=FORCE条件来执行剩余的升级操作  

mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &

带着--upgrade=FORCE重启，server做一些mysql升到mysql8所需的改动，也做一些performance_schema、information_schema、sys和业务库到mysql8版本的兼容问题

• 在MySQL8.0.16之前的版本

升级
# mysql_upgrade -u root -p

重启生效
# mysqladmin -u root -p shutdown
# mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &

5.注意是否需要删除MySQL系统库的event和proc表

DROP TABLE mysql.event;
DROP TABLE mysql.proc;

三、主从复制升级

MySQL supports replication from one release series to the next higher release series. 
For example, you can replicate from a source running MySQL 5.6 to a replica running MySQL 5.7
, from a source running MySQL 5.7 to a replica running MySQL 8.0, and so on. However, 
you might encounter difficulties when replicating from an older source to a newer replica if 
the source uses statements or relies on behavior no longer supported in the version of MySQL used on the replica. For example, 
foreign key names longer than 64 characters are no longer supported from MySQL 8.0.

允许通过主从复制来进行升级

四、升级的注意点

从5.7升级到8.0的注意点：
1.关键字的占用问题，如：rank()、system； 【MySQL 8.0 New Keywords and Reserved Words，只看新增的关键字】 
  https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series

2.日期比较的问题，occurDate<'2022-02-28 00:00:00'可以查出行，'2022-02-29 00:00:00'不存在的日期会导致条件为否查不出数据
例子：
select * 
from sod.sod_payment_bill_info
where serviceCode='SC5001050001' AND
      occurDate BETWEEN '2022-02-01 00:00:00'
      and '2022-02-29 00:00:00'
      
3.不支持{GROUP BY ASC DESC}：select id,count(*) from t group by id desc; 

4.mysql8.0.16之后的外键没有加CONSTRAINT symbol，则系统自动生成一个constraint name；MySQL5.7则会按index_name生成一个constraint name 
  例子：alter table tab2 add CONSTRAINT fk_ttid FOREIGN KEY [index_name] (tt_id) REFERENCES tt(id); 

5.IFNULL()、CASE()这两个函数需要确认是否有报错 【https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-sql-changes】

6.character_set_server and character_set_database由latin1变成utf8mb4，collation_server and collation_database由latin1_swedish_ci变成utf8mb4_0900_ai_ci

7.sql_mode不能包含NO_AUTO_CREATE_USER