How to backup MySQL database using Mysqldump without locking?

By default, the mysqldump utility, which allows to back a MySQL database, will perform a lock on all tables until the backup is complete.

In many cases, the amount of data in the database and the uptime requirements will not allow this lock in real life. Therefore, there has to be another way.

Well, there is. You can use the Mysqldump utility with a specific flag, –single-transaction, which will allow you to backup your database tables without locking them.

Some internals on how this actually works – before the utility starts fetching data from the server, it sends it a START TRANSACTION command. This command serves few goals in this case. The first one, is to have a consistent backup created, from a specific point in time, regardless of changes that occur after the backup started. The second goal is to prevent those locks from happening, as we are performing our actions as part of a database transaction.

Please note that this flag will only work with transactional tables, such as InnoDB tables. This will not work with other types such as MyISAM and MEMORY tables. Tables of these types can change their state during the backup, as they are not transactional.

If you’re backing up large tables using –single-transaction, you should consider using it together with the –quick flag, which will allow the utility to fetch the data one row at a time and not buffer the entire row set in memory (which can fail in case your tables are too large, or if you don’t have enough memory).