Could not connect to mysql database

This can be a pretty annoying error message to come across, thankfully though it doesn't *always* mean the end of the database! when you look in the /var/log/.err log you will large chunks of data in the log file - it looks like gibberish and lots of square characters in putty and sometimes a message that the database is rolling back on itself. this loop, rollback -> fail -> rollback can occur indefinately and eats away at the ram and cpu on the system as well as creating large log files and sometimes excessive swapping!

first thing to do is start the database in recovery mode, that will stop the loop mentioned above and hopefully allow you to take a dump of your tables.

Find my.cnf - normally in /var/db/mysql/my.cnf and edit adding the following line:

innodb_force_recovery = 1

restart the database - at this stage a

mysql -u root -p ***** databaseName

will let you verify that you can connect again.

Then dump the tables - the command would look like this:

mysqdump -u root databaseName tablename1 tablename2 > /var/database.dump

Next move the inodb and ibdata files out of the way normally located in /var/db/mysql

and then the tables that you are backing up in /var/db/mysql/databaseName

After this we are now ready to remove or comment out

innodb_force_recovery

in my.cnf and restart mysql server.

Now import the dump taken earlier:
mysql -u root -p ***** databaseName < /var/database.dump

and if all goes to plan, you might just have saved your database!

the import process can sometimes take some time, iv seen up to 30 minutes with some large systems. If there is an issue with the import, the command will fail. This can mean that the dump that you have taken is corrupt :( or that MySql is running out of memory. This can be adjusted by configuring the MySql memory buffers:

innodb_buffer_pool_size

this is supposed to be set between 50 to 80% of your systems Ram. the highest I have set it to would be 400MB.