MySQL database prompts data sheet loss fix solution

For some time recently, company's server every once in a while (about 24 hours), will appear inaccessible situation, and then restart the server, everything is back to normal, has not known what is the problem, so asked the engine room engineer, he helped me analyze the server's running log, Found a lot of MySQL bugs.

Basically every 20 seconds, there will be an error prompt, look at this hint should be this table needs to fix

Error causes

Check the Internet to find out why the table damage, basically get the following reasons:
frequently query and update MySQL database tables, resulting in indexing errors.
MySQL database for some reason to be damaged, such as: The database server sudden power outages, in the database table to provide services to the table of the original file to do some operation may cause the MySQL database table is corrupted and can not read data. In short, the damage to the table is caused by some unpredictable problems.
This database is running a discuz forum, has been people in the brush user registration, I am more agree with the first kind of situation, and table Pre_commen_regip is also the user registration related IP record table, share the reasons for this occurrence, we say the solution.

Solving method

We can use database management tools, such as phpMyAdmin, log in to the database, select the form that needs to be repaired, and then click the Repair table operation:

If you do not have a database management tool, you can go to the server remotely and then use the command line to repair the damaged table:

Locate the Bin/myisamchk tool for the MySQL installation directory and enter at the command line:

Myisamchk-c-R. /data/tablename/posts. Myi

The Myisamchk tool will then help you recover the index of the datasheet, and you won't have to restart MySQL to solve the problem.

About the MySQL repair

When you try to fix a problem with a damaged table, there are three types of fixes. If you get an error message stating that a temporary file cannot be established, delete the file indicated by the information and try again – this is usually the legacy of the last repair operation.
The three repair methods are as follows:

% Myisamchk--recover--quick/path/to/tblname
% Myisamchk--recover/path/to/tblname
% Myisamchk--safe-recover/path/to/tblname

The first is the fastest, to fix the most common problems, and the last is the slowest one to fix problems that some other methods can't fix.
Review and repair MySQL data files
If the above method cannot fix a damaged table, you can try these two techniques before you give up:
1, if you suspect the index file of the table (*. Myi An unrecoverable error occurred, or even lost the file, you can use the data file (*. MYD) and the data format file (*.FRM) to regenerate it. First, make a copy of the data file (TBLNAME.MYD). To restart your MySQL service and connect to this service, delete the contents of the table using the following command:

Mysql> DELETE from Tblname;

When you delete the contents of a table, a new index file is created. Quit logging on and shut down the service and overwrite the new (empty) data file with the data file (tblname.myd) you just saved.
Finally, use MYISAMCHK to perform a standard fix (the second method above) to regenerate index data based on the contents of the table's data and the table's format file.
2, if your table's format file (TBLNAME.FRM) is missing or an unrecoverable error occurred, but you know how to use the corresponding create
Table statement, you can regenerate a new. frm file and use it with your data files and index files (if there is a problem with the index file, using the above method to reconstruct a new one). First make a copy of the data and index file, and then delete the original file (delete all the records in the data directory about the table).

Start the MySQL service and create a new table using the original CREATE table file. The new. frm file should work, but the best thing to do is to perform the standard fix (the second method above).

Summarize

In order not to make rash repairs, so take a conservative approach, we know that MySQL an efficient management tool is phpMyAdmin, and in this management software contains the table inspection, analysis, repair, optimization functions, compared to the online supply of vague command line is more secure and easier.
Through practice, after using the checklist function did find the problem, and then repaired using the repair function, the feedback results each table is OK, and then perform an optimization, the test visit site finally returned to normal.