MySQLcheck Optimizing and Repairing All Databases

optimize repairing mysql databases with mysqlcheck
mysql optimize all tables

In this tutorial, we will show you how to use MySQLcheck for optimizing and repairing all tables and databases in MySQL. We will talk about mysqlcheck which is a maintenance command-line tool that allows you to check, analyze, repair, and optimize MySQL/MariaDB tables and databases. Repairing MySQL database is not a complicated process, just follow the steps below carefully.

1. Check one table in the MySQL database

The following command will check the table posts in the database blog:

$ mysqlcheck -c blog posts
blog.posts                         OK

If your database is protected by a password add -u root -p at the end of the command:

$ mysqlcheck -c blog posts -u root -p
Enter password:
blog.posts                         OK

2. Analyze all tables in a MySQL database

The following command will check the table posts in the database blog:

Need a fast and easy fix?
✔ Unlimited Managed Support
✔ Supports Your Software
✔ 2 CPU Cores
✔ 2 GB RAM
✔ 50 GB PCIe4 NVMe Disk
✔ 1854 GeekBench Score
✔ Unmetered Data Transfer
NVME 2 VPS

Now just $43 .99
/mo

GET YOUR VPS
$ mysqlcheck -a blog posts
blog.posts                         OK

If the MySQL/MariaDB server is running on a remote host, add -h at the end of the command:

$ mysqlcheck -a blog posts -h remotehost.com
blog.posts                         OK

3. Optimize all tables in all MySQL database

$ mysqlcheck -o --all-databases
blog.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
mysql.time_zone_transition_type                    Table is already up to date
mysqlcheck optimize all databases

Table does not support optimize, doing recreate + analyze instead means that we’re doing OPTIMIZE on an InnoDB table that doesn’t support this option. When doing OPTIMIZE on an InnoDB table it creates an empty table, copies all rows from the existing table into to the new one, deletes the old one and renames the new table, and then runs ANALYZE on the table.
Table is already up to date means that the table is already up to date and there is no need to check it.

4. Repair multiple MySQL databases

The following command will repair all tables in both blog and blog2 databases:

$ mysqlcheck -r --databases blog blog2

If you see note : The storage engine for the table doesn't support repair it means that you are doing REPAIR on an InnoDB.

5. Optimize and repair all tables in all MySQL databases

mysqlcheck

The following command will check all tables in all databases and if some table is corrupted it will automatically fix that table:

 

$ mysqlcheck --auto-repair -o --all-databases

6. Most used mysqlcheck arguments

-c, --check Check table for errors.
-a, --analyze Analyze given tables.
-o --optimize Optimize the tables.
-r, --repair Perform a repair that can fix almost anything except unique keys that are not unique.
--auto-repair If a checked table is corrupted, automatically fix it. Repairing will be done after all tables have been checked.
-A, --all-databases Check all the databases. This is the same as –databases with all databases selected.
-B, --databases Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names.
--tables Overrides the –databases or -B option such that all name arguments following the option are regarded as table names.
-g, --check-upgrade Check tables for version-dependent changes. May be used with –auto-repair to correct tables requiring version-dependent updates.

mysqlcheck optimize

Of course, if you use one of our  Optimized MySQL Hosting services, you can always contact and ask our expert Linux admins (via chat or ticket) about repairing MySQL database and anything related to MySQL optimization and how to repair a corrupted database. They are available 24×7 and will provide information or assistance immediately.

PS. If you liked this post on how to use mysqlcheck to optimize and repair all tables and databases in MySQL, please share it with your friends on the social networks using the buttons below or simply leave a reply. Thanks.

3 thoughts on “MySQLcheck Optimizing and Repairing All Databases”

  1. You should just make a copy of mysqlcheck named mysqlrepair, or make a symbolic link to mysqlcheck named mysqlrepair. If you invoke mysqlrepair, it repairs tables.

    Reply

Leave a Comment