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 mysql.proc

    UPDATE 2021-12-25 12:20

    According to https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_routines

    --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`
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2