1、创建一个空的数据库
shell> mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
... ...
mysql> CREATE DATABASE xxxx;
Query OK, 1 row affected (0.00 sec)
2、用8.0备份的文件进行恢复出错,原因是mysql8.0 用了utf8mb4_0900_ai_ci编码5.7不支持
shell> mysql -uroot -p xxxx < xxxx.sql
Enter password:
ERROR 1273 (HY000) at line 54: Unknown collation: 'utf8mb4_0900_ai_ci'
3、进入mysql,删除刚才建的数据库重新指定utf8mb4编码
mysql> drop database xxxx;
Query OK, 1 row affected (0.06 sec)
mysql> create database xxxx default character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)
4、修改备份文件的编码,修改之前先备份,文件较大时过程较长
shell> vim xxxx.sql
:%s/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g
:wq
5、再次恢复,成功
shell> mysql -uroot -p xxxx < xxxx.sql
Enter password: