Maintaining MySQL Databases

As a MySQL administrator, you’ll probably end up doing some preventive and corrective database maintenance.  You can use mysqlcheck for both.

First, consider the “virtual” database.  It has  one table called ‘accounts”.  You can check the whole database with the following command:
mysqlcheck -p -u root virtual
Enter password:
virtual.accounts                              OK

Now, what if we just want to check one table of the database?

mysqlcheck -p -u root virtual trivia
Enter password:
virtual.accounts                              OK

As you can see, all you have to do to check just one table is to specify the table name after you specify the database name.  Now, what if you have a database with more than two tables, and you want to check more than one, but not all of the tables?  That’s easy.  Just specify all of the tables that you want to check after you specify the database.

mysqlcheck -p -u root mysql db host proc
Enter password:
mysql.db                                           OK
mysql.host                                         OK
mysql.proc                                         OK

You can also check more than one database at a time.  Let’s say that you want to check the “payroll” and the “contact” databases.

mysqlcheck -p -u root –databases payroll contact
Enter password:
payroll.last_name                                   OK
payroll.first_name                                  OK
payroll.SSN                                         OK
payroll.pay_rate                                    OK
contact.last_name                                   OK
contact.first_name                                  OK
contact.phone_number                                OK

This time, by adding the “–databases” switch, all names that you enter on the command-line will be treated as database names.

It’s also a simple matter to check all databases at once, just by using the “–all-databases” switch:

mysqlcheck -p -u root –all-databases
Enter password:
contacts.names                                     OK
contacts.phone_numbers                             OK
contacts.trivia                                    OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

You can also use mysqlcheck to perform corrective maintenance.  There’s only one catch, though.  MySQL databases can use two different types of tables–either MyISAM tables or InnoDB tables.  While mysqlcheck can perform checks on either type of table, it can only repair MyISAM tables.

mysqlcheck -p -u root –repair virtual
Enter password:
virtual.accounts                              OK

You can do more extensive repairs by adding another switch:

mysqlcheck -p -u root –repair –extended virtual
Enter password:
virtual.accounts                              OK

Or, you can also do a quick repair:

mysqlcheck -p -u root –repair –quick virtual
Enter password:
virtual.accounts                              OK