When I back up the Databases...

[CitroJim]

I normally back up the FCF and BXC databases around 21:45 every evening, in the belief that a "snapshot" would be taken and forum performance would not be affected during this operation which takes about 10 or so minutes per forum.

I was wrong. This evening I was posting whilst the backup was in progress and I noted when I went to submit my post, the posting screen froze. I had a look at the backup progress and saw one of the rather larger tables was in the process of being backed up and the posting I was making at the time did not complete until the backing up of this table was finished.

The table in question was the phpbb_search_wordmatch which is a huge table containing links to every unique word in every post and is used when searching the forum. Every new posting adds new links to this table. Whilst it is being backed up, it cannot be written to, hence the freeze.

So, if the forum seems to freeze on posting, don't worry, it's just because the backup is in progress although from now on I'll time it to take place during a less active period like 0600 in the moring when activity is much lower.

My apologies for any inconvenience that may have arisen when the backups are running.

I live and learn...

[ellevie]

These backups must be a bit of a pain. It's a pity that phpbb doesn't offer some kind of incremental backup on a per post & per pm basis which would run continuously rather having to take snapshots periodically. The daily increment must be absolutely tiny compared to the database sizes, especially the FCF one which has been going for about 7 or 8 years. Most of the Citroenz site archive was lost a while back which was a pity. Even for ordinary individual users backups are important but most of us do it very infrequently.

[MikeT]

Having to perform a complete backup every time is a bit early for an April's Fools joke isn't it, or am I missing something?

[ellevie]

I presume "snapshot" means taking a backup of the complete database.

[MikeT]

Whilst it is being backed up....hence the freeze.

I missed this bit. That's a problem, and no small "bug" either. I'm sure we can find a solution.

[ellevie]

I don't think it's a full blown bug, it's just phpbb keeping things safe, but it should really issue a warning saying something like "backup in progress, please try again later".

To be honest, once you know that it's not a fault, a 10 minute delay is nothing compared to the problems we used to have.

[CitroJim]

There is a problem with the backup function of phpBB in that it's not recommended for backing up large forums. I'm happy to report that by phpBB's standards, the FCF is a BIG forum

In my experience, the phpBB backup does not work

I take backups directly from the MySQL database in the form of an SQL Dump. Essentially this writes SQL statements to a file that can be run as a script to rebuild the database. All it is in effect is a set of DROP, CREATE and INSERT INTO statements.

I've tested this backup strategy by restoring a test copy and ensuring it works and it does.

The phpbb_search_wordmatch table in the FCF is now some 2.2 million rows big so it takes a few minutes to back up. Whilst it is being backed up it is locked and cannot be written to, hence the freeze. It is perfectly normal for a table to be locked when it is being backed up, otherwise the backup could be inconsistent.

I do an ntbackup of the server once a week (yes, it's a Windows machine) to tape and archive a months' worth of Forum database backups to DVD.

I'm very fastidious over backups. I have seen too many times the fallout from not taking backups or having a duff backup strategy

To further ensure data integrity, the database lives on a RAID array.

Make no mistake, the data contained in the FCF database is priceless and I look after it very carefully.

[ellevie]

Well that baby is definitely safe in your hands Jim, no doubt about it.

You've hit the nail on the head when you say that you've tested the strategy by restoring and verifying it works ok. This idea often occurs to ordinary computer users after they have been religiously backing up their work for a period, but then they find themselves in a bit of dilemma "what if the backup doesn't work and they destroy all their data in the process." It's absolutely vital for somebody choosing a backup strategy on a new computer to do a dummy run and convince themselves that it definitely will work if ever needed.

My own strategy is pretty pathetic, I just backup my emails, docs, and pretty much anything I've generated myself to a CD from time to time. It's very far from ideal but at least it wouldn't be a total disaster if the unthinkable happened.