mysqldump: insufficient privileges to show create function 'function name'

Question

I want to dump my database with function and procedure in mysql workbench. I could dump my DB without functions and procedures.but when I try to export the data with procedures, it showed me the error like:

"mysqldump:user has insufficient privileges to show create function function name"

Answer 1

To dump a database also with stored procedures, you must grant SELECT rights to mysql.proc:

Execute this statement once as root:

GRANT SELECT ON mysql.proc to 'your-mysqldump-user';

Replace your-mysqldump-user with the real username.

Then flush the privileges:

FLUSH PRIVILEGES

Now you should be able to dump the database including stored procedures with your mysqldump-user.

UPDATE: as @Michael pointed out in the comments, FLUSH PRIVILEGES isn't nesseccary in this case