I am in the process of setting up a remote backup for my MariaDB server (running on 64 bit Ubuntu 12.04).
The first step in this process was easy - I am using automysqlbackup as explained here.
The next step is mostly pretty easy - I am uploading the latest backup in the folder /var/lib/automysqlbackup/daily/dbname to a remote backup service (EVBackup in the present case) via SFTP. The only issue I have run into is this
The files created in that folder by automysqlbackup bear names in the format dbname_2014-04-25_06h47m.Friday.sql.gz and are owned by root. Establishing the name of the latest backup file is not an issue. However, once I have got it I am unable to use file_get_contents since it is owned by the root user and has its permissions set to 600. Perhaps there is a way to run a shell script that fetches me those contents? I am a novice when it comes to shell scripts. I'd be much obliged to anyone who might be able to suggest a way to get at the backup data from PHP.
For the benefit of anyone running into this thread I give below the fully functional script I created. It assumes that you have created and shared your public ssh_key with the remote server (EVBackup in my case) as described here.
In my case I had to deal with one additional complication - I am in the process of setting up the first of my servers but will have several others. Their domain names bear the form svN.example.com where N is 1, 2, 3 etc.
On my EVBackup account I have created folders bearing the same names, sv1, sv2 etc. I wanted to create a script that would safely run from any of my servers. Here is that script with some comments
#! /bin/bash
# Backup to EVBackup Server
local="/var/lib/automysqlbackup/daily/dbname"
#replace dbname as appropriate
svr="$(uname -n)"
remote="$(svr/.example.com/)"
#strip out the .example.com bit to get the destination folder on the remote server
remote+="/"
evb="user@user.evbackup.com:"
#user will have to be replaced with your username
evb+=$remote
cmd='rsync -avz -e "ssh -i /backup/ssh_key" '
#your ssh key location may well be different
cmd+=$local
cmd+=$evb
#at this point cmd will be something like
#rsync -avz -e "ssh -i /backup/ssh_key" /home bob@bob.evbackup.com:home-data
eval $cmd
