I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables. One particular table (called 'responses') is 8Gb - this table is InnoDB.
The live database is replicated to a different server & I take backups using MySQLDump from the replication server... the live server runs Windows, but the replication server runs CentOS.
The MySQLDump command is:
mysqldump --verbose --lock-tables=true --max-allowed-packet=1024M --host=192.168.1.182 --user=myusername --password=mypassword --opt --databases databasename > databasename.sql
The command runs until half-way through the 'responses' table - when we get: Error 2013 - Lost connection to MySQL server during query when dumping table responses at row 12891212
After some Googling, I tried added the following commands to the MySQLDump (independently & at the same time):
The error still occurred at the same place.
For completeness, I've tried dumping the database in NaviCat and MySQL Workbench - both return the same error.
So, I wondered if it was an issue with corruption. I tried to run the following (on the replication server):
CHECK TABLE responses;
CHECK TABLE responses EXTENDED;
Both returned the following: Error 2013 - Lost connection to MySQL server during query.
The live database is VERY busy, so I'm a bit worried... I started CHECK TABLE on the live table, but unsurprisingly, it caused the application to stop so I had to stop it.
Any advise would be welcome here... the ultimate goal is to get the backups using MySQLDump working.
Update: I tried copying the table (on the replication server) to a new database on another server using NaviCat - it failed with the same message (transferred records: 12,889,214): Error 2013 - Lost connection to MySQL server during query