Modifying mysqldump to Remove DEFINER and Add Progress Display for MySQL 5.7

When restoring MySQL backups that contain views, functions, stored procedures, triggers, or events, the operation often fails with ERROR 1227 (42000) because the import account lacks the SUPER privilege required to create objects owned by other accounts.

Typical scenarios include restoring RDS instances (which do not provide SUPER) and migrating from a development database to a production environment with different account permissions.

Two main remediation methods are suggested: (1) change the object owners in the source database to the import account or set SQL SECURITY to INVOKER ; (2) export the dump with mysqldump and replace the original owners in the SQL file.

The article then dives into MySQL's permission model, explaining that objects have a DEFINER and a SQL SECURITY attribute, which determine whose privileges are used during execution. If the DEFINER does not exist and the object uses DEFINER security, queries will raise ERROR 1449 (HY000) .

To solve the DEFINER problem without manual SQL editing, the author proposes modifying the mysqldump source code. The goals are to automatically strip DEFINER clauses from views, functions, procedures, triggers, and events, and to add a simple progress indicator similar to the --verbose output.

Because MySQL 5.7.27 and later share the same mysqldump.c , the author chose this version for the changes, noting that MySQL 8.0 has a completely different implementation.

The backup order of mysqldump is described, highlighting that temporary views are created first to resolve dependencies before the real views are dumped.

Source code modifications include:

Adding a new print_dump_msg() function that mimics verbose_msg() for session‑level output.

Inserting row‑count printing into the dump_table loop, with a configurable frequency (e.g., every 10,000 rows) to limit performance impact.

Removing DEFINER clauses:

Relevant code snippet (original definitions) is shown below:

--视图定义
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test
--函数定义
CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER
--存储过程定义
CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER
--触发器定义
CREATE DEFINER=`root`@`%` trigger t_test
--事件定义
CREATE DEFINER=`root`@`%` EVENT `e_test`

After recompiling the modified mysqldump , the backup process prints detailed progress information and the generated SQL no longer contains DEFINER clauses, solving the import errors.

Performance testing with sysbench showed that printing a message for every 10,000 rows adds negligible overhead, while printing for every row severely degrades speed. Additionally, building the binary without debug symbols restored normal backup speed.

In conclusion, directly patching the mysqldump source effectively resolves the DEFINER issue, provides useful progress feedback, and can be adapted for cross‑database migrations by adjusting type mappings and syntax.