字符集导致的恢复失败

说明：PXC是从5.7 升级到8.0.25

现象：在一个安装好的8.0.25 mysql中做恢复演练。

[root@dba-testbak ~]# cat /tmp/imp162.log
ERROR 1273 (HY000) at line 39: Unknown collation: 'utf8mb4_0900_ai_ci'
[root@dba-testbak ~]#

查看MySQL8.0.25字符集

root@dba-testbak 13:35:  [(none)]>  SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb3            |
| character_set_connection | utf8mb3            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb3            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8mb3            |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_0900_ai_ci |
| collation_server         | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

查看PXC8.0.25字符集

mysql>  SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8mb3            |
| collation_connection     | utf8mb4_0900_ai_ci |
| collation_database       | utf8mb4_0900_ai_ci |
| collation_server         | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

那么改字符集吧

[mysqld]
init-connect = 'set collation_connection = utf8mb4_0900_ai_ci;'

重新启动数据库，然后进行查看

创建一个普通用户看看

官方文档

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

翻译过来需要注意的