Does mysqldump lock entire database or just a table by table?

I'm wondering if during a mysqldump executed in the following manner

mysqldump --all-databases --master-data > dump.sql

all tables are locked for the entire process, or just a table by table? In other words does the mysqldump interrupts (suspend) all writing queries durring the process, or only those that are trying to modify the table being exported currently? Are those querries interrupted or suspended?

I have a large database serving continuously an app used by customers. It's about 50GB and will take hours till the dump is done. I cannot stop the database and need to dump it without affecting much its working capacity. I plan to use that dump to replicate the database on another server under master-slave strategy. FYI, the binary log is on and server_id is set.

+-------------------------------+
| version()                     |
+-------------------------------+
| 5.6.14-1+debphp.org~precise+1 |
+-------------------------------+

Yes, mysqldump must lock all tables (it uses FLUSH TABLES WITH READ LOCK) if your database uses non-transactional tables. This is to ensure a consistent state of data in the backup.

A better practice is to use InnoDB tables, which support transactions, and run the dump with:

mysqldump --single-transaction ...other options...

Using a transaction allows the mysqldump to run as long as it needs to, without locking the tables. It can ensure a consistent state of the data it dumps by virtue of the repeatable-read transaction isolation.

Other clients can continue to read and write any tables in the database in the meantime.

InnoDB is the default storage engine in MySQL 5.6.14 (which, FWIW, was released 2013-09-20, so it's more than ten years old by now, and no longer supported).

StackExchange Database Administrators Q#337818, answer score: 4