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
statisticsutility. Dump files have names likemysql.daily.dump.1.gzlocated 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
2 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
Please sign in to leave a comment.