字符集导致的恢复失败

说明：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

翻译过来需要注意的