Assuming that a backup takes 15 minutes, what happens to an INSERT
query during a MySQL backup? Does it end up in the backup? What happens to the new row?
I got asked this question on an interview.
Using mysqldump
, it really depends on the options you use. The --opt
tag is common (shorthand for a bunch of options) and contains --lock-tables
, which according to the docs:
For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.
So if you don't lock at all you can have inconsistent tables. If you use --lock-tables
you can have inconsistent databases.
It also depends on whether you're using MyISAM or InnoDB.
If you're backing up from a slave, --lock-all-tables
is a better option.
Generally it's a bad idea to lock any of your production databases.
You should also keep in mind backing up from a slave can also be dangerous as well! You have to monitor the slaves replication progress and make sure it doesn't get out of sync. And if the replication slave uses "MIXED" or "STATEMENT" mode the replica itself can be inconsistent from the master, depending on the queries used (check the error logs).
There's also an option to do a "hot backup" with XtraDB, an improved version of InnoDB.
Some people also do delayed replication. There's also a page here on MySQL backup methods.
As far as what happens to the inserted row during the backup process: If the table is locked an INSERT / UPDATE / DELETE query will hang and eventually timeout if the lock is held too long. The timeout interval depends on the configuration.
I highly recommend reading the mysqldump documentation carefully.