A website is not responding when a database backup/copy operation is running in Plesk: Error establishing a database connection

Symptoms

• When backing up or copying a large MySQL database in Plesk, the website that uses this database stops responding. CLOSE_WAIT connections can be observed on the server:

  # netstat -tanp | grep apache | awk '{print $6}' | sort | uniq -c | sort -n
  2 LISTEN
  24 ESTABLISHED
  62 CLOSE_WAIT

• During a server-wide Plesk backup, all websites are not accessible with one of the following errors:

  Error establishing a database connection

  ---

  504 gateway timeout

• When connecting to the database of an affected website directly via MySQL, the operation fails with the following error message:

  ERROR 1203 (42000): User database_admin_user already has more than 'max_user_connections' active connections

  or the following error when connecting via phpMyAdmin at Domains > example.com > Databases > database_name:

  mysqli::real_connect(): (HY000/1203): User database_admin_user already has more than 'max_user_connections' active connections

Cause

By default, mysqldumputility locks all tables and after that performs a data backup which leads to an increase in the number of connections waiting in the queue.

Resolution

Enable single-transaction option and disable lock-tables in MySQL configuration:

1. Connect to the Plesk server via SSH.

2. Open MySQL 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 these options at the end of the file:

   [mysqldump]
   skip-lock-tables
   single-transaction

4. Save the changes and close the file.

Note: According to official MariaDB documentation, this configuration is a much better option for transactional tables such as InnoDB, because it does not need to lock the tables at all.

Currently, the option single-transaction can be enabled only globally via MySQL configuration file my.cnf.
If you wish to see more flexible options for database backup/copy operations in Plesk, vote for this feature request on Plesk UserVoice:

• https://plesk.uservoice.com/forums/184549-feature-suggestions/suggestions/45790528