Check, Repair, Optimize, Analyze MySQL tables

Checking and Repairing MySQL Tables

                Every Database Administrator has a worst nightmare and that is when their data or database has been corrupted and this is a very big problem. This will affect the whole operation of a company.

                But if your database is MySQL luckily there’s still hope to retrieve or recover your database, by the use if the built-in MySQL Recovery tool to check and try to recover your corrupted database.

                Two MySQL Built-in commands you can use to recover corrupted database.

1.       The MySQL distribution ships with a tool called "myisamchk," designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line.

2.       In addition to the tool above, MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

I will show you how to use MySQL commands. The CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE commands.

1.       Open  your MS Command Prompt and open your MySQL  server using

>mysqld

                2. Then open new command prompt and open mysql using

                      >mysql –u root

                3. Then choose the database you want to repair

                >use [database name]

4. And then you can now use the MySQL Administrator commands for checking repairing and optimize tables.

                >check table [table name] = If you're having trouble accessing a table you can use this command to check errors

>repair table [table name] =  If your check reveals errors, or if you see cryptic error messages about table                      handlers or corrupt indexes, then you need to try repairing the table.

>optimize table [table name]= OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

>analyze table [table name] = ANALYZE TABLE analyzes and stores the key distribution for a table.