1

I want a complete backup of my server. It's CentOS 5.

My thoughts: I can literally take every single file on the server and back it up, and I'll have a backup of everything. Server config, my PHP files, MySQL DB, EVERYTHING... is this an accurate assumption?

I was going to backup MySQL additionally, but perhaps that's not necessary since the DB files are included in my Server backups?

Further... I'm trialing RackSpace Server Backup. This basically takes every file on my disk, and backs it up over SSL, and encrypts the data on the backup disk. I can run it every day, and only "updated files" are sent to the backup destination.

Thanks for the help here...

Shackrock
  • 208
  • 6
  • 16
  • So what is your second question regarding your "further" remark? – Nils Aug 21 '11 at 19:31
  • @Nils, I guess I was trying to figure out if this is a good method (or not really)... I can schedule the backups to occur at any interval (meaning it checks for new timestamps, and the new time stamps are written over to the backup location). I think if I have daily MySQL backups, I'm fine with it. If I lose everything, I can just copy those folders back over? – Shackrock Aug 21 '11 at 19:43
  • Differential file-based-backup with databases will most propably backup the whole files containing your data - even if a single bit was changed. Unless you`ve got a backup method that actually compares file differences and can transfer partial file changes. – Nils Aug 21 '11 at 20:21

2 Answers2

2

I can't agree with Nils. I admit that I can't speak to MySQL, but I can say from experience that that is not true of Oracle.

Taking a snapshot of the database files on disc is fairly meaningless even if it's instantaneous. The DB contains many transactions, some of which are nearly completely flushed to disc, some of which are partially flushed, and some of which remain almost entirely in RAM. What's on-disc may be recoverable with DB tools, but it's unlikely to be a workable database.

Worse still: for any sizeable database, backups will take some time, and that means disc-only backups are downright useless. Think of it as a very blurry snapshot; you photograph something moving quite fast (your DB) and you hold the shutter open for the time required to take the backup (20 minutes? 2 hours? 28 hours? (for some of my production DBs)). The photograph will be very blurry; you'll catch some tables early, with few transactions committed to disc; others you may catch many hours later, with some hundreds of thousands more transactions on-disc by then. I can't say that your disc backup won't work straight-off; I can't even say that you won't be able to recover from it with DB forensic tools; but I wouldn't trust my job to it.

If your FS supports snapshots, and your DB supports hot-backups (or equivalent), those can work. Hot-backup is an instruction to the database to quiesce the tablespaces; it buffers all pending transactions on some kind of logfile, and applies them en bloc when you take the tablespaces out of hot backup state. This can work while you quiesce the tablespaces and back them up to tape, but it works an absolute treat if you can quiesce the tablespaces, snapshot the underlying filesystem, then release the tablesapces from hot backup and roll the snapshot off to tape.

If you can't manage that - and I often can't - then what I do is tell the backup software to ignore the datafiles entirely, and have MySQL run mysqldumps to online storage first, then roll that off to tape. I've never had a problem recovering from those.

But as I always add in these cases, whatever you do, you should document it well and test it frequently. That last is more vital than any other tip. If you know it works, who cares what you're doing? You can back up using zip to WORM archives written on the wing-cases of cicadas, if it's known to work reliably. But if you aren't testing it, who cares what the talking heads of serverfault say? It's not fit for purpose.

MadHatter
  • 79,770
  • 20
  • 184
  • 232
  • Interesting. I was actually completely considering what you stated there about first dumping, then backuping up the dumps with a service like rackspace. After reading this I will absolutely do that. Question: Can I automate MySQL dumps from within PHPMyAdmin? Or, do I need to write a cron job for this? – Shackrock Aug 21 '11 at 19:58
  • I don't know phpmyadmin, but you can automate it from cron, for sure. Personally, since I'm toweringly lazy, I have the backup software kick off the SQL dumps, then roll them off to tape as soon as they're done. – MadHatter Aug 21 '11 at 20:00
  • haha. What backup software? – Shackrock Aug 21 '11 at 20:02
  • In my case, bacula (http://www.bacula.org). It does everything stock Netbakups can do, but it's ugly (being free). I love it, and it has saved my arse frequently. – MadHatter Aug 21 '11 at 20:03
  • @Mad Hatter: Oracle is far more complex than mySQL. And I did not say that it is perfect to use file-based backups. mySQL does not write-cache much (as opposed to Oracle). – Nils Aug 21 '11 at 20:19
  • Nils: I completely agree, you did indeed note that, and I apologise if I might appear to be devaluing your answer. I have had bad experiences trying to restore both Oracle and MySQL databases from simple disc dumps, but - as I tried to say - that does **not** mean it can't be done. It's important for the OP that (s)he hears from people who do one thing, with honest reports of how they got on, and from people who do something else, with honest reports of their experiences in turn. **All** informed comment is valuable, I think - yours and mine both. – MadHatter Aug 21 '11 at 20:26
1

Basically yes - everything is a file. But: the files on disk cntaining the data my be inconsistent due to the fact that some data is still in RAM-cache and has not yet been written to disk.

So I you backup a DB under heavy load just using file-backup you might loose some transactions. The safest way to have a complete backup by file is shut down the DB, sync the disks and then start the backup. Or use DB-means to backup the database contents to disk - these backups will be - more or less - consistent. In mySQL you can choose different backup modes...

EDIT: Try mysql-Administrator (if you have it available). There you can specify backup-jobs via GUI. The result will be a corresponding backup-command as cron-job using your backup-specification.

Nils
  • 7,695
  • 3
  • 34
  • 73
  • ok, I see... I am trying to automate the backup as best I can, and not ever take things down. I think for my purposes, this might be good enough... – Shackrock Aug 21 '11 at 19:42
  • if you want to take a DB backup without stopping the live DB you need to take the backup from a slave – goo Aug 21 '11 at 19:55
  • @Geoff: You`ve got the same problem with the slave: You need to make a consistent snapshot. And you have to have space for the slave. And the slave will slow down your system. I would use mysqldump with some options for generating a consistent dump. – Nils Aug 21 '11 at 20:16
  • no. Stop the slave, run your backup from the slave, restart the slave. – goo Aug 21 '11 at 20:20
  • @Geoff: So you make it consistent by stopping the slave. What about the other drawbacks? – Nils Aug 21 '11 at 20:25
  • A slave will be a small overhead to the master DB. If you want to take a backup without having an outage you have to accept this overhead. – goo Aug 21 '11 at 20:33
  • What about the mysql-dump-stuff? Is the locking during the dump-progress so bad that you would not recommend that either? – Nils Aug 22 '11 at 21:33