MySQLUserclone或只是複製的MySQL用戶

Original post: http://anothermysqldba.blogspot.com/2014/04/mysqluserclone-or-just-copy-mysql-users.html

我最近跨越跑了MySQL的論壇發帖說一直在尋找如何將用戶遷移到新的系統。

雖然這可能是做了許多方法，我想這給了我一個機會發揮和演示的mysqluserclone工具。

所以，我有兩個數據庫，只有一個Wiki用戶：


root@localhost [(none)]> select VERSION();
+------------+
| VERSION() |
+------------+
| 5.6.10-log |
+------------+
root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+

select VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.30-log |
+------------+
show grants for wikiuser@localhost;
ERROR 1141 (42000): There is no such grant defined for user 'wikiuser' on host 'localhost'

所以現在使用mysqluserclone工具


Usage: mysqluserclone --source=user:pass@host:port:socket --destination=user:pass@host:port:socket joe@localhost sam:secret1@localhost

# mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning wikiuser@localhost to user wikiuser@localhost
# ...done.

root@localhost [(none)]> select VERSION();
+------------+
| VERSION() |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show grants for wikiuser@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


請注意，它缺少一個密碼，因為我並沒有為用戶設置一個密碼。 哎呀，是不是不夠好。

授予它的偉大工程，如果你想設置新密碼的新用戶帳戶。 我可以通過新密碼作為克隆命令的一部分


mysqluserclone --source=root:<PASSWORD>@localhost:3306 --destination=root:@localhost:3307 wikiuser@localhost wikiuser:<PASSWORD>@localhost

但是，如果我不知道密碼？ 我要確保所有的客戶有他們所有的密碼相同。

我還可以做下面的過程。

• 從DB 1收集補助
• 編輯文件助學金我想移動
• 編輯用戶文件添加註釋，使安全的SQL
• 將文件加載到數據庫2

select CONCAT('SHOW GRANTS FOR `',USER,'`@',HOST,';') as showgrants FROM mysql.user INTO OUTFILE '/tmp/showgrants.sql';

vi /tmp/showgrants.sql

mysql < /tmp/showgrants.sql > /tmp/user_grants.sql

vi /tmp/user_grants.sql
:%s/Grants for/#Grants for/g
:%s/\n/;\r/g
mysql --port=3307 -u root -p < /tmp/user_grants.sql
> show grants for wikiuser@localhost;
+----------------------------------------------------------------------------------------------------------+
| Grants for wikiuser@localhost |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wikiuser'@'localhost' IDENTIFIED BY PASSWORD '1e09502e61120480' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES ON `wikidb`.* TO 'wikiuser'@'localhost' |
+----------------------------------------------------------------------------------------------------------+

現在，我也有同樣的用戶，主機和密碼。