I perform daily database backups to my production database. Currently the database is small so the backup takes just a second or two. However, there’s a potential for a sudden spike in users over the coming months and I began wondering about what happens when I’m backing up my database with a mysqldump? Are users locked out of the database during that process? Is there some some sort of “queue” that gets handled after the dump? Currently I use shared hosting which may or may not affect the response.
If you don’t use the --lock-tables parameter then the database will not be locked while the backup is running. Any updates in progress while the backup is being made have the potential to cause problems with only part of an update being recorded in the backup.
Provided all the tables support transaction processing (eg. you only use innoDB tables) then you can apply a --single-transaction flag to the backup and it will generate a checkpoint prior to starting the backup and only backup to the checkpoint. Any updates that occur while the backup is running will be after the checkpoint and not backed up.