Linux系统中 mysql server 之间拷贝用户权限

最新推荐文章于 2021-08-08 17:51:48 发布

lwei_998

最新推荐文章于 2021-08-08 17:51:48 发布

阅读量1.4k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/lwei_998/article/details/18604533

版权

MySQL 专栏收录该内容

123 篇文章 1 订阅

订阅专栏

目的：将本机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