加速 mysql 转储和导入
是否有任何记录在案的技术可以加速 mySQL 转储和导入？
这将包括 my.cnf 设置、使用 ramdisk 等。
仅查找记录的技术，最好使用显示潜在加速的基准测试。
Are there any documented techniques for speeding up mySQL dumps and imports?
This would include my.cnf settings, using ramdisks, etc.
Looking only for documented techniques, preferably with benchmarks showing potential speed-up.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
比mysql快< dumpfile
像 innodb 这样的重度事务引擎非常慢。 插入
进入非事务性引擎，例如
MyISAM 速度要快得多。
更新您的表，而不是转储它们并导入它们。 但
您可能正在谈论备份/恢复。
faster than mysql < dumpfile
heavily transactional engine like innodb is awfully slow. Inserting
into a non-transactional engine like
MyISAM is much much faster.
update your tables rather than dumping them and importing them. But
you're probably talking about backups/restores.
http://www.maatkit.org/ 有 mk-parallel-dump 和 mk-parallel-restore
mysqldump 中还有各种潜在的选项，例如在导入转储时不创建索引 - 而是在完成时批量创建索引。
http://www.maatkit.org/ has a mk-parallel-dump and mk-parallel-restore
There are also various potential options in mysqldump such as not making indexes while the dump is being imported - but instead doing them en-mass on the completion.
假设您正在使用 InnoDB...
我的情况是有一堆现有的 mysqldump 输出文件，我想在合理的时间内导入它们。 这些表（每个文件一个）大约 500MB，每个表包含大约 5,000,000 行数据。 使用以下参数，我能够将插入时间从 32 分钟减少到 3 分钟以下。
您还需要有一个相当大的
innodb_buffer_pool_size设置。因为我的插入是一次性的，所以我后来恢复了设置。 如果您打算长期继续使用它们，请确保您知道它们在做什么。
我在 Cedric Nilly 的博客 上找到了使用这些设置的建议以及详细的解释每个设置都可以在MySQL 文档。
Assuming that you're using InnoDB...
I was in the situation of having a pile of existing mysqldump output files that I wanted to import in a reasonable time. The tables (one per file) were about 500MB and contained about 5,000,000 rows of data each. Using the following parameters I was able to reduce the insert time from 32 minutes to under 3 minutes.
You'll also need to have a reasonably large
innodb_buffer_pool_sizesetting.Because my inserts were a one-off I reverted the settings afterwards. If you're going to keep using them long-term, make sure you know what they're doing.
I found the suggestion to use these settings on Cedric Nilly's blog and the detailed explanation for each of the settings can be found in the MySQL documentation.
我想您的问题还取决于瓶颈在哪里：
-C/--compress标志到mysqldump。另外，请查看
mysqldump的--quick标志（如果您使用的是 MyIsam，请查看--disable-keys）。I guess your question also depends on where the bottleneck is:
-C/--compressflag tomysqldump.Also, have a look at the
--quickflag formysqldump(and--disable-keysif you are using MyIsam).如果您要导入到 InnoDB，您可以做的最有效的事情就是
在导入运行时暂时放入
my.cnf。 如果需要 ACID，可以将其放回1。If you are importing to InnoDB the single most effective thing you can do is to put
in your
my.cnf, temporarily while the import is running. You can put it back to1if you need ACID.确保在转储时使用 mysqldump 的
--opt选项。 这将使用批量插入语法、延迟键更新等...如果您仅使用 MyISAM 表，您可以通过停止服务器、将它们复制到已停止的服务器并启动该服务器来安全地复制它们。
如果您不想停止源服务器，可以按照以下步骤操作：
但我很确定当您需要停止复制到服务器时将它们放置到位。
Make sure you are using the
--optoption to mysqldump when dumping. This will use bulk insert syntax, delay key updates, etc...If you are ONLY using MyISAM tables, you can safely copy them by stopping the server, copying them to a stopped server, and starting that.
If you don't want to stop the origin server, you can follow this:
But I'm pretty sure your copy-to server needs to be stopped when you put them in place.
有一种使用 LVM 快照进行备份和恢复的方法，这对您来说可能是一个有趣的选择。
考虑使用 LVM 来拍摄 MySQL 数据目录的快照，而不是执行 mysqldump。 使用 LVM 快照可以让您拥有近乎实时的备份能力、对所有存储引擎的支持以及令人难以置信的快速恢复。 引用下面的链接，
“恢复时间与数据恢复和标准 MySQL 崩溃恢复一样快，而且还可以进一步缩短。”
http://www .mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
There is an method for using LVM snapshots for backup and restore that might be an interesting option for you.
Instead of doing a mysqldump, consider using LVM to take snapshots of your MySQL data directories. Using LVM snapshots allow you to have nearly real time backup capability, support for all storage engines, and incredibly fast recovery. To quote from the link below,
"Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further."
http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
关闭外键检查并打开自动提交。
turn off foreign key checks and turn on auto-commit.
在转储中使用扩展插入应该会使导入速度更快。
Using extended inserts in dumps should make imports faster.
如果您只有 MyIsam 表，
mysqlhotcopy也可能是您的替代方案。mysqlhotcopymight be an alternative for you too if you only have MyIsam tables.使用索引但不要太多，激活查询缓存，使用sphinx进行大数据库，这里有一些很好的技巧http://www.keedeo.com/media/1857/26-astuces-pour-accelerer-vos-requetes-mysql（法语）
Using indexes but not too much, activate query cache, using sphinx for big database, here is some good tips http://www.keedeo.com/media/1857/26-astuces-pour-accelerer-vos-requetes-mysql (In French)