Plesk upgrade or databases backup fails: mysqldump: unknown variable

Symptoms

• Plesk upgrade fails with:

  /usr/bin/mysqldump: unknown variable 'bind-address=203.0.113.2'
  ERROR while trying to backup MySQL database

  /usr/bin/mysqldump: unknown variable 'sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

• Unable to create a backup of all databases with the following command:

  # MYSQL_PWD=cat /etc/psa/.psa.shadow mysqldump -u admin --all-databases --routines --triggers > /tmp/all-databases.sql
  mysqldump: unknown variable 'datadir=/var/lib/mysql'
  mysqldump: unknown variable `symbolic-links=0`

• There are some parameters under [mysqldump] section un-commented in /etc/my.cnf:

  # cat /etc/my.cnf
  ...
  [mysqldump]
  symbolic-links=0

Cause

An incorrect variable in the MySQL/MariaDB configuration file.

Resolution

1. Connect to the server via SSH.

2. Create a backup copy of my.cnf file:

   # cp /etc/my.cnf /etc/my.cnf.bak

3. Open /etc/my.cnf file for editing.

4. Comment all lines in [mysqldump] section, including [mysqldump] itself:

   #[mysqldump]
   #symbolic-links=0

5. Restart MySQL/MariaDB service:

   # service mysql restart || service mariadb restart || service mysqld restart