Is this a one off backup or a regular one @rlfedor?

Make a script called backup.sh like below (you need to modify it, test it, and perhaps improve it):

#!/bin/bash

DATE=`date "+%Y-%m-%d-%H%M"`
DIR="/path/to/backupfolder/$DATE"
mkdir $DIR
cd $DIR

/usr/bin/mysqldump -r backup.sql --databases your_piwigo_database_name
/bin/gzip backup.sql

tar -zcvf piwigo.tar.gz /path/to/www/piwigo_folder

scp backup.sql.gz yourusername@yourothervps:/home/yourusername/backup.sql.gz.$DIR
scp piwigo.tar.gz yourusername@yourothervps:/home/yourusername/piwigo.tar.gz.$DIR

rm -f backup.sql.gz piwigo.tar.gz

Then do

chmod u+x backup.sh

to make it executable.

Then readup about crontab for your system, and make it run the script once a day, preferably at night. On Ubuntu 16, you would create a file by typing

sudo pico /etc/cron.d/anyfilename

and add:

# Nightly MySQL backup
1 2 * * *  yourusername if [ -x /home/yourusername/backup.sh ]; then /home/yourusername/backup.sh > /dev/null; fi

To make it autonomous (no password), you would need to add the appropriate ssh keys on your other VPS. Google it.

rlfedor wrote:

I would ideally like to schedule regular backups to an off-site VPS.  I created a Pwigo gallery for my dad and after seeing all the time and effort into getting up and running, I would like to be able to back it up for a quick restore if needed.

Zentalquabula wrote:

Make a script called backup.sh like below (you need to modify it, test it, and perhaps improve it):

nice script, i made in the past weeks at something similar but more simple.
i have copied the database files directly without making an sql dump.

usually the files are in the /var/ directory, but you can move them anywhere by setting "datadir    = /var/somefolder/" in "/etc/my.conf" .

i can confirm that backup with the files copied directly if working fine, but it was tested on a non-production server. i don't know what it may happened if some data is written in the database exactly when you are copying the files.

so probably using sqldump is better , maybe it has some protection algorithms that wait for the data to be completely written (SQL query) then pauses all queries while making a dump. i think i will adopt your solution is probably more safe.

You set the password in ~/.my.cnf and then protect it with chmod 600 ~/.my.cnf, otherwise there will be a warning message about insecure handling of passwords, which may interfere with the script.

[client]
user=root
password=yourpassword
socket=/var/run/mysqld/mysqld.sock

eliz82 wrote:

by the way i have tried to execute
"mysqldump -r backup.sql --databases your_piwigo_database_name"
in the shell console and it give me an error

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

this one that specify a mysql user and password is working:
mysqldump -u root -pmypassword -r backup.sql --databases your_piwigo_database_name
or
mysqldump --user=root --password=mypassword -r backup.sql --databases your_piwigo_database_name