Minimum MySQL User Privileges Required for mysqldump: A Secure Backup Approach

When setting up automated database backups using mysqldump, security-conscious DBAs should create dedicated backup users with minimal privileges. The fundamental requirement is the SELECT privilege, but there are important nuances to consider.

The minimum privilege grant would be:

GRANT SELECT ON *.* TO 'backup_user'@'backup_host' IDENTIFIED BY 'complex_password';

However, for more secure implementations, we should:

Restrict to specific databases
Add necessary auxiliary privileges
Consider lock-related permissions

For comprehensive backups including stored procedures and triggers:

GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER 
ON *.* TO 'backup_user'@'backup_host' 
IDENTIFIED BY 'complex_password';

For better security, limit to specific databases:

GRANT SELECT, LOCK TABLES, SHOW VIEW 
ON important_db.* TO 'backup_user'@'backup_host';

Complete setup example for a production environment:

-- Create dedicated backup user
CREATE USER 'backup_prod'@'192.168.1.100' IDENTIFIED BY 'S3cureP@ss!';

-- Grant necessary privileges
GRANT SELECT, RELOAD, LOCK TABLES, PROCESS, SHOW VIEW, EVENT, TRIGGER 
ON prod_database.* TO 'backup_prod'@'192.168.1.100';

-- Verify privileges
SHOW GRANTS FOR 'backup_prod'@'192.168.1.100';

Sample cron job using the restricted user:

0 2 * * * /usr/bin/mysqldump -u backup_prod -p'S3cureP@ss!' --single-transaction --routines prod_database > /backups/db_$(date +\%F).sql

Always use TLS/SSL for remote backups
Rotate passwords regularly
Store credentials securely (consider using MySQL option files)
Restrict source IP addresses in user grants

If you encounter "Access denied" errors during backup:

-- Check effective privileges
SHOW GRANTS FOR CURRENT_USER;

-- Verify table-level permissions
SELECT table_schema, table_name, grantee, privilege_type 
FROM information_schema.table_privileges 
WHERE grantee LIKE '%backup_user%';

When setting up automated database backups using mysqldump, security-conscious admins face a dilemma: how to grant the minimum necessary privileges while ensuring all required data gets backed up. The default approach of using root credentials poses significant security risks.

For a basic mysqldump operation, these privileges are mandatory:

GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'backup_host' 
IDENTIFIED BY 'complex_password_123!';

However, additional considerations apply:

For stored procedures: EXECUTE privilege
For performance_schema: temporary table privileges
For consistent backups: LOCK TABLES or REPLICATION CLIENT

For enhanced security, consider these strategies:

Database-Specific Grants

Instead of wildcards, specify exact databases:

GRANT SELECT, SHOW VIEW ON production_db.* TO 'backup_user'@'backup_host';

Stored Procedure Backup

To include stored procedures in your dump:

GRANT SELECT, EXECUTE ON PROCEDURE db_name.* TO 'backup_user'@'backup_host';

Here's a complete setup for a secure backup user:

CREATE USER 'bkup_agent'@'192.168.1.100' IDENTIFIED BY 'Str0ngP@ss!';
GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES 
ON production_db.* TO 'bkup_agent'@'192.168.1.100';
FLUSH PRIVILEGES;

Always verify permissions before production deployment:

SHOW GRANTS FOR 'bkup_agent'@'192.168.1.100';
mysql -u bkup_agent -p -e "SHOW DATABASES;"

Implement IP-based restrictions for the backup host
Use SSL connections for remote backups
Consider read-only replica for backup operations
Rotate credentials regularly

Error: "Access denied when using LOCK TABLES"
Solution: Add RELOAD privilege (with caution) or use --single-transaction with InnoDB

Error: Missing stored procedures in dump
Solution: Grant EXECUTE on specific procedures or the entire database