Robot Butler
  Making those complicated tasks nice and easy.
  A hub for guides, walkthroughs and general information.
Follow us on Twitter RSS Feed
Article Image If MySQL isn't shut down correctly you may end up with what are referred to as "crashed tables". These are basically tables that were stopped in the middle of doing something, generally due to a crash in MySQL itself, the server running out of RAM or a sudden power failure.

Going through each database and repairing each table can be tedious, luckily MySQL has command line tools that allow for modification of multiple tables at once, or even multiple databases. Doing this requires that MySQL tools are installed (generally the case if you have a MySQL server running as they are usually a dependency) and that you have access to the root MySQL user or a user with root-like privileges.

In the below code, simply replace user, password and database with your relevant login details and the database you wish to use.

To perform a repair on all the tables on one database, you would use the following syntax:
mysqlcheck -uuser -ppassword --auto-repair database

Alternatively if you wanted to repair all databases on the server:
mysqlcheck -uuser -ppassword --auto-repair -A

But as you're working through all the tables on your server anyway, it is probably worth doing an "optimise" instead which will fix any errors found as well as shuffling everything around inside your tables to speed things up. This is done internally, nothing changes data wise as far as you will see. To quote the official material:

After deleting a large part of a table, or making many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

This is a simple change compared to the above code to optimise all your tables instead:
mysqlcheck -uuser -ppassword -o -A

Depending on how large your databases are and the speed of the server, this may take a few seconds up to a few minutes for very large (GB+) databases.


No comments yet.

Post a Comment

    Email (not displayed)

The name of this website is Robot...      


  • 14 categories
  • 50 articles

Site, design and code by Benjamin Hodgetts.