Using SSH to analyse, repair and optimize ALL MySQL Databases

2 min read

There are several methods to analyse, repair and optimize tables for MySQL databases.

  1. Using PHPMyAdmin
  2. Using SSH using mysql command
  3. Using SSH mysqlcheck

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 mysql command

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.

Using SSH mysqlcheck

The mysqlcheck command is useful for analysing, repairing and optimizing all database tables in one go. This process can take time, so be 

# analyze
$ mysqlcheck --all-databases -a
# repair
$ mysqlcheck --all-databases -r 
# optimize
$ 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

Official documentation

Related post