加速 mysql 转储和导入

Speeding up mysql dumps and imports

提问by deadprogrammer

Are there any documented techniques for speeding up mySQL dumps and imports?

是否有任何用于加速 mySQL 转储和导入的记录技术？

This would include my.cnf settings, using ramdisks, etc.

这将包括 my.cnf 设置、使用 ramdisks 等。

Looking only for documented techniques, preferably with benchmarks showing potential speed-up.

只寻找记录在案的技术，最好有显示潜在加速的基准。

采纳答案by Alister Bulman

http://www.maatkit.org/has a mk-parallel-dump and mk-parallel-restore

http://www.maatkit.org/有一个 mk-parallel-dump 和 mk-parallel-restore

If you've been wishing for multi-threaded mysqldump, wish no more. This tool dumps MySQL tables in parallel. It is a much smarter mysqldump that can either act as a wrapper for mysqldump (with sensible default behavior) or as a wrapper around SELECT INTO OUTFILE. It is designed for high-performance applications on very large data sizes, where speed matters a lot. It takes advantage of multiple CPUs and disks to dump your data much faster.

如果您一直希望使用多线程 mysqldump，请不要再希望了。此工具并行转储 MySQL 表。它是一个更智能的 mysqldump，既可以作为 mysqldump 的包装器（具有合理的默认行为），也可以作为 SELECT INTO OUTFILE 的包装器。它专为处理非常大数据的高性能应用程序而设计，其中速度非常重要。它利用多个 CPU 和磁盘来更快地转储数据。

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.

在 mysqldump 中还有各种潜在的选项，例如在导入转储时不创建索引 - 而是在完成时将它们集中起来。

回答by JBB

1. Get a copy of High Performance MySQL. Great book.
2. Extended inserts in dumps
3. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile
4. Import with multiple threads, one for each table.
5. Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
6. Look at the table compare script in the Maakit toolkit and see if you can update your tables rather than dumping them and importing them. But you're probably talking about backups/restores.

1. 获取一份高性能 MySQL。很棒的书。
2. 转储中的扩展插入
3. 使用 --tab 格式转储，以便您可以使用 mysqlimport，这比 mysql < dumpfile 更快
4. 使用多个线程导入，每个表一个。
5. 如果可能，请使用不同的数据库引擎。导入像 innodb 这样的大量事务引擎非常慢。插入像 MyISAM 这样的非事务性引擎要快得多。
6. 查看 Maakit 工具包中的表比较脚本，看看您是否可以更新您的表，而不是转储它们并导入它们。但您可能在谈论备份/恢复。

回答by Aleksandar Ivanisevic

If you are importing to InnoDB the single most effective thing you can do is to put

如果您要导入 InnoDB，您可以做的最有效的事情就是将

innodb_flush_log_at_trx_commit = 2

in your my.cnf, temporarily while the import is running. You can put it back to 1if you need ACID.

在您的my.cnf，暂时在导入运行时。1如果你需要 ACID，你可以把它放回去。

回答by Ztyx

I guess your question also depends on where the bottleneck is:

我想你的问题还取决于瓶颈在哪里：

• If your network is a bottleneck you could also have a look at the -C/--compressflag to mysqldump.
• If your computer runs out of memory (ie. starts swapping) you should buy more memory.

• 如果您的网络是瓶颈，您还可以查看-C/--compress标志mysqldump。
• 如果您的计算机内存不足（即开始交换），您应该购买更多内存。

Also, have a look at the --quickflag for mysqldump(and --disable-keysif you are using MyIsam).

另外，看看--quick标志mysqldump（--disable-keys如果你使用的是 MyIsam）。

回答by longneck

turn off foreign key checks and turn on auto-commit.

关闭外键检查并打开自动提交。

回答by che

Using extended inserts in dumps should make imports faster.

在转储中使用扩展插入应该会使导入速度更快。

回答by Ztyx

mysqlhotcopymight be an alternative for you too if you only have MyIsam tables.

mysqlhotcopy如果您只有 MyIsam 表，也可能是您的替代方案。

回答by paul

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)

使用索引但不要太多，激活查询缓存，使用 sphinx 进行大数据库，这里有一些不错的技巧http://www.keedeo.com/media/1857/26-astuces-pour-acceler-vos-requetes-mysql(法语）

回答by p4guru

Another alternative is http://www.mydumper.org- multi-threaded mysql backup/restore which is 3x to 10x times faster than mysqldump and can handle both MyISAM and InnoDB as well as Drizzle http://vbtechsupport.com/1695/

另一种选择是http://www.mydumper.org- 多线程 mysql 备份/恢复比 mysqldump 快 3 到 10 倍，并且可以处理 MyISAM 和 InnoDB 以及 Drizzle http://vbtechsupport.com/1695/