mysqldump not exporting all tables

Question

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?

Answer 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.