mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES

# mysqldump -u root -p performance_schema > performance_schema.sql

Enter password:

Problem:
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES

Solution:

By default, in order to ensure the data is in a consistent state during the dump, mysqldump will lock all the tables included in the dump. This means that your databases will only be able to perform read queries.

Since InnoDB has support for transactions, we can use the flag --single-transaction to let mysqldump put everything in a transaction, read the database in the current state and create a consistent data dump.

# mysqldump -u root -p --single-transaction performance_schema > performance_schema.sql

Enter password:

# ls -lrt per*

-rw-r--r--. 1 root root 29792227 Oct 18 14:09 performance_schema.sql