MySQL – How to Fix Mysqldump Error

mysqldump

I'm using mysql version 5.0.5 and trying to dump my data, in order to prepare for version upgrading, and get an error.

I run:

./mysqldump -u root -p --add-drop-table --all-databases > /data/all_db_dump.mysql

And get:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `abc` at row: 408640

I went over some similar questions and found some suggestions:

  • run with --skip-extended-insert – Did not help
  • run with --hex-blob – Did not help
  • configured max_allowed_packet to 1024M – Did not help (I have 4 GB RAM)
  • configured net_read_timeout to 7200 – Did not help
  • configured net_write_timeout to 7200 – Did not help

For all of the above and get the same error.

I monitored the server's memory while dumping the data and didn't see any issue.
What could be the issue?

Best Answer

Considering that you've already set the timeout, packet and other variables at GLOBAL level and not session level, there are few other options that I can think of.

Option1 : verify table corruption

Analyze and repair table 'abc' to verify the corruption in case of myisam engine. (Also consider migrating tables to innodb if possible) Another approach to identify table corruption is to select data (row:408640) from those table and you should get an error or MySQL may shutdown as well.

Suggestion to what you're trying to achieve (in case you couldn't get mysqldump working):

You can upgrade MySQL by in-place upgrade method as well. Steps are as follows :

  • stop MySQL server (with innodb_fast_shutdown=0) and copy full datadir folder as backup (alternative of mysqldump, in case of any issues)

  • upgrade(replace) MySQL binaries (/basedir/bin/) with the binaries of version you're upgrading to

  • start MySQL server as usual and execute mysql_upgrade

Note :

  1. In-place upgrade is much faster than logical dump in case your data size is too large

  2. do not skip major release version while upgrading I.e. 5.0 to 5.6 In stead you'll have to do 5.0 -> 5.5 -> 5.6

  3. look out for deprecated variables before upgrading to any version
  4. make sure to analyze/repair tables after any method of upgradation.

One more thing, when you dump using mysqldump, you need to generate .sql file not .mysql file (all_db_dump.sql)

Related Question