(Update: Forge auto-magically redirects cron job output to a job specific file, which means my cronjob below doesn’t quite do what is expected. I’ve written a new post about how this affects this strategy because it made more sense than trying to correct/update this one.)
Now that I’ve got some data piling up in my MySQL databases, I thought it wise to start backing up my few machines. Linode has a terrific, no-frills backup service that requires basically zero configuration to get going.
The service will basically do a filewise backup—think rsync—of your server, so there are some limitations if you’ve locked your server down, or done something funky.
But what about MySQL?
Depending on what storage engine(s) you use, you may or may not be able to restore from the files that the backup service copies. You may also end up with files that are out of sync, which, while not impossible to restore from, can be terribly frustrating.
Since I don’t yet have a huge dataset, I don’t need an enterprise-ready solution. So, I figured I’d just use
Here’s a couple of tips though:
- DO NOT blindly follow the Linode instructions for setting up your cron job. You should absolutely never put your password into a cron file, or expose it on the command line in clear text. Instead, add it to a
- Use the
forgeuser to run
mysqldumpinstead of using
forgeuser was pretty much created for reasons like this, and has all the necessary MySQL permissions to dump your databases.
- Use Forge’s “Scheduler” tab to manipulate your cron tab for you without all the
First, go ahead and add your password to
my.cnf in the
forge user’s home directory.
(Here, I’m assuming no such file exists, so adjust accordingly if you already have one inlace to avoid trampling existing settings.)
SSH into your Linode (or whatever you’ve got), then:
Now make sure only the
forge user can read the file
chmod 600 .my.cnf
Add The Cron Job
Now, head over to your Forge console. The job we are going to add is this:
/usr/bin/mysqldump --all-databases > dump-$( date '+%Y-%m-%d_%H-%M-%S' ).sql -u forge
That basically tells
mysqldump to dump all of the databases ion the server to a file that starts with
dump- and will have the date and time that the dump is executed.
If you don’t want to have more than one file—because, hey, we’re also backing up the server, right—then go ahead and change the name of the file to something like
Select the “Scheduler tab in your console and enter the above command. Make sure the user is
forge and that “Nightly” is selected.
(Note: This will run the job at precisely midnight [your server time]. I you want a different time, like 2am, choose “Custom” and adjust accordingly.)
If everything looks good, click “Schedule Job” and the result should look similar to:
I’m glossing over a ton of stuff here: deleting dumps after N days, even more security, more frequent / incremental dumps, etc.
As I get closer to deploying the services I’m working on, I will do more in depth articles on some of those topics.
This is really a super-quick way to get started, and as always, there’s way more than one way to go when it comes to backups.