MySQL tables becoming corrupt is a common issue in the eCommerce / application hosting industry. Corrupt MySQL tables is a nerve wracking situation and could be very stressful if you do not know how to fix it quickly before business is impacted.
In this article let me help you fix MySQL tables easily. It’s always better to be proactive than reactive as it could save our day. If we do not act early on, then it will snowball into a bigger issue. So I recommended checking your db tables once in a while as a proactive approach.
Assumptions:
- Assuming you are on a linux machine and have full root level access to the system running Mysql.
- Assuming you have already taken the MySQL backup and stored in a safe place as a recovery system if something goes wrong.
- Also, that someone with MySQL knowledge is executing these steps or commands.
Let’s start !
Step 1: First lets check and see if any of the db that you work with has a corrupted table. For this, use the Mysql command mysqlcheck. See how it is used below,
Mysqlcheck -u < username> -p <password> -database <database name>
Step 2 : Now we know that the table appdb.ID_user_details is corrupted. To fix it, we will login to Mysql and use the repair command that mysql offers. See below
Step 3: Login to your MySQL prompt with the below command.
Mysql -u <username> -p <password>
Step 4: Let us now list all the databases we have. On the MySQL prompt execute the following SQL query.
mysql#> show databases;
Step 5: Let us select the database on which we plan to perform these activities. Our db is appdb and lets select it.
mysql#> use appdb;
Step 6: Now let us repair the table that had an error. The table that we need fixed is ID_user_details. So we will be replacing the table name with ID_user_details
mysql#> repair table <table name>
Mysql#> repair table ID_user_details;
If the status of the outcome is OK; it shows the table has been repaired.
Now, you know how to repair tables when you find one of your tables gets corrupted.
Read more MySQL articles: Unable to connect to database, Lost connection to MySQL server during query and many more.