11

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.

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

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

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

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.

4

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.

  • 1
    I got the following error when trying to run that command: mysqldump: Got error: 1049: "Unknown database 'data'" when selecting the database – Andy Oct 27 '16 at 15:36
  • 1
    @Andy you right something wrong with this now. dev.mysql.com/doc/refman/5.7/en/…. I think "data" should not be there at all. – Rui Marques Oct 31 '16 at 22:51
1

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

  • I receive the same error. – Cerin Nov 12 '11 at 15:46
  • I couldn't find anything in the documentation to support this statement. AFAIK reads and writes on the table are supported when --single-transaction is performed, but table-structure altering statements (ALTER, CREATE, TRUNCATE, etc) may cause the dump to fail or give unexpected data. (dev.mysql.com/doc/refman/5.7/en/…) – Code Commander Oct 3 '17 at 18:39
1

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
0

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;
  • innodb_lock_wait_timeout is a static parameter on RDS that can't be changed. – Cerin Nov 14 '11 at 22:29
  • @Cerin : According to the Docs, you can set it in your session. – RolandoMySQLDBA Nov 14 '11 at 22:40
  • What do you mean "my session"? mysqldump lists no option to adjust any timeouts. – Cerin Nov 16 '11 at 16:14
  • Sorry, I was looking at it backwards. I meant to say set innodb_lock_wait_timeout in Amazon RDS. – RolandoMySQLDBA Nov 16 '11 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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