4

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?

6
  • You've no space or permission issues? Mar 23, 2016 at 20:18
  • Windows or not?
    – Rick James
    Mar 24, 2016 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, 2016 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, 2016 at 13:15
  • Is there anything directly after the stopping point that looks suspicious?
    – dezso
    Oct 19, 2016 at 11:19

1 Answer 1

1

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.