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?