mysql拷贝用户权限

mysql拷贝用户权限


mysql版本(5.7.15)
linux版本CentOS 6.6


步骤：


 1,构造出show grants  命令
 2,执行show grants 命令生产授权语句
 3,给grant 语句加上分号
 4,将授权语句传递给目标mysql，并执行


示例：


1，选择所有用户：select distinct(user) from mysql.user;
| uei             |
| ueislave        |
| root            |
| user_slaver_229 |


2，根据用户拼权限：

#mysql -uroot -ppwd@123  -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user where user='uei'" |mysql -uroot -ppwd@123   -N |sed 's/$/;/' 

注意：mysql 5.6.15 中，执行结果为

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A' WITH GRANT OPTION 

mysql 5.7.15中，执行结果为 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION  ,没有"IDENTIFIED BY PASSWORD "关键字，需要先处理。处理步骤如下：

a.
mysql -uroot -ppwd@123  -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user where user='uei'" |mysql -uroot -ppwd@123   -N |sed 's/$/;/' 
b.
GRANT ALL PRIVILEGES ON *.* TO 'uei'@'%';
GRANT ALL PRIVILEGES ON `uei`.* TO 'uei'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `ueiclub`.* TO 'uei'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `uei_linux`.* TO 'uei'@'%' WITH GRANT OPTION;
没有 IDENTIFIED BY PASSWORD 关键字
c.
select host,user,authentication_string from mysql.user where user='uei'
+------+------+-------------------------------------------+
| host | user | authentication_string                     |
+------+------+-------------------------------------------+
| %    | uei  | *2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A |
+------+------+-------------------------------------------+
d.
GRANT ALL PRIVILEGES ON *.* TO 'uei'@'%' IDENTIFIED BY PASSWORD '*2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A';
GRANT ALL PRIVILEGES ON `uei`.* TO 'uei'@'%' IDENTIFIED BY PASSWORD '*2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `ueiclub`.* TO 'uei'@'%' IDENTIFIED BY PASSWORD '*2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON `uei_linux`.* TO 'uei'@'%' IDENTIFIED BY PASSWORD '*2CC949A5ED23512BD2461B0F4D7AFBD1B7A1945A' WITH GRANT OPTION;

3，将输出结果进行保存，循环替换用户名。

4，连接新数据库，执行刚才保存的内容。

参数说明：
mysql -N  不显示列名
$mysql --help|grep 'skip-column-names'
     (Defaults to on; use --skip-column-names to disable.)
 -N, --skip-column-names
mysql -e   执行命令


$ mysql --help|grep e,
 -e, --execute=name  Execute command and quit. (Disables --force and history
     otherwise only from the first line, before an enter.
     PAGER. Valid pagers are less, more, cat [> filename],

     order of preference, my.cnf, $MYSQL_TCP_PORT,

参考URL：http://blog.csdn.net/lwei_998/article/details/18604533