Mysql database optimization and restoration tool mysqlcheck details

1. Introduction to mysqlcheck

The mysqlcheck client can check and repair the MyISAM table. It can also be optimized and analyzed.

The function of mysqlcheck is similar to that of myisamchk, but it works differently. The main difference is that mysqlcheck must be used when the mysqld server is running, while myisamchk is used when the server is not running. The advantage of using mysqlcheck is that you do not need to stop the server to check or repair tables. Failure to fix myisamchk is irreversible.

Mysqlcheck provides a convenient way to use SQL statements such as CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE. It determines which statement is used in the operation to be executed, and then sends the statement to the server to be executed.

Compared with other clients, mysqlcheck has a special feature. Renaming the binary can change the default behavior of the checklist (-- check ). If you want a tool that can repair tables by default, you only need to copy mysqlcheck to mysqlrepair again, or use a symbol to link mysqlrepair to mysqlcheck. If you call mysqlrepair, follow the command to repair the table.

The following name can be used to change the default behavior of mysqlcheck:
Mysqlrepair
The default value is -- repair.

Mysqlanalyze
The default option is -- analyze.

Mysqloptimize
The default option is -- optimize.

Ii. Use of mysqlcheck

1. There are three methods to call mysqlcheck:
Copy codeThe Code is as follows:
Shell> mysqlcheck [options] db_name [tables]
Shell> mysqlcheck [options] --- database DB1 [DB2 DB3. ..]
Shell> mysqlcheck [options] -- all -- database

If no table is specified or the --- database or -- all -- database option is used, check the entire database.

2. Fix all databases

Copy codeThe Code is as follows: mysqlcheck-a-c-o-r-m -- all-databases-uroot-p

Use the above command to optimize all databases

Parameter meaning:
Copy codeThe Code is as follows:-a = Analyse given tables.
-C = Check table for errors
-O = Optimise table
-R = Can fix almost anything except t unique keys that aren't unique
-M = -- medium-check

Note: When the MYSQL version is 5.5, delete-o in the command.

3. Fixed the specified database.

Copy codeThe Code is as follows: mysqlcheck-o-r-uroot-p888888 dataname
Mysqlcheck-hlocalhost-auto-repair-uroot-p ****** dataname

4. Here is a brief description of the usage of mysqlcheck.
Usage:
Copy codeThe Code is as follows: mysqlcheck [OPTIONS] database [tables]
Mysqlcheck [OPTIONS]-databases DB1 [DB2 DB3. ..]
Mysqlcheck [OPTIONS]-all-databases
Common options:
-A: All databases
-A: analyze the specified table
-C: Check the database or table
-R: repository or table repair
-O optimized the specified table
-H mysql service host
-P Port
-U User Name
-P Password
-- Auto-repair: repair damaged tables

Three rows using mysqlcheck.exe

Download the mysql installation package of your corresponding version number through mysql.com (no installation is required for noinstall ). After the mysql database is installed, the command is installed under the path mysql \ mysql server 5.0 \ bin.

Iv. Detailed description of mysqlcheck parameters:

--- Help ,-?

Displays the help message and exits.

-- All -- database,-

Check all tables in all databases. Like using the --- database option, name all databases in the command line.

-- All-in-1,-1

Instead of issuing a statement for each table, a statement is executed for each database of all tables to be processed in the named database.

-- Analyze,-

Analysis table.

-- Auto-repair

If a table is damaged, it is automatically repaired. After all the tables are checked, all the necessary repairs are automatically performed.

-- Character-sets-dir = path

Character Set installation directory. See section 5.10.1 "Character Set for data and sorting ".

-- Check,-c

Check table errors.

-- Check-only-changed,-C

Only the tables that have been changed since the last check or are not properly closed are checked.

-- Compress

Compress all information sent between the client and the server (if both support compression ).

--- Database,-B

Process all 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 debugging logs. 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 "Character Set for data and sorting ".

-- Extended,-e

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

If you are using this option to repair the table, running the extension fix will not only take a long time, but also generate a large number of junk rows!

-- Fast,-F

Only the tables that are not properly closed are checked.

-- Force,-f

Continue even if an SQL error occurs.

-- Host = host_name,-h host_name

Connect the MySQL server on the specified host.

-- Medium-check,-m

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

-- Optimize,-o

Optimize the table.

-- Password [= password],-p [password]

The password used to connect to the server. If you use the short option format (-p), there must be no space between the option and password. If there is no password value after the -- password or-p option in the command line, a prompt is displayed for entering a password.

-- Port = port_num,-P port_num

The TCP/IP Port used for connection.

-- Protocol = {TCP | SOCKET | PIPE | MEMORY}

The connection protocol used.

-- Quick,-q

If you are using this option in the checklist, it prevents scanning rows to check the error link. This is the fastest way to check.

If you are using this option to repair a table, it tries to repair only the index tree. This is the fastest solution.

-- Repair,-r

The execution can fix most of the problems, but it cannot be repaired if the unique value is not unique.

-- Silent,-s

Silence mode. Print only error messages.

-- Socket = path,-S path

The socket file used for connection.

-- Tables

Overwrite the --- database or-B option. All parameters following the option are treated as table names.

-- User = user_name,-u user_name

The MySQL user name used to connect to the server.

-- Verbose,-v

Lengthy mode. Print Information about program operations at various stages.

-- Version,-V

Display version information and exit.