How to restore Plesk system databases from a Plesk daily / pre-upgrade dump

Restoring from a Plesk daily dump

1. Switch to the directory with daily dumps:

   # cd /var/lib/psa/dumps

2. List all available Plesk daily dumps:

   # ls -l mysql.daily*
   -rw------- 1 root root 236253 Feb 3 01:51 mysql.daily.dump.0.gz
   -rw------- 1 root root 229653 Feb 2 01:48 mysql.daily.dump.1.gz
   -rw------- 1 root root 222485 Feb 1 01:56 mysql.daily.dump.2.gz

   where mysql.daily.dump.0.gz is the most recent daily dump.

    

   Example 1: Restoring a single system database
   The following command restores the Plesk 'psa' database from the latest available daily dump mysql.daily.dump.0.gz:
   

   Note: To restore another system database, replace psa with a database name you want to restore.

   # zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | plesk db

    

   Example 2: Restoring a single table
   The following command restores the Plesk log_actions table from the latest available daily dump mysql.daily.dump.0.gz:

   # zcat mysql.daily.dump.0.gz | sed -n '/-- Table structure for table `log_actions`/,/-- Table*/p' | plesk db

    

   Example 3: Restoring all system databases
   The following command restores all system databases from the latest available daily dump mysql.daily.dump.0.gz:

   # zcat mysql.daily.dump.0.gz | plesk db

    

    

Restoring from a Plesk pre-upgrade dump

Pre-upgrade dumps are used to restore the system to previous state if Plesk/MySQL upgrade has failed.

1. Switch to the directory with pre-upgrade dumps:

   # cd /var/lib/psa/dumps

2. List all available pre-upgrade dumps:

   # ls -l mysql.preupgrade*
   -rw------- 1 root root 212341 Feb 1 05:18 mysql.preupgrade.12.5.30-12.5.30.20190214-051821.dump.gz
   -rw------- 1 root root 213230 Feb 1 13:50 mysql.preupgrade.12.5.30-17.8.11.20190214-135000.dump.gz

   In the example below, we are restoring all system databases from a dump file that was created before an upgrade from Plesk 12.5 to Plesk Onyx 17.8.

3. Restore system databases:

   # zcat mysql.preupgrade.17.8.11-17.8.11.20180902-224319.dump.gz | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin

1. Switch to the directory with daily dumps:

   C:\> cd %plesk_dir%Mysql\Backup

2. List all available Plesk daily dumps sorted by date (newest first):

   C:\> dir /O:-D
   
   Directory of C:\Program Files (x86)\Plesk\MySQL\Backup
   
   10/18/2018 02:07 AM 106,128 horde-20181018020721.sql
   10/18/2018 02:07 AM 122,124 config-20181018020721.reg
   10/18/2018 02:07 AM 28,707 apsc-20181018020721.sql
   10/18/2018 02:07 AM 389,032 psa-20181018020721.sql

3. Restore a system database:

   The following command restores the Plesk psa database from the latest available daily dump psa-20181018020721.sql:

   C:\> plesk db < psa-20181018020721.sql

For security reasons, Plesk automatically creates dump files that include system databases.

Linux

On Linux servers, Plesk creates two types of dump files - Plesk daily dump and Plesk pre-upgrade dump.

By default, these dump files are located in /var/lib/psa/dumps/ (The directory is defined by the DUMP_D variable in the file /etc/psa/psa.conf). To list these database dumps, run the command:

# ls -l /var/lib/psa/dumps/mysql*

• Plesk daily dumps

  Every night Plesk automatically creates a daily dump of system databases in scope of the Plesk daily task /etc/cron.daily/50plesk-daily.

  A daily dump file has the name mysql.daily.dump.X.gz, where X is a number starting from 0 (0 means latest). The following databases are included in it (in order of appearance):

  • psa
  • mysql
  • horde
  • sitebuilder5
  • apsc
  • roundcubemail
  • phpmyadmin_*

• Plesk pre-upgrade dumps

  Before upgrading Plesk or installing important MySQL updates, Plesk Installer creates a pre-upgrade dump of system databases. Pre-upgrade dumps are used to restore the system to previous state if Plesk upgrade/update has failed.

  This dump file has the name mysql.preupgrade.PLESK_VER_BEFORE-PLESK_VER_AFTER.DATE_CREATED.dump.gz. The following databases are included in it (in order of appearance):

  • mysql - MySQL system database
  • psa - Plesk system database
  • apsc - Plesk application database
  • horde - Horde database, if installed

Windows Server

On Windows servers, every night Plesk automatically creates a daily dump of system databases in scope of the scheduled task Backup of vital Plesk settings in Windows Task Scheduler (command C:\Program Files (x86)\Plesk\admin\bin\sshost.exe --settings-backup).

These dump files are located in %plesk_dir%Mysql\Backup. The following Plesk databases are backed up:

• psa - Plesk system database
• apsc - Plesk application database
• horde - Horde database, if installed