How to repair MySQL tables from the command line?


The repair of MySQL tables is only applicable to MyISAM engine type, not for InnoDB. Therefore, we need to change the Engine type to MyISAM.

The following is an example.

Creating a table

mysql> create table RepairTableDemo
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.51 sec)

To convert this table to engine type MyISAM, use ALTER.

mysql> ALTER TABLE RepairTableDemo ENGINE = MyISAM;
Query OK, 0 rows affected (1.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

Inserting records into table.

mysql> insert into RepairTableDemo values(1,'John'),(2,'Carol'),(3,'Johnson');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from RepairTableDemo;

The following is the output.

+------+---------+
| id   | name    |
+------+---------+
|    1 | John    |
|    2 | Carol   |
|    3 | Johnson |
+------+---------+
3 rows in set (0.00 sec)

Let us now see the syntax to repair a table.

REPAIR TABLE yourTableName;

The following is the query −

mysql> REPAIR TABLE RepairTableDemo;

Here is the output. It shows that the repair status is fine.

+--------------------------+--------+----------+----------+
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| business.repairtabledemo | repair | status   | OK       |
+--------------------------+--------+----------+----------+
1 row in set (0.10 sec)

Updated on: 30-Jul-2019

261 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements