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

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