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
Output:
awesomeuser %
root 127.0.0.1
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
