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.

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

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

View information about MySQL databases

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:


You can select a database like this:

USE dbname;

See which database is currently selected:


To see which tables the selected database contains:


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:


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


Executing a SQL query from a script in Linux

It was necessary recently to write a script that executes a sql query into the MySQL database.
Example content:

# Description, here I wrote for others that the script is added to the crontab, so that it is not moved
mysql -u USER -pPASSWORD -h -e "UPDATE nika_system.abon SET otkl=0 WHERE depozit > '10' AND (otkl='-1' OR otkl='-2');";

In order for the script to run automatically, open the crontab file in any text editor (in the nano editor CTRL+X for the output and y/n for saving or canceling the changes):

sudo nano /etc/crontab

Add the following line to it:

0 9 * * * root /home/nika/scripts/ > /dev/null 2>&1

Now every day at 9 am the script will be executed.

Here is an example of daily automatic output of data from a sql table into a text file:

mysql -u USER -pPASSWORD -h -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/ > /srv/samba/dir/mirazh/$(date +%Y-%m-%d).txt

For security reasons, it’s better not to specify the password in scripts, see my article – Connecting to MySQL from localhost without entering a password