Articles in this section

See more

How to back up/restore Plesk system databases

Avatar
Kuzma Ivanov
Updated
Follow

Applicable to:

  • Plesk for Linux
  • Plesk for Windows

Question

How to create a dump of system databases in Plesk?

How to restore system databases from a database dump?

Answer

Plesk interacts with the following databases:

  • psa - Plesk system database
  • apsc - Plesk application database
  • mysql - MySQL system database
  • roundcubemail - Roundcube database, if installed
  • horde - Horde database, if installed
  • atmail - atmail database, if installed
  • sitebuilder5 - Web Presence Builder, if installed
  • phpmyadmin_* - phpMyAdmin database, if installed

 

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

 

Plesk for Linux

 

Backing up/restoring system databases manually

 

Creating a manual database dump

  • Plesk Onyx

    # plesk db dump <database_name> > /path/to/<dump_name>.sql

  • Plesk 12.5 and previous releases

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin <database_name> > /path/to/<dump_name>.sql

     

    Example
    The following command creates a database dump of the Plesk 'psa' database in the /root directory with today's timestamp:

    • For Plesk Onyx

      # plesk db dump psa > /root/psa_dump.sql

    • For Plesk 12.5 and previous releases

      # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin psa > /root/psa_dump.sql

    The following database dump will be created:

    # ls /root/psa_dump.sql
    psa_dump.sql

 

 

Restoring a database from a manual database dump

  • Plesk Onyx

    # plesk db < /path/to/<dump_name>.sql

  • Plesk 12.5 and previous releases

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/<dump_name>.sql

     

    Example
    The following command for Plesk Onyx restores the Plesk psa database from the database dump 'psa_dump.sql' located in the /root directory:

    # plesk db < /root/psa_dump.sql

 

Restoring system databases from a Plesk daily/pre-upgrade dump

 

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.

 

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 for Plesk Onyx 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 all system databases
    The following command for Plesk Onyx 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

 

 

Plesk for Windows Server

 

Backing up/restoring system databases manually

 

Creating a manual database dump

  • Plesk Onyx

    C:\> plesk db dump <database_name> > C:/path/to/<dump_name>.sql

  • Plesk 12.5 and previous releases

    Note: MySQL admin password is required for this operation.

    C:\> "%plesk_dir%"\Mysql\bin\mysqldump.exe -uadmin -p -P8306 <database_name> > C:/path/to/<dump_name>.sql

     

    Example
    The following command for Plesk Onyx creates a database dump of the Plesk 'psa' database on C:\ drive:

    C:\> plesk db dump psa > C:\psa_dump.sql

 

 

Restoring a database from a manual database dump

  • Plesk Onyx

    C:\> plesk db < C:/path/to/<dump_name>.sql

  • Plesk 12.5 and previous releases

    Note: MySQL admin password is required for this operation.

    C:\> "%plesk_dir%\Mysql\bin\mysql.exe" -uadmin -p -P8306 psa < C:\<dump_name>.sql

     

    Example
    The following command for Plesk Onyx restores the Plesk 'psa' database from the database dump psa_dump.sql located on C:\ drive:

    C:\> plesk db < C:\psa_dump.sql

 

Restoring system databases from a Plesk daily dump

 

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

  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:

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

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

     

    Example 2: For Plesk 12.5 and previous releases
    The following command restores the Plesk psa database from the latest available daily dump psa-20181018020721.sql:

    Note: MySQL admin password is required for this operation.

    C:\> "%plesk_dir%\Mysql\bin\mysql.exe" -uadmin -p -P8306 psa < psa-20181018020721.sql

 

 

Additional Information

 

About Plesk daily/pre-upgrade dumps

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

 

Related Articles

Comments

4 comments

Sort by Date Votes
  • Avatar
    Alex Rubio Cazador

    I have a issue when i try to do this command included in a crontab

    plesk db dump DATABASE_NAME > C:\FULL\PATH\TO\FILE_NAME.sql

    when I execute manually the command runs perfect and i can backup all my sql files, but when i use this comend inside a crontab, they create and retund the .sql files empty

    Thank you.

    0
    Permalink
  • Avatar
    Denis Bykov

    @Alex Rubio Cazador
    Try using the full path to the executable instead of the alias:

    For Linux:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin DATABASE_NAME > /full/path/to/BACKUP_FILE_NAME.sql

    For Windows Server:

    C:\>"%plesk_dir%\Mysql\bin\mysqldump.exe" -uadmin -p<plesk_admin_password> -P8306 DATABASE_NAME > C:\FULL\PATH\TO\FILE_NAME.sql

    0
    Permalink
  • Avatar
    santhosh

    hai am new to plesk am getting error while am updating plesk

    Internal error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'psa.ServiceInstallations' doesn't exist

    how to rectify this error

    0
    Permalink
  • Avatar
    Ivan Postnikov

    Hello @santhosh,

    This behavior may be caused by several issues:

    • Server is running out of RAM.
    • Some limits are exceeded on a container or on a hardware node.
    1. Connect to the server via SSH;
    2. Make sure that it is enough memory or container resources:

      # free -m
      # cat /proc/user_beancounters

      There should be enough memory and all 'failcnt' values should be 0. 

      Do the following in order to fix the issue:

      1. If you are leasing a virtual container, please contact your service provider in order to increase container limits.
      2. Increase amount of RAM or SWAP on the server.
    3. After fixing the limits issue, re-run the upgrade How to upgrade Plesk to the next release

    Check this article for reference:
    https://support.plesk.com/hc/en-us/articles/360006408573-Unable-to-open-Plesk-after-upgrade-Table-psa-ServiceInstallations-doesn-t-exist

    0
    Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles