Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Reinstall MySQL (And Bring Your Grants and Users With You)

DZone's Guide to

How to Reinstall MySQL (And Bring Your Grants and Users With You)

Reinstalling MySQL while preserving permissions and users can be a headache. Fortunately, the Percona Toolkit has a way to bring what you need along for the ride.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

MySQL Grants and Users

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 the 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:
pt-show-grants > grants.sql
  1. On the target, or to restore MySQL privileges, run:
mysql  < grants.sql
  1. 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).

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
grants ,privileges ,database ,mysql ,tutorial

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}