Does anyone have any strategies for making a full back-up of a web server's data without slowing down the server itself?
Our VPS is usually quite performant; the most notable exception is when we're backing up. We basically achieve this by rsyncing the web root directory to the back-up computer (a couple times a day to a "standby" VPS in a different physical data center, plus once a day to my desktop machine), then doing a MySQL dump of the entire database, bzipping it and piping it through an SSH connection. This latter task invariably pegs the processor of the server; incoming connections seem to "hang" until either they time out or the backing up completes and the page is served as normal. Now this only happens a couple of times a day for a couple minutes each time, so most of the time it passes without notice, but at times it can happen at inopportune moments like when we're giving a demo or training.
I've decided to do some experimenting with this, but I'd like to concurrently see if I can get feedback from others with this issue. One thing I'm going to try is removing the bzipping step; that will probably make the process take longer, but maybe the server will just be slow instead of unresponsive. I'll also see if the mysqldump and/or bzip processes can be reniced to reduce their priority, though I think I tried that in the past without success.
Comments
MySQL dump will lock the server
Setup mysql to run master/slave and get backups from the slave.
Alt: https://launchpad.net/percona-xtrabackup
I was going to say we
I was going to say we couldn't do something like this because we're only working with one box, but I guess that's not true - we could set up the "slave" on the standby VPS, even though we're only really using it for synching purposes. Hmm, I'll look into that.
The Boise Drupal Guy!
All right, I've given the the
All right, I've given the the master/slave approach a try, but it seems to be comically difficult to do according to mysql.org's docs. I found a seemingly simpler guide, but it seems to leave a lot out, and ultimately didn't seem to work.
Can anyone point me toward a straightforward guide to getting this started for n00bs like me?
The Boise Drupal Guy!
Did you try the alternative?
https://launchpad.net/percona-xtrabackup
How To's
http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_howto
http://wiki.railsmachine.com/NightlyDatabaseBackupsWithXtrabackup
Edit: No lock can be done if your only using InnoDB; if any table is MyISAM then the db will have a read lock applied.
This tool seems to only
This tool seems to only support InnoDB tables, though. Our DB is using entirely MyISAM tables (for no particular reason).
The Boise Drupal Guy!
Works on both
The tool supports InnoDB & MyISAM. Doing a "live" backup can only be done if your exclusively using InnoDB.
If you want to convert to InnoDB the dbtuner module can do the conversion for you.
For MySQL use master/slave
For MySQL use master/slave setup and run backups only from slave. For files - low I/O usage backups - use http://code.google.com/p/lsyncd/ and for remote use http://duplicity.nongnu.org/. See also: http://www.mig5.net/content/duplicity-and-rackspace-cloudfiles-api-backups
HTH ~Grace
[EDIT] It was a sync with mikeytown2 =)
lsyncd looks interesting, but
lsyncd looks interesting, but its reliance on inotify means it's a Linux-only solution (my servers run FreeBSD and my desktops run OS X). As for Duplicity, I didn't quite get what the difference between that and standard rsync is.
I'm on the tail of BSD-compatible alternatives to lsyncd, though. Not quite the problem I'm trying to solve at the moment, but interesting nonetheless.
The Boise Drupal Guy!
If you're using MyISAM, you
If you're using MyISAM, you can simply rsync the MySQL table files as well. (To be on the safe side, execute FLUSH TABLES WITH READ LOCK beforehand.)
http://httpremix.com
Is this a for-sure thing? I
Is this a for-sure thing? I remember learning years ago back when I first got into web development that I should never, ever, ever attempt to back up a database this way. How does the restore process work?
The Boise Drupal Guy!
copying raw data files...
Yes -- I learned about copying database files through the MySQL documentation. You restore by coping the files back.
http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html
http://httpremix.com
I thought the only safe way
I thought the only safe way to do this (a copy of the actual files) was to shutdown MySQL because of risk of corruption. IIRC, the MyISAM key files update after an insert or edit to the data files and if you catch a backup in the middle, that table will be corrupt.
Can someone confirm? ... or did I understand this wrong.
Mike Milano
Check out the MySQL
Check out the MySQL documentation (link above)...
http://httpremix.com
hmm .. i missed the 'to be on
hmm .. i missed the 'to be on the safe side' comment of your original reply.
what does your script look like that locks before the rsync? a shell script or something like php? i've never used rsync to backup mysql like that.
seems like the locks would be unacceptably long to fall into the "without slowing down the server" category considering the OP is backing up to a separate DC and his home computer.
Mike Milano
Bash scripts are the way to
Bash scripts are the way to go. It would execute the MySQL commands.
If bandwidth is an issue, it would be best to execute the read lock, rsync MySQL to a local backup directory, unlock the tables, and then rsync the backup directory to the home computer. There would be hardly any latency on the live server then.
http://httpremix.com
I seem to be having success
I seem to be having success taking the rsync approach without damaging any data I've noticed so far, but I don't see how to lock the database via a script using the methods described in the documentation.
This page says I want to fire up the CLI client and run FLUSH TABLES WITH READ LOCK, then keep the client open, because the lock will be released when the session ends. But how do I do that non-interactively? My Unix-fu is failing me.
The Boise Drupal Guy!
If you're doing MYISAM only
If you're doing MYISAM only and going to do read locks, what about:
http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html
Not as vigorous as master/slave, but seems to do the same as you are talking about.
offtopic: mmilano please fix
offtopic: mmilano please fix your avatar, i cant stand it . or rotate it 90 degrees left or right:D
Use Amazon EC2 w/ XFS
Another potential solution is to use a cloud-based server like Amazon EC2 w/ the XFS file system. With that kind of setup, you can initiate a snapshot of the entire disk instantaneously so there is very-little to no slow-down. Here's an article that describes how to do that: http://alestic.com/2009/09/ec2-consistent-snapshot
LVM Snapshots work great too
Sorry, I'm late to the party, but LVM Snapshots work excellent for MySQL backups. See http://www.lenzg.net/mylvmbackup/ for a canned script that will do this for you. It does require that your mysql data resides on a LVM logical volume though.