MySQL is a relational database management system based on the Structured Query Language, which is the popular language for accessing and managing the records in the database. MySQL is open-source and free software under the GNU license. It is supported by Oracle Company.
Database corruption is not always as simple as a "database corrupted" error message. We can only act based on the symptoms that suggest database corruption. The corruption may be limited to a single table, a data block, or a single entry.
Common causes of database corruption are:
Server hardware failure.
Unexpected machine shutdown.
The MySQL server stopped in the middle of a write.
Using 3rd party software to access the database
Software bugs within the MySQL code.
Wrong data schema.
Backing up MySQL Data
Stop MySQL database using the following command.
# systemctl stop mysqld
Create a backup now.
# cp -r /var/lib/mysql /var/lib/mysql_backup
Restart the MySQL database.
# systemctl start mysqld
Finding MySQL Engine Type
Log in to MySQL using username and password.
# mysql -u root -p
List the databases in MySQL.
SHOW DATABASES;
Use the following command to know the database table engine. If it is InnoDB or MyISAM.
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'layerstack_innodb';
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'layerstack_myisam';
Repairing MyISAM Tables
Select layerstack_myisam database using the following command.
USE layerstack_myisam;
Check for corruption on tables running the MyISAM storage engine, such as the customer's table above, using the following MySQL prompt.
CHECK TABLE layerstack_myisam;
Use the following command to repair myisam table.
REPAIR TABLE layerstack_myisam;
Repairing MySQL Database with mysqlcheck
The mysqlcheck command is an alternative to repairing database tables in the terminal.
Change directory to /var/lib/mysql.
# cd /var/lib/mysql
Use mysqlcheck command.
# mysqlcheck -u root -p --safe --recover <database> <tablename>
Repairing InnoDB Tables
Start InnoDB recovery, use the following command to edit mysqld.cnf file.
# vim /etc/my.cnf
And add the following
innodb_force_recovery=1
By default, the forced recovery option is set to 0 (off) and goes up to 6. Every new level includes the recovery features of the lower numbers.
Restart the MySQL service.
# systemctl restart mysqld
Create mysqldump of layerstack_innodb database.
# mysqldump -u root -p --routines --triggers --events layerstack_innodb > backup_db.sql
The next step is to drop the table from the database.
# mysql -u root -p --execute="DROP TABLE layerstack_innodb.layerstack_innodb"
Restore the table.
# mysql -u root -p layerstack_innodb < backup_db.sql
This tutorial demonstrated how to fix a corrupted MySQL table running the MyISAM or InnoDB storage engines.
Related Tutorials
