How to backup DB without overloading server?

My DB is nearing 3 million posts, and I do not use fulltext searching. So my database is quite large (I do have attachments/images stored outside of the database though). I use mysqlhotcopy to do a nightly backup at 2am Central time. Problem is, my site stops responding for about 2 minutes at this time, and then for the next 3 minutes or so users get "server too busy" errors. The site is back up and running around 2:07am according to user reports.

I know this might seem like a small price to pay for a nightly backup... but my Australian visitors are getting a bit irked, as it happens every single day during their peak usage time.

My DB server is quite robust with dual opterons and 15k drives - during peak usage during the day loads never begin to reach .5. But the backup sure does overload things. Is there anything I can do to tweak mysqlhotcopy? Or some other means to do a reliable nightly backup? Thanks!

[orban]

I stop the webserver, backup, start webserver.

I don't think you can do livebackups at this sizes anymore.

[kontrabass]

Orban,

Do you have a script that stops your webserver and does the backup, or do you do all of this manually every x days?

[firstrebel]

I use mysqlhotcopy. It locks tables and backs up the db in seconds. See http://www.vbulletin.com/forum/showt...t=mysqlhotcopy

Bob

[kontrabass]

Originally Posted by firstrebel

I use mysqlhotcopy. It locks tables and backs up the db in seconds. See http://www.vbulletin.com/forum/showt...t=mysqlhotcopy

Bob

That's what I use too, but with a 5 GB database it takes at least a few minutes - and leaving the forums on during this makes the mysql server hurl

[jason|xoxide]

If you run a slave DB server you can run your backup on that without making your forums stop responding. One thing worth noting though is that it will make your forums look a bit off for a few minutes while replication backs up (new PMs show up on the count but don't appear, updated posts don't move to the top, etc.)...

[Amman-DJ.CoM]

SSH Shell Account

its can take any huge size of DB in a Minutes WithOut Touching The CPU or OverLoad Server

Ask Your Host To Activate That Service

Note: Safe Mode Must Be Off in Order To Use SSH

[zagman76]

Originally Posted by Amman-DJ.CoM

SSH Shell Account

its can take any huge size of DB in a Minutes WithOut Touching The CPU or OverLoad Server

Ask Your Host To Activate That Service

Note: Safe Mode Must Be Off in Order To Use SSH

Well - for big databases, you shouldn't be using the built-in backup or phpmyadmin...

ssh is always the way to go.

one of my boards has a db over 1GB, and I typically use a mysqldump to backup. It takes about 7 mins to complete.

I tried the mysqlhotcopy method, but I don't like that it dumps the files into a directory - makes it harder and more lengthy to download nightly.

[kontrabass]

Originally Posted by jason|xoxide

If you run a slave DB server you can run your backup on that without making your forums stop responding. One thing worth noting though is that it will make your forums look a bit off for a few minutes while replication backs up (new PMs show up on the count but don't appear, updated posts don't move to the top, etc.)...

Thanks Jason, I knew the time would come but was hoping to hold off another server purchase. Can't be avoided though. And it wouldn't hurt for searches. I just wish I hadn't signed a 2 year contract with my host. Now that they "have me", they know they can charge me up the wazoo for additional hardware. They're trying to slap a 40% overcharge on me (40% more than they charged me for identical hardware 6 months ago). Never sign a 2 year deal in this business

[eoc_Jason]

One thing I liked about EV1 (now The Planet) is there is no long-term commitment and the prices are great.

I have to agree with stopping the http service while doing the backup, or at least switching to another web server or something smaller (something that won't access the DB every connection) if you don't want your users to freak.

Be aware that eventually even a master / slave setup will not be all that optimal for searching. It alievates the table locking issues but doesn't make searching any faster, and the more posts you have the slower searching will be. The best solution I found for searching was to switch to sphinx... the best change I ever made.

You can try doing your backup at a different time. If you use MRTG to graph other stuff you can graph your forum users, then at the lowest usage point is the time to do your backups. I still have a lot of people on @ 2am... but between 4am-6am there are a lot less.