Issue System Error 11 in mysqldump

[dosmasdos]

Server operating system version

AlmaLinux 8.9 (Midnight Oncilla)

Plesk version and microupdate number

Versión 18.0.57 Actualización 1

Hi all!

I have this error in all of my scheduled backups

<description>Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: mysqldump: Got error: 2013: "Lost connection to server at 'handshake: reading initial communication packet', system error: 11" when trying to connect

Best regards.

 

[Jan Bludau]

Increase max_allowed_packet Size: If your query has blob data, this issue can be fixed by increasing the max_allowed_packet size in your my.ini file. By default, this will be 1M (the allowed maximum value is 1024M). If the supplied value is not a multiple of 1024K, it will automatically be rounded to the nearest multiple of 1024K.

addjust my.cnf

max_allowed_packet = 500M

 

[dosmasdos]

OK, i will try but the error is the same in all databases and ocurrs from 3 days ago only...

 

[dosmasdos]

Dont work
Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: mysqldump: Got error: 2013: "Lost connection to server at 'handshake: reading initial communication packet', system error: 11" when trying to connect

 

[Jan Bludau]

Dont work
Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: mysqldump: Got error: 2013: "Lost connection to server at 'handshake: reading initial communication packet', system error: 11" when trying to connect

did you really stored this in the right section?

[mysqld]
max_allowed_packet = 2G

what is your commandline for mysqldump ? there are the option --skip-lock-tables
did you restart you mysql / mariadb Server?
and also --skip-extended-insert

 

[dosmasdos]

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
max_allowed_packet = 2G

# this is only for the mysqld standalone daemon
[mysqld]
max_allowed_packet = 2G

[mysqldump]
max_allowed_packet = 2G

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
max_allowed_packet = 2G

# This group is only read by MariaDB-10.6 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.6]
max_allowed_packet = 2G

~
~
~
~

 

[dosmasdos]

[root@srv7000 my.cnf.d]# service mysql restart || service mariadb restart || service mysqld restart
Redirecting to /bin/systemctl restart mysql.service

 

[dosmasdos]

did you really stored this in the right section?

[mysqld]
max_allowed_packet = 2G

what is your commandline for mysqldump ? there are the option --skip-lock-tables
did you restart you mysql / mariadb Server?
and also --skip-extended-insert

I dont use any special setting. Its the plesk configuration GUI

 

[dosmasdos]

The issue ocurrs after MU1 update

 

[Jan Bludau]

try to use mysqldump with --skip-lock-tables

mysqldump --skip-lock-tables --skip-extended-insert --max_allowed_packet=512M -u [username] -p[password] [database] > backup.sql

 

[dosmasdos]

try to use mysqldump with --skip-lock-tables

mysqldump --skip-lock-tables --skip-extended-insert --max_allowed_packet=512M -u [username] -p[password] [database] > backup.sql

But we need our users to use the PLESK GUI panel...

 

[Jan Bludau]

But we need our users to use the PLESK GUI panel...

How big is the database approximately?

did you tried this Unable to copy/back up a database in Plesk: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table - Support Cases from Plesk Knowledge Base?

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600
net_buffer_length = 64K

 

[dosmasdos]

How big is the database approximately?

did you tried this Unable to copy/back up a database in Plesk: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table - Support Cases from Plesk Knowledge Base?

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600
net_buffer_length = 64K

10MB only.... but ocurrs in all domains and all databases

 

[Jan Bludau]

10MB only.... but ocurrs in all domains and all databases

innodb_buffer_pool_size=100M

100MB only is looking strange should be 75%-80% of the RAM

 

[dosmasdos]

 

[dosmasdos]

 

[Jan Bludau]

View attachment 24909

MariaDB Tuning Script.

 wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

chmod +x mysqltuner.pl

./mysqltuner.pl

Funktioniert die Plesk Support Anleitung?

 

[Jan Bludau]

Sind sie bei MariaDB oder mySQL ?

Bei MariaDB liegt die config Datei hier

nano /etc/mysql/mariadb.conf.d/50-server.cnf

 

[Peter Debik]

- When you run the backup and at the same time watch the output of
> show processlist;
of your database server, do you see many "waiting on table lock" entries?

- Does the database server stay online all through the backup process or are you kicked out of "# plesk db" while running the backup or creating dumps?

 

[dosmasdos]

MariaDB [psa]> show processlist;
+--------+-------------+-----------+---------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+-----------+---------------+---------+------+----------+------------------+----------+
| 4 | admin | localhost | psa | Sleep | 33 | | NULL | 0.000 |
| 5 | admin | localhost | psa | Sleep | 14 | | NULL | 0.000 |
| 148947 | es_ilorbqf | localhost | emailsecurity | Sleep | 291 | | NULL | 0.000 |
| 149337 | es_ilorbqf | localhost | emailsecurity | Sleep | 1377 | | NULL | 0.000 |
| 149587 | es_ilorbqf | localhost | emailsecurity | Sleep | 230 | | NULL | 0.000 |
| 152178 | es_ilorbqf | localhost | emailsecurity | Sleep | 2592 | | NULL | 0.000 |
| 153477 | es_ilorbqf | localhost | emailsecurity | Sleep | 1307 | | NULL | 0.000 |
| 154126 | es_ilorbqf | localhost | emailsecurity | Sleep | 260 | | NULL | 0.000 |
| 157922 | es_ilorbqf | localhost | emailsecurity | Sleep | 1482 | | NULL | 0.000 |
| 159698 | es_ilorbqf | localhost | emailsecurity | Sleep | 395 | | NULL | 0.000 |
| 161226 | es_ilorbqf | localhost | emailsecurity | Sleep | 1021 | | NULL | 0.000 |
| 161482 | es_ilorbqf | localhost | emailsecurity | Sleep | 1162 | | NULL | 0.000 |
| 166578 | admin | localhost | psa | Sleep | 505 | | NULL | 0.000 |
| 166872 | moodle_aero | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 166875 | admin | localhost | psa | Query | 0 | starting | show processlist | 0.000 |
| 166912 | moodle_aero | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 166942 | admin | localhost | psa | Sleep | 37 | | NULL | 0.000 |
| 166950 | moodle_aero | localhost | moodle | Sleep | 0 | | NULL | 0.000 |
| 166960 | admin | localhost | psa | Sleep | 0 | | NULL | 0.000 |
+--------+-------------+-----------+---------------+---------+------+----------+------------------+----------+