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:

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)

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:

--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

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:

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;

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 127.0.0.1 -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/reset_credit_nika.sh > /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:

#!/bin/bash
mysql -u USER -pPASSWORD -h 192.168.1.1 -s -N -e "SELECT id FROM nika_system.abon WHERE tarif=109;";

In /etc/crontab we add:

0 8 * * * root /scripts/freektb.sh > /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

IPTables rules for MySQL

If iptables locks all incoming connections (INPUT DROP) and to add external access to MySQL, you need to add rules:

iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

To access only a particular network, for example 10.0.0.0/24:

iptables -A INPUT -s 10.0.0.0/24 -p tcp -m tcp --dport 3306 -j ACCEPT

To remove a rule, we’ll specify the same command, replacing -A with -D, for example:

iptables -D INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

To view the list of rules, use the command:

sudo iptables -nvL

I note that in order to open external access, you also need to comment out the line “bind-address = 127.0.0.1” in the my.cnf configuration file.

If by default INPUT ACCEPT, we first specify which IPs are allowed access, and only the last rule is blocked by all the others:

/sbin/iptables -A INPUT -s 127.0.0.1 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -s 192.168.1.5 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP

For example, using nmap, you can check locally and externally whether the access is filtered:

nmap -p 3306 localhost
nmap -p 3306 192.168.1.5

See also:
Configuring IPTables
Other my articles about MySQL

Solution of the error “Using unique option prefix pass instead of password is deprecated …”

I noticed some errors coming to the root mail with the subject and the text:

Cron /usr/bin/test -x /usr/local/cpanel/scripts/update_db_cache && /usr/local/cpanel/scripts/update_db_cache
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.

The problem lies in the file ~ / .my.cnf, that is /root/.my.cnf in my case.

In which the parameter “pass” is obsolete and must be changed to a new “password”.

For example, that’s how it was when the error occurred:

[client]
user=root
pass=password

Changed to password and the error disappeared:

[client]
user=root
password=password

See also:
Connecting to MySQL from localhost without entering a password

Connecting to MySQL from localhost without entering a password

Suppose you need to make the user root can connect to MySQL from localhost without entering a password.

To do this, create the MySQL configuration file in the home directory of the user you are connecting from (in the nano text editor, press CTRL + X to exit, y / n to save or cancel changes):

sudo nano ~/.my.cnf

For security purposes, we will configure access to the .my.cnf file only for the root user of the system.

sudo chown root:root ~/.my.cnf
sudo chmod 400 ~/.my.cnf

And add the client section, specifying the user’s login and password in it, as well as several other parameters:

[client]
host=localhost
user=root
password=PASSWORD
socket=/var/run/mysqld/mysqld.sock

Let’s try to connect, immediately the MySQL console should appear (without requesting a password):

mysql

Change max_connections in MySQL

Normally, the parameter max_connections in MySQL is set to 151 and allowed for one connection more, that is, there may be 152 connections.
If there are more connections, there will be a “too many connections” error.
On an example I use MySQL 5.5.54.

To increase the value, we connect to MySQL:

mysql -u root -p

Let’s see a list of current connections:

show processlist;
SHOW FULL PROCESSLIST\G
show status where `variable_name` = 'Threads_connected';

Let’s see the current value of max_connections:

show variables like "max_connections";

If you need to increase it without restarting MySQL (it will work before restarting MySQL):

set global max_connections = 300;

So that after the restart of MySQL the value of max_connections is preserved, in the configuration file /etc/mysql/my.cnf, in the [mysqld] section we specify it:

max_connections = 300