1.逻辑导出和导入降级

虽然MySQL 5.7到5.6官方支持逻辑降级:但是从8.0降级到5.7却并非如此。官方文档只是简单提到不支持，因此，如果你尝试就要接受对应的风险。无法将8.0的系统表导入到5.7中。

从8.0降级到5.7，如果你参考到5.7到5.6的一些步骤。(参考：https://dev.mysql.com/doc/refman/5.7/en/downgrade-binary-package.html#downgrade-procedure-logical)

你会看到很多类似如下的错误：

$ mysql --force < 8.0.33.dump
ERROR 1273 (HY000) at line 24: Unknown collation: 'utf8mb4_0900_ai_ci'
ERROR 1726 (HY000) at line 35: Storage engine 'InnoDB' does not support system tables. [mysql.columns_priv]
ERROR 1146 (42S02) at line 51: Table 'mysql.columns_priv' doesn't exist
ERROR 1812 (HY000) at line 63: InnoDB: A general tablespace named `mysql` cannot be found.
ERROR 1146 (42S02) at line 119: Table 'mysql.db' doesn't exist
ERROR 1146 (42S02) at line 1021: Table 'mysql.user' doesn't exist

5.7实例也受到损坏，因为缺少一些关键的表。即使常用的upgrade也无法修复：

mysql_upgrade -uroot -p
Checking if update is needed.
Checking server version.
Error occurred: Query against mysql.user table failed when checking the mysql.session.

因此，你必须只是将用户数据dump出来，系统表需要从升级前的备份中获取。升级后8.0对系统表做的任何修改可能都会遇到问题，因为一些基础命令已经发生了改变。例如，你无法dump和还原用户创建内容：

mysql 80 > show create user msandbox_ro@localhostG
*************************** 1. row ***************************
CREATE USER for msandbox_ro@localhost: CREATE USER `msandbox_ro`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

mysql 57 > CREATE USER `msandbox_ro`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' at line 1

这就意味着，如果你想降级，你就必须跟踪每个新特性。比如账户、事件、存储过程等。

值得一提的是，逻辑导出可以使用更快的工具，比如MySQL Shell、mydumper，util.dumpInstance()缺省情况下不会导出系统表；util.loadDump()也支持排除指定的表。对于不支持的行为，首先会给出警告：

MySQL  localhost:5751 ssl  JS > util.loadDump("/data/backup/mysql80_1/", {threads: 4})
Loading DDL and Data from '/data/backup/mysql80_1/' using 4 threads.
Opening dump...
Target is MySQL 5.7.42. Dump was produced from MySQL 8.0.33
ERROR: Destination MySQL version is older than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. Enable the 'ignoreVersion' option to load anyway.
Util.loadDump: MySQL version mismatch (MYSQLSH 53011)

但是，可以强制执行：

MySQL localhost:5751 ssl JS > util.loadDump("/data/backup/mysql80_1/", {threads: 4, ignoreVersion: true})
Loading DDL and Data from '/data/backup/mysql80_1/' using 4 threads.
Opening dump...
Target is MySQL 5.7.42. Dump was produced from MySQL 8.0.33
WARNING: Destination MySQL version is older than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway.
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
4 thds loading / 100% (108.61 MB / 108.61 MB), 16.27 MB/s, 124 / 191 tables and partitions done
Recreating indexes - done
Executing common postamble SQL
191 chunks (800.14K rows, 108.61 MB) for 155 tables in 6 schemas were loaded in 9 sec (avg throughput 16.08 MB/s)
0 warnings were reported during the load.

2.使用5.7的replica作为降级的备份路径

在升级到MySQL 8.0后的一段时间内，拥有一个副本留在5.7版本上似乎是个选择。但是，这同样没有得到官方支持：https://dev.mysql.com/doc/refman/8.0/en/replication-compatibility.html

从8.0复制到5.7需要做一点努力：https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/

有个主要的问题就是字符集的问题。

mysql 80 > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes';
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         | 0       | NO PAD        |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
1 row in set (0.08 sec)

mysql 57 > select * from information_schema.COLLATIONS where CHARACTER_SET_NAME='utf8mb4' AND IS_DEFAULT='Yes';
+--------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+----+------------+-------------+---------+
| utf8mb4_general_ci | utf8mb4            | 45 | Yes        | Yes         | 1       |
+--------------------+--------------------+----+------------+-------------+---------+
1 row in set (0.00 sec)
mysql 57 > select * from information_schema.COLLATIONS where id=255;
Empty set (0.00 sec)

一个针对8.0的简单sysbench将破坏到5.7版本的复制。

Relay_Master_Log_File: mysql-bin.000023
Exec_Master_Log_Pos: 761428
Last_SQL_Errno: 22
Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/data/opt/mysql/5.7.42/share/charsets/Index.xml' file' on query. Default database: 'test'. Query: 'BEGIN'

对应的二进制日志中的set：

# at 761428
#230624 22:33:55 server id 5748 end_log_pos 761514 CRC32 0x2cd0da71 Anonymous_GTID last_committed=1000 sequence_number=1001 rbr_only=yes original_committed_timestamp=1687638835657515 immediate_commit_timestamp=1687638835662742 transaction_length=401
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1687638835657515 (2023-06-24 22:33:55.657515 CEST)
# immediate_commit_timestamp=1687638835662742 (2023-06-24 22:33:55.662742 CEST)
/*!80001 SET @@session.original_commit_timestamp=1687638835657515*//*!*/;
/*!80014 SET @@session.original_server_version=80033*//*!*/;
/*!80014 SET @@session.immediate_server_version=80033*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 761514
#230624 22:33:55 server id 5748 end_log_pos 761584 CRC32 0x18c98746 Query thread_id=27 exec_time=0 error_code=0
SET TIMESTAMP=1687638835/*!*/;
/*!C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=192/*!*/;
BEGIN
/*!*/;

暂时还没有找到转换的方法。

这就意味着，除非你能控制app使用的mysql 5.7的驱动连接程序，或者能手动改set names

因此，如果你遇到兼容性问题，可能需要先升级一个副本，如本示例图所示：

然后在升级的节点后再做一个5.7的副本。

最终，一旦8.0被证明可以正常工作了，且新加的副本也可以正常工作，再升级原始的5.7

请注意，虽然这些截图是由Orchestrator制作的，这是一个非常容易更改复制拓扑的工具，但在这种情况下，我必须手动将5.7副本移动到8.0下，因为，这是不支持的操作:

# orchestrator-client -c relocate -i 127.0.0.1:5749 -d 127.0.0.1:5750
2023-06-22 20:31:10 ERROR przemek-dbg:5749 cannot replicate from przemek-dbg:5750. Reason: instance przemek-dbg:5749 has version 5.7.42-log, which is lower than 8.0.33 on przemek-dbg:5750

3.还原升级前的备份，并追赶在8.0中新生成的数据

这也包含不被官方支持的操作：将8.0的日志应用到5.7。日志中有些内容是不被5.7支持的。

$ mysqlbinlog binlog.000005 | mysql -v
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------
(...)
--------------
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255
--------------
ERROR 1115 (42000) at line 33: Unknown character set: '255'

为了能应用这些日志，我们需要将字符集255和排序规则255转换成57中支持的。

在8.0中

mysql 80 > set @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255;
Query OK, 0 rows affected (0.00 sec)
mysql 80 > select @@session.character_set_client,@@session.collation_connection,@@session.collation_serverG
*************************** 1. row ***************************
@@session.character_set_client: utf8mb4
@@session.collation_connection: utf8mb4_0900_ai_ci
@@session.collation_server: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在5.7中可以，切换成：

mysql 57 > set @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45;
Query OK, 0 rows affected (0.00 sec)
mysql 57 > select @@session.character_set_client,@@session.collation_connection,@@session.collation_serverG
*************************** 1. row ***************************
@@session.character_set_client: utf8mb4
@@session.collation_connection: utf8mb4_general_ci
@@session.collation_server: utf8mb4_general_ci
1 row in set (0.00 sec)

因此，我们需要改写一下日志的内容：

$ mysqlbinlog binlog.000005 | sed 's/SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/set @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/' | mysql
$