Unable to copy/back up a database in Plesk: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

Symptoms

• When creating a copy of a database in Plesk at Domains > example.com > Databases, the process hangs without any result. The following error message appears in the Plesk logfile /var/log/plesk/panel.log:

  CONFIG_TEXT: ERR [panel] Unable to dump source database: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

• A Plesk backup task finishes with the following error message:

  PLESK_INFO: Not all the data was backed up into /path/to/databases/example_db successfully. Total space: 100.00 GB; Available space: 60.00 GB; Mounted on: /datavolume. mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

Cause

The current limits in MySQL/MariaDB configuration are not enough for dumping the database.

Resolution

Increase the limits:

1. Connect to the Plesk server via SSH.

2. Open the MySQL/MariaDB configuration file my.cnf in a text editor. In this example, we are using the vi editor:

   • on CentOS/RHEL-based distributions:

     # vi /etc/my.cnf

   • on Debian/Ubuntu-based distributions:

     # vi /etc/mysql/my.cnf

3. Add the following lines under the [mysqld] section:

   CONFIG_TEXT: innodb_buffer_pool_size=100M
   max_allowed_packet=1024M
   net_read_timeout=3600
   net_write_timeout=3600
   net_buffer_length = 64K
   [mysqldump]
   max_allowed_packet = 1024M

4. Save the changes and close the file.

5. Restart MySQL/MariaDB server using one of the commands below. The command depends on operating system and installed MySQL/MariaDB version:

   # service mysql restart
   
   # service mariadb restart