Category Archives: Databases

In order to see the process of importing SQL files, I use the Pipe Viewer utility.
Pipe Viewer is very useful especially when the SQL file is large, allows you to roughly estimate the remaining time before the import of the file is completed, displays the import process in percent and the time passed.

Continue reading “How to see the process of importing a SQL file?”

I recently imported a dump into MySQL and found the following error:

ERROR 1062 (23000) at line 14096: Duplicate entry '????????' for key 'name'

Continue reading “How to solve “ERROR 1062 (23000) at line X: Duplicate entry ?????? for key X””

Once I decided to import sql dump:

mysql -u root database < database.sql.gz

And I noticed the following error:

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

The error occurs because the dump is compressed and must first be unpacked:

gunzip database.sql.gz

And then import:

mysql -u root database < database.sql

See my other articles in the category MySQL

I once ran a SQL query:

GRANT REPLICATION SLAVE ON TESTDATABASE.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

And I found the following error:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Since the REPLICATION SLAVE privileges are global and can not be assigned to a particular database, they must be specified globally in the query, so the SQL query should look like this:

GRANT REPLICATION SLAVE ON *.* TO "replication"@"192.168.1.9" IDENTIFIED BY "password";

After that, the query succeeded:

Query OK, 0 rows affected, 1 warning (0,01 sec)

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;