awsmysql

Recover a MySQL database

Check logs

Before trying to recover a MySQL database, you should check the exact error in the log file. To check the latest entries in the MySQL log file, run the command below:

$ sudo tail -n 100 /opt/bitnami/mysql/logs/mysqld.log

Restart and repair the database

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

MySQL

  • 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 /opt/bitnami/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 --skip-external-locking --port=3306 --sock=/opt/bitnami/mysql/tmp/mysql.sock

  • Open a new console and try to log in to 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 /opt/bitnami/ctlscript.sh restart mysql

If you find a different error or cannot fix an issue, we can try to help at https://github.com/bitnami/vms.

Last modification February 9, 2023