mysql 5.6 5.7 导出用户授权信息

1.  5.6授权信息 

参考:http://blog.csdn.net/yumushui/article/details/50264123

我做了一点点改动实测可用；

#!/bin/bash  
#Function export user privileges  
# updated by tsong
source /etc/profile

pwd=password 
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -N  -P3306  $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -P3306 -f  $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  
}  

expgrants > ./grants.sql  

2. 5.7授权信息:

mysql 5.7暂时发现这两个点变动:

1. mysql.user 存储加密密码字段的变动

2.show grants for ; 不会出现 identified by 信息；需要使用 show create user ;

参考: https://dev.mysql.com/doc/refman/5.7/en/show-grants.html

这样子上面5.6的脚本就使用不了，修改为下面脚本，实测可用（但是没有更具体验证~~）:

#/bin/bash
#updated by tsong
#Function export user privileges
#5.7存在问题: show grants for 不会给出密码信息，必须用 show create user
# https://dev.mysql.com/doc/refman/5.7/en/show-grants.html  

# show create user 为5.7版本开始存在，5.6执行报错。

source /etc/profile

pwd=password 
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -N  -P3306  $@ -e "SELECT CONCAT(  'SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -P3306 -f  $@ | \
  sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' 
 
  mysql -B -u'root' -p${pwd} -N  -P3306  $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -P3306 -f  $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'   
}  

expgrants > ./5.7_grants.sql

参考:

http://blog.csdn.net/yumushui/article/details/50264123

https://dev.mysql.com/doc/refman/5.7/en/show-grants.html