mysqldump not dumping stored procedures

Question

I had executed the following command :

mysqldump -u root db_name --add-drop-database --routines --verbose > db_name.sql 2>db_name.log

But when I checked the sql file content, there were no stored procedure creation syntaxes. I also checked the log file and received the same result.

Anyone know what is the root cause of this issue? I have already googled around and found this one https://github.com/sequelpro/sequelpro/issues/517. But still there's no workaround.

FYI, I'm using MariaDB 10.2.

Answer 1

You might be missing a permission:

· --routines, -R

Included stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MariaDB account that has appropriate privileges for the mysql database.