(Note: This was done on Ubuntu 15.10, but should apply to other Linux distros as well)
I’m running a local WordPress installation for my personal diary, and I was looking for a way to automatically back up the database. On my WordPress sites that are online and hosted with my service provider, such as this one, I’m using a great plugin for the task, WP-DB-Backup by Austin Matzko. Works great and does exactly what it says. You can manually back up or have a regular backup sent to you per e-mail. For my local installation, however, this is not an option since I don’t want to install and configure a mail server as well. And even if I did, the file would still be in a local e-mail, which would make backing it up to Dropbox all the harder.
After quite a bit of digging and experimenting I got it set up the way I wanted. (Actually, installing the mail server might have been faster 😝. However, I have to admit I was totally enjoying myself. And there’s nothing like success to boost your spirits.) So, here it is in full. (Note: The encryption part is only meant to keep my diary somewhat private on Dropbox. This process uses some locally stored passwords, so if you’re worried about the NSA stealing your nudies directly from your computer, then this is not for you 😉.)
Step one: Backing up the database
This is done through
mysqldump. I found a nice tutorial for this located here, with a more in-depth explanation of mysqldump here, so I don’t need to write that out again. I ended up trying this line in /etc/crontab:
(Obviously MYSQLPASSWORD needs to be replaced with the actual MySQL password. And no, there really is no space after -p. See man mysqldump)
00 08 * * * root mysqldump -u root -pMYSQLPASSWORD wordpress | gzip > /media/me/Data/backup/mysql/wordpress_gzipped.sql
Except instead of 00 08 I used a time that was coming up, like 26 17 or whatever was two minutes away at the time. This worked exactly as explained in the tutorial. The only downside to this approach is having to settle on a fixed time since there really isn’t one particular time of the day where my computer is always on. I don’t really know enough about cron to know if that’s an issue or not, and in any case I wasn’t done. This would only leave me with a file on the same hard disk as my WordPress installation, which is precisely what I wanted to avoid. So the next logical step was copying the file to my Dropbox folder.
[Update:] In order to make this safer, I just changed this to the wordpress user instead of the mysql root user, so it would look like
root mysqldump -u wordpress -pMYSQLPASSWORD wordpress | gzip > /media/me/Data/backup/mysql/wordpress_gzipped.sql. In order for this to work, the user wordpress needs to have the “Lock tables” permission added. [/Update]
Step Two: Copying to Dropbox
Note: Originally I had all this code in the /etc/crontab file, but when it was getting too long I simply put a script in /root/bin. Everything following gets put in that script.
Now that we have our local backup called wordpress_gzipped.sql, we need to copy it over to Dropbox. First, I created a Folder ~/Dropbox/Backup/Wordpress. Without the encryption, the copying process is quite simple. Since this script is being executed as root, we need to run this command through sudo, otherwise the files in the Dropbox folder will be owned by root, which is obviously very undesirable:
sudo -u me cp /media/me/Data/backup/mysql/wordpress_gzipped.sql /home/me/Dropbox/Backup/Wordpress
Step Three: Encryption with encFS
I’m using encFS for the encyption. There are tons of great tutorials out there for that, so I’m most definitely not going into details here 😉.
Let’s assume for the purpose of this tutorial you have your encrypted folder under ~/Dropbox/.foo and mount it into ~/Foo.
I started out mounting ~/Foo manually and then figuring out the copy process. Using a slightly modified version of the cp command above, I copied the file into the encrypted folder instead of the regular Dropbox folder:
sudo -u me cp /media/me/Data/backup/mysql/wordpress_gzipped.sql /home/me/Foo/Wordpress
This time the
sudo command is an absolute necessity because even root can’t access the encrypted storage. At least ended I up with a permission denied error otherwise.
Step Four: Auto-mounting the encrypted file system specifically for this task
This was the tricky part because of the password prompt for mounting an encFS folder. I found several solutions online, but for some people said they didn’t work, others seemed overly complicated for this task. I did stumble over the
--extpass option for encfs, and while I initially thought this would not be helpful, it ended up being exactly what I needed. (Here comes the part that would make any security professional cringe, but for this particular purpose I really don’t care.)
It is possible to mount your encrypted folder using this command:
encfs --extpass="echo 'encfs-password'" /home/me/Dropbox/.foo /home/me/Foo
Again, since the script is being executed as root, we need to send the above command through sudo:
sudo -u me encfs --extpass="echo 'encfs-password'" /home/me/Dropbox/.foo /home/me/Foo
The same goes for unmounting:
sudo -u me fusermount -u /home/me/Foo
Note: It is important in this script to always use the full path to the user home folder, e.g. /home/me/Dropbox, instead of the ~ shortcut. This is because the script is executed as root, and even when using sudo -u me, ~ still points to /root.
Step Five: The wp-content folder
Of course your WordPress blog doesn’t only consist of the blog entries stored in the database. You’ll also want your uploads, and possibly themes and plugins, too, so we’ll need one more copying process.
Now, in this case,
cp would not be the best choice since it would overwrite everything that’s already there and create new timestamps, so Dropbox would constantly overwrite files on the server that haven’t changed. After a little more digging, I decided that rsync would be the best solution. The final line of code:
sudo -u me rsync -tr /media/me/Data/srv/wordpress/wp-content /home/me/Foo/Wordpress
Putting it together
The final script looks like this:
#!/bin/bash # mount the encrypted file system as user me sudo -u me encfs --extpass="echo 'encfs-password'" /home/me/Dropbox/.foo /home/me/Foo # Export the database mysqldump -u wordpress -pMYSQLPASSWORD wordpress | gzip > /media/me/Data/backup/mysql/wordpress_gzipped.sql # copy the exported database to encrypted file system as user me sudo -u me cp /media/me/Data/backup/mysql/wordpress_gzipped.sql /home/me/Foo/Wordpress # copy wp-uploads als user me sudo -u me rsync -tr /media/me/Data/srv/wordpress/wp-content /home/me/Foo/Wordpress # unmount the encrypted file system sudo -u me fusermount -u /home/me/Foo
I saved the final file as /root/bin/wp-backup. Don’t forget to make it executable.
Now, getting back as to when this script should be run. I thought the easiest thing to do would be to simply run it once at boot since I didn’t feel like getting into all the technical details about cron and init.d (in order to run it before shutdown.) The simplest solution I came across uses
cron @reboot. For this to work, I put a file into /etc/cron.d called wp-backup. It only needs one line:
@reboot root /root/bin/wp-backup
Now everything is set up and should work. It did for me 😜.
As I said, this is not a solution for the paranoid, since there are actually two passwords saved in a plain text file, the mysql password and the encfs password. But in this case, it’s really not that important, since I only really care about the encryption on the Dropbox server. It’s not a matter of national security, and anybody with access to my computer would have access to the diary anyway.