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.