Introduction
Note MySQL tables can and teh data within the tables can become corrupted and usually it is no more readable. And if a user tries to read / write / append those table which is corrupted will lead to the server crashing.
Some common causes :
- MySQL server stops in middle of a write.
- MySQL is modified by an external program and server at the same time.
- MySQL installed server or machine was shut down unexpectedly.
- MySQL installed server or machine hardware fails.
- MySQL installed server or machine has a bug om software of MySQL code.
I recommend to backup your data first and then stop the MySQL.
$ sudo systemctl stop mysql or $ sudo systemctl stop mysqld
Backup
First copy and paste the data in folder mysql_backup
$ sudo cp -r /var/lib/mysql /var/lib/mysql_backup
Wait for the backup to finish.
Troubleshoot
Strart MySQL service and run check table instance. If the table is corrupted use the 3rd command to repair it.
$ sudo systemctl start mysql ..... $ CHECK TABLE table_name; ..... $ REPAIR TABLE table_name;
After successful repair , there will be output:
Output +--------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------+--------+----------+----------+ | database_name.table_name | repair | status | OK | +--------------------------+--------+----------+----------+
If the table is still corrupted download and follow the documentation from MySQL attached.
MySQL Rebuilding or Repairing Tables
Now try reatrting MySQL Server
$ sudo systemctl restart mysql
If the server is still not accessible, try and enable InnoDB’s force_recovery
option.
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
The output of /etc/mysql/mysql.conf.d/mysqld.cnf
. . . [mysqld] . . . innodb_force_recovery=1
Now utility to dump your table data to a new file and rename the file.
mysqldump database_name table_name > out.sql
And Now drop the table from the database.
mysql -u user -p --execute="DROP TABLE database_name.table_name"
Now restore the table with the dump
mysql -u user -p < out.sql
At this point Corrupter TABLES problem should be fixed. You can always verify the logs
Logs verification
Remeber , the root cause of slowness, crashes, or other unexpected behavior in MySQL can be determined by analyzing its error logs. It is stored in /var/log/mysql/error.log
.
$ sudo less /var/log/mysql/error.log
f MySQL isn’t behaving as expected, you can obtain more information about the source of the trouble by running this command and diagnosing the error based on the log’s contents.