Aus EUserv Wiki

---

Version vom 14:36, 16. Jan. 2013

MySQL database repair

Inhaltsverzeichnis 1 General 2 MyISAM tables 2.1 Checking 2.1.1 Tool: mysqlcheck 2.1.2 Tool: myisamchk 2.1.3 Tool: phpMyAdmin 2.2 Repair 2.2.1 Tool: mysqlcheck 2.2.2 Tool: myisamchk 2.2.3 Tool: phpMyAdmin 3 InnoDB tables 3.1 Checking 3.1.1 Tool: mysql and mysqlcheck 3.2 Repair 3.2.1 Tool: OPTIMIZE TABLE 3.2.2 Tool: InnoDB Recovery

General

In the following wiki article you will find an instruction how to check your MySQL database for errors and repair these.

MyISAM tables

Checking

Tool: mysqlcheck

---

To check your MySQL database you can use the client mysqlcheck. It checks, repairs, optimizes, or analyzes tables.
The utility program mysqlcheck can be executed if the MySQL server is still running.
The client provides the SQL commands CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE.

Run the following command in your console window for the check:

mysqlcheck -u <username> -p <password> --all-databases --analyze --check

Replace <username> and <password> with your corresponding MySQL username and the corresponding MySQL password. The parameter -- all-databases checks all tables in all databases for errors, -analyze analyzes the database tables and --check checks the tables for errors.

Under the following link you will find a documentation to mysqlcheck.

http://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html

Tool: myisamchk

---

The myisamchk utility gets information about your database tables and checks, repairs, or optimizes them.
This client should be used only if the MySQL server is not accessible unlike mysqlcheck.
Enter the following command, so myisamchk can check your table:

 myisamchk --check <tablename>   

Here <tablename> is replaced with the corresponding name for the table of the MySQL database which should be checked.
The parameter --check indicates that the table should be checked for errors.
In addition, further parameters can be specified:

• --check-only-changed checks only tables that have changed since the last check.
• --extend-check checks the table very thoroughly and takes a long time.
• --fast checks only tables that haven't been closed properly.

More information about myisamcheck can be taken from the documentation:

http://dev.mysql.com/doc/refman/5.1/en/myisamchk.html

Tool: phpMyAdmin

---

If you have installed phpMyAdmin on your dedicated server and you use this to manage your MySQL databases, you can check your databases for errors with it.

Login to phpMyAdmin.

Select the MySQL table from your database that is supposed to be checked.

Select the tab Operations.

Finally click on the link Check table.

The command CHECK TABLE has been executed and the result will be displayed.

Here you can find the documentation for this SQL command:

http://dev.mysql.com/doc/refman/5.1/en/check-table.html

Click on the link Analyze table at the bottom.

The command ANALYZE TABLE has been executed and the result will be displayed. Here you can find the documentation for this SQL command:

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

Repair

Tool: mysqlcheck

---

With the client mysqlcheck you have the possibility to repair a faulty database, even if the MySQL server is running.
Enter the following command:

 mysqlcheck - u <username> -p <password> --auto-repair -o -c --all-databases

Replace <username> and <password> with your corresponding MySQL username and the corresponding MySQL password. The parameter --all-databases checks all databases for errors, --auto-repair automatically fixes corrupt database tables, -o optimizes the tables and -c checks all tables for errors.

On the following link you will find a documentation for mysqlcheck:

http://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html

Tool: myisamchk

---

With myisamchk a table repair can be performed with the following command:

 myisamchk  --recover <tablename>

The parameter <tablename> has to be replaced with the appropriate name of the table of the MySQL database. The parameter --recover performs a repair which practically repairs every problem.
If the table cannot be restored with --recover, the operation --safe-recover can be performed. This parameter performs a repair, using an old recovery method that select all records and updates all index trees according to the found records.

 myisamchk  --safe-recover <tablename>

Other repair options, provided by myisamchk, can be taken from the documentation:

http://dev.mysql.com/doc/refman/5.1/en/myisamchk-repair-options.html

Tool: phpMyAdmin

---

In case of a faulty database with phpMyAdmin you have the possibility to repair this.
Click on the link Repair table at the bottom.

The command REPAIR TABLE is executed and the result will be displayed. Here you can find the documentation for this SQL command:

http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Click on the link Optimize table.

The command OPTIMIZE TABLE is executed and the result will be displayed. Here you can find the documentation for this SQL command:

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

InnoDB tables

Checking

Tool: mysql and mysqlcheck

---

If you are running MySQL with InnoDB tables, the following error messages point to a corrupt database:

 InnoDB: (index “PRIMARY” of table “test”.”test”)
 InnoDB: Database page corruption on disk or a failed

To check all the tables in your database for errors, the following command can be used:

 mysqlcheck -u root -p --check --databases dbname

Replace dbname with the name of your database.

Individual tables can be checked within the MySQL command line:

 mysql -u root -p
 mysql> CHECK TABLE {table name};

If errors are found, the MySQL server automatically turns off, to prevent a spread of more errors.

Repair

Tool: OPTIMIZE TABLE

---

The command OPTIMIZE TABLE tries to recover the table and its indexes. It is possible to apply the command globally or on individual tables:

 mysqlcheck -u root -p --optimize --databases dbname

This command optimizes all tables of the database (dbname must be replaced the name of the corresponding database).

 mysql -u root -p
 mysql> OPTIMIZE TABLE {table name};

This command optimizes the selected table (table name must be replaced with the name of the corresponding table).

If the warning Table does not support optimize, doing recreate + analyze instead occurs, this represents no problem, the command OPTIMIZE proceeds differently to MyISAM tables.

Here you can find the documentation for this SQL command:

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

Tool: InnoDB Recovery

---

The recovery mode for InnoDB tables tries to restore damaged tables in the running operation.

Stop the MySQL server:

 mysqld stop

Now the recovery mode is activated. To do this, add the configuration file of MySQL

 /etc/mysql/my.cnf

in the section [mysqld] add the following line:

 innodb_force_recovery = 4

and finally start the MySQL server:

 mysqld start 

This starts the InnoDB storage engine. The value after "=" (0-6) sets the strictness of the data control. The value 4 continues the MySQL server, even if it encounters corrupt data structures. Simultaneously erasing, writing and updating of tables with this option is no longer possible, so in the recovery mode, manipulation of the database is not possible.

Now create a dump of the database backup:

 mysqldump -u user -p passwort -A > dump.sql

Stop the MySQL server and change in

 /etc/mysql/my.cnf

the value of

 innodb_force_recovery 

to 1.

This allows a writing access to the database. Now, restart the MySQL server and find the corrupt tables manually:

Increase the limit value gradually until the following error is displayed:

 ERROR 2013 (HY000): Lost connection to MySQL server during query

Now, you know in which row of the table the error has occured and you can try to fix it or to delete the table with DROP.

If you are done with the troubleshooting, you can create a second dump with the fixed database and stop the MySQL server:

 mysqldump -u user -p passwort -A > dump2.sql
 mysqld stop

Delete the content of the directory /var/lib/mysql/.

Now remove the line

 innodb_force_recovery = 4 

from the file /etc/mysql/my.conf

and start the MySQL server:

 pre>mysqld start

Now enter the dump:

 mysql -u user -p passwort < dump2.sql

If the corrupted InnoDB engine with the maximum value 6 from innodb_force_recovery cannot be restored, the recovery from a backup is required.