EmergencyEMERGENCY? Get 24/7 Help Now!

Reinstall MySQL and Preserve All MySQL Grants and Users

 | January 11, 2017 |  Posted In: Insight for DBAs, MySQL

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
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

10 Comments

  • 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.

  • 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

    • 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.

      • Unfortunately myqlpump works only with version >= 5.7.8:

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

        • 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.

  • 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

    • 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!

Leave a Reply

 
 

Percona’s widely read Percona Database Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.