How to backup DB without overloading server?

[kontrabass]

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/showthread.php?t=134821&page=4&highlight=mysqlhotcopy

Bob

[kontrabass]

I use mysqlhotcopy. It locks tables and backs up the db in seconds. See http://www.vbulletin.com/forum/showthread.php?t=134821&page=4&highlight=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 :D WithOut Touching The CPU or OverLoad Server :D

Ask Your Host To Activate That Service

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

[zagman76]

SSH Shell Account

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

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...:confused:

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]

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.

[jason|xoxide]

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.

That is true, I have also switched my larger sites over to Sphinx. That's not to say though that there still isn't a use for the master/slave setup. Quite a few queries get offloaded to the slave (about 20% on my sites) and you can run backups without having to take the site down.

[GHDpro]

While a slave server might be the best solution, perhaps only backing up non-recoverable data might help. Our forum DB is about 1.1 gigabyte, but without indexes (both sql and vBulletin search index tables) it shrinks by about 400 Mb to just over 700 Mb.

Obviously rebuilding the vBulletin search index will take time though, so if you need to be back online fast, don't skip it in the backup. But rebuilding the sql indexes can be done relatively fast using myisamchk.

[jwocky]

Great thread,

I use MysqlDump myself and it seems to work relativly fast and efficiently, the way i have it now, it runs the dump while my forum is online during a slow time.

Im concerned that as the foum gets even bigger doing the dump while the forum is active may cause problems here and there

So I'm wondering if anyone knows a way to turn the forum off (from the command line) and then do the mysqldump.

My cron script would

1) turn off vbulletin
2) do mysql dump
3) turn vbulletin back on

any ideas on how to do step #1 and #3?

[kontrabass]

Great thread,

I use MysqlDump myself and it seems to work relativly fast and efficiently, the way i have it now, it runs the dump while my forum is online during a slow time.

Im concerned that as the foum gets even bigger doing the dump while the forum is active may cause problems here and there

So I'm wondering if anyone knows a way to turn the forum off (from the command line) and then do the mysqldump.

My cron script would

1) turn off vbulletin
2) do mysql dump
3) turn vbulletin back on

any ideas on how to do step #1 and #3?

If you're using mysqldump, https://vborg.vbsupport.ru/showthread.php?t=126925

[TheFrienzNet]

If you're using mysqldump, https://vborg.vbsupport.ru/showthread.php?t=126925

Yup, that mod is great. 200+ users online on a regular basis with over 100,000+ posts and this doesn't lag at all. I say, take this mod!

[bigbadbob0]

I have a web server and a separate DB server. The web server is also a write-only slave to the DB server. Since it's write only and vBulletin does far more reads than writes the extra load to run this slave is minimal. I've got 750,000 posts and 6,000 unique visitors a day.

I then backup the slave, using mysqldump in a cron job, during an offpeak time. Zero impact to users.