What should you do to repair the MySQLadmin table and protect your data?
Here you will find out:
- how to repair table
- how DiskInternals can help you
Are you ready? Let's read!
How to check MySQL tables?
There are actually several ways to check the table for errors, but the most efficient and truthful way is to use the MyISAMCHK utility or CHECK TABLE.
The command that invokes this utility is MyISAMCHK table-file.
It is strongly recommended to shut down the server before using this program, as MyISAMCHK requires exclusive access to tables (it works directly with table files).
MyISAMCHK requires exclusive access to tables (it works directly with table files).
You can also use a few additional parameters when checking the table for errors, which you will learn about in the next paragraph.
The tool MyISAMCHK requires clients to be locked while running diagnostics, and no client can lock the table being checked while MyISAMCHK is running. This should be considered; there is a large amount of verification.
In such a case, you can configure MyISAMCHK to use large buffers (use the command MyISAMCHK – help) or use another method to check your tables: the CHECK TABLE command.
The CHECK TABLE command forces the server to check tables without shutting down the server and releasing all locks on the tables. It looks like this:
MySQL> CHECK TABLE airport;
Here, you can also use additional parameters to execute the required test type.
It should be understood that if your server is down, you cannot run the CHECK TABLE utility.
And MyISAMCHK can work flawlessly in such conditions, as it works at the file level. And remember when choosing a table validation method:
MyISAMCHK only works with the MyISAM storage engine, and you must use the CHECK TABLE command to validate InnoDB tables.
Table 1
You can check the tables for errors and correct them using the following parameters:
-c or --check : check the tables for errors; used by default.
-e or --extend-check : check the table carefully. The operation will be quite slow in the case of numerous indexes. This option should be used in extreme situations, and the key_buffer_size value should be significantly increased beforehand.
The -F or --fast : the operation will only check tables that have not been closed correctly.
-m or - medium-check : faster than an extended check, but in most cases sufficient for effective table checking.
How to repair tables?
If you find errors after checking a table, you must first make a backup of that table and then fix the table.
The familiar MyISAMCHK tool can also be used to repair a damaged table; for this, only use other options. So, use the –recover parameter with the table file name, and the repair process will start. There are other options for correcting tables that are available in the next paragraph.
Remember that MyISAMCHK is a file-level tool and, therefore, requires all locks to be unlocked and all clients removed.
When correcting a table, you should try the quickest and simplest options first, and only move on to the slower and more complex options if absolutely necessary.
In addition to the MyISAMCHK tool, there is another option for restoring a table - this is the REPAIR TABLE command with the name of the table file. To use the REPAIR TABLE, the server must be running, while you must use MyISAMCHK only if the server crashed - don't forget this.
Table 2
-r or --recover : this fix fixes almost everything except unique keys.
-o or --safe-recover is a : slow fix where all rows are read in a row and all index trees are updated based on the rows found. If fixing with the -r option does not work, you should use -o.
-q or --quick : Repair without modifying the data file, express method.
Protect your MySQL databases
DiskInternals MySQL Recovery is a dedicated MySQL database recovery application for experienced database administrators. Here, you can recover disks in the following file systems: NTFS or HFS, Ext2 / Ext3 / Ext4, exFAT, FAT and FAT32; it is also possible to restore inaccessible InnoDB databases.
MySQL Recovery provides two recovery modes: for simple cases, the automatic mode is the best. In more complex situations, the administrator can use manual mode with many additional options and independently restore the database or rebuild it. The text files and images found by the program can be previewed before recovery. Support for any set of national characters, including Unicode tables, is also supported by MySQL Recovery.
Step-by-step MySQL Recovery:
- After downloading the program and installing it on your computer, open DiskInternals MySQL Recovery and click the wizard icon.
Then select one of the following recovery options:
- After downloading the program and installing it on your computer, open DiskInternals MySQL Recovery and click the wizard icon.
Then select one of the following recovery options:
- recover MySQL database
- recover the database from disk
Then the scanning phase begins, which will end when you see the "Finish" button. Click on it and preview the available files using the preview function.
Now, the last step is to save the recovered tables. For this, the Export Wizard is also provided, which will offer you all possible export locations. However, for this, you must purchase a license for this professional software.