The mysqlcheck program can check, analyze, optimize, and repair the MariaDB database tables. Basic syntax for the command is as follows:

mysqlcheck [options] [-u username] [-p] database_name [table_name]

Here is an example of running the command to check our test database, and its output:

daniel@gandalf:~$ mysqlcheck -u root -p test
Enter password:
test.employees                                     OK

We can specify multiple databases using the --databases option as follows:

mysqlcheck -u root -p --databases db_name1 db_name2 db_name3

We can also tell the program to check all our databases with the --all-databases option, as follows:

mysqlcheck -u root -p --all-databases

By default, mysqlcheck will only perform basic checks when it is run. To get it to optimize, analyze, or repair tables, we use one of the following options:

--optimize
--analyze
--repair

Not all of the options work on all tables. For example, InnoDB tables cannot be repaired with mysqlcheck. The program displays an error message if it cannot perform a requested action.

Thankfully, MariaDB is a very mature and stable program, and problems are few and very far between. However, power does sometimes go out and hardware sometimes fails catastrophically or gradually, so there may come a time when a table in our database has problems and needs to be repaired.

MyISAM and Aria tables can often be repaired with the mysqlcheck program, so if mysqlcheck reports that a table needs repairing then we can usually simply re-run the program with the --repair option as described previously in this section. Unfortunately, mysqlcheck cannot repair InnoDB tables.

However, InnoDB and XtraDB are crash safe, which means that they are protected to a certain extent when failures do occur. This protection means that the chances of a hardware failure causing corruption are very low. InnoDB and XtraDB also have a built-in crash recovery mechanism. The way to use it is to add the innodb_force_recovery option to the [mysqld] section of our my.cnf or my.ini file set to a number between 1 and 6. Setting this variable to 0, or removing it entirely, disables it. While this option is set, MariaDB will not allow any InnoDB tables to be changed. The higher the number, the more aggressively MariaDB will try to repair the tables. Full documentation of this feature is available at the following location:

https://mariadb.com/kb/en/xtradbinnodb-recovery-modes/

If innodb_force_recovery does not work, we may need to dump and reload our affected tables. This procedure can take a long time on a large server, so it should only be used as a last resort. The basic procedure to dump and reload a database is the same as we went over in the mysqldump section previously, but here it is again:

mysqldump [options] database_name > dump.sql
mysql database_name < dump.sql

So to dump and reload our test database, we might do the following:

mysqldump -u root -p test > dump.sql
mysql -u root -p test < dump.sql

This reload process is more likely to succeed if it is used in conjunction with the innodb_force_recovery variable. For example, a setting of 1 tells InnoDB and XtraDB to skip the corrupt indexes and records instead of attempting to read them. Refer to the XtraDB/InnoDB recovery modes page in the MariaDB Knowledge Base found previously in this section for more information.

If the preceding reload process doesn't fix the error, we might want to call in some experts. There are various other recovery strategies out there, but they are beyond the scope of this book. We could also try reloading from a backup; we may lose some data depending on how old the backup is, but losing some data is better than losing everything.