When attempting to back up MySQL databases using mysqldump, many administrators encounter a puzzling error:

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

This error occurs specifically when dumping the mysql system database in MySQL 5.5.8 and later versions. The cond_instances table is part of MySQL's performance schema implementation, and even the root user might not have sufficient privileges by default.

Here are three proven solutions to bypass this issue:

Option 1: Skip Performance Schema Tables

mysqldump --skip-lock-tables --ignore-table=mysql.cond_instances mysql > mysql_backup.sql

Option 2: Grant Specific Privileges

GRANT SELECT, LOCK TABLES ON mysql.cond_instances TO 'root'@'localhost';
FLUSH PRIVILEGES;

Option 3: Complete Performance Schema Solution

GRANT ALL PRIVILEGES ON performance_schema.* TO 'root'@'localhost';
GRANT ALL PRIVILEGES ON mysql.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

If the above solutions don't work, consider these alternatives:

Using mysqlpump (MySQL 5.7+):

mysqlpump --exclude-databases=performance_schema mysql > backup.sql

Physical Backup with Percona XtraBackup:

xtrabackup --backup --target-dir=/path/to/backup --datadir=/var/lib/mysql

The behavior varies across MySQL versions:

• MySQL 5.5: Requires explicit privileges for performance schema tables
• MySQL 5.6+: Improved permission handling but may still need configuration
• MySQL 8.0: New privilege model with more granular control

To avoid similar issues in the future:

# Create a dedicated backup user with proper privileges
CREATE USER 'backupadmin'@'localhost' IDENTIFIED BY 'securepassword';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, 
PROCESS ON *.* TO 'backupadmin'@'localhost';
FLUSH PRIVILEGES;

---

When working with MySQL 5.5.8, you might encounter this frustrating error during backup operations:

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

Despite having root privileges, MySQL is denying access to performance_schema tables (like cond_instances) during backup. This typically happens because:

• The performance_schema database has special permission requirements
• There might be version mismatches between server and client tools
• Default configurations in MySQL 5.5.x handle these tables differently

Here are several proven solutions:

# Solution 1: Skip performance_schema tables
mysqldump --skip-lock-tables --ignore-table=mysql.cond_instances -u root -p mysql > backup.sql

# Solution 2: Alternative backup approach
mysql -u root -p -e "SHOW TABLES FROM mysql" | grep -v Tables_in | \
xargs mysqldump --skip-lock-tables -u root -p mysql > selective_backup.sql

# Solution 3: Upgrade your backup strategy
mysqldump --single-transaction --skip-lock-tables -u root -p --all-databases > full_backup.sql

For a long-term solution, modify your MySQL configuration:

# Add to my.cnf or my.ini
[mysqld]
performance_schema=OFF

Then restart MySQL:

service mysql restart

If you specifically need performance_schema tables in your backup:

# Grant specific privileges
mysql -u root -p -e "GRANT SELECT ON performance_schema.* TO 'root'@'localhost';"
FLUSH PRIVILEGES;

# Then backup with specific parameters
mysqldump --skip-opt --single-transaction performance_schema > perf_schema_backup.sql

Always validate your backups with:

mysql -u root -p -e "CREATE DATABASE backup_test;"
mysql -u root -p backup_test < backup.sql
mysqlcheck -u root -p backup_test