MySQL 有没有更快的方法来加载 mysqldumps？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1112069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a faster way to load mysqldumps?
提问by Ben K.
Possible Duplicate:
Speeding up mysql dumps and imports
可能的重复：
加速 mysql 转储和导入
mysqldumpis reasonably fast, but dumps of a medium-sized database (20-30 megs) take several minutes to load using mysql my_database < my_dump_file.sql
mysqldump相当快，但是中型数据库（20-30 兆）的转储需要几分钟才能使用 mysql my_database < my_dump_file.sql
Are there some mysql settings I can tune to speed up the load? Is there a better way to load saved data?
是否有一些 mysql 设置可以调整以加快加载速度？有没有更好的方法来加载保存的数据？
I've experimented using the mysqlimport utility with CSV-based dumps. These load slightly--but not appreciably--faster. I'm tempted to just copy raw database files around, but that seems like a bad idea.
我已经尝试将 mysqlimport 实用程序与基于 CSV 的转储一起使用。这些加载稍微快一点——但不是很明显——更快。我很想只复制原始数据库文件，但这似乎是个坏主意。
采纳答案by Jonathan
回答by Jeff Hiltz
Assuming that you're using InnoDB...
假设您正在使用 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.
我的情况是有一堆现有的 mysqldump 输出文件，我想在合理的时间内导入。这些表（每个文件一个）大约 500MB，每个表包含大约 5,000,000 行数据。使用以下参数，我能够将插入时间从 32 分钟减少到 3 分钟以下。
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
You'll also need to have a reasonably large innodb_buffer_pool_sizesetting.
您还需要有一个相当大的innodb_buffer_pool_size设置。
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 blogand the detailed explanation for each of the settings can be found in the MySQL documentation.
我在Cedric Nilly 的博客上找到了使用这些设置的建议，每个设置的详细说明可以在MySQL 文档中找到。
回答by gahooa
Make sure you are using the --optoption to mysqldump when dumping. This will use bulk insert syntax, delay key updates, etc...
确保--opt在转储时使用mysqldump 选项。这将使用批量插入语法、延迟密钥更新等...
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.
如果您仅使用 MyISAM 表，则可以通过停止服务器、将它们复制到已停止的服务器并启动它来安全地复制它们。
If you don't want to stop the origin server, you can follow this:
如果你不想停止源服务器，你可以按照这个：
- Get a read lock on all tables
- Flush all tables
- Copy the files
- Unlock the tables
- 获取所有表的读锁
- 刷新所有表
- 复制文件
- 解锁桌子
But I'm pretty sure your copy-to server needs to be stopped when you put them in place.
但是我很确定当您将它们放置到位时，您的复制到服务器需要停止。
回答by Gav
Are you sure the data is sane, and there aren't any filesystem or system performance issues? Several minutes for a 20-30 meg database is a long time. I'm on a MacBook with 2GB of RAM, a 320GB HD and the standard 2.1GHz processor. I grabbed one of my databases for a quick benchmark:
您确定数据是健全的，并且没有任何文件系统或系统性能问题吗？对于 20-30 兆的数据库，几分钟是很长的时间。我使用的是配备 2GB RAM、320GB HD 和标准 2.1GHz 处理器的 MacBook。我抓取了我的一个数据库进行快速基准测试：
gavinlaking$ du -sm 2009-07-12.glis
74 2009-07-12.glis
gavinlaking$ mysql -pxxx -e "drop database glis"
gavinlaking$ mysql -pxxx -e "create database glis"
gavinlaking$ time mysql -pxxx glis < 2009-07-12.glis
real 0m17.009s
user 0m2.021s
sys 0m0.301s
17 seconds for a 74 megabyte file. That seems pretty snappy to me. Even if it was 4 times bigger (making it just shy of 300 megabytes), it finishes in just under 70 seconds.
74 兆字节的文件需要 17 秒。这对我来说似乎很活泼。即使它大 4 倍（使其不到 300 兆字节），它也能在不到 70 秒的时间内完成。
回答by p4guru
Try out https://launchpad.net/mydumper- multi-threaded mysql backup/restore which is 3x to 10x times faster than mysqldump http://vbtechsupport.com/1695/
试试https://launchpad.net/mydumper- 多线程 mysql 备份/恢复比 mysqldump http://vbtechsupport.com/1695/快 3 到 10 倍
回答by p4guru
There is an method for using LVM snapshots for backup and restore that might be an interesting option for you.
有一种使用 LVM 快照进行备份和恢复的方法，这对您来说可能是一个有趣的选择。
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,
与其执行 mysqldump，不如考虑使用 LVM 来获取 MySQL 数据目录的快照。使用 LVM 快照可以让您拥有近乎实时的备份功能、支持所有存储引擎以及令人难以置信的快速恢复。从下面的链接引用，
"Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further."
“恢复时间与放回数据和标准 MySQL 崩溃恢复一样快，而且可以进一步缩短。”
http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
