How to Fix “Access Denied for user when using LOCK TABLES” Error in MySQL Dump Operations


2 views

>
>
>
>

When attempting to dump the information_schema database using mysqldump, many developers encounter this permission-related error:

>
>

mysqldump -u root -ppassword --databases information_schema > test.sql
>
>mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
>
>

>
>

This occurs because MySQL's information_schema is a virtual database containing metadata, and special permissions apply.

>
>
>
>

The information_schema database has several unique characteristics:

>
>

    >
    >

  • It's implemented as a set of memory tables and views

    • >
    >

  • Doesn't support typical DML operations

    • >
    >

  • Has special permission requirements

    • >
    >

>
>

MySQL intentionally prevents locking these tables because:

>
>

1. They're fundamentally read-only
>
>2. Locking could disrupt server metadata access
>
>3. No actual data modification occurs

>
>
>
>

Solution 1: Skip lock tables option

>
>

The simplest fix is adding the --skip-lock-tables parameter:

>
>

mysqldump -u root -ppassword --skip-lock-tables --databases information_schema > schema_dump.sql
>
>

>
>

Solution 2: Use --single-transaction

>
>

For transactional storage engines like InnoDB:

>
>

mysqldump -u root -ppassword --single-transaction --databases information_schema > schema_backup.sql
>
>

>
>
>
>

If you specifically need information_schema data:

>
>

# Direct query alternative
>
>mysql -u root -ppassword -e "SELECT * FROM information_schema.tables" > tables_info.txt
>
># For specific metadata extraction
>
>mysql -u root -ppassword -e "SHOW VARIABLES" > mysql_vars.txt
>
>

>
>
>
>

Even with skip-lock-tables, ensure your user has:

>
>

GRANT SELECT ON information_schema.* TO 'user'@'localhost';
>
>FLUSH PRIVILEGES;
>
>

>
>
>
>

For critical systems, consider:

>
>

    >
    >

  • Creating dedicated backup users with limited privileges

    • >
    >

  • Setting up automated backup verification

    • >
    >

  • Using mysqlpump for newer MySQL versions (5.7+)

    • >
    >

>
>

# Example mysqlpump command
>
>mysqlpump -u backup_user -p --exclude-databases=mysql --skip-lock-tables > full_backup.sql
>
>

>
>

This common MySQL error occurs when a user lacks sufficient privileges to execute a mysqldump operation with table locking. The specific error message:

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

indicates two key problems:

  • The user doesn't have LOCK TABLES privilege
  • You're attempting to dump the information_schema database (which is special in MySQL)

The information_schema is a virtual database that MySQL generates dynamically. You cannot lock its tables because:

  • It doesn't contain physical tables that can be locked
  • It's meant for metadata queries, not modifications
  • MySQL intentionally restricts write operations on it

Here are three working approaches:

Solution 1: Skip Table Locking

The simplest fix is to disable locking during the dump:

mysqldump -u root -ppassword --skip-lock-tables --databases information_schema > test.sql

However, this might lead to inconsistent backups if databases are being modified during the dump.

Solution 2: Grant Proper Privileges

If you must use locking, first verify and grant privileges:

mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> GRANT LOCK TABLES ON *.* TO 'root'@'localhost';

Solution 3: Exclude information_schema

For regular backups, you typically don't need information_schema:

mysqldump -u root -ppassword --all-databases \
--ignore-table=information_schema.schemata \
--ignore-table=information_schema.tables \
--ignore-table=information_schema.columns \
> full_backup.sql

For system databases, consider these alternatives:

Using mysqlpump (MySQL 5.7+)

mysqlpump -u root -ppassword --exclude-databases=%information_schema% > dump.sql

Physical Backup with Percona XtraBackup

For large databases:

xtrabackup --backup --user=root --password=password --target-dir=/path/to/backup
  • Never use --skip-lock-tables in production without considering consistency
  • information_schema changes between MySQL versions - backup its structure separately
  • For replication setups, consider using --master-data instead of LOCK TABLES