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 backup/restore these databases, follow the instructions below:
To backup/restore system databases, connect to a Plesk server via SSH.
Creating a 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 for Plesk Onyx creates a database dump of the Plesk psa database in the/root
directory with today's timestamp:# plesk db dump psa > /root/psa_dump.sql
The following database dump will be created:
# ls /root
psa_dump.sql
Restoring a database from a 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 a database 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
-
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 database
The following command for Plesk Onyx 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 all system databases
The following command for Plesk Onyx 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
To backup/restore Plesk system databases, connect to a Plesk server via RDP and start a command prompt as an Administrator.
Creating a 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 onC:\
disk:C:\> plesk db dump psa > C:\psa_dump.sql
Restoring a database from a 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:\> MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < C:\<dump_name>.sql
Example
The following command for Plesk Onyx restores the Plesk psa database from the database dumppsa_dump.sql
located onC:\
disk:C:\> plesk db < C:\psa_dump.sql
Restoring a database 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.
-
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:
Example 1: For Plesk Onyx
The following command restores the Plesk psa database from the latest available daily dumppsa-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 dumppsa-20181018020721.sql
:Note: MySQL admin password is required for this operation.
C:\> "%plesk_dir%"\Mysql\bin\mysql.exe -ua<dmin -p -P8306 < 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 (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
4 comments
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.
@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
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
Hello @santhosh,
This behavior may be caused by several issues:
# 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:
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
Please sign in to leave a comment.