How to restore all MySQL / MariaDB user databases from a Plesk backup

Question

How to restore all MySQL / MariaDB user databases from a Plesk backup?

Answer

1. Connect to your Plesk server via SSH.

2. Download the script to your server:

   # wget https://support.plesk.com/hc/en-us/article_attachments/29158452093463 -O restore-all-db-plesk-backup.zip

3. Unzip it:

   # unzip restore-all-db-plesk-backup.zip

4. Make the script executable:

   # chmod +x restore-all-db-plesk-backup.sh

5. Find the current backup location:

   # grep "DUMP_D" /etc/psa/psa.conf | grep -v "#"
   DUMP_D /var/lib/psa/dumps

6. List all available database backups:

   Note: If custom backup location is used, replace /var/lib/psa/dumps with the path from step 5.

   # find /var/lib/psa/dumps -name "backup_sqldump_*" -type f -printf '%f\n' | sort | uniq

   File name contains the date the backup has been taken. For example, timestamp in file backup_sqldump_2501120422.tzst stands for "January 12, 2025, 04:22 AM"

   Note: The most recent backup will be at the end of the output.

7. Open the downloaded script in a text editor. In this example, we are using vi editor:

   # vi restore-all-db-plesk-backup.sh

8. Replace the backup name in the TIMESTAMP line:

   CONFIG_TEXT: TIMESTAMP=backup_sqldump_timestamp.tzst

   with the backup name you wish to restore the databases. For example:

   CONFIG_TEXT: TIMESTAMP=backup_sqldump_2501120422.tzst

9. If custom backup location is used, change the value of DB_PATH from/var/lib/psa/dumps to the path from step 5.

10. Save the changes and close the file.

11. Dry run the script:

    # ./restore-all-db-plesk-backup.sh

12. Check the list of the databases to be restored.

13. Open the script in a text editor again:

    # vi restore-all-db-plesk-backup.sh

14. Change the DRYRUN value from 1 to 0.

15. Save the changes and close the file.

16. Run the scrip:

    # ./restore-all-db-plesk-backup.sh