Articles in this section

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

Kuzma Ivanov
Updated
Plesk for Windows Plesk for Linux ABT: Group B kb: auxiliary

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 a single table from a Plesk daily / pre-upgrade dump?

Answer

If a system database 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 restore a system database, connect to your Plesk server via SSH (Linux) / RDP (Windows Server) and follow the instructions below:

For Plesk on Linux

 

Restoring a database 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 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 table of a database
    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 a database 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

For Plesk on Windows Server

 

  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

 

Additional Information

To learn more about Plesk daily/pre-upgrade dumps, click on the section below:

About Plesk daily/pre-upgrade dumps

 

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

 

Linux

On Linux, 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
Was this article helpful?

Comments

2 comments
Date Votes
  • Avatar
    Ehud Ziegelman

    Hi Kuzma Ivanov,

     

    What I got was:

    /var/lib/psa/dumps# zcat mysql.daily.dump.0.gz | plesk db
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    exit status 1

     

    Can you please share your thoughts regarding the above?

     

    0
  • Avatar
    brambamberg
    (Edited )

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

    0

Please sign in to leave a comment.