个人

  • 整理数据时，尝试把以前给雪舞读的故事博客数据库导出来，发现只有 MySQL 的数据文件了，为了导出 MySQL 语句，遇到了各种问题：

    • 忘记了 mysql 的版本，导致用 docker-compose 启动的时候报各种错误：

      db_1       | 2023-09-20T21:06:25.674785Z 0 [Warning] The plugin 'unix_socket' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.
db_1       | 2023-09-20T21:06:25.675092Z 0 [Warning] System table 'time_zone_leap_second' is expected to be transactional.
db_1       | 2023-09-20T21:06:25.675531Z 0 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
db_1       | 2023-09-20T21:06:25.675552Z 0 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
db_1       | 2023-09-20T21:06:25.675572Z 0 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
db_1       | 2023-09-20T21:06:25.677161Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(80).
db_1       | 2023-09-20T21:06:25.677173Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
db_1       | 2023-09-20T21:06:25.677256Z 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
db_1       | 2023-09-20T21:06:25.677282Z 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT', ...

    • 虽然 MySQL 还是启动成功了，但是 mysqldump 不行：

      root@d587871a3053:/# mysqldump -h 127.0.0.1 shirley
-- MySQL dump 10.13  Distrib 5.7.9, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: shirley
-- ------------------------------------------------------
-- Server version	5.7.9

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

    • 然后尝试用 mysql（cli）直接导出，不行，没有办法，只能查看 select 的结果。

    • 然后就是用 adminer 登录后台：发现原来的数据库没有设置密码…… Adminer 4.6.3 及以后不允许……

    • 换了 Adminer 4.6.2 后成功进入，并导出成功

    • 可以删除数据文件了。

    • 长记性啊！以后可只能保存 sql 文件了，不要再保存不知道版本的 mysql 数据文件夹。

桃子的碎碎念 桃子 编辑