Why does mysqldump not backup procedures?
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?
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
UPDATE 2021-12-25 12:20
According to https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_routines
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`