mysqlcheck - mysql repair tables

With a recent OS upgrade, some of the mysql database tables got corrupted. Below is how I was able to get it repaired.

1. Stop mysql server.
2. Once mysql server is stopped, run a repair on all of *.MYI files via myisamchk:
   
   # myisamchk -r /var/lib/mysql/*/*.MYI
3. Bring up the mysql server.
4. Run a mysqlcheck of all databases via:
   
   # mysqlcheck -c --all-databases | tee /tmp/dbcheck.log
5. Grep for "error" on the log and proceed to create a sql file to be run to repair the tables.
   
   # grep error -B1 /tmp/dbcheck.log | grep -v "error\|--" | sed 's/\(.*\)/REPAIR TABLE \1;/' >/tmp/dbrepair.sql
6. The file output should be something like:
   
   REPAIR TABLE database1.table1;
REPAIR TABLE database1.table2;
REPAIR TABLE database2.table1;
7. Log into mysql and source the repair script:
   
   # mysql> source /tmp/dbrepair.sql
8. That should run and repair all of the corrupted tables. Verify by running another check and maybe an extended one.
   
   # mysqlcheck -c -e --all-databases

How To Check And Repair All MySQL Databases on Debian And Ubuntu Linux

The Following command can be used to check and repair all MySQL databases on a Ubuntu or Debian Linux System.

Ubuntu Linux

sudo mysqlcheck --defaults-file=/etc/mysql/debian.cnf --auto-repair --optimize --all-databases

Debian Linux

mysqlcheck --defaults-file=/etc/mysql/debian.cnf --auto-repair --optimize --all-databases

The benefit of the above command is that it uses the debian-sys-maint login to MySQL which is available on every Debian and Ubuntu System, so you don't have to provide the MySQL root login details.

mysqlcheck utility commands in MySQL

mysqlcheck

mysqlcheck --help
mysqlcheck --version

Usage: mysqlcheck [OPTIONS] database [tables]
OR     mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqlcheck [OPTIONS] --all-databases

mysqlcheck --login-path=dba -c testdb
mysqlcheck --login-path=root -r testdb emp
mysqlcheck --login-path=dba -o devdb dept

mysqlcheck --login-path=mydba --check --all-databases --medium-check --auto-repair --compress --force --medium-check --skip-write-binlog --debug-info
mysqlcheck --login-path=local --analyze --databases mydb --compress --skip-write-binlog --debug-info

mysqlcheck --login-path=dba --check --databases devdb
mysqlcheck --login-path=dba --analyze --databases proddb
mysqlcheck --login-path=dba --repair --databases testdb
mysqlcheck --host=linux001 --port=3308 --user=root --password -c proddb

This program can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) tables. Some of the options (like -e or -q) can be used at the same time. Not all options are supported by all storage engines.

Please check MySQL manual for latest information about the above. 
The options -c, -r, -a, and -o are exclusive to each other, which means that the last option will be used, if several was specified.

The option -c will be used by default, if none was specified.
You can change the default behavior by making a symbolic link, or copying this file somewhere with another name, the alternatives are:
mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

Default options of mysql check, are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlcheck client

