Following on from my recent MYSQL DB, it’s one thing to back up the data, but you have to get the data off the server.
So, with my trusty Raspberry pi, that is going to use considerable less power than my NAS (which has been used for VMs to do this), it’s now time to get some scripts working on the pi to do the offline/off server backups.
The biggest problem is finding a solution to download the data securely. It’s easy to use plain FTP, which is not secure, but I need to have SFTP.
So, as it turns out, there is a program called sftp built in to the pi.
I went down the path of using sshpass. My FTP password has to be saved somewhere, and as it’s only for SQL backups, I’m not too concerned.
You have a few options other than using public key authentication. I have take this example from here.
- Use sshpass (less secured but probably that meets your requirement)
If you decide to give sshpass a chance here is a working script snippet to do so:
You should copy all of this in to a script.sh file.
export SSHPASS=your-password-here sshpass -e sftp -P PORTNUMBER -oBatchMode=no -b - sftp-user@remote-host << ! lcd local_backup_path1 cd db_backup_path1 get *.gz cd.. lcd .. lcd local_backup_path2 cd db_backup_path2 get *.gz cd .. lcd .. bye !
Please note the PORTNUMBER there. It has also be a capital for it to work.
Then, run the script with sh ./script.sh