In 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 |
pt-show-grants > grants.sql |
|
1 |
mysql  < grants.sql |
|
1 |
pt-show-grants --drop  --ignore root@localhost | grep "^DROP USER " | mysql |
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!
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.
Hi, You mean Percona Toolkit does not support Windows I assume 🙂 Thank you for sharing your package!
Excellent tool
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
Thanks for sharing other options 🙂
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!