How to Force mysqldump to Continue on Permission Errors with –force Flag


2 views

We've all been there - you're trying to dump a MySQL database when suddenly:

mysqldump: Couldn't execute 'show create table some_table': 
execute command denied to user 'some_user'@'%' for routine 'some_routine' (1370)

The dump fails immediately, leaving you with an incomplete backup. This typically occurs when your user lacks privileges for certain database objects.

The --force flag is your lifeline in these situations. While you included it in your command, it's worth understanding exactly what it does:

--force, -f
Continue even if an SQL error occurs during a table dump.

This tells mysqldump to:

  • Skip problematic tables/routines
  • Continue with the rest of the dump
  • Still report errors (so you know what was missed)

Here's an enhanced version of your command with additional useful flags:

mysqldump -u username -h localhost --port=4406 -p \
--databases database_name \
--skip-lock-tables \
--force \
--no-data \
--routines \
--events \
--triggers \
--single-transaction \
--result-file=database_name.sql

Key improvements:

  • --no-data: Only dump schema if you don't need data
  • --single-transaction: For consistent backups with InnoDB
  • --result-file: Better than > for special characters

If certain tables consistently cause issues, you can exclude them:

mysqldump -u username -p database_name \
--ignore-table=database_name.problem_table1 \
--ignore-table=database_name.problem_table2 \
> partial_dump.sql

Always check your dump file for completeness:

grep -i "error" database_name.sql
grep -i "warning" database_name.sql

And verify structure with:

mysql -u username -p -e "SHOW CREATE DATABASE database_name;"

Remember: A backup you haven't verified is no backup at all!

When running mysqldump, you might encounter errors like:

mysqldump: Couldn't execute 'show create table some_table': 
execute command denied to user 'some_user'@'%' for routine 'some_routine' (1370)

This typically occurs when your database user lacks EXECUTE permissions on stored procedures, functions, or triggers, even if they have general table access.

Many developers try the --force flag first:

mysqldump -u user -p --force database_name > output.sql

While this continues after some errors, it still fails for certain permission-related issues on routines. Here's why:

  • --force mainly handles SQL execution errors during restore
  • It doesn't bypass permission checks during the dump phase
  • Routine permissions are checked separately from table permissions

To truly handle these cases, combine these flags:

mysqldump -u username -h localhost --port=4406 -p \
--databases database_name \
--skip-lock-tables \
--force \
--skip-triggers \
--routines=FALSE \
> database_name.sql

--skip-triggers: Skips dumping triggers that might require execute permissions

--routines=FALSE: Prevents attempts to dump stored procedures/functions

--no-tablespaces: Another useful flag for some permission scenarios

If you need the routines in your dump, temporarily grant permissions:

GRANT EXECUTE ON database_name.* TO 'some_user'@'%';
FLUSH PRIVILEGES;
# Run mysqldump
REVOKE EXECUTE ON database_name.* FROM 'some_user'@'%';

For automated backups, consider creating a dedicated backup user:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EXECUTE ON *.* TO 'backup_user'@'localhost';