Okay first of all, if it matters, it's a Windows server, not linux. The issue is that backing up our database (with a mysqldump) locks the database tables for more than ten minutes and locks much of the site since the site is a database driven forum site. We're about to choose a new server and we have a choice of different RAID configurations. I heard that it's possible with some kind of RAID to stop one of the disks, to get a snapshot for a backup, including doing a mysqldump from that disk, and then when finished let it "catch up" and mirror to the working disk. (This way the site never gets locked up by doing mysqldumps off the frozen mirror disk.) Is this really possible? And if so, what kind of RAID do we need for that? We have a choice between: Software RAID 1 Hardware RAID 5 Hardware RAID 6 (but expensive) Or if I totally don't know what I'm talking about, please advise as to how we can regularly do mysqldumps (both MyISAM and INNODB tables) without locking the site. Thanks
You don't want to be playing around with your RAID configs once you've set-up a stable system. What you're proposing is sort-of technically possible, but it's a big risk and a lot of work, just to backup a database. From my own experience, you'd need to switch off the server (assuming no hot-swap on the RAID controller), remove a disk, break the raid so that the server booted up with the single disk (assuming RAID1), make your disk copy (somehow), switch the server off, put the disk back into the server, reboot the server, re-set the RAID, let the disk re-build the array to the previously removed disk. That's a bit of a carry-on and would take much more time and result in more downtime than the 10 minutes you're talking about. That's just RAID1, with RAID5 or RAID6 that would be impossible because of the parity where data is stored on 1 additional drive (RAID5) or 2 additional drives (RAID6). How large is your database to be causing it to lock for so long? We run a batch file every early morning to backup all MySQL databases on dedicated MySQL servers and it never takes more than 2-3 minutes to backup all several hundred databases. Most are small, but there are a few larger ones. Maybe it's the version of Windows that you're running....what version do you have? The only reliable way I've found to backup MySQL databases so that they are always recoverable has been via a Dump and that will always temporarily lock tables, but not for long. Some backup software will backup MySQL on the fly, but I've never used them myself...maybe someone else can confirm. Surely 10 minutes isn't that big a problem? Do it in the early hours when the site is quietest, or set-up an automated backup like we do.
Ron, thanks very much. So I guess it's inevitable that the tables will be locked during the backup/dump. I was just thinking that there must be some way that the big sites do it without any downtime or "lock time". But maybe for smaller operations we just have to live with it. The database is about 1.5 GB and constantly growing. The server OS is Windows Server 2008 R2. I did recently realize that I'm backing up to the same disk that it's reading the database from so it might be faster to write it to the other disk. Well also I allocated more ram to MySQL so maybe that will help too? I mean maybe it won't need to read as much from the disk. Pardon any ignorance here. Also I've never optimized my tables -- perhaps that will help too?
It's certainly better to live with it than not do backups at all...that's just asking for trouble. We copy the backups to the same disks too, so it can't be that. 1.5GB is quite a large DB but 10 minutes seems a little too long but not excessively so - a lot will depend on disk I/O which will be determined by native disk speeds, RAID, and disk controllers, but even a v high-end system will take a little time. Bigger companies might be clustering their databases or using an active/passive failover that makes copying less of an issue. If you're running windows 2008, why not use MS SQL 2008 R2? The Web Hosting version is very inexpensive from your host, and the express version is free. The performance of SQL 2008 R2 on windows is phenomenal. It's also a lot easier to backup as most windows backup software will backup running files using Windows VSS to lock the DB for about 1 second while a snapshot is taken. No disrespect to MySQL as it's a great database, but it isn't a patch on MS SQL 2008 (or 2012 when it's released).
Ron, hmm thanks. Well we're running vBulletin for the forum and unfortunately it requires MySQL. Though I think in future versions they're planning to make it work with different database servers so you're suggestion to use MS SQL is definitely something to keep in mind. Oh, and our server is two years old so maybe when we upgrade to a newer, faster server, the backups will be faster.