Proper database backup

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
jasonf1
Registered User
Posts: 46
Joined: Mon Aug 20, 2007 11:10 pm

Proper database backup

Post by jasonf1 »

I've got a cron job that executes a daily mysqldump to backup my phpBB3 database. Should I also have binary update logging enabled?
User avatar
pentapenguin
Former Team Member
Posts: 11030
Joined: Thu Jul 01, 2004 4:15 am
Location: GA, USA

Re: Proper database backup

Post by pentapenguin »

It wouldn't hurt especially if you are dealing with critical data. I don't think there's much of a performance impact but I'm not an expert on this.
Support Resources: Support Request Template
If you need professional assistance with your board, please contact me for my reasonable rates.
jasonf1
Registered User
Posts: 46
Joined: Mon Aug 20, 2007 11:10 pm

Re: Proper database backup

Post by jasonf1 »

This brings up another question - what happens if a member of one of my forums submits a topic or profile change to the DB at the exact time the mysqldump begins? Mysqldump has --opt set by default and this locks the tables, so will that members DB update not be commited? Will there be inconsistencies?
Dan Epps
Registered User
Posts: 46
Joined: Fri Dec 14, 2007 3:36 pm

Re: Proper database backup

Post by Dan Epps »

I'm not sure about MySql but if it utilizes transaction logging, all transactions are written to the transaction log regardless of whether backups are running or not.

I use MS SQL and have full transaction logging set. I do a full database backup daily and transaction logs every hour. That prevents losing more than one hour's worth of transactions. If I lost the database I would restore from the last full backup to a point-in-time just before the crash and everything would be there.
User avatar
pentapenguin
Former Team Member
Posts: 11030
Joined: Thu Jul 01, 2004 4:15 am
Location: GA, USA

Re: Proper database backup

Post by pentapenguin »

jasonf1 wrote:This brings up another question - what happens if a member of one of my forums submits a topic or profile change to the DB at the exact time the mysqldump begins? Mysqldump has --opt set by default and this locks the tables, so will that members DB update not be commited? Will there be inconsistencies?
I think that MySQL will just cache the queries and wait until the tables are unlocked....
Support Resources: Support Request Template
If you need professional assistance with your board, please contact me for my reasonable rates.
jasonf1
Registered User
Posts: 46
Joined: Mon Aug 20, 2007 11:10 pm

Re: Proper database backup

Post by jasonf1 »

pentapenguin wrote:
jasonf1 wrote:This brings up another question - what happens if a member of one of my forums submits a topic or profile change to the DB at the exact time the mysqldump begins? Mysqldump has --opt set by default and this locks the tables, so will that members DB update not be commited? Will there be inconsistencies?
I think that MySQL will just cache the queries and wait until the tables are unlocked....
Thanks pp for your help. You're right about the caching, but I just found out that the lock applies only to the table which is currently affected by the mysqldump. So, if a sinqle query involves writing/deleting data to/from multiple tables, then it is possible that inconsistencies can arise. For example, while one table is being backed up, a different table that was already backed up during the same dump instance may undergo a change....thereby creating an inconsistency.

The seemingly right solution is to use the --lock-all-tables option -

mysqldump --lock-all-tables dbname | gzip -f > backup_filename.sql.gz

The downside to this is that a read lock is applied to all the tables in the database, effectively preventing forum users from browsing the forums while the backup is being performed. So, if one is to use the --lock-all-tables option, then they should probably perform their backup late at night when there's lower amounts off traffic on the server....probably should be performing backups late at night regardless.
User avatar
pentapenguin
Former Team Member
Posts: 11030
Joined: Thu Jul 01, 2004 4:15 am
Location: GA, USA

Re: Proper database backup

Post by pentapenguin »

Well critical actions like submitting a post are done within transactions so all the tables (topics, posts, users, search index, etc.) stay in sync. But here's an idea for you...modify your shell script so before mysqldump is called, you execute a SQL query to disable the board and set the board disabled message. Then when mysqldump is finished you can re-enable it. That way you can be 100% assured that the backup was successful, and it's more user friendly.
Support Resources: Support Request Template
If you need professional assistance with your board, please contact me for my reasonable rates.
Locked

Return to “[3.0.x] Support Forum”