Table of Contents
Background
Having a broken MySQL / MariaDB database is no fun. Broken tables can also lead to half complete backups really compounding the situation and increasing risk exposure. If you suspect there is trouble, follow this easy guide.
How to Check and Repair a Broken MySQL Database
By Using a User Interface
Most hosted servers already have PhpMyAdmin already built-in. If you want to make your life easier, just use this. If you’re still rolling your own, congratulations, you’re learning a lot. But for the other sane rest of us, there’s a UI.
Simply navigate to your database, scroll to the bottom, select all tables, and “Check Table” from the menu. Easy does it tiger.
By Using the Command Line
Here is a command to check all your databases:
mysqlcheck -c -u root -p --all-databases
The -c flag means check and is the default operation.
Should you wish to repair a database, do the following:
mysqlcheck -r database table
The -r flag means repair. Follow that with the database name and the table name.
Example of a Broken Table
To illustrate output when a table is corrupt or broken, see the output below:
[root@server ~]# mysqlcheck -c database1 database2 -u root database2.tablename3 warning : Table is marked as crashed error : Deleted block at 101956 doesn't point back at previous delete link error : record delete-link-chain corrupted error : Corrupt
How to Repair the Broken Table
Issue the following command to try and repair the table:
mysqlcheck -r database2 tablename3
Please note that not all database tables are repairable as corruption and crashed tables can take many forms. The only sure way to ensure safety is to have proper backups which are restored on a regular basis so that their integrity are tested.
Contact us if you need assistance with your database or backups or follow the references to learn more.