Automating tasks is a normal behaviour of a sane and lazy developer. One of the tasks that are usually automated, is the backup of databases in the servers. With MySQL, the logical backups using mysqldump are pretty easy to do and to automatize. However, inside a Plesk environment, you may have problems with details as knowing what's the password to access as admin/root in MySQL server. MySQL is installed and configured with Plesk does not have a user with name 'root' by default. It is renamed to 'admin' and have only 'localhost' access. On Linux, password is encrypted and stored in /etc/psa/.psa.shadow. Knowing this, you may want to write your own shell script to automatize non-incremental (full) backups of your databases by yourself, but what if we told you that there's already a script written for it that may be useful for you?
In this article, we'll share with you a very simple script to generate compressed backups with mysqldump in Plesk.
1. Create Backup Script
Proceed to create the backup script, that in this case we'll name plesk_db_backup.sh and append the following code to it:
#!/bin/bash
# Script to export all the databases stored in MySQL Server in Plesk
# Note: This file needs to use the LF break line format, not CRLF
# Is recommendable as well to use absolute paths for the directories
# @author Carlos Delgado <[email protected]>
# Define constants of the script
USERNAME="admin"
BACKUP_DIRECTORY="/var/ourcodeworld-db-backups/backups"
# Important:
# This directory will be created and **DELETED** after executing the script
TMP_BACKUP_DIRECTORY="/var/ourcodeworld-db-backups/tmp-backups"
EXCLUDED_DATABASES="Database|information_schema|performance_schema|mysql"
# 1. Build array of databases registered
BASES_DE_DATOS=$(MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u $USERNAME -e "SHOW DATABASES;" | tr -d "| " | egrep -v $EXCLUDED_DATABASES)
printf "Starting backup of databases ... \n\n"
# 2. Verify if the temporary backup folders exists, otherwise create it
[ -d $TMP_BACKUP_DIRECTORY ] || mkdir $TMP_BACKUP_DIRECTORY
# 3. Iterate over the database array available for the signed user (admin)
for db in $BASES_DE_DATOS; do
printf " Exporting database: '$db' | "
# 3. Dump database with mysqldump
# MYSQL_PWD is used to access as admin in MySQL inside Plesk based servers
# MYSQL_PWD=`cat /etc/psa/.psa.shadow`
BKP_FILENAME=`date +%Y%m%d`.$db.sql.gz
MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u $USERNAME --databases $db | gzip > $TMP_BACKUP_DIRECTORY/$BKP_FILENAME
printf "OK\n"
done
printf "\nThe backups have been succesfully generated ... Compressing all databases into a single file ... \n"
# 5. Verify if the backup folder exists in the current directory, otherwise create it
[ -d $BACKUP_DIRECTORY ] || mkdir $BACKUP_DIRECTORY
# 6. Compress generated backups (.sql files)
FNL_BKP_FILENAME=`date +%Y_%m_%d_%H_%M_%S`.mysql_databases_backup.tar.gz
tar -zcvf $BACKUP_DIRECTORY/$FNL_BKP_FILENAME $TMP_BACKUP_DIRECTORY
printf "\nCleaning up !\n"
# 7. Delete the temporary directory of backups
rm -rf $TMP_BACKUP_DIRECTORY
printf "\nBackup succesfully generated. \n"After pasting the content, be sure that the shell script is saved on your system using the LF (unix) end of lines and not the CLRF (Windows), or the shell script won't run (even with execution permissions).
How it works
The logic of the backup works as follows: as first, you need to define in constanst the username that you use to access plesk as admin, in our case the username is the same "admin". You need to define as well some variables that contain some directories that will be used during the backup, some to store temporary files and other that will contain the final backup files. Then, a new variable will be created and it will contain an array of names of the databases available on the MySQL server, you will have as well another variable to exclude some of the databases in case that you need to exclude them.
Once the array is available, the script needs to verify if there's a temporary backup directory that will contain the dumps that will be compressed later, if it doesn't exist it will be created and the dumps will be created inside. Now the important point of the backup, the execution of the mysqldump command that is executed for every item in the array of names of the databases. The dumped .sql file is gzipped and stored in the temporary directory. Once every database has been dumped, the script compress all the content of the temporary directory and creates a new gzipped tar file inside the backup directory.
After creating the backup, the temporary folder is removed and the process finishes !
2. Run Backup Script
Now that you know how the script works, you only need to execute it, so be sure to grant execution permissions to the SH file with the following command:
chmod +x plesk_db_backup.shAnd then run the shell script in the way you want:
# Simply run the shell script directly if it has write permissions
./plesk_db_backup.sh
# Or run the shell script with the sh command
sh ./plesk_db_backup.shThe output generated in the terminal will look something like:
Starting backup of databases ...
Exporting database: 'ourcodeworld-es' | OK
Exporting database: 'ourcodeworld' | OK
Exporting database: 'ourcodeworld-docs' | OK
Exporting database: 'other_database' | OK
The backups have been succesfully generated ... Compressing all databases into a single file ...
/var/ourcodeworld-db-backups/tmp-backups/20180517.ourcodeworld-es.sql.gz
/var/ourcodeworld-db-backups/tmp-backups/20180517.ourcodeworld.sql.gz
/var/ourcodeworld-db-backups/tmp-backups/20180517.ourcodeworld-docs.sql.gz
/var/ourcodeworld-db-backups/tmp-backups/20180517.other_database.sql.gz
Cleaning up !
Backup succesfully generated.After the script finishes, you will have in the directory where the script was created a new folder, namely backups. Inside you will find a gzipped file that contains all the compressed dumps generated by mysqldump of every database available in the MySQL server of your Plesk:
Happy coding !
