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

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 2

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 3

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

Answer 5

The following is just some brainstorming on mysqldump and InnoDB:

Please think about the behavior of mysqldump against an InnoDB table. If there are any dirty pages in the InnoDB Buffer Pool belonging to a table you are dumping, that table's dirty pages has to be flushed out to disk before a SELECT /* SQL_NO_CACHE */ can be executed against it.

Since you are using Amazon RDS, my gut feeling is that your database is in a multitenant infrastructure (Feel free to correct this statement if I am oversimplifying this). Other databases may be using a shared InnoDB Buffer Pool, a shared metadata file (ibdata1), and a shared tablespace (ibdata1 if innodb_file_per_table is disabled).

There may also be some redundancy of the database going on, which could affect MVCC against the database, even though it is a small dataset.

You may want to increase innodb_lock_wait_timeout (default 50 seconds) in your mysqldump session to see if this has any effect on Amazon RDS (or have Amazon increase this limit). Also, try experimenting with dumping individual tables.

UPDATE 2011-11-14 17:58 EDT

Try executing this within your DB Session (sets it to two minutes):

SET innodb_lock_wait_timeout = 120;