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

Source: Internet
Author: User

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:

 
 
  1. 110726 17:02:23 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  2.  
  3. 110726 17:02:23 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/host' is marked as crashed and last (automatic?) repair failed  
  4.  
  5. 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  
  6.  
  7. 110726 17:02:23 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended  
  8.  
  9. 110726 17:24:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  
  10.  
  11. 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:

 
 
  1. Cd/var/lib/mysql
  2.  
  3. Myisamchk-of host. MYI
  4.  
  5. -Recovering (with keycache) MyISAM-table 'host. myi'
  6.  
  7. Data records: 0
  8.  
  9. 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.

 
 
  1. /usr/local/mysql/libexec/mysqld: Table './mysql/plugin' is marked as crashed and last (automatic?) repair failed  
  2.  
  3. /usr/local/mysql/libexec/mysqld: Table 'plugin' is marked as crashed and last (automatic?) repair failed  
  4.  
  5. 110726 17:24:31 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  6.  
  7. 110726 17:24:31 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/user' is marked as crashed and last (automatic?) repair failed  
  8.  
  9. 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  
  10.  
  11. 110726 17:24:31 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended  
  12.  
  13. 110726 17:27:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql  
  14.  
  15. 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.

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

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

 
 
  1. /usr/local/mysql/libexec/mysqld: Table './mysql/plugin' is marked as crashed and last (automatic?) repair failed  
  2.  
  3. /usr/local/mysql/libexec/mysqld: Table 'plugin' is marked as crashed and last (automatic?) repair failed  
  4.  
  5. 110726 17:27:13 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.  
  6.  
  7. 110726 17:27:13 [ERROR] /usr/local/mysql/libexec/mysqld: Table './mysql/db' is marked as crashed and last (automatic?) repair failed  
  8.  
  9. 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  
  10.  
  11. 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:

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

10. Start the mysql service.

 
 
  1. /usr/local/mysql/bin/mysqld_safe & 

11. Check whether the service is running.

 
 
  1. [root@localhost ~]# netstat -anp | grep mysqld  
  2.  
  3. tcp        0      0   
  4.  
  5. 0.0.0.0:3306                  
  6.  
  7. 0.0.0.0:*                   LISTEN        
  8.  
  9. 4360/mysqld           
  10.  
  11. unix  2      [ ACC ]     STREAM     LISTENING     14172    
  12.  
  13. 4360/mysqld         /tmp/mysql.sock 

The service is running.

12. log on to mysql.

 
 
  1. [root@localhost ~]# mysql -uroot -p123456  
  2.  
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.  
  4.  
  5. Your MySQL connection id is 35  
  6.  
  7. Server version: 5.1.55-log Source distribution  
  8.  
  9. Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.  
  10.  
  11. This software comes with ABSOLUTELY NO WARRANTY. This is free software,  
  12.  
  13. and you are welcome to modify and redistribute it under the GPL v2 license  
  14.  
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
  16.  
  17. 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.