There are several methods to analyse, repair and optimize tables for MySQL databases.
- Using PHPMyAdmin
- Using SSH using
- Using SSH
Point 1 & 2 are not practical if you would like to do this to all databases on the server. So we will focus on point 3 with some very simple commands.
Using SSH using
If you have a need for a single table follow the commands below.
# Log in to the server using SSH.
# replace [username] with your username and without the brackets
$ mysql -u [username] -p
# Enter your password
# You have now entered MySQL
# Without brackets, enter the following to access a specific table
$ use [databasename];
# Use this command to show the tables
$ show tables;
# Unlike phpMyAdmin, you can only repair one table at a time
# To check a table for errors, enter
$ check table [yourtablename];
# To repair a table, enter
$ repair table [yourtablename];
#Type quit to return to the command prompt.
mysqlcheck command is useful for analysing, repairing and optimizing all database tables in one go. This process can take time, so be
$ mysqlcheck --all-databases -a
$ mysqlcheck --all-databases -r
$ mysqlcheck --all-databases -o
# Run this command to Auto-Repair tables
# Important: mysqlcheck will first check all tables and then repair those that filed the check
$ mysqlcheck -A --auto-repair
# For a quick repair
$ mysqlcheck --all-databases -r -q