Database Reference
In-Depth Information
We can also tell the program to check all of our databases with the
--all-databases
option, as follows:
mysqlcheck -u root -p --all-databases
By default,
mysqlcheck
will only check tables 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.
Full documentation of the
mysqlcheck
utility is found at
https://mariadb.com/
kb/en/mysqlcheck/
.
Repairing tables
Thankfully, MariaDB is a very mature and stable program. 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. Unfortunately,
mysqlcheck
cannot repair
InnoDB tables.
Thankfully, InnoDB and XtraDB are crash safe, which means 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 with
it 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 at
https://mariadb.com/kb/en/
xtradbinnodb-recovery-modes/
.
