Reinstall MySQL and Preserve All MySQL Grants and Users

Reinstall MySQL and Preserve All MySQL Grants and Users

PREVIOUS POST
NEXT POST

MySQL Grants and UsersIn this blog post, we’ll look at how to preserve all MySQL grants and users after reinstalling MySQL.

Every so often, I need to reinstall a MySQL version from scratch and preserve all the user accounts and their permissions (or move the same users and privileges to another server).

As of MySQL 5.7, MySQL does not make this easy! MySQL SHOW GRANTS only shows permissions for one user, and the method suggested on StackExchange – dumping tables containing grants information directly – is not robust (as Rick James mentions in the comments). It also doesn’t work between different MySQL versions.

This problem is easily solved, however, with the pt-show-grants tool from Percona Toolkit (which serves pretty much as a mysqldump for user privileges).  

All you need to do is:

  1. On the source, or to backup MySQL privileges, run:

  1. On the target, or to restore MySQL privileges, run:

  1. If you would like to clean up the old privileges from MySQL before loading new ones, use:

This removes all the users (except the root user, which you will need to connect back and load new privileges).

With Percona Toolkit, preserving your grants and user privileges is easy!

PREVIOUS POST
NEXT POST

Share this post

Comments (10)

  • Daniel Popiniuc Reply

    As Percona does not support Windows as operation system I’m afraid that’s not an option, so to solve similar problem in a consistent manner for multi-platforms I have developed a small PHP package: https://github.com/danielgp/rights-mysql that might be useful or at least provide an alternative.

    January 11, 2017 at 3:07 pm
    • Peter Zaitsev Reply

      Hi, You mean Percona Toolkit does not support Windows I assume 🙂 Thank you for sharing your package!

      January 11, 2017 at 3:13 pm
  • Fernando Mario Reply

    Excellent tool

    January 11, 2017 at 7:11 pm
  • lefred Reply

    mysqlpump also provides you similar functionality (http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html)

    I agree that mysqlpump didn’t currently receive the deserved attention from Community 😉

    try:

    mysqlpump –exclude-databases=% –users

    January 12, 2017 at 8:16 am
    • Peter Zaitsev Reply

      Lefred,

      Is MySQL Pump able to convert privileges between different versions of Grants Tables ? For Different MySQL versions as well as MariaDB ?

      In my experience counting on the system tables compatibility for grants is very bad practice and you should only transfer privileges as set of GRANT statements not rely on the internal presentation of those.

      January 12, 2017 at 8:37 am
      • lefred Reply

        Unfortunately myqlpump works only with version >= 5.7.8:

        Server version is not compatible. Server version should be 5.7.8 or above.

        January 13, 2017 at 4:01 am
        • Peter Zaitsev Reply

          Thanks,

          Indeed my use case might not be very clear. For me support for different versions is important. I often like to reinstall different versions such as 5.6 and 5.7 to run some tests with them and I often would like to have grants preserved.

          You may ask why I’m not using sandbox in this case or something like it or just run with no authentication for testing – this is because I prefer to do the testing in the maximally close to the production environment which means using repositories for installations, standard startup scripts and different users for different benchmarks, monitoring applications etc i’m running.

          January 13, 2017 at 8:27 am
  • Georgi Iovchev Reply

    upto 5.6 I use:

    mysql -NBe”select distinct concat( ‘SHOW GRANTS FOR ‘,quote(user),’@’,quote(host),’;’) from mysql.user;” | mysql -NB> perms.sql

    In 5.7 we have new command “show create user”, that can be used in the same way for generating create user statements:

    mysql -NBe”select distinct concat( ‘SHOW CREATE USER ‘,quote(user),’@’,quote(host),’;’) from mysql.user;” | mysql -NB > users.sql
    mysql -NBe”select distinct concat( ‘SHOW GRANTS FOR ‘,quote(user),’@’,quote(host),’;’) from mysql.user;” | mysql -NB >> users.sql

    January 13, 2017 at 8:48 am
    • Peter Zaitsev Reply

      Thanks for sharing other options 🙂

      January 13, 2017 at 8:58 am
    • Fadi El-Eter (itoctopus) Reply

      Thank you – but are you sure these work? You get something like:

      GRANT ALL PRIVILEGES ON roundcube.* TO ’roundcube’@’localhost’

      Note the quotes around the @ sign. This code doesn’t really work… Of course, this is a simple parsing issue, but I think there are other issues as well. Can you confirm that you have tried the above code successfully?

      What’s nice about the Percona tool is that it does everything in one shot. I really did want to use yours – but it just needs a little more work…

      Thanks again!

      January 18, 2017 at 3:25 pm

Leave a Reply