1

When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql schema have no user privileges. (eg. Schema "abc" has the correct user privileges for routines but in schema "qrs" they are missing.)

The dump I am using is created by:

mysqldump --allow-keywords --all-databases --single-transaction --events --routines --flush-logs --flush-privileges --hex-blob

When I have run into this issue in the past I would load the dump and then reload just the mysql schema, which then gives the missing users privileges on the routines.

However recently I have started using GTIDs and as a result I can't reload the just the mysql schema from the dump as the GTIDs overlap, as when I try to do so i get this error:

@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

Is there a way to reload a dump made this way and have all the user permissions be exactly the same after reloading the dump on another server?

  • Why you decide that mysqldump stores privileges? – Akina Jul 29 at 15:45
  • @Akina The procs_priv, tables_priv etc in the mysql schema stores the privileges which are dumped when --all-databases is specified as an option. – Sam Pearson Jul 29 at 15:48
  • MySQL 8.0 Reference Manual / ... / mysqldump — A Database Backup Program As of MySQL 8.0, the mysql.event and mysql.proc tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using --all-databases, use the --routines and --events options explicitly. – Akina Jul 29 at 15:53
  • And later: --routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. ... The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the routines. And no words about privileges. – Akina Jul 29 at 15:54
  • @Akina The data dictionary tables may not be dumped thus nessitating the use of --events and --routines, but the dump of the mysql schema from specifying --all-databases does include the proc_priv table which contains the privilages for routines. When using in conjunction with --flush-privilages whis writes a FLUSH PRIVILEGES statement which should restore the user priviliges from the dumped server, which it does for all tables and some of the routines. But I am trying to get it to restore the privileges for all routines so that I can dump from a sever and to get another in an identical state. – Sam Pearson Jul 30 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.