mysqldump recurring error

[SantaMufasa]

Each night, I use mysqldump to backup the contents of our mysql databases. For several evenings now, I have been encountering the same error whilst running the dumps. The scenario is that three of my four databases on this installation successfully dump. The fourth database dump consistently errors out on row 244,316 of its 324,653 total rows (after successfully dumping 81 previous tables), with this diagnostic:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `dd_data_elements` at row: 244316
Error: Backup of schema <next schema name> was not successful (0 seconds):
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) when trying to connect

As part of my troubleshooting, to determine whether the data near row 244316 is corrupted or readable, I have queried the following:

mysql> select data_element_id,data_element_name
    -> from dd_data_elements
    -> limit 244315,3;
+-----------------+-------------------+
| data_element_id | data_element_name |
+-----------------+-------------------+
|      2011375808 | Single            |
|      2011375858 | Single            |
|      2011375912 | Single            |
+-----------------+-------------------+
3 rows in set (5.07 sec)

I have even successfully done a:

SELECT * FROM dd_data_elements limit 244315,1;

Further, I have tried, unsuccessfully, to dump just the offending table, with there results:

$ mysqldump -u<username> -p<password> <database> dd_data_elements > dd_data.dump
mysqldump: Got error: 2013: Lost connection to MySQL server during query when retrieving data from server

Does anyone have troubleshooting suggestions for me to try from here?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[guelphdad]

have you tried using mysqlhotcopy?

 

[SantaMufasa]

Guelphdad,

Our organization has been using mysqldump across all of our MySQL servers to perform our nightly dumps. I understand that mysqlhotcopy can occur only on MyISAM and ARCHIVE tables.

My questions for you at this point are:

1) Why would mysqlhotcopy bypass the problems that I am having? What is mysqldump doing that results in my errors?

2) Why query can I run to assess whether or not mysqlhotcopy can accommodate all of our schema objects?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[guelphdad]

I haven't used mysqlhotcopy myself, just knew it was another backup program. If you try that on the table in question and it works, then you know for sure that the issue is not tied into the table directly. If it fails on the table as well you might have to repair your table.

 

[SantaMufasa]

Thanks for the suggestion, Guelphdad.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]