Just had a quick question about backing up a large MySQL DB. I have a database that is 50gb with about half a billion entries in it. One table itself is about 40gb, the other 10gb consists of smaller tables.
The problem is, I want to back the database up and be able to keep it LIVE at the same time (as it will fall behind quickly if it's pulled for more than a few hours, as there are somewhere in the area of a million entries an hour, plus other deletions and queries). Anyone have any ideas about how to do this?
Here is a link with instructions on how one user has accomplished this. If you attempt this, please update this post and tell us how it went. I am curious to know how it performs with your "big" (aka Effing Huge) db.
Thanks for the tip, I'm going to give it a shot. It does lock all the tables once it's started, so does stop all insertions, deletions, updates but at least the site is live, it just won't have up to the minute info.
I've never really had to deal with a database of this magnitude before, and will probably be moving it to a SCSI based server shortly which I hope will drastically increase the backups - as it's only on SATA right now due to budget constraints.
It seems to rip fast through backing up the MYD (Table data files) but go a lot slower on the MYI (Table index files), I wonder why this is... If anyone has any other suggestions as well please mention them, I don't mind giving things a shot.
Just as an update, a little over 2 hours and it was backed up to a secondary hard drive - looks like this should do the trick, hopefully can cut the speed down a little later with some SCSI drives too!