Knowledge Base

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

Question

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

Answer

If some of system databases got corrupted, but there are no manually created backups available, use Plesk daily/pre-upgrade dumps for restoration. To learn more about Plesk daily/pre-upgrade dumps, see the Additional Information section.

To back up/restore these databases, connect to a Plesk server via SSH (Linux) / RDP (Windows Server) and follow the instructions below:

Restoring a system database from a Plesk daily/pre-upgrade dump on Linux

Restoring from a Plesk daily dump

Switch to the directory with daily dumps:

# cd /var/lib/psa/dumps
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.

Switch to the directory with pre-upgrade dumps:

# cd /var/lib/psa/dumps
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.
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

Restoring a system database from a Plesk daily dump on Windows Server

Switch to the directory with daily dumps:

C:> cd %plesk_dir%MysqlBackup
List all available Plesk daily dumps sorted by date (newest first):

C:> dir /O:-D

Directory of C:Program Files (x86)PleskMySQLBackup

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
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