Site Tools


New release available: 2020-07-29 "Hogfather". upgrade now! [51.3] (what's this?)
New release candidate 3 available: 2020-06-09 "Hogfather". upgrade now! [51.2] (what's this?)
New release candidate 2 available: 2020-06-01 "Hogfather". upgrade now! [51.1] (what's this?)
New release candidate available: 2020-06-01 "Hogfather". upgrade now! [51] (what's this?)
Hotfix release available: 2018-04-22c "Greebo". upgrade now! [50.3] (what's this?)
mysql_mariadb_galera:repair_and_optimization

Finding Problems

The easiest way I have found to find problem tables is by using the following command

mysqlcheck -u root -p --check --all-databases |grep -vE ' OK'

This will check all tables and the “grep” will get rid of all tables that are OK from the output. You are left with only errors.

Repair and Optimization

Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

mysqlcheck is available in MySQL 3.23.38 and later.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. The exact operations are determined by the underlying storage engine used.

You must execute it on running database. It is recommended that you kill other incoming queries (like from your website) before running this which makes it a lot faster. I simply stop my httpd server before running it. You may want to use a Site Unavailable message instead so that the MySQL server is left alone.

MySQLTuner

mysql_mariadb_galera/repair_and_optimization.txt · Last modified: 2020/06/09 10:45 by 46.30.112.131