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:
-
Connect to the Plesk server via SSH.
-
Open MySQL configuration file
my.cnfin 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
-
-
Add these options at the end of the file:
[mysqldump]
skip-lock-tables
single-transaction -
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:
with