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?

Improve this question
7
  • Why you decide that mysqldump stores privileges?
    – Akina
    Jul 29, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 8:21

1 Answer 1

Reset to default
2

You need to add the --set-gtid-purged=OFF flag to your mysqldump statement. This will stop it from writing the set-gtid-purged line in the dump file.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.