How to check the corrupted MySQL Data File

How can I check if the MySQL data file is corrupted? This may be a problem that many people have encountered. It doesn't matter. The following describes how to check the corrupted MySQL Data File.

Due to temporary power failure, kill-9 can be used to stop the MySQL service process, and all these may destroy MySQL data files. If the service is changing the file when it is disturbed, the file may be in an incorrect or inconsistent state. This destruction is sometimes not easy to find. It may be a long time before you find this mistake. So when you find this problem, maybe all the backups have the same error.

Chapter 1 of the MySQL Reference Manual describes MySQL's built-in myisamchk function and how to use it to check and repair your MySQL data files. Although this chapter is recommended for anyone who wants to build a strong MySQL service, we still need to discuss the key points here.

Before proceeding, you must realize that the myisamchk program should be unique in accessing the MySQL data file used for checking and modifying. If MySQL is using a file and modifying the file myisamchk is checking, myisamchk will mistakenly think that an error has occurred, and will try to fix it -- this will cause the MySQL service to crash! In this way, to avoid this situation, we usually need to close the MySQL service at work. You can also temporarily disable the service to make a copy of a file and then work on the copy. After you have finished, close the service again and replace the original file with the new file (You may also need to use the change log during the period ).

The MySQL data directory is not hard to understand. Each database corresponds to a sub-directory, and each sub-directory contains files corresponding to the data tables in the database. Each data table corresponds to three files with the same name but different extensions. The tblName. frm file is a table definition that stores the content and type of data columns contained in the table. The tblName. MYD file contains the table data. The tblName. MYI file contains the table index (for example, it may contain a lookup table to help improve the query of the table's primary key columns ).

To check the error of a table, you only need to run myisamchk (in the bin directory of MySQL) and provide the file location and table name, or the index file name of the table:

Myisamchk/usr/local/mysql/var/dbName/tblName

Myisamchk/usr/local/mysql/var/dbName/tblName. MYI

You can run the preceding two commands to check the specified table. To check all tables in the database, you can use wildcards:

Myisamchk/usr/local/mysql/var/dbName/*. MYI

To check all tables in all databases, you can use two wildcards: myisamchk/usr/local/mysql/var/*. MYI

Without any options, myisamchk performs a normal check on the table file. If you have doubts about a table, but normal checks cannot find any errors, you can perform a more thorough check (but it is also slower !), This requires the -- extend-check option:

Myisamchk -- extend-check/path/to/tblName

The check for errors is not destructive, which means you don't have to worry about executing the check on your data files will make the existing problems worse. On the other hand, the repair option is usually secure, but its changes to your data files cannot be undone. For this reason, we strongly recommend that you first make a backup when trying to repair a corrupted table file and ensure that your MySQL service is disabled before the backup is made.

 

10 common MySQL command lines

Seven common MySQL command lines

MySQL permission table Introduction

Cancel MySQL user permissions

Introduction to modifying table structure statements in MySQL