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.

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.

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

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!

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.