An instance where a MySQL database is shut down illegally and data tables are damaged

This article introduces the problem caused by an illegal hardware shutdown.MySQL databaseOfData Table Corruption, An instance where the database cannot run normally. The next step is the author's troubleshooting process. Let's take a look at it.

The procedure for troubleshooting and repairing data tables is as follows:

1. When you access the webpage, an error is displayed, indicating that the database cannot be connected.

2. Start the mysql service, uninstall and disable the mysql service installed by rpm, and install postfix yesterday as if mysql was installed by yum). Run the netstat-anp | grep mysqld command to check whether mysql service is available, mysql-uroot-p cannot connect to the server.

3. view the error message:

 
 

  
  
110726 17:02:23 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  
  
 
  
  
110726 17:02:23 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/host' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:02:23 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:02:23 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended  
  
  
 
  
  
110726 17:24:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  
  
  
 
  
  
110726 17:24:31 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 
 
 

The database table is damaged .. /Mysql/host)

4. Restore the database table:

 
 

  
  
Cd/var/lib/mysql
  
  
 
  
  
Myisamchk-of host. MYI
  
  
 
  
  
-Recovering (with keycache) MyISAM-table 'host. myi'
  
  
 
  
  
Data records: 0
  
  
 
  
  
The host. MYI table is successfully repaired.
 
 

5. Start the service again, check whether the service is started, and log on to mysql. So check the error log again.

 
 

  
  
/usr/local/mysql/libexec/mysqld: Table './mysql/plugin' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
/usr/local/mysql/libexec/mysqld: Table 'plugin' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:24:31 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  
  
 
  
  
110726 17:24:31 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/user' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:24:31 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:24:31 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended  
  
  
 
  
  
110726 17:27:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  
  
  
 
  
  
110726 17:27:13 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 
 
 

6. The./mysql/user table is damaged.

 
 

  
  
[root@localhost mysql]# myisamchk -of user.MYI  
  
  
 
  
  
- recovering (with keycache) MyISAM-table 'user.MYI'  
  
  
 
  
  
Data records: 6 
 
 

7. The table is successfully repaired, but the service cannot be started. continue to view the error log.

 
 

  
  
/usr/local/mysql/libexec/mysqld: Table './mysql/plugin' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
/usr/local/mysql/libexec/mysqld: Table 'plugin' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:27:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  
  
 
  
  
110726 17:27:13 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/db' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:27:13 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/db' is marked as crashed and last (automatic?) repair failed  
  
  
 
  
  
110726 17:27:13 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended 
 
 

8. The last error is that the./mysql/db table has not been fixed./mysql/db table.

9. Execute the following command to repair the./mysql/db table:

 
 

  
  
[root@localhost mysql]# myisamchk -of db.MYI  
  
  
 
  
  
- recovering (with keycache) MyISAM-table 'db.MYI'  
  
  
 
  
  
Data records: 0  
  
  
 
  
  
Data records: 2 
 
 

10. Start the mysql service.

 
 

  
  
/usr/local/mysql/bin/mysqld_safe & 
 
 

11. Check whether the service is running.

 
 

  
  
[root@localhost ~]# netstat -anp | grep mysqld  
  
  
 
  
  
tcp        0      0   
  
  
 
  
  
0.0.0.0:3306                  
  
  
 
  
  
0.0.0.0:*                   LISTEN        
  
  
 
  
  
4360/mysqld           
  
  
 
  
  
unix  2      [ ACC ]     STREAM     LISTENING     14172    
  
  
 
  
  
4360/mysqld         /tmp/mysql.sock 
 
 

The service is running.

12. log on to mysql.

 
 

  
  
[root@localhost ~]# mysql -uroot -p123456  
  
  
 
  
  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
  
  
 
  
  
Your MySQL connection id is 35  
  
  
 
  
  
Server version: 5.1.55-log Source distribution  
  
  
 
  
  
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.  
  
  
 
  
  
This software comes with ABSOLUTELY NO WARRANTY. This is free software,  
  
  
 
  
  
and you are welcome to modify and redistribute it under the GPL v2 license  
  
  
 
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
  
  
 
  
  
mysql>  
 
 

Can log on.

13. You can access the webpage normally. It proves that the data table of the MySQL database is successfully repaired.

This section describes the problem of data table corruption in the MySQL database. If you are interested in understanding