I have a Drupal site with a fair amount of content, about 12G in size, with 500+ distinct fields making for almost 1400 tables. We're migrating to a new server environment so I need to be able to export the DB and then import it on the new servers. I've successfully used mysqldump on this system in the past, but the last couple days each export is incomplete, with the data for some tables (in particular, the critical node and users tables) not getting dumped.

The process I follow is to put the site into maintenance mode and then halt any external process that hits the database. I then export the structure first

$ mysqldump -u XXXXXX -p DBNAME --no-data | gzip > structure.sql.gz

and then the table data

$ mysqldump -u XXXXXX -p DBNAME --no-create-info | gzip > data.sql.gz

(The second query is a little more complicated as I tell mysqldump to skip the cache files, the search index etc. but you get the point.)

Both times mysqldump runs without complaint, and the resulting SQL files load without error messages, but looking through the DB afterwards it's clear the data dump process got partway through the list of tables and then stopped after completing a table. Note that it does not stop at the same place each time.

Has anyone encountered this problem? Are there any tricks to get mysqldump to better handle large DBs?

  • You've no space or permission issues? – Vérace Mar 23 '16 at 20:18
  • Windows or not? – Rick James Mar 24 '16 at 5:52
  • you have written "second query is a little more complicated as I tell mysqldump to skip the cache files, the search index etc.". Can you explain where you use this query? It is not clear to me. – Giovanni Mar 24 '16 at 7:41
  • Sorry I was not clear. This is on Ubuntu 13.04. There are no space or permission issues. Finally, the second command includes things like --ignore-table=search_index --ignore-table=search_total, etc. Tables I do not need to port over. – TLM Mar 25 '16 at 13:15
  • Is there anything directly after the stopping point that looks suspicious? – dezso Oct 19 '16 at 11:19

When you have a database with that many tables, what must you remember ???

  • Use --single-transaction with --no-create-info : This will produce a dump of all table data with the same point-in-time
  • Setup Replication : You can run STOP SLAVE; and then run your mysqldump, assured that no other activity in going on in the Slave. When dump is finished, that run START SLAVE;.
  • Don't do any DDL during the dump : Running DDL while dumping from a MySQL Instance can produce some spurious actions (dump reload not completing, wrong structure of table prior to data reload, and things like these)

If your database is all-MyISAM (you start hearing Twilight Zone music) : Don't mysqldump. Shutdown mysql and maker a copy of /var/lib/mysql (LVM Snapshot, scp, rsync, etc). That way, there is no SQL controversy.

You should probably dump data and table structure together, just to be sure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.