IT Blog

I’ll give an example of a simple check of tables of all databases using mysqlcheck:

mysqlcheck -u root -p -A

Where the -u option specifies the user name under which to connect to MySQL, -p to request a password, -A to check the tables of all databases.

Example of restoring a specific database:

mysqlcheck -u root -p -r db_name

Example of restoring a specific table in the specified database:

mysqlcheck -u root -p -r db_name table_name

I’ll describe the possible startup options.

Checking all databases and their tables, except INFORMATION_SCHEMA and performace_schema:

--all-databases, -A

You can specify the list of databases using the option:

--databases, -B

Run all with one query instead of the individual for each database:

--all-in-1, -1

Analyze tables:

--analyze, -a

After checking, repair the damaged tables:

--auto-repair

Specify the address of the connection to the MySQL server (for example, if there are several):

--bind-address=ip_address

Connecting to the MySQL server on the specified node:

--host=host_name, -h host_name

Directory with character settings:

--character-sets-dir=dir_name

Checking the tables for errors (default):

--check, -c

Check the tables that have changed since the last check or that were not closed properly:

--check-only-changed, -C

Checking the tables for compatibility with the current version of the server:

--check-upgrade, -g

Data compression is transmitted between the client and the server if both support it:

--compress

Write and display debugging information (on the standard mysqlcheck test in Ubuntu 16.04 was compiled without debug support):

--debug=debug_options
--debug-check
--debug-info

Specifying the default character set:

--default-character-set=charset_name

Use only the specified parameter file:

--defaults-file=file_name

The following option makes the test longer and guarantees the integrity of the tables, it will also take a lot of time to recover and produce many unnecessary rows:

--extended, -e

Check only the tables that were not closed properly:

--fast, -F

Continue even in the case of SQL errors:

--force, -f

Faster than extended, there are 99.99% errors:

--medium-check, -m

Do not read the options files, however the .mylogin.cnf file will be read:

--no-defaults

See also my article – Connecting to MySQL from localhost without entering a password.

Optimize tables:

--optimize, -o

Specify the password when connecting:

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

TCP/IP port number for connection:

--port=port_num, -P port_num

Displaying the name of the program and all the parameters it receives from the parameter files:

--print-defaults

Specify the connection protocol:

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

A quick method for checking tables, prevents scanning of lines for checking invalid links:

--quick, -q

Performing repairs:

--repair, -r

Quiet mode, only errors are displayed:

--silent, -s

Skip specified database:

--skip-database=db_name

Specifying the socket when connecting:

--socket=path, -S path

Connection using SSL:

--ssl*

Override -databases or -B, all the name arguments following the option are treated as table names:

--tables

Specifying a list of protocols separated by commas, which are allowed to be used for encrypted connections:

--tls-version=protocol_list

When recovering, get the table structure from the .frm file:

--use-frm

The MySQL user name that will be used when connecting:

--user=user_name, -u user_name

Detailed mode, more information is displayed:

--verbose, -v

Display version of mysqlcheck and exit:

--version, -V

See also:
Installing and configuring a MySQL server on Ubuntu

To view information about MySQL databases and their tables, we first connect to the MySQL console, for example, as root:

mysql -u root

Or so, if you need to enter a password:

mysql -u root -p

Let’s see a list of existing databases:

SHOW DATABASES;

You can select a database like this:

USE dbname;

See which database is currently selected:

SELECT DATABASE();

To see which tables the selected database contains:

SHOW TABLES;

Example of viewing the contents of the specified table:

SELECT * FROM tablename LIMIT 25;

To view the structure of a particular table, execute the command:

DESCRIBE tablename;

Where Field is the name of the column, Type is the data type, Null – determines whether the column can contain NULL values, Key – whether the column is indexed, Default – determines the default value for the column.

To see information about the indexes of the table, you can do this:

SHOW INDEX FROM tablename;

View information about the table, the encoding, its type (ENGINES), etc. you can use:

SHOW CREATE TABLE tablename;

To see what types the server supports, you can do this:

SHOW ENGINES;