So this was a great big mystery this morning. How the heck do you transfer the users and their privileges out of a old mysql server and “import” them into a new server. We recently upgraded from OSX 10.4.11 to a couple of spanking new Snow Leopard servers and during the mysql export/import cycle this issue came up. Well the simple answer is…..DON’T USE mysqldump on your mysql DB (you know the default DB that stores all your users and privileges. It’s a bad idea and will probably do more harm than good. Instead use the following procedure:
1) On your old server (the one that has your data/users/tables on it) issue the following command (replace YOUR dbadmin/root username and password in the 2 appropriate places:
mysql -B -N --user=admin --password=yourpassword -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" |mysql --user=admin --password=yourpassword | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'The output of this command is something like this:
## Grants for admin@127.0.0.1 ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
## Grants for fabrik@localhost ##
GRANT USAGE ON *.* TO 'fabrik'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT ALL PRIVILEGES ON `fabrikdb`.* TO 'fabrik'@'localhost' WITH GRANT OPTION;
## Grants for nerdlogger@localhost ##
GRANT USAGE ON *.* TO 'nerdlogger'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT ALL PRIVILEGES ON `nerdlogger`.* TO 'nerdlogger'@'localhost' WITH GRANT OPTION;
## Grants for research@localhost ##
GRANT USAGE ON *.* TO 'research'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `research`.* TO 'research'@'localhost';
## Grants for admin@localhost ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD' WITH GRANT OPTION;
## Grants for root@server.domain ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'server.domain' WITH GRANT OPTION;2) Now you’re ready to selectively cut and paste the appropriate users and associated grant into a new mysql session (which you have to open) on the new server.
Goodluck….