The following mysqlcheck options may be given as the first argument:
--print-defaults         Print the program argument list and exit.
--no-defaults            Don't read default options from any option file,  except for login file.
--defaults-file=#        Only read default options from the given file #.
--defaults-extra-file=#  Read this file after the global files are read.
--defaults-group-suffix=#  Also read groups with concat(group, suffix)
--login-path=#           Read this path from the login file.

  -A, --all-databases  Check all the databases. This is the same as --databases with all databases selected.
  -a, --analyze        Analyze given tables.
  -1, --all-in-1       Instead of issuing one query for each table, use one query per database, naming all tables in the database in a comma-separated list.
  --auto-repair        If a checked table is corrupted, automatically fix it.  Repairing will be done after all tables have been checked, if corrupted ones were found.
  --bind-address=name  IP address to bind to.
  --character-sets-dir=name  Directory for character set files.
  -c, --check          Check table for errors.
  -C, --check-only-changed      Check only tables that have changed since last check or  haven't been closed properly.
  -g, --check-upgrade  Check tables for version-dependent changes. May be used  with --auto-repair to correct tables requiring version-dependent updates.
  --compress           Use compression in server/client protocol.
  -B, --databases      Check several databases. Note the difference in usage; in this case no tables are given. All name arguments are  regarded as database names.

  -#, --debug[=#]      This is a non-debug version. Catch this and exit.
  --debug-check        This is a non-debug version. Catch this and exit.
  --debug-info         This is a non-debug version. Catch this and exit.
  --default-character-set=name      Set the default character set.
  --default-auth=name  Default authentication client-side plugin to use.
  --enable-cleartext-plugin     Enable/disable the clear text authentication plugin.
  -F, --fast           Check only tables that haven't been closed properly.
  --fix-db-names       Fix database names.
  --fix-table-names    Fix table names.
  -f, --force          Continue even if we get an SQL error.
  -e, --extended       If you are using this option with CHECK TABLE, it will ensure that the table is 100 percent consistent, but will take a long time. If you are using this option with REPAIR TABLE, it will force using old slow repair with keycache method, instead of much faster repair by  sorting.

  -?, --help           Display this help message and exit.
  -h, --host=name      Connect to host.
  -m, --medium-check   Faster than extended-check, but only finds 99.99 percent of all errors. Should be good enough for most cases.
  --write-binlog       Log ANALYZE, OPTIMIZE and REPAIR TABLE commands. Use --skip-write-binlog when commands should not be sent to replication slaves. (Defaults to on; use --skip-write-binlog to disable.)
  --secure-auth        Refuse client connecting to server if it uses old (pre-4.1.1) protocol. Deprecated. Always TRUE
  -o, --optimize       Optimize table.

  -p, --password[=name]  Password to use when connecting to server. If password is not given, it's solicited on the tty.
  --plugin-dir=name    Directory for client-side plugins.
  -P, --port=#         Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
  --protocol=name      The protocol to use for connection (tcp, socket, pipe, memory).
  -q, --quick            If you are using this option with CHECK TABLE, it prevents the check from scanning the rows to check for  wrong links. This is the fastest check. If you are using this option with REPAIR TABLE, it will try to repair only the index tree. This is the fastest repair method for a table.
  -r, --repair         Can fix almost anything except unique keys that aren't unique.
  -s, --silent         Print only error messages.
  --skip-database=name  Don't process the database specified as argument
  -S, --socket=name    The socket file to use for connection.

  --ssl-mode=name      SSL connection mode.
  --ssl                Deprecated. Use --ssl-mode instead.  (Defaults to on; use --skip-ssl to disable.)
  --ssl-verify-server-cert   Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
  --ssl-ca=name        CA file in PEM format.
  --ssl-capath=name    CA directory.
  --ssl-cert=name      X509 cert in PEM format.
  --ssl-cipher=name    SSL cipher to use.
  --ssl-key=name       X509 key in PEM format.
  --ssl-crl=name       Certificate revocation list.
  --ssl-crlpath=name   Certificate revocation list path.

  --tls-version=name   TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2
  --tables               Overrides option --databases (-B).
  --use-frm            When used with REPAIR, get table structure from .frm file, so the table can be repaired even if .MYI header is corrupted.
  -u, --user=name      User for login if not current user.
  -v, --verbose        Print info about the various stages.
  -V, --version        Output version information and exit.

mysqlcheck variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-in-1                              FALSE
auto-repair                        FALSE
bind-address                      (No default value)
character-sets-dir               (No default value)
compress                           FALSE
databases                          FALSE
default-character-set          utf8
default-auth                       (No default value)
enable-cleartext-plugin       FALSE
fast                                   FALSE
fix-db-names                      FALSE
fix-table-names                  FALSE
force                                 FALSE
extended                           FALSE
host                                  (No default value)
write-binlog                       TRUE
secure-auth                       TRUE
plugin-dir                          (No default value)
port                                  3306
quick                                FALSE
silent                               FALSE
skip-database
socket                              /data/mysql/mysql.sock
ssl                                   TRUE
ssl-verify-server-cert         FALSE
ssl-ca                              (No default value)
ssl-capath                        (No default value)
ssl-cert                            (No default value)
ssl-cipher                         (No default value)
ssl-key                            (No default value)
ssl-crl                              (No default value)
ssl-crlpath                        (No default value)
tls-version                        (No default value)
use-frm                            FALSE
user                                (No default value)

Checking, analyzing,optimizing and Repairing MySQL Tables through MySQLcheck

