The script for adding IP addresses from a file to ipset

It took one day to write a script to add to ipset all the IP for which the session was started on the access server, Abills billing was used, so I decided to take IP addresses from the MySQL billing table.

The first step is to create a test ipset:

ipset create test iphash

Continue reading “The script for adding IP addresses from a file to ipset”

Solution MySQL ERROR 1055 (42000)

Recently performed the necessary SQL queries and noticed the following error:

ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.table_name.column_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Continue reading “Solution MySQL ERROR 1055 (42000)”

How to see the process of importing a SQL file?

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.

You can install the Pipe Viewer in Ubuntu/Debian using the command:

sudo apt install pv

In Fedora:

yum install pv

Suppose earlier we imported a sql file and did not see the process:

mysql -u root DATABASE_NAME < dump.sql

Now, to see the process, we will perform the import using the Pipe Viewer:

pv dump.sql | mysql -u root DATABASE_NAME

Solution of error with binary-mode when importing MySQL dump from the command line

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

Solution of error “ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES”

I once ran a SQL query:


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"@"" IDENTIFIED BY "password";

After that, the query succeeded:

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

Restoring MySQL tables

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:


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


Connecting to the MySQL server on the specified node:

--host=host_name, -h host_name

Directory with character settings:


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:


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


Specifying the default character set:


Use only the specified parameter file:


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:


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:


Specify the connection protocol:


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:


Specifying the socket when connecting:

--socket=path, -S path

Connection using SSL:


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


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


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


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