Why does mysqldump not backup procedures?

Question

I am running MySQL 8.0.23 and try to backup/restore procedures. Therefore I created a user "dump@localhost" with the following rights:

> Grants for dump@localhost
> GRANT PROCESS ON *.* TO `dump`@`localhost` 
> GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON `mydb`.* TO `dump`@`localhost`

Creating a backup like this:

 mysqldump -u dump -p -n -d -t -R mydb > test.sql

The command executes but no procedures are within the sql file.

--
-- Dumping routines for database 'mydb'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-12-25 17:21:39

However, if I use my own user account instead of "dump" or also using root, it will dump the procedures.

According to the MySQL 8 docu the provided rights should be sufficient: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions.

A typical procedure of mine looks like this:

create
definer = root@localhost procedure SUM_prices_d()
INSERT IGNORE INTO SUM_prices_d
...

What rights am I missing?

Answer 1

There is a very strong likelihood you need one more grant:

GRANT SELECT ON mysql.proc TO `dump`@`localhost`;

REASON : Dumping Stored Procedures requires reading from mysql.proc

UPDATE 2021-12-25 12:20

According to MySQL Documentation, you need global SELECT privileges

--routines, -R

Include stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege.

The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines.

You may need to adjust grants as follow

GRANT PROCESS,SELECT ON *.* TO `dump`@`localhost`

or create a separate user for the sole purpose of dumping stored procedures

GRANT PROCESS,SELECT ON *.* TO `dump_storedprocs`@`localhost`