When a MySQL table got corrupted, We can use mysqlcheck command to repair it. Not only repair, mysqlcheck provides a excellent way to checks, repairs, optimizes and analyzes the tables. 

Lets see How it works (Ofcourse, Some examples included) 

1. Check a Specific Table in a Database

While being a system admin who maintains one of leading web hosting servers, I often came across Table crashes mostly with social engine, Joomla etc.. When a table says or feels like some tables are corrupted, I do run a mysqlcheck command to check that ones.

The following example checks “xyz” table in teksupport database.

# mysqlcheck -c teksupport xyz -u root –p    
Enter password:
teksupport.xyz    OK

You should pass the username/password to the mysqlcheck command. If not, you’ll get the following error message.

# mysqlcheck -c teksupport abc
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

Using mysqlcheck command, you can check and repair corrupted table while the database is still running.

2. Check All Tables in a Database

To check all the tables in a particular database, don’t specify the table name. Just specify the database name.

The following example checks all the tables in the alfresco database.

# mysqlcheck -c teksupport -u root -p
Enter password:
teksupport.ABC_TS2                            OK
teksupport.ABC_TS1                            OK
..

3. Check All Tables and All Databases

To check all the tables and all the databases use the “–all-databases” along with -c option as shown below.

# mysqlcheck -c  -u root -p --all-databases
Enter password:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    OK 
..
..
mysql.help_category
error    : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it!
mysql.help_keyword
error    : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it!
..

If you want to check all tables of few databases, specify the database names using “–databases”.

The following example checks all the tables in teksupport and techie database.

# mysqlcheck -c  -u root -p --databases teksupport techie
Enter password:
teksupport.employee                              OK
techie.ABC_XYZ1                                    OK 
techie.ABC_XYZ2                                    OK 
techie.ABC_XYZ3                                    OK 
..

4. Analyze Tables using Mysqlcheck

The following analyzes employee table that is located in teksupport database.

# mysqlcheck -a teksupport XYZ1 -u root -p
Enter password:
teksupport.employee   Table is already up to date

Internally mysqlcheck command uses “ANALYZE TABLE” command. When mysqlcheck is executing, the analyze command the table will be locked and available for other process only in the read mode.i.e. No changes can be made on the data.

5. Optimize Tables using Mysqlcheck

The following optimizes employee table that is located in teksupport database.

# mysqlcheck -o teksupport XYZ1 -u root -p
Enter password:
teksupport.XYZ1               OK

Internally mysqlcheck command uses “OPTIMIZE TABLE” command. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates.

6. Repair Tables using Mysqlcheck

The following repairs employee table that is located in teksupport database.

# mysqlcheck -r teksupport EMP1 -u root -p
Enter password:
teksupport.XYZ1          OK

Internally mysqlcheck command uses “REPAIR TABLE” command. This will repair and fix a corrupted MyISAM and archive tables.

7. Combine Check, Optimize, and Repair Tables

Instead of checking and repairing separately. You can combine check, optimize and repair functionality together using “–auto-repair” as shown below.

The following checks, optimizes and repairs all the corrupted table in teksupport database.

# mysqlcheck -u root -p --auto-repair -c -o teksupport

You an also check, optimize and repair all the tables across all your databases using the following command.

# mysqlcheck -u root -p --auto-repair -c -o --all-databases

If you want to know what the command is doing while it is checking, add the –debug-info as shown below. This is helpful while you are checking a huge table.

# mysqlcheck --debug-info -u root -p --auto-repair -c -o teksupport EMP1
Enter password:
teksupport.EMP1   Table is already up to date

User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 456, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12, Involuntary context switches 9

8. Additional Useful Mysqlcheck Options

The following are some of the key options that you can use along with mysqlcheck.

• -A, –all-databases Consider all the databases
• -a, –analyze Analyze tables
• -1, –all-in-1 Use one query per database with tables listed in a comma separated way
• –auto-repair Repair the table automatically it if is corrupted
• -c, –check Check table errors
• -C, –check-only-changed Check tables that are changed since last check
• -g, –check-upgrade Check for version dependent changes in the tables
• -B, –databases Check more than one databases
• -F, –fast Check tables that are not closed properly
• –fix-db-names Fix DB names
• –fix-table-names Fix table names
• -f, –force Continue even when there is an error
• -e, –extended Perform extended check on a table. This will take a long time to execute.
• -m, –medium-check Faster than extended check option, but does most checks
• -o, –optimize Optimize tables
• -q, –quick Faster than medium check option
• -r, –repair Fix the table corruption

