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

[The Chief]

Originally Posted by Erwin

300 mb is relatively small. My database is 20 Gb. The chances of corruption is very high using indirect methods (I know from experience!).

A databse of 20gb, wow...lol

[kerplunknet]

Erwin: You just "cp" the MySQL database(s) directory?

i.e.

cp -R /var/lib/mysql /other/cool/backup/directory

?

[Erwin]

Essentially. I do it in a script, rm -Rf the old directory, mv dir1 to dir2 etc to keep up with a whole list of backups etc., then cp.

[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.

[parafieldtower]

Hello guys, I'm in a rather serious situation here. My server has gone haywire and I can not do any SSH or FTP to it. The only thing that seems to be working is vBulletin. Everything else such as mail server or cronjobs are not functioning. What's worst is that all the database I backup every night are not accessible. What I need to do is to backup the current database (somehow) so we can re-instate the server to the beginning.

Now I have tried to download the file by using vBulletin's admin cp panel but I think because php has this timeout feature, it got to about 45MB and stopped. Database should be around 250MB. Does someone have any idea of how to do this? Can we modify this timeout feature by uploading a plugin script? I'm using vBulletin 3.5.4.

[The Prohacker]

Originally Posted by parafieldtower

Hello guys, I'm in a rather serious situation here. My server has gone haywire and I can not do any SSH or FTP to it. The only thing that seems to be working is vBulletin. Everything else such as mail server or cronjobs are not functioning. What's worst is that all the database I backup every night are not accessible. What I need to do is to backup the current database (somehow) so we can re-instate the server to the beginning.

Now I have tried to download the file by using vBulletin's admin cp panel but I think because php has this timeout feature, it got to about 45MB and stopped. Database should be around 250MB. Does someone have any idea of how to do this? Can we modify this timeout feature by uploading a plugin script? I'm using vBulletin 3.5.4.

If your server is having that many major issues, I would have your server provider mount the current disk as slave and install a new primary drive with a fresh install of the operating system of your choosing. I would then get the new server configured and start to just copy information off of the slave drive such as the database, files, logs, etc.

[COBRAws]

Originally Posted by Erwin

My database is 20 Gb.

Attachments and avatars in DB system?
is it vb.org?

xD

I just use SSH and do a mysqldump

[Erwin]

Not vB.org.

No, attachments/avatars/profilepics etc is another 20 Gb, in the file system.

[ramprage]

Prohacker how is the replication setup ? I'm not very famililar with this method.

[The Prohacker]

Originally Posted by ramprage

Prohacker how is the replication setup ? I'm not very famililar with this method.

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