Check and Maintain MariaDB Tables with mysqlcheck

This guide takes about 10 minutes for a single table, plus the time needed to process the data. You will check a table without changing it, then choose a deliberately scoped analyse, optimise or repair operation. The commands use the installed MariaDB 10.11.14 client and its mysqlcheck name. The same client is also installed under names such as mariadb-check and mariadbcheck.

Before you start

You need a running MariaDB server, a database account permitted to run the relevant table statement, and a table name you have checked twice. The client sends SQL to the server, so it does not require a local database directory or a service restart. It is an ordinary user command, but database privileges still apply. Use sudo only if your local authentication setup specifically requires it.

Every processed table is locked. A check uses a read lock, while maintenance can make a table unavailable to other sessions. A large database-wide run can therefore take a long time and affect an active service. Schedule broad work for a quiet period and start with one known table.

1. Confirm the client and its defaults

Check the executable before connecting. This is read-only and also records the version that matters when you compare behaviour with another host.

$ command -v mysqlcheck
$ mysqlcheck --no-defaults --version
mysqlcheck  Ver 2.7.4-MariaDB Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)

MariaDB clients read option files by default. Those files can add a host, user, socket or other option that you did not type. --print-defaults shows the arguments assembled from those files without starting the client:

$ mysqlcheck --print-defaults
mysqlcheck would have been started with the following arguments:

Use --no-defaults as the first argument when you need a clean diagnostic. If it changes the connection behaviour, inspect the option files and the account settings rather than blindly adding more flags.

Checkpoint: target one table

Write down the exact database and table before continuing. In the ordinary form, the first name is the database and later names are tables. Omitting the table names checks the whole database, so this command is intentionally narrow:

$ mysqlcheck --no-defaults --user=DB_USER --password DB_NAME TABLE_NAME
Enter password:

Leaving the value off --password prompts for it. Do not put a real password in the command line: it can be exposed through process inspection or shell history. Prefer an appropriately protected client option file for repeatable jobs. The command above will usually print a line for the selected table followed by an OK result when the check succeeds.

2. Run an explicit check

The default operation is --check, but spelling it out makes a script and a copied command easier to audit. The short form is -c. Add --verbose when you need to see the stages of the operation:

$ mysqlcheck --no-defaults --user=DB_USER --password --check --verbose DB_NAME TABLE_NAME
Enter password:

A successful result normally names the table and reports OK. An error or a storage-engine note is not a prompt to try repair immediately. Check the table engine, server error log and available backup first. Engines do not all support the same maintenance operations. For example, InnoDB tables can be checked, but this client cannot repair them with REPAIR TABLE.

3. Reduce the scope or check speed

For a database with many tables, --check-only-changed checks tables changed since the last check or not closed properly. --fast checks only tables that were not closed properly. --quick is the fastest check mode because it avoids scanning rows for incorrect links; use it as a quick signal, not as a complete consistency check. --extended is the opposite trade-off: it aims for a 100 percent check but can take much longer.

$ mysqlcheck --no-defaults --user=DB_USER --password --check-only-changed DB_NAME
Enter password:

Do not combine the operation selectors. --check, --repair, --analyze and --optimize are exclusive, and the installed help states that the last one wins if several are supplied. Keeping one selector per command avoids a subtle copy-and-paste error.

4. Analyse or optimise after checking

--analyze sends ANALYZE TABLE, while --optimize sends OPTIMIZE TABLE. These are maintenance operations, not generic fixes for a slow query. Run them against a named table first, watch the lock and runtime, and confirm the result before widening the scope:

$ mysqlcheck --no-defaults --user=DB_USER --password --analyze DB_NAME TABLE_NAME
Enter password:
$ mysqlcheck --no-defaults --user=DB_USER --password --optimize DB_NAME TABLE_NAME
Enter password:

For a database-wide operation, use --databases and name databases explicitly. Without it, extra names are interpreted as tables in the first database:

$ mysqlcheck --no-defaults --user=DB_USER --password --analyze --databases DB_NAME
Enter password:

--all-databases is broader still. Treat it as a planned maintenance window, not a harmless diagnostic.

5. Treat repair as a recovery operation

--repair runs REPAIR TABLE, primarily for table engines that support it, such as MyISAM. Repair can cause data loss in some circumstances, so take and verify a backup before running it. Do not use it as a routine response to a failed InnoDB check.

$ mysqlcheck --no-defaults --user=DB_USER --password --repair DB_NAME TABLE_NAME
Enter password:

There is no general undo command for a repair. Recovery means restoring the affected table or database from a known-good backup, then checking it again. The --auto-repair option is more dangerous than it looks: it checks tables first and repairs corrupted ones afterwards. Keep it out of exploratory commands unless you have a tested recovery plan.

6. Understand aliases, option files and replication

The aliases are not separate maintenance programs. A name can select a default operation: mysqlrepair defaults to repair, mysqlanalyze to analyse and mysqloptimize to optimise. State the operation explicitly when a command may be run by someone else or from automation. The short names mariadb-check and mysqlcheck refer to the same installed client behaviour here.

Options can be placed in the [mysqlcheck] and [client] groups, among others. If a command behaves unexpectedly, compare --print-defaults with a clean --no-defaults run. Maintenance statements are written to the binary log by default. Use --skip-write-binlog only when you understand the replication and recovery consequences, because it prevents these statements being sent to replicas through the binary log.

Common failure traps

• Wrong target: a database name with no table name checks the whole database. Re-read the command before pressing Enter.
• Unsupported operation: storage engines differ. A check result that says the engine does not support an operation is a capability boundary, not proof of corruption.
• Unexpected connection: option files may select a socket or host. Use --print-defaults, then specify the connection deliberately.
• Long lock: large tables and --extended or database-wide work need time and can block other sessions.
• Credential exposure: use the prompt or a protected option file, never --password=REAL_PASSWORD.