How to backup or restore a Plesk database? – Plesk Help Center

Question

How to backup or restore a Plesk database?

How to restore Plesk database from backup/dump?

Answer

Plesk for Linux:

Automatic Plesk database dumps are stored in the directory defined by the DUMP_D variable value in the file /etc/psa/psa.conf.

There are two types of automatic dumps:

Daily dumps that are created every night, together with running of the statistics utility. Dump files have names like mysql.daily.dump.1.gz located in /var/lib/psa/dumps/ by default and each file includes a dump of the following databases:
- psa
- horde (if installed)
- mysql
- roundcubemail (if installed)
- sitebuilder5 (if installed)
- phpmyadmin
Pre-upgrade dumps that are made when the Plesk package update is run. Dump file names look like mysql.preupgrade.<version and time stamp>.dump.gz , and each file includes a dump of:
- mysql
- psa
- apsc
- horde

Connect to the server using SSH and use the command below to perform a database backup manually:

Note: DATABASE_NAME should be substituted either with psa or apsc

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

Since Plesk 12.5 the shortcut command is available for backup creation:

# plesk db dump DATABASE_NAME > /full/path/to/BACKUP_FILE_NAME.sql

The example below shows how to restore Plesk databases from the daily dump located in /var/lib/psa/dumps/:

# cd /var/lib/psa/dumps/

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa < mysql.daily.dump.0

The following example shows how to restore ' PSA ' database from the pre-upgrade backup file:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin -e"DROP DATABASE psa;"
# zcat /var/lib/psa/dumps/mysql.preupgrade.<version and time stamp>.dump.gz | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -f -uadmin

In case restoring all of three databases, delete them before restoration using the DROP DATABASE SQL command.

Plesk for Windows:

Automatic Plesk database dumps are stored in the %plesk_dir%\Mysql\Backup directory.

Connect to the server using RDP and use the command below (run Windows command prompt as Administrator) to perform a database backup manually:

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

Since Plesk 12.5 the shortcut command is available for backup creation:

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

The following example shows how to restore the %plesk_dir%MySQL\Backup\psa-20131202194200.sql backup file:

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

Comments

4 comments

Alex Rubio Cazador August 11, 2018 09:15

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

Denis Bykov August 19, 2018 19:59

@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

santhosh September 06, 2018 05:35

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

Ivan Postnikov September 07, 2018 18:30
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:
  
  If you are leasing a virtual container, please contact your service provider in order to increase container limits.
  
  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