Page 1 of 1

Proper database backup

Posted: Sat Dec 15, 2007 6:03 am
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?

Re: Proper database backup

Posted: Sat Dec 15, 2007 6:55 am
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.

Re: Proper database backup

Posted: Sat Dec 15, 2007 3:48 pm
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?

Re: Proper database backup

Posted: Sat Dec 15, 2007 3:56 pm
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.

Re: Proper database backup

Posted: Sat Dec 15, 2007 7:37 pm
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....

Re: Proper database backup

Posted: Sun Dec 16, 2007 7:22 am
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.

Re: Proper database backup

Posted: Sun Dec 16, 2007 8:26 am
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.