How to migrate mysql-user/permissions

2 November 2016

Sometimes we have to migrate some tables from one host to another. And sometimes we want to migrate users as well. This is how I managed to do this..

First let’s read out all users

mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql


awesomeuser	%
root	awesomeserver
debian-sys-maint	localhost
root	localhost

And this is how I migrated the awesome-user

mysql --batch --skip-column-names -e"SHOW GRANTS FOR 'awesomeuser'@'%'"

I cut and pasted the following output into the mysql-shell of the other server:

GRANT ALL PRIVILEGES ON *.* TO 'awesomeuser'@'%' IDENTIFIED BY PASSWORD '*766fd8db17e9dcb277aa74f3f1dee75e883b30e2' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `awesomedb`.* TO 'awesomeuser'@'%' WITH GRANT OPTION

Update for Percona-User : percona-toolkit ships with pt-show-grants

[ Sysadmin  Tricks  Database  Shell  ]
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution 3.0 Unported License.

Copyright 2015-present Hoti