Mysqlcheck: Table maintenance and repair procedures

The Mysqlcheck client can examine and repair the MyISAM table. It can also optimize and analyze tables.

Mysqlcheck function is similar to MYISAMCHK, but its work is different. The main difference is that when the MYSQLD server has to use Mysqlcheck at run time, Myisamchk applies to the server when it is not running. The advantage of using Mysqlcheck is that you do not need to stop the server to check or fix the table.

Mysqlcheck provides a convenient way for users to use SQL statements check table, REPAIR table, ANALYZE table, and optimize table. It determines which statement is used in the operation to be performed, and then sends the statement to the server to be executed.

There are 3 different ways to invoke Mysqlcheck:

shell> mysqlcheck[options] db_name [tables]

Shell> Mysqlcheck[options]---database DB1 [DB2 DB3 ...]

Shell> Mysqlcheck[options]--all--database

If you do not specify any tables or use the---database or--all--database option, check the entire database.

Compared with other clients, Mysqlcheck has a special feature. Renaming the binary allows you to change the default behavior of the checklist (--check). If you want a tool that can fix a table by default, simply copy Mysqlcheck to Mysqlrepair or use a symbolic link mysqlrepair link mysqlcheck. If you call Mysqlrepair, you can repair the table by command.

The following name can be used to change the default behavior of Mysqlcheck:

Mysqlrepair	The default option is--repair
Mysqlanalyze	The default option is--analyze
Mysqloptimize	The default option is--optimize

Mysqlcheck

The following options are supported:

· ---help,-?

Displays a help message and exits.

· --all--database,-a

Check all tables in all databases. As with the---database option, all databases are named on the command line.

· --all-in-1,-1

Instead of issuing a statement for each table, you execute a statement for each database in the named database for all the tables that are being processed.

· --analyze,-a

Analysis table.

· --auto-repair

If a checked table is corrupted, fix it automatically. All required repairs are done automatically after checking all tables.

· --character-sets-dir=path

The installation directory for the character set. See section 5.10.1, "Data and sorting with character sets."

· --check,-c

Check the table for errors.

· --check-only-changed,-c

Checks only those tables that have changed since the last check or have not been properly closed.

· --compress

Compresses all information sent between the client and the server, if both support compression.

· ---database,-b

Handles all the tables named in the database. With this option, all Word name parameters are treated as database names, not table names.

· ---debug[=debug_options],-# [debug_options]

Write debug Log. The debug_options string is usually ' d:t:o,file_name '.

· --default-character-set=charset

Use the Charsetas default character set. See section 5.10.1, "Data and sorting with character sets."

· --extended,-e

If you are using this option to check the table, make sure that they are 100% consistent, but will take a long time.

If you are using this option to fix the table, run the extended fix, not only for a very long time, but also for a lot of garbage rows!

· --fast,-f

Only check for tables that are not properly closed.

· --force,-f

Continue even if a SQL error occurs.

· --host=host_name,-h host_name

Connect to a MySQL server on a given host.

· --medium-check,-m

Perform a faster check than the--extended operation. Only 99.99% of the errors can be found, and in most cases this is enough.

· --optimize,-o

Tuning tables.

· --password[=password],-p[password]

The password to use when connecting to the server. If you use the Short option form (-p), there is no space between the option and the password. If you do not have a password value after the--password or P option on the command line, you are prompted to enter a password.

· --port=port_num,-p Port_num

The TCP/IP port number to use for the connection.

· --protocol={tcp | SOCKET | PIPE | MEMORY}

The connection agreement used.

· --quick,-q

If you are using this option in the checklist, it prevents scan rows to check for error link checks. This is the quickest way to check.

If you are using this option in the repair table, it tries to repair only the index tree. This is the quickest way to fix it.

· --repair,-r

Perform fixes that can fix most problems, except that the unique values are not fixed for a while.

· --silent,-s

Silent mode. Only error messages are printed.

· --socket=path,-s Path

The socket file to use for the connection.

· --tables

Overwrite---database or-b option. All parameters after the option are treated as table names.

· --user=user_name,-u user_name

The MySQL user name to use when connecting to the server.

· --verbose,-v

Verbose mode. Print information about program actions for each stage.

· --version,-v

Displays version information and exits.