When setting up a read-only MySQL user for backup purposes, many administrators encounter the frustrating "Access denied for user to database 'information_schema'" error, particularly when using mysqldump with the --all-databases flag or tools like automysqlbackup.

The information_schema database is special in MySQL - it's a virtual database that contains metadata about all other databases. While it appears like a regular database, it requires different permissions to access.

Your initial approach with:

GRANT SELECT, LOCK TABLES ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'password';

was mostly correct but missed some key aspects regarding system databases.

Here's the proper way to create a backup user with the minimum required privileges:

-- Create user if it doesn't exist
CREATE USER IF NOT EXISTS 'backupuser'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant necessary privileges
GRANT SELECT, SHOW VIEW, RELOAD, PROCESS, LOCK TABLES ON *.* TO 'backupuser'@'localhost';

-- Special permissions for system schemas
GRANT SELECT ON mysql.* TO 'backupuser'@'localhost';
GRANT SELECT ON performance_schema.* TO 'backupuser'@'localhost';

-- This is the key permission that solves the information_schema issue
GRANT SELECT ON information_schema.* TO 'backupuser'@'localhost';

FLUSH PRIVILEGES;

To verify your backup user works correctly, try these commands:

# Basic test dump (should work without errors)
mysqldump -u backupuser -p --single-transaction --routines --triggers --all-databases > full_backup.sql

# Test with automysqlbackup
sudo automysqlbackup

• information_schema isn't normally dumped by mysqldump as it's a virtual database
• The error typically appears when using LOCK TABLES privilege but lacking explicit information_schema access
• Some backup tools attempt to lock all tables including system tables
• Adding the --single-transaction flag can often prevent the need for table locks

If you're using MySQL 5.7+ or MariaDB 10.1+, consider mysqlpump which handles system databases differently:

mysqlpump -u backupuser -p --exclude-databases=information_schema,performance_schema --all-databases > backup.sql

1. Always use strong, unique passwords for database users
2. Consider restricting the backup user to specific IP addresses
3. Store credentials in a protected my.cnf file instead of scripts
4. Regularly rotate backup credentials
5. Review MySQL's audit logs to monitor backup user activity

If you still experience problems, check:

SHOW GRANTS FOR 'backupuser'@'localhost';
SELECT * FROM mysql.user WHERE User='backupuser'\G

Remember that privilege changes sometimes require a MySQL server restart to take full effect, especially when dealing with system database permissions.

Creating a dedicated MySQL user for backups is absolutely the right security practice, but the devil's in the details. The standard approach of granting SELECT and LOCK TABLES often hits a snag with system databases.

Here's the proper way to create a backup user that works with both manual mysqldump and automation tools:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

The error occurs because:

• information_schema is a virtual database
• Standard users can't lock virtual tables
• MySQL 5.7+ enforces stricter permissions

For automysqlbackup, add this to your config:

DBUSER='backup_user'
DBPASS='strong_password_here'
DBNAMES='all' 
DBEXCLUDE='information_schema performance_schema'

If you want strict least-privilege:

CREATE USER 'min_backup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, LOCK TABLES ON your_db.* TO 'min_backup'@'localhost';

• Always FLUSH PRIVILEGES after GRANT statements
• MySQL 8.0+ may require BACKUP_ADMIN privilege
• For replication setups, add REPLICATION CLIENT