Incomplete mysqldump

Question

I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB.

I'm running it like:

mysqldump --force --single-transaction --quick --user myuser --password=mypass -h mydatabasehost mydb > /tmp/snapshot.sql

Checking the exit code reports 0.

My version is: mysqldump Ver 10.13 Distrib 5.1.52, for redhat-linux-gnu (i386)

I've seen some similar posts and even an official bug report, but neither solutions seem to apply.

How to I get mysqldump to take a complete database snapshot?

EDIT: My database currently resides on Amazon's RDS.

Answer 1

Have you tried?

mysqldump --compress --add-drop-table data --routines --events  --comments --extended-insert -h {host} -u {user} -p {database} > dbdump.sql

This is simple the way I always do it without any problems. Basically doing the dump this way you get everything you have (data, objects and sometimes precious comments) at a certain moment ignoring uncommitted transactions.

Answer 2

As far as I understand the mysql docs --single-transaction will fail if a read is performed on the table while you are dumping. What's the result when running without "--force --single-transaction --quick " ?

Answer 3

It may have been a problem with max_allowed_packet not being set high enough on both the client (i.e. mysqldump) and the server (i.e. Amazon RDS). I set this to 500M on both and that seems to have fixed the problem.

Since InnoDB's information schema tables only give row count estimates, it's hard to tell if my snapshot truly includes everything from RDS. All the tables are there, but the row counts differ. I'll update with a more definitive answer when I have some time to script a more thorough analysis.

Answer 4

It it entirely possible that the table is corrupt. I do not mean that the data and/or index pages are damaged. There could be something very simple that is broken.

I recently experienced a problem with a backup script on a Slave Server when I parallel mysqldumped multiple databases. Running mysqldump on one of the databases resulted in a very small mysqldump. The DB had 80+ tables. However, the mysqldump stopped at the fifth table in the DB. When I ran SHOW CREATE TABLE tblname\G on the table on the Slave, I got the error "Table Not Found". When I ran SHOW CREATE TABLE tblname\G on the Master, the table description displayed as expected.

What happened was a little crazy: A client asked for a restore of table and an engineer restored the .ibd file of the InnoDB table from a disk backup. The tablespace id of the .ibd file (which was 25) did not match the tablespace id registered in ibdata1 (which was 28).

I fixed the problem by hosing the slave, mysqldumping the master, and setting up replication from scratch. Fortunately, the data and index spave totalled 7GB. Thus rstore process was not a big deal.

MORAL OF THE STORY

The basic problem is that mysqldump does not report an error on an InnoDB when the tablespace id is incorrect. When a mysqldump finishes and does not dump every table in alphabetical order, that indicates it terminated by an error and did so without printing an error message.

Check to make sure

• you can display the table's structure using SHOW CREATE TABLE
• you can query everything about a table from INFORMATION_SCHEMA.TABLES