☰

恢复 MySQL 数据库非常慢

tag-icon • tag-icon mysql database-restore
恢复 MySQL 数据库非常慢

我刚买了一台新的家用台式机。我有三个 MySQL 数据库需要迁移。在从旧机器上移除驱动器之前，我使用 mysqldump 转储了所有数据库。

恢复最大的数据库需要很长时间（到目前为止已经 24 小时了）。我通过以下方式进行恢复：

mysql -uxxxx -p
Enter password: yyyy
mysql> create database foo;
mysql> use foo;
mysql> start transaction;
mysql> \. <dump-file>

我祈祷我记得commit这一切何时结束！

我放弃了第一次尝试恢复，因为没有事务，所以花了太长时间。我打算在恢复其他两个数据库时不考虑事务，因为这似乎没什么区别。

我可以做些什么来加快恢复速度？我是否应该以不同的方式进行备份 - 我只是使用了 vanillamysqldump命令行，因为--skip-column-statistics否则转储会失败，如建议的那样这个stackoverflow答案。

作为参考，新机器运行 Windows 10 和 MySQL 8.0.25 Community；旧机器运行 Windows 7 和 MySQL 5.6.22。

仔细查看转储文件（以查找服务器版本），我发现表是用索引创建的，后面跟着很多 INSERT INTO 语句。这似乎不是特别聪明。有没有办法诱导mysqldump生成一个脚本：

  1. 创建没有索引的表
  2. 插入行；然后
  3. 创建索引。

对于恢复数据库来说，这似乎是一种更好的方法。（令人惊讶的是，这不是默认设置！）我认为这在 MySQL 中是可能的 - 过去 3 年我一直在使用 SQLLite，这在那里肯定是可能的。

更新

这个问题不是完全地回答者如何加快从转储文件恢复 MySQL 的速度？，因为该问题特别涉及 MyISAM 表，而我的是 InnoDB。（抱歉。我可能应该在最初的问题中提到存储引擎。）

然而，这个答案确实激励并启发了我解决问题的尝试，我在下面的自己的答案中记录了这一点。

答案1

24 小时后，我最终放弃等待导入完成。

我修改了生成的脚本mysqldump从而创建没有二级索引的表，并且只有当所有INSERT语句都执行完毕后才创建索引。 经过这样的更改，我在开始导入三个小时后检查时，导入已经完成。

然后我进一步修改了脚本，通过在开头添加 SET AUTOCOMMIT=0; START TRANSACTION; 并在结尾添加 COMMIT; 来将行插入事务中。通过此更改，恢复在 90 分钟内完成。

在编辑生成的转储时，我注意到它们包括

/*!40000 ALTER TABLE `xxx` DISABLE KEYS */;

和

/*!40000 ALTER TABLE `xxx` ENABLE KEYS */;

围绕这些INSERT陈述。我查阅了文档对这些命令进行了测试，发现它们仅适用于 MyISAM 表。文档mysqldump公用事业指出，如果您指定以下行（包括注释分隔符和幻数 40000），则会生成 --禁用键在命令行上。实用程序文档还指出，如果您在命令行上指定，它将添加“在单个事务中插入所有内容”行为，我发现这是理想的行为--no-autocommit。

总之，我应该使用

 mysqldump --no-autocommit...

至少就我而言（mysqldump版本 8.0.25 和 InnoDb 表）--disable-keys选项没有区别；ALTER TABLE ... ENABLE|DISABLE KEYS始终会生成语句（注释掉，并带有魔法数字）。我做INSERT INTO需要修改生成的转储以创建没有辅助键的表，并在执行完所有语句后添加它们。

相关内容