导入8GB的mysql转储需要很长时间 [英] Import of 8GB mysql dump takes a long time

问题描述

我有一个用mysqldump创建的InnoDB表的8GB MYSQL数据库转储。我导入数据：

I've got an 8GB MYSQL database dump of InnoDB tables created with mysqldump. I import the data with:

mysql -uroot -p my_db < dump.sql

在一小时内导入了5 GB的DB转储。 8 GB转储耗时50小时计数。当我用

An 5 GB dump of the DB was imported within an hour. The 8 GB dump takes 50 hour and counting. When I inspected the processlist with

SHOW PROCESSLIST;

SHOW PROCESSLIST;

大多数情况下，只有一个INSERT查询可见，状态为'释放项

Most of the time there was a single INSERT query visible with state "'freeing items"

除了复制原始文件之外，是否有办法加速导入过程？

Is there a way, besides copying the raw files, to speed up the import process?

推荐答案

真正的诀窍是确保最大的单个表适合innodb缓冲池。如果没有，那么插入（当然导入）将非常慢。

The trick really, is to ensure that the biggest single table fits in the innodb buffer pool. If it does not, then inserts (and import of course) will be extremely slow.

整个数据库的大小并不重要，但是最大的单个表。

It doesn't matter the size of the whole database, but the biggest single table.

对于大得多的数据库，您可能需要考虑传输数据库的其他方法，例如文件系统快照。如果您的机器运行相同版本的数据库，操作系统和体系结构，这当然最有效。

For significantly larger databases, you might want to consider alternative methods of transfering the database, such as filesystem snapshots. This of course works best if your machines are running the same version of the database, OS and architecture.

这篇关于导入8GB的mysql转储需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！