Cannot restore domain in Plesk: Failed to restore the remote database for security reasons

Symptoms

• Plesk backup restoration failed:

  CONFIG_TEXT: Failed to restore the remote database DB_NAME (domain example.com). It cannot be restored for security reasons. Please contact your hosting provider to solve this issue

• Unable to delete or import a database in Domains > example.com > Databases > <databaseName>:

  Error dropping database (can't rmdir './example', errno: 39 "Directory not empty")

• The database example contains files:

  #ls -las /var/lib/mysql/security/
  total 784
  4 drwx------ 2 mysql mysql 4096 abr 14 16:29 .
  12 drwxr-xr-x 130 mysql mysql 12288 abr 14 15:29 ..
  64 -rw-rw---- 1 mysql mysql 65536 abr 14 15:27 wp_commentmeta.ibd
  ....
  ....
  64 -rw-rw---- 1 mysql mysql 65536 abr 14 15:28 wp_usermeta.ibd
  64 -rw-rw---- 1 mysql mysql 65536 abr 14 15:28 wp_users.ibd

• The database is absent from Tools & Settings > Database Servers > server name > Databases tab

Cause

Database files cannot be re-created due to incorrect permissions/ownership on MySQL files and directories. This led to state when the database was removed from Plesk but persists on MySQL server and disk. A point of improvement was created PPPM-13867 to show notifications when database removal errors occur in MySQL when it is deleted from Plesk.

Resolution

1. Log in to the server via SSH

2. Fix permissions and ownership:

   # chown -R mysql:mysql /var/lib/mysql

   # find /var/lib/mysql/* -type d -exec chmod 0700 {} \;

   # find /var/lib/mysql/* -type f -exec chmod 0660 {} \;

3. Remove existing database files:

   # rm -rf /var/lib/mysql/example_db

4. Access Plesk database:

   # plesk db

5. Remove the database:

   MYSQL_LIN: > drop database DB_NAME;

6. Repeat domain or database restoration.