All sites on a Plesk server are inaccessible while a backup creation task is running: Error establishing a database connection

Symptoms

• The backup task is running on the server in Tools & Settings > Backup Manager.

• All websites are not accessible with the following errors:

  Error establishing a database connection

  ---

  504 gateway timeout

• Database assigned to affected websites is not accessible with the error:

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

  or the following if checking via Domains > example.com > Databases > database_name > phpMyAdmin:

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

Cause

Mysqldump locks all tables, and after that performs a data backup, which leads to the growth of number of connections waiting in a queue, and to the error when the defined max_user_connections value is reached.

Resolution

1. Connect to the server via SSH.

2. Customize behaviour of mysqldump used by Backup Manager by adding the following options in /etc/my.cnf (CentOS/RHEL) or /etc/mysql/my.cnf(Debian/Ubuntu):

   [mysqldump]
   skip-lock-tables
   single-transaction