How to repair MySQL tables from the command line?

MySQLMySQLi Database

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)
raja
Published on 21-Nov-2018 13:52:45
Advertisements