Linode does not really back up databases (unless they are purely file/disk based which most databases are not) so that's why you have to dump them to disk yourself if you want them backed up.

Oh, I see. That is critical information. Thank you for the heads up.

Well, more a snapshot in anticipation of being backed up. In other words, this addresses your earlier consistency question. A raw filesystem backup of the files mysql is using for the database is not necessarily guaranteed to be consistent. However, a dump will always be consistent as of the point when it was made. So by making a dump just in advance of the filesystem-based backup, you can be sure that when that dump file is included in the backup, it represents a consistent checkpoint of the database.

You could just as easily do it after the Linode backups ran, but then your guaranteed consistent checkpoint (as part of a Linode backup) would in effect lag one day behind the main content of the backup.

Note that if scheduling in advance, be sure to leave enough time to ensure that the dump finishes - if the dump is incomplete when the regular backup starts, it'll be useless as a snapshot. So you may need to monitor dump time as your database grows, or script it so the prior dump remains while a new dump is made, etc... Or just give it plenty of room (time wise). The only negative to scheduling it earlier is the larger time between snapshot and actual backup, but since the backup itself is only daily, unless you're archiving dumps or replicating you're going to potentially lose a chunk of recent data on a restoration anyway so a little extra time is unlikely to be material.

BTW, even without this, if you need to restore from a Linode backup, there's a good chance that the filesystem restoration will be fine, assuming your tables use a journaling engine such as InnoDB (the default as of 5.5 I believe). The whole point of journaling transactions is to maintain consistency in the presence of failures (albeit sometimes by rolling back to an earlier state). As far as that process is concerned, the backup/restore is, in effect, just like a machine crash. But system tables, even in recent releases, are still MyISAM so much less resiliant (though also changed much less frequently), plus just like after a hardware crash, something could theoretically go wrong with recovery. So it's always prudent to have a known good snapshot to fall back on, should the default restoration method fail to start.

-- David

Thank you for the detailed reply. That was really helpful.

Regarding Linode backups, is it backing up everything minus the database? And if so, when I restore a Linode backup does it not effect the database at all? So for example, if there were additional changes to the database would the database keep these changes even on a revert?

The Linode backup will backup every file on the disk - it has no concept of what files are database related and which aren't. Restoring a backup creates a new disk image with the exact contents of the backup - nothing preexisting is preserved. So the Linode backups will backup and restore the raw database files; you'll just have an additional dump file in the backup set with your own preferred database state.

Linode backups are best for disaster recovery / bare metal restorations. You can theoretically jump through some hoops to get at single files, but not easily. That's not a bad thing BTW - they're a super efficient way of knowing you can restore your system quickly to a defined state with one click. But if you want a more fine grained or frequent set of backups you'll need to layer your own on top of the Linode backups.

In order to use your dump during a restoration, you'd first issue the Linode restore, which would restore both the raw database files as well as your dump. You'd then delete the existing database (or rename or otherwise take it out of service at your preference), and restore a new copy of the database from your dump, at which point you'd enable services, which would then find the database restored to the point in time of the dump.

It's critical that you actually test a restoration procedure as part of any backup system (otherwise, how do you know it'll work when you need it most). That'll also let you get familiar with exactly what happens during a restoration. For just the database, you can test restoring a dump (you can just use a different machine or mysql instance for this). But it's best to test the whole process. Luckily that's pretty easy to do with Linode. Temporarily create a new Linode and use it as your test platform. Make believe that your main Linode has failed. Restore your backup to the test Linode and work through the process to get yourself back up and running from that clean slate state.(*) Bonus points for documenting the process for later :-)

-- David

(*) Since in this case the production Linode really hasn't failed, you do have to be careful if you have tasks that run on your Linode that originate communications to other hosts (cron jobs, task scripts, etc...). Booting the test Linode after restoration will have a different IP address so you shouldn't get unexpected inbound communication to trigger anything, but if it makes outbound connections they'll still happen. To block this, after restoration, use the Finnix recovery boot (Rescue tab in the manager) for the first boot, mount your filesystems and disable any such tasks (or just disable the network interface in general and use Lish initially). Since this can be an issue even during a regular restoration (where you may wish to ensure nothing happens until you are ready) it's a good process to become familiar with in any event.

Thank you, that was really informative. I would give you an upvote if I could. I do have one last question, is there any way to do more than 1 manual snapshot or a work around that's not too difficult?

Not as part of the backup service, no - there's just the single snapshot "slot" in addition to the automatic backup slots.

If you can afford the quota, you could always restore a snapshot to your Linode (it'll get its own disk image), and then reuse the snapshot slot. Or, if you need all of your Linode's disk quota for actual operation, creating a second Linode to hold snapshots would also work, albeit as a somewhat expensive solution unless it was short term. If your actual disk usage fits within the size limits (2GB) you may be able to use the disk imaging facility ("Create Image" while editing the disk definition) for additional snapshots.

Otherwise, at that point I think you start looking at other backup mechanisms to layer on top of the Linode service.

-- David

Down the line when you go into production look at Innobackupex to backup mysql assuming you're using InnoDB (you should be). It's online so you don't need to put a maintenance page up and it's much much faster than mysqldump for large databases.

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

@db3l: Thank you, you're awesome.

@obs: I'll check it out. Thanks!

You should not be relying on Linode for the protection of your data. The backups are useful, but only for a full restores. If it gets corrupted, then oh well, you then have nothing. Doing a snapshot is cool if you are about to make some changes, but again, I would never depend on it for MY data and code.

You should setup your own backup system as well to take the backups offline so that YOU have the data in your control.

I do both a fat finger backup (a simple tarbar of important info on the server left on the server so I can get something in case I messed up... Doing a full restore of your entire system because you accidentally deleted or messed up a single file can be a real problem and very time consuming.

And a Full remote backup which sends the backups off site in a format that I can access, file by file to restore else where if necessary. The remote backup can be done many ways, including incremental backups to speed the process up etc.. there are many options.

The responsibility of backing up your data that you can use lands on you.

_________________
Kevin a.k.a. Dweeber

No, you're dumping your running database to a file *before* linode's own backup to avoid the problem you stated in the beginning (unstable backup due to DB activity). The DB dump will be a "clean" backup. If you had to restore, you'd first restore the linode backup, then restore your DB from the dump. The closer your dump is to the time of the linode backup, the less lost data.

Another option for database backups would be to use replication and back up the slave, because AIUI you can stop/start the slave pretty much at will as long as it can keep up with the master when you finish the backup.

Since you're a new user at Linode, I'll mention that it's a good idea (with all due respect to Linode) _not_ to rely on Linode's - or any other single - backup. I also back my Linodes up with rsync to a local NAS that keeps 4 weeks' snapshots along with a nightly, among a few other smaller (more portable) backups of the most critical files that go elsewhere. You can never have too many backups...

ok got it, thanks for your post...