目的:将本机mysql的用户权限拷贝到IP 为192.168.0.*** 端口为3316的mysql中
步骤:
构造出show grants 命令
执行show grants 命令生产授权语句
给grant 语句加上分号
将授权语句传递给目标mysql,并执行
示例:
mysql -uroot -ptest1 -h 127.0.0.1 -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user where user='cream'" |mysql -uroot -ptest1 -h 127.0.0.1 -N |sed 's/$/;/' |mysql -uroot -ptest2 -h 192.168.0.*** -P3316
参数说明:
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,
$ mysql -uroot -ptest1 -h 127.0.0.1 -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user where user='cream'"
Warning: Using a password on the command line interface can be insecure.
+------------------------------+
| SHOW GRANTS FOR 'cream'@'%'; |
+------------------------------+
mysql> SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user where user='cream';
+----------------------------------------------------------------+
| CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') |
+----------------------------------------------------------------+
| SHOW GRANTS FOR 'cream'@'%'; |
+----------------------------------------------------------------+
1 row in set
mysql> SHOW GRANTS FOR 'cream'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for cream@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cream'@'%' IDENTIFIED BY PASSWORD '*A2C70F654129BF445C8AFFEEDF94155B81E01731' |
| GRANT ALL PRIVILEGES ON `cream`.* TO 'cream'@'%' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------+ 2 rows in set