Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is there enough diskspace? And have you run CHECK TABLE to see there is no DB corruption on the tables? –  Adrian Cornish Nov 11 '11 at 23:48
    
Have you tried removing the --force param to see what error you get? Or --quick? –  Yzmir Ramirez Nov 11 '11 at 23:50
    
@Adrian, Yes, I have about 10GB of free space, more than enough. And yes, all tables check out ok. –  Cerin Nov 11 '11 at 23:56
    
@Yzmir, Yes, the same problem occurs. –  Cerin Nov 11 '11 at 23:59
add comment

migrated from stackoverflow.com Nov 12 '11 at 0:08

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
    
I receive the same error. –  Cerin Nov 12 '11 at 15:46
add comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.