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:
- On the source, or to backup MySQL privileges, run:
pt-show-grants > grants.sql
- On the target, or to restore MySQL privileges, run:
mysql < grants.sql
- If you would like to clean up the old privileges from MySQL before loading new ones, use:
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!