Recover a MySQL database
Before trying to recover a MySQL database, you should check the exact error in the MySQL log file at installdir/mysql/data/mysqld.log. Check the latest entries in the MySQL log file with the following command:
$ sudo tail -n 100 installdir/mysql/data/mysqld.log
In this case, assume the following error in the log file:
110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed
Here are some steps to resolve this error:
The MySQL database is configured to use InnoDB engine by default. You can add the innodb_force_recovery=1 option in the main MySQL configuration file at installdir/mysql/etc/my.cnf to try and fix the database:
[mysqld] innodb_force_recovery = 1
Start the MySQL database with the following command:
$ mysqld --skip-grant-tables --user=mysql --pid-file=installdir/mysql/data/mysqld.pid --skip-external-locking --port=3306 --sock=installdir/mysql/tmp/mysql.sock
Open a new console and try to log in the database:
$ mysql -u root -p
In this case, the error was related to the mysql.user table. Run these commands:
mysql> use mysql; mysql> repair table user; mysql> check table user; mysql> exit;
If the table is recovered, you should see “OK” in the mysql.user status table. Do not forget to remove the innodb_force_recovery option from the my.cnf file and restart the MySQL server again.
$ sudo installdir/ctlscript.sh restart mysql
If you find a different error or cannot fix an issue, we can try to help at http://community.bitnami.com.