Backing up large databases

Hey,
What's the best way to dump a huge database? Our server tends to crash at the very end of dumping... our site isn't too huge, but still pretty big (360,000+ posts). I haven't been experimenting too much simply because of not wanting to crash the site if I mess it up
- Jesse

[Erwin]

Originally Posted by The Prohacker

http://dev.mysql.com/books/hpmysql-excerpts/ch07.html

We have several database servers for our sites, so we have a single slave server called dbbackup. It runs multiple instances of Mysql all running as slaves replicating data from their respective master. From there we have a perl/shell script locks all tables on the slave process and copies all binary files and then unlocks all tables when done.

We actually have 6 master DB servers, so its rater interesting to get everything to replicate to a single server from which you can create backups.

This eliminates the table locking issue on the master server and possibly taking your sites offline for backups.

I highly recommend getting the book High Performance MySQL which the above chapter is from..

-Mat

All I can say is wow. That's a lot of master DB servers.

[The Prohacker]

Originally Posted by Erwin

All I can say is wow. That's a lot of master DB servers.

None of them are linked with each other WHT, dBforums, HotScripts, DeletedDomains, and our ad server have their own servers with the smaller sites scattered across them to fill in the gaps. We are at capacity at this point. I would love to move to a mCluster type system, but I am skeptical. It's a job keeping replication going at times. I don't envy having to host a single site the size of yours Erwin. My biggest problem with replication is DeletedDomains database which has 2500 tables and is 150Gb.

-Mat

[Erwin]

Holy moly, you own WHT and Hostscripts? Yes, mine is a single db which has its own issues.

[DevilYellow]

at what point does it really matter?

is mysqldump still ok for DB's in the range of a few GB's?

currently I just hit 1GB and plan on splitting to two new servers in about 12 months. I am still on mysql3.

[Xorlev]

Oh boy...I recommend you get up to MySQL 4.1 at least. And mysqldump should still work, but I'm not too sure about that as my database is currently only a few hundred megabytes and not twenty gigabytes.

[DevilYellow]

I am going to to build a new DB server and run Mysql 5... then somehow re-do the current server with a new OS, new php, and maybe litespeed httpd.

I do not know how I am going to do that tho.

[Erwin]

I just did the same and run MySQL 5.1 with new OS and using lighttpd.

[kmike]

Here's an interesting backup technique: using LVM to take live snapshots of the database, (almost) without any downtime:
http://pointyhair.com/tiki-view_blog...gId=1&postId=5
http://mike.kruckenberg.com/archives...backups_u.html

[jcerious]

Originally Posted by Krofh

Thanks for the recommendations everyone... We're moving to our new server tomorrow. It's only a 300 MB database, so hopefully it shouldn't be too hard.

Only 300 Mbs - mine's only 11! I feel like a bug in your ocean!

[Wynand]

i'm letting my database back-up automatic. CRONTAB in SSH makes it automatic and removes the old ones. Every hour a backup (when forum get's bigger, possibly every minute..) and a delete of the backup of 2 days ago

Just a littel linux knowlegde :banana: