Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to restore Plesk system databases from a Plesk daily/pre-upgrade dump?
How to restore 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 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.gzwhere
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 dumpmysql.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 Plesklog_actions
table from the latest available daily dumpmysql.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 dumpmysql.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.gzIn 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
-
Switch to the directory with daily dumps:
C:\> cd %plesk_dir%Mysql\Backup
-
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 -
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
Additional Information
To learn more about Plesk daily/pre-upgrade dumps, click on the section below:
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
Comments
2 comments
Hi Kuzma Ivanov,
What I got was:
Can you please share your thoughts regarding the above?
There's an error in this guide; if you've done a complete rebuild of the database by following the steps in "How to fix InnoDB corruption cases for the MySQL databases on Plesk for Linux?", then running `plesk db` as per this guide will result in an error message because `psa` doesn't exist.
The workaround is to do this:
```
zcat mysql.daily.dump.0.gz | sed -n '/-- Table structure for table `log_actions`/,/-- Table*/p' | mysql -u admin -p`cat /etc/psa/.psa.shadow`
```
Please sign in to leave a comment.