In this blog post, we are going to show you how to fix corrupted tables in MySQL.
MySQL is an open-source relational database management system written in C and C++. This management system is used widely and has a large community that is increasing on a daily basis. MySQL is a part of the famous LAMP(Linux, Apache, MySQL, and PHP) stack used in millions of websites today. Since it is used on a daily basis, the probability of some tables crashing and getting corrupted is very high. There are billions of transactions every second on every Linux server using MySQL.
In this blog post, we will install MySQL first and then explain the procedure to fix the corrupted tables. We will use Ubuntu 22.04, but you can choose any Linux distro. Let’s get started!
Table of Contents
Prerequisites
- A server with Ubuntu 22.04 as OS
- User privileges: root or non-root user with sudo privileges
Update the System
Update the system packages to their latest version available before installing MySQL on your system.
sudo apt-get update -y && sudo apt-get upgrade -y
Install MySQL database server
To install the MySQL database server, execute the command below.
sudo apt install mysql-server -y
Start and enable the mysql.service with the following commands:
sudo systemctl start mysql && sudo systemctl enable mysql
Check the status of the mysql.service
sudo systemctl status mysql
You should receive the following output:
root@host:~# sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2022-11-30 17:57:25 CST; 22s ago
Main PID: 78283 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4575)
Memory: 364.2M
CPU: 1.579s
CGroup: /system.slice/mysql.service
└─78283 /usr/sbin/mysqld
Nov 30 17:57:24 host.test.vps systemd[1]: Starting MySQL Community Server...
Nov 30 17:57:25 host.test.vps systemd[1]: Started MySQL Community Server.
Why are MySQL tables getting corrupted
There are many reasons why MySQL tables are getting corrupted. These are the common reasons:
Server shutdown unexpectedly: If there is a lack of RAM or the CPU is overloaded, then the server can stop and cause MySQL to shut down incorrectly, which may lead to database corruption.
Modification in the MySQL queries: If the developers modify the MySQL queries and there is some syntax error or similar, the table may get corrupted.
Hard Disk 100% Used: If the hard disk is full, the MySQL service will automatically stop due to insufficient space and may corrupt the tables.
Malicious code injected in the website: If somehow the malicious code is injected into the document root of the website, then into the code of the file may be some queries that can lead to table corruption.
Fix corrupted tables
Finally, we will show you how to fix the corrupted tables in MySQL with examples.
First of all, you should know that before taking any actions, you must backup your MySQL files. To do that, execute the following commands:
sudo systemctl stop mysql rsync -Waq /var/lib/mysql/ /var/lib/mysql.backup/
Once the copy is made, you can start the MySQL service again:
sudo systemctl start mysql
To repair the corrupted table mysqlcheck executes the following command:
mysqlcheck -r "database_name" "database_table" -u root -p
This command will repair the database_table in the database_name</b using the root username and MySQL root password. The flag for repairing the database is defined with -r.
If you want to check the table and auto-repair if it is corrupted, execute the following command:
mysqlcheck -c --auto-repair "database_name" "database_table" -u root -p
If you are not sure about the exact command, then you can repair the table using the MySQL console:
mysql -u root -p use "database_name"; repair table "database_table";
You should receive the following output:
+--------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------+--------+----------+----------+ | database_name.table_name | repair | status | OK | +--------------------------+--------+----------+----------+ 1 row in set (0.04 sec)
Please note that this will work only if the table’s storage engine supports repair.
If the storage engine for the table does not support repair, you will get the following message:
mysql> repair table wp_users; +-------------------------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------+--------+----------+---------------------------------------------------------+ | database_name.table_name| repair | note | The storage engine for the table doesn't support repair | +--------------------+--------+----------+--------------------------------------------------------------+ 1 row in set (0.04 sec)
If these commands are not fixing the corrupted tables, the next step is adding the innodb_force_recovery=1 into the MySQL configuration file.
The MySQL configuration file on Debian-based distributions is located at /etc/mysql/mysql.conf.d/mysqld.cnf. Open this file with your favorite editor and add these lines of code:
[mysql] innodb_force_recovery=1
Save the file, close it and restart the MySQL service. After restarting the MySQL service, it will repair the corrupted tables.
Congratulations! You just learned how to fix the corrupted database tables with several commands. If somehow this is not working and you do not know., the next steps you can simply contact our technical support, and they will help you with any aspect of your database service. You just need to submit a support ticket via our ticketing system. We are available 24/7.
PS. If you liked this post on how to fix corrupted tables in MySQL, please share it with your friends on social networks or simply leave a reply below. Thanks.