Raspberry Pi as a MySQL backup server

Raspberry Pi as a MySQL backup server for web hosting database

If you run a website with a MySQL database at a classic web host, then you have the problem that you can not make a decent backup here. You can do that manually with phpMyAdmin or similar tools. But unfortunately, you have to do it manually. There are also tools that do it fully automatically. But then the backup is still on the server of the web hoster. It would be better, you could pull the backup fully automatically on a separate computer.

There are many solutions to backing up from a MySQL database. Most assume that the backup is controlled on the web host server. However, one usually has only limited possibilities on the server of a web host. And not everyone has and does not want a root server.

Because of this problem, I originally had a mini-computer running Windows XP running permanently on which DumpTimer for MySQL ( http://www.richtsoft.com/ ) was installed. That worked quite well for several years. Unfortunately, Windows XP is now outdated and dummy timer is no longer maintained. Two good reasons to say goodbye to this installation and to look for a new solution.

Ideas for a solution with Raspberry Pi as a MySQL backup server

The initial situation is that here in the home network a Raspberry Pi with Raspbian should run. There, once a night, a connection to the web host server is set up, a MySQL backup initiated, and then a download of the backup done.

The usual solutions are to log in to the Webhoster server via SSH, trigger a MySQL dump, and then download the file. This can be done with a simple one-liner on the command line.

ssh -C {USER} @ {HOST} "mysqldump -u {DB-USERNAME} --password = {DB-USER-PASSWORD} --add-drop-table --complete-insert {DB-NAME} --socket = /tmp/mysql5.sock "| gzip> db.sql.gz

The whole thing has the disadvantage that it works manually but is not script-driven because you still need to enter the SSH password for the web host server. Quickly one then comes to tools such as “sshpass”, that is able to script-controlled the SSH login to enter the password. But unfortunately that is pretty crippled and does not really work. At least not right away.

The next logical step would be to set up SSH keys. That is, SSH login with with password but certificate. Unfortunately, you do not always get access to the SSH settings of the Webhosting server to set it up. Unless you have a root server. In the case but not.

The usual solutions do not work. So what to do?
alternative solution
Let’s get away from the idea that the transfer must be done with SSH. What else do we have? Actually only HTTPS (not HTTP). Which possibilities arise?
It would be conceivable that one abuts a PHP script on the web server that creates the MySQL dump. This is not a problem. The second step would be to download the dump via HTTPS. Also no problem.
The whole process can be controlled by a bash script on Raspberry Pi.
For security one would still protect the directory in which the PHP script and the dump lie by password (HTTP Basic Authentication). Most web hosts provide the facilities for this in their configuration interfaces.

Solution with HTTPS in detail
So what does the solution look like in detail?

We’ll create a PHP script called backupdb.php that triggers a MySQL dump when invoked. The directory in which the PHP script is located should be password-protected because it also stores the dump.

<? Php
system ('mysqldump -u {DB-USERNAME} --password = {DB-USER-PASSWORD} --add-drop-table --complete-insert {DB-NAME} --socket = /tmp/mysql5.sock | gzip> backupdb.sql.gz ');
echo '+ DONE';
?>

A second script called “backupdb.sh” is located on Raspberry Pi in the home directory of “pi” (/ home / pi / backupdb). The script calls the PHP script and then downloads the dump.

#! /Bin/bash


# Trigger backup/dump
wget -q --spider --http - user = {HTTP - USER} --http - password = {HTTP - PASSWORD} https://{SERVER}/backupdb/backupdb.php


# Download
wget -q -N --http-user = {HTTP-USER} --http-password = {HTTP-PASSWORD} -P /home/pi/backupdb/ https://{SERVER}/backupdb/backupdb.sql. gz

The bash script has to be made executable.

sudo chmod + x backupdb.sh

If the manual execution of the bash script works, you can enter it into the crontab.

sudo nano /etc/crontab

Here you add the following line below:

0 2 * * * pi bash /home/pi/backupdb/backupdb.sh

“0 2 * * *” runs the script once at 2 o’clock. “pi” means with the rights of the user “pi”.

Done is that fully automatic backup of a MySQL database on a web host server.
Extension: Save MySQL dump to USB stick
An SD card as plugged into the Raspberry Pi is less suitable for storing data on a large scale. It is recommended to save periodic data, such as this MySQL database backup, to a USB stick.

save backup to USB stick automatically

A computer is not good enough if you can not extend it to storage. Raspberry Pi is best suited for USB sticks. However, those under Raspbian Wheezy are not automatically included, such as on Windows, Mac OS or other Linux systems. Therefore, the possibility should be established that USB sticks are automatically integrated and that also files can be stored on it.

task

Set up the possibility that USB sticks are automatically “mounted” when plugged in.
Make sure that you can also save files to the USB stick.

Solution: Automatically mount / mount USB sticks (Raspbian Wheezy)
It is about a USB stick to “mount” automatically. This means “embed” or “mount” so that you can access it via the directory system. To do this, we install the software “usbmount”, which is specially designed to “automatically mount” USB sticks.

sudo apt-get install usbmount

After installation, a directory named “media” is created in the root directory. In it are further directories with the names “usb0” to “usb7”, as well as a link with the name “usb”, which points to “usb0”. If a USB stick is now plugged in, it is automatically mounted.

Unfortunately, one can not write to the USB sticks as a normal user. For this you have to configure a little something. To do this, open the following file.

sudo nano /etc/usbmount/usbmount.conf

Here you look for the following option:

FS_MOUNTOPTIONS = ""

and change it into

FS_MOUNTOPTIONS = "- fstype = vfat, gid = users, dmask = 0007, fmask = 0117"

Then save the file: Ctrl + O, Enter, Ctrl + X. Then reboot the system:

sudo reboot

Then you have access as a user writing on the inserted USB sticks.

To test, insert a USB stick, create and save the following file.

nano /media/usb/test.txt

Show if the file really exists:

cd /media/usb
ls

Then you can delete the file:

rm test.txt

Leave a Reply Cancel reply