Recovery InnoDB MySQL

From TKJOnline Wiki
Jump to: navigation, search

This article describes how to repair MySQL databases and tables. As a database's tables grow, errors may occur from time to time. When they do, MySQL includes several tools that you can use to check and repair database tables. To do this, follow the procedures below in the order in which they appear.

STEP 1: BACKING UP THE DATABASES

Before you attempt to repair any database, you should back it up first. To back up all of the files from all of your databases, follow these steps:

  • Log in to your server using SSH.
  • Stop the MySQL server using the appropriate command for your Linux distribution:

For CentOS and Fedora, type:

service mysqld stop

For Debian and Ubuntu, type:

service mysql stop
  • Type the following command:
cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)
  • For CentOS and Fedora, type:
service mysqld start
  • For Debian and Ubuntu, type:
service mysql start

STEP 2: CHECKING AND REPAIRING A TABLE WITH MYSQLCHECK

After you back up your databases, you are ready to start troubleshooting. The mysqlcheck program enables you to check and repair databases while MySQL is running. This feature is useful when you want to work on a database without stopping the entire MySQL service.
Additionally, mysqlcheck works on tables that use the MyISAM or InnoDB database engines.
To use mysqlcheck, follow these steps:

  • As the root user, type the following command:
cd /var/lib/mysql
  • Type the following command, replacing DATABASE with the name of the database that you want to check:
mysqlcheck DATABASE
  • Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table. However, if mysqlcheck reports an error for a table, type the following command to try to repair it. Replace

DATABASE with the database name, and TABLE with the table name:

mysqlcheck -r DATABASE TABLE
  • If mysqlcheck cannot successfully repair the table or tables, go to the following procedure.

STEP 3: RUNNING ENGINE-SPECIFIC DIAGNOSTICS

Repairing MyISAM tables with myisamchk

Running the InnoDB recovery process

If MySQL is unable to drop a database, you can delete it manually in step 8 below after you stop the MySQL server.

  • Stop the MySQL server using the appropriate command for your Linux distribution:

For CentOS and Fedora, type:

service mysqld stop

For Debian and Ubuntu, type:

service mysql stop
  • If you were unable to drop a database in step 6, type the following commands to delete it manually. Replace DBNAME with the name of the database that you want to delete:
cd /var/lib/mysql
rm -rf DBNAME

Make sure you do not delete the mysql or performance_schema directories!

  • Use your preferred text editor to open the my.cnf file on your server, and then comment out the following line in the [mysqld] section as shown:
#innodb_force_recovery=4

This disables InnoDB recovery mode.

  • Save the changes to the my.cnf file, and then start the MySQL server using the appropriate command for your Linux distribution:

For CentOS and Fedora, type:

service mysqld start

For Debian and Ubuntu, type:

service mysql start
  • Type the following command to restore the databases from the backup file you created in step 5:
mysql < databases.sql
  • Test the restored database.