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