I want a ONE liner to migrating MySQL users between servers. We know that migrating databases is easy enough using mysqldump, but migrating users from one MySQL instance or server to separate one can be annoying. Based on well known export commands, here is a neat little piped set of commands that you can run on the source DB server. The command will generate all the GRANT statements that you can simply run on the destination DB server.
|
1 |
mysql –u <ROOTUSERNAME> –B –N –p<ROOTPASSWORD> –e “SELECT user, host FROM user” mysql | awk ‘{print “mysql -u <ROOTUSERNAME> -p<ROOTPASSWORD> -B -N -e\”SHOW GRANTS FOR ” $1 “@” $2 “\””}’ |
If you are logged in as root user and do NOT need to supply user and password:
|
1 |
mysql –B –N –e “SELECT user, host FROM user“ mysql | awk ‘{print “mysql -B -N -e\”SHOW GRANTS FOR ” $1 “@” $2 “\””}’ |
Hey, lets go two better, lets run the output, and add semicolons at the end of the lines to make the output appropriate to run on the destination server.
|
1 |
mysql –B –N –e “SELECT user, host FROM user” mysql | awk ‘{system( “mysql -B -N -e\”SHOW GRANTS FOR ” $1 “@” $2 “\””)}’ | sed ‘s/$/;/’ |
The beauty of this is that you need not know the passwords for any of the mysql user accounts and are then given one block of output, instead of the commands to run to get the grants.
*Migrating all databases except mysql and information_schema:
|
1 |
mysql –u <MYSQLROOTTUSERNAME> –p<MYSQLROOTPWD> –B –N –e“SHOW DATABASES;” | grep –v mysql | grep –v information_schema | xargs mysqldump –u <MYSQLROOTTUSERNAME> –p<MYSQLROOTPWD> —databases > mysqldbdump.sql |
Originaly found on http://jasonbos.co/migrating-mysql-users-from-one-server-to-anot
Leave a Reply