Back-up Databases in Plesk remotely

Some databases are more important then others. In some cases you want to back-up just a couple database more often. I have a Synology Diskstation DS413J at home. To have an off-site back-up for my most important databases is extremely important.  Some database must be retained for seven years for tax services here in the Netherlands. That is why I made a little shell script which exports databases and then, ftp explicit SSL, transfer it to the NAS device. First get the server ready transfer files over FTP(e)S.

sudo apt-get install lftp

The above code installs a lftp client on the in this case Ubuntu machine.

Next create a dir in which you want to dump the sql files in. I consider that you use Plesk 9+. In my case the folder is called /dumps. Create two .sh files with the corresponding code:

run.sh

	date=$(date +%x)
	exportdir="/dumps"
	while read database; do
	  filename="$exportdir/$database-$date.sql"
	  mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $database > $filename
	done </root/databases.txt
	lftp -f /root/ftps.sh

ftps.sh

debug 10
set ftp:ssl-auth TLS
set ssl:verify-certificate no
set ftp:ssl-protect-data yes
open USER:PASSWORD@SERVER.TLD:21/DATADIRECTORY
mput /dumps/*
close

In databases.txt you list the databases that will be back-upped. The FTPS script copies any file in the Dump directory.

Then make a cron job which runs this task.