How can I setup a 'Currently Unavailable Due To Maintenance' page when doing the automated backup?

Why would the site be unavailable when the backup runs?

• Les

Basically I want it to be in a maintenance state so users can not interact with the site. This is to prevent database transactions getting backed up in a weird state.

Or do I not have to worry about this when Linode does an auto backup?

Linode's Backup Service doesn't affect anything running on the Linode when it happens. For your database, just have automysqlbackup or some such dump out the database to a file or some such once a day. Unless you have a massive DB or very heavy writes (unlikely), nothing should be affected.

• Les

I understand that Linode's backup service doesn't influence anything running when it happens. But can't the backup of the database snapshot be in an odd state if users are interacting with it as it's being backed up? I have queries which update more than one table and multiple rows at a time. If I were to use automysqlbackup, wouldn't I want to still display a page showing that the site is under maintenance during that duration?

Yes, you want to dump the DB to a file once a day.

No, automysqlbackup shouldn't impact folks using the site. The exception, as I noted, would be write-heavy operations (think payment processing or the like).

• Les

Do you mean write-heavy operations (like payment processing) would impact Linode's snapshot but not automysqlbackup? Or do you mean it would impact both if the write operations are heavy enough?

> No, automysqlbackup shouldn't impact folks using the site. The exception, as I noted, would be write-heavy operations (think payment processing or the like).

Sorry, I just needed some clarification because I'm planning on adding payment processing in the near future. Optimally never taking the page down would be the best and I was hoping that would be the case. But it sounds like automysqlbackup could be impacted by write-heavy operations. I'm not much of a server guy. I'm more of a C# guy. It sounds like I should stop user interactions while backing up the database. Do you know any good way to do this? I'm pretty open to suggestions and I really appreciate your input.

My advice to you: don't to payment processing. It's damn hard to get right, it's damn easy to get credit cards stolen, and then you're in for a world of pain. There are all kinds of fun payment processors that you can offload that to: do it.

If you actually want to take down the DB, you should look into horizontally scalable datastores such that you can take a backup without taking down the actual service of the DB. Of note, you're going to need a server guy unless you want that to end with "oh god oh god please can a server guy come save me". Highly available services are hard work.

• Les

Thanks for the detailed reply. That's good to know. At the moment it's just me working on the site in my free time, and I haven't even started the database migration yet from my local machine to Linode, but if it makes any money and makes sense to do so I'll definitely be hiring a server guy full time.

Relatively simple way of backing up the database without interruptions with this cron:

25 3 * * 4 mysqldump -u USERNAME -pPASSWORD -h localhost --single-transaction --quick --ignore-table=mysql.event --databases LIST OF DATABASES | gzip > /home/bakki/mysqld_db_dumb_thursday.gz

Just add few more lines for each day and if you want to do it every 12 hours. preferably schedule it to happen just before linodes own backup.

-Tuxie

Thanks for the reply! I'll definitely check out the documentation to understand the commands. Can you explain the reasoning behind scheduling it before Linode's own backup? Is there a disadvantage doing it after Linode's?

Well if your system fails and you need to use linodes backup to restore you would want to have the most recent database backup to be as recent as possible and not half a day or day old.

Ah, I see. So you're treating the manual mysqldump as a backup of linode's own backup. That makes sense. I'll do it that way too.

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.

@rotaercz:

Ah, I see. So you're treating the manual mysqldump as a backup of linode's own backup. That makes sense. I'll do it that way too.
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?

@rotaercz:

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?

@rotaercz:

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.

@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.

@rotaercz:

Ah, I see. So you're treating the manual mysqldump as a backup of linode's own backup. That makes sense. I'll do it that way too.

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... :o