For some reason, whatever I do I cannot access one of my databases (through command line or through something like phpmyadmin). However, the application that depends on this database is not having any issues and is working perfectly.
I've tried a few things to have it connect
$ mysql -u root -p
$ use my_database
$ show tables
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: my_database
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server
I really am only trying to get the dump of the database
$ mysqldump -u root -p my_database | gzip > my_database.sql.gz
this gives me the following error
mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES
I've also tried
mysqlcheck -u root -p -A
It goes through all the tables until it tries to access the tables on my_database where it fails and throws this error
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
I just can't figure out what's wrong. If there is any other way of backing this database up please let me know or how to fix these errors.
Best Answer
I frequently use innodb force recovery in this case, and it often resolves the issue. On Mac and Windows installs, this is done by editing my.ini
Search for innodb_force in the text, uncomment that line, set the value to 2. Restart the MySQL service. Undo your edits ( comment out that line in my.ini) Restart the service and test. You can safely do level 3 in the same manner, but research more about it before attempting level 4 or higher.
Hope this helps.