<p>Are there any documented techniques for speeding up mySQL dumps and imports?</p>

<p>This would include my.cnf settings, using ramdisks, etc. </p>

<p>Looking only for documented techniques, preferably with benchmarks showing potential speed-up.</p>

[{'answer_id': 67243, 'author': 'che', 'author_id': 7806, 'author_profile': 'https://Stackoverflow.com/users/7806', 'pm_score': 2, 'selected': False, 'text': '<p>Using extended inserts in dumps should make imports faster.</p>\n'}, {'answer_id': 72853, 'author': 'JBB', 'author_id': 12332, 'author_profile': 'https://Stackoverflow.com/users/12332', 'pm_score': 5, 'selected': False, 'text': "<ol>\n<li>Get a copy of <em>High Performance MySQL</em>. Great book. </li>\n<li>Extended inserts in dumps </li>\n<li>Dump with --tab format so you can use mysqlimport, which is\nfaster than mysql &lt; dumpfile</li>\n<li>Import with multiple threads, one for each table.</li>\n<li>Use a different database engine if possible. importing into a\nheavily transactional engine like innodb is awfully slow. Inserting\ninto a non-transactional engine like\nMyISAM is much much faster.</li>\n<li>Look at the table compare script in the Maakit toolkit and see if you can\nupdate your tables rather than dumping them and importing them. But \nyou're probably talking about backups/restores.</li>\n</ol>\n"}, {'answer_id': 72955, 'author': 'longneck', 'author_id': 8250, 'author_profile': 'https://Stackoverflow.com/users/8250', 'pm_score': 2, 'selected': False, 'text': '<p>turn off foreign key checks and turn on auto-commit.</p>\n'}, {'answer_id': 73067, 'author': 'Aleksandar Ivanisevic', 'author_id': 12537, 'author_profile': 'https://Stackoverflow.com/users/12537', 'pm_score': 4, 'selected': False, 'text': '<p>If you are importing to InnoDB the single most effective thing you can do is to put</p>\n\n<pre><code>innodb_flush_log_at_trx_commit = 2\n</code></pre>\n\n<p>in your <code>my.cnf</code>, temporarily while the import is running. You can put it back to <code>1</code> if you need ACID.</p>\n'}, {'answer_id': 77892, 'author': 'Alister Bulman', 'author_id': 6216, 'author_profile': 'https://Stackoverflow.com/users/6216', 'pm_score': 5, 'selected': True, 'text': '<p><a href="http://www.maatkit.org/" rel="noreferrer">http://www.maatkit.org/</a> has a mk-parallel-dump and mk-parallel-restore</p>\n\n<blockquote>\n  <p>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.</p>\n</blockquote>\n\n<p>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.</p>\n'}, {'answer_id': 1112246, 'author': 'gahooa', 'author_id': 64004, 'author_profile': 'https://Stackoverflow.com/users/64004', 'pm_score': 3, 'selected': False, 'text': "<p>Make sure you are using the <code>--opt</code> option to mysqldump when dumping.  This will use bulk insert syntax, delay key updates, etc...</p>\n\n<hr/>\n\n<p><strong>If you are ONLY using MyISAM tables</strong>, you can safely copy them by stopping the server, copying them to a stopped server, and starting that.</p>\n\n<p>If you don't want to stop the origin server, you can follow this:</p>\n\n<ol>\n<li>Get a read lock on all tables</li>\n<li>Flush all tables</li>\n<li>Copy the files</li>\n<li>Unlock the tables</li>\n</ol>\n\n<p>But I'm pretty sure your copy-to server needs to be stopped when you put them in place.</p>\n"}, {'answer_id': 1112305, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>There is an method for using LVM snapshots for backup and restore that might be an interesting option for you. </p>\n\n<p>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, </p>\n\n<p>"Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further."</p>\n\n<p><a href="http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/" rel="nofollow noreferrer">http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/</a></p>\n'}, {'answer_id': 2666356, 'author': 'Ztyx', 'author_id': 260805, 'author_profile': 'https://Stackoverflow.com/users/260805', 'pm_score': 3, 'selected': False, 'text': '<p>I guess your question also depends on where the bottleneck is:</p>\n\n<ul>\n<li>If your network is a bottleneck you could also have a look at the <code>-C</code>/<code>--compress</code> flag to <code>mysqldump</code>.</li>\n<li>If your computer runs out of memory (ie. starts swapping) you should buy more memory.</li>\n</ul>\n\n<p>Also, have a look at the <code>--quick</code> flag for <code>mysqldump</code> (and <code>--disable-keys</code> if you are using MyIsam).</p>\n'}, {'answer_id': 2666363, 'author': 'Ztyx', 'author_id': 260805, 'author_profile': 'https://Stackoverflow.com/users/260805', 'pm_score': 1, 'selected': False, 'text': '<p><code>mysqlhotcopy</code> might be an alternative for you too if you only have MyIsam tables.</p>\n'}, {'answer_id': 7115112, 'author': 'paul', 'author_id': 901528, 'author_profile': 'https://Stackoverflow.com/users/901528', 'pm_score': -1, 'selected': False, 'text': '<p>Using indexes but not too much, activate query cache, using sphinx for big database, here is some good tips <a href="http://www.keedeo.com/media/1857/26-astuces-pour-accelerer-vos-requetes-mysql" rel="nofollow">http://www.keedeo.com/media/1857/26-astuces-pour-accelerer-vos-requetes-mysql</a> (In French)</p>\n'}, {'answer_id': 8346162, 'author': 'Jeff Hiltz', 'author_id': 13471, 'author_profile': 'https://Stackoverflow.com/users/13471', 'pm_score': 5, 'selected': False, 'text': '<p>Assuming that you\'re using InnoDB...</p>\n\n<p>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.</p>\n\n<blockquote>\n  <p>innodb_flush_log_at_trx_commit = 2<br>\n  innodb_log_file_size = 256M<br>\n  innodb_flush_method = O_DIRECT  </p>\n</blockquote>\n\n<p>You\'ll also need to have a reasonably large <code>innodb_buffer_pool_size</code> setting.</p>\n\n<p>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.</p>\n\n<p>I found the suggestion to use these settings on <a href="https://lordgun.com/2008/05/tuning-mysql-innodb/" rel="noreferrer">Cedric Nilly\'s blog</a> and the detailed explanation for each of the settings can be found in the <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html" rel="noreferrer">MySQL documentation</a>.</p>\n'}]
['https://Stackoverflow.com/questions/67117', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/556/']