Simple, automated and low cost MySQL backup strategy

Setting up a mysql backup strategy it’s hardly an exciting task, so having a simple solution it’s key to actually get it out of your to-do list.

Here’s a simple, automated and low-cost alternative that I use to keep MySQL database backups of small to medium-sized projects.

Setting up automatic backups

automysqlbackup it’s a simple shell script that automates the creation of daily, weekly and monthly MySQL backup.

It’s available on the repositories for Debian and Ubuntu. The project it’s officially hosted on SourceForge but you can also find lots of several of forks hosted on GitHub.

If you’re using Ubuntu, the installation it’s completely straightforward; all you need it’s sudo apt install automysqlbackup and you’re done.

Backups are saved on /var/lib/automysqlbackup, organized by daily/weekly/monthly directories and then by database name.

There are a few settings that you can modify on /etc/default/automysqlbackup, such as the backup dir, whether to send informative e-mails or to keep a “latest” directory.

Off-site backups

Of course, having automated backups it’s just part of the solution: you need to keep an off-site copy of your data in case your server it’s compromised.

A very simple and cheap alternative it’s using Google Drive as external storage: for USD 1.99 you get 100 GB which are shared with other Google services such as Photos and Gmail, but even the free 15 GB are plenty.

For saving your backups to Drive you can use drive, which it’s a tiny program to pull or push files to the service. There are several platform packages for various distributions.

After the installation, you need to initialize the client, so you can get an OAuth token to authorize the application access to your Drive account.

The client doesn’t do synchronization, it trusts the user to determine the authoritative version of a file or folder, which might be problematic in some cases but it’s specially useful for copying the backups, since that will automatically take care of deleting older backups —which you can still find on your “Trash” for 30 days since deletion.

You can set a daily cron job to upload your latest backups using something like this:

25 4 * * * cd /root/gdrive/mysql-backups && drive push -no-prompt

Horizontally scaling PHP applications

One of the most common worries of the enterprise IT world about WordPress and other Open Source apps it’s how you can scale it — which it’s kind of ironic when their enterprise-y web services response times are usually measured in the scale of tens of seconds…

DigitalOcean has published a high-level practical-overview on horizontally scaling PHP apps that’s a good starting point and I guess it could also apply to other kinds of apps as well.

Debugging memory usage in PHP apps

As your app gets increasingly more complex, you might run into Memory exhausted errors, and even though you can always increase the allowed memory usage — either by tweaking php.ini or locally with ini_set() — it should be a better option to find out what’s using so much memory in the first place.

Continue reading “Debugging memory usage in PHP apps”

Sharing a single WordPress codebase for multiple multisite installations

Ok; the title seems a tongue-twister, but the situation it’s:

  • One of our clients, a rather large university, needed to create independent sites for each of its faculties aaaand we love WordPress, so it was clear that we were going to use it as their CMS
  • Each of these faculties had to be able to create and manage sites for their pregrad careers, postgrads and research centers, so instead of  separate installations for every site or just one huge multisite setup, we chose to set a multisite network for each of the faculties, where they could add and manage their sub-sites as needed
  • The codebase for all the sites it’s the same; they’re all part of the same project and all features are shared. Each faculty has a default theme (which it’s a child theme from a shared parent) and they all use the same plugins
  • Every faculty lives on a different subdomain, which we could use as a unique identifier for every site, so all sites had URLs like:

At one moment we had separate WordPress installs for each faculty, but sharing a single installation (multi tenancy) has some benefits, such as:

  • On the previous setup, we needed to update various directories from an SVN repo; if something went wrong updating one of these (because someone f*cked up the permissions or something like that) we would end up with different versions on the different sites
  • Keeping a single set of WordPress files should improve performance for an opcode cache such as APC or the built-in Zend Optimizer in PHP 5.5
  • Well… it just makes sense, doesn’t it?

So, how to do it?

First, you’ll need to tweak your wp-config.php file just a little bit. What’s important here it’s:

  • Getting the subdomain for the site that the user it’s viewing, which we’ll use as a unique identifier for connecting to the database and configuring the uploads directory
  • Set wp-config to use wp-content/sunrise.php, where we can identify the blog (sub-site) that the visitor requested, and map that to the correct uploads directory
  • Define the database connection constants used by WordPress for the requested site. Since all of us in the development team had local copies and slightly different setups, we did this on a local config called local-config.php

Of course, this assumes that you need to follow some sort of convention for the names of the databases and the uploads directories, and all canonical URLs for the sites are set to the subdomain that you’ll use as your id.

Both the wp-config.php and wp-content/sunrise.php were included on the project repository, along with a local-config-sample.php that should be used to define everything that was specific to the local enviroment.

The local-config.php that’s included defines:

  • DOMAIN_CURRENT_SITE which it’s needed by WordPress on Multisite setup
  • Also, it could be used to define other constants such as WP_DEBUG and related

The wp-content/sunrise.php we used was very similar to the one used by the WordPress MU Domain Mapping plugin, but with one important difference: we needed to define BLOGUPLOADDIR for getting the uploads from the correct site and subsite.

You can check these snippets on GitHub for a working example:

Continue reading “Sharing a single WordPress codebase for multiple multisite installations”

Install Dropbox On Ubuntu Server (10 & 11)

This post will help you install the Linux Dropbox client on your headless Ubuntu Server and link it up to your Dropbox account. Unlike the process of mounting an S3 bucket we looked at before the Dropbox approach is a much better solution for sharing files. If you’re a daily Dropbox user you’ll quickly get hooked on the convenience of having your servers in the same file sharing loop as all your other Dropbox connected devices!

Originally posted on March 21, 2012 at 11:50AM at Install Dropbox On Ubuntu Server (10 & 11)