Proper database backup

jasonf1 · Post by **jasonf1** » Sat Dec 15, 2007 6:03 am

I've got a cron job that executes a daily mysqldump to backup my phpBB3 database. Should I also have binary update logging enabled?

pentapenguin · Post by **pentapenguin** » Sat Dec 15, 2007 6:55 am

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.

jasonf1 · Post by **jasonf1** » Sat Dec 15, 2007 3:48 pm

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 · Post by **Dan Epps** » Sat Dec 15, 2007 3:56 pm

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.

pentapenguin · Post by **pentapenguin** » Sat Dec 15, 2007 7:37 pm

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

jasonf1 · Post by **jasonf1** » Sun Dec 16, 2007 7:22 am

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.

pentapenguin · Post by **pentapenguin** » Sun Dec 16, 2007 8:26 am

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.

advertisements