exp_user_grants.sh

#!/bin/bash# line:           V1.0# mail:           gczheng@139.com# data:           2018-04-25# script_name:    exp_grants.sh# Function export user privilegesMY_USER="gcdb"MY_PASSWORD="iforgot"MY_IP="192.168.49.246"MYSQL_CONN_OPTION=" -u$MY_USER -p$MY_PASSWORD -h$MY_IP"#=======================================================================# 导出用户权限设置 （函数）#=======================================================================function exp_grants(){    mysql $MYSQL_CONN_OPTION -B -N $@ -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql $MYSQL_CONN_OPTION $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'}#=======================================================================# 导出用户帐号 （函数）#=======================================================================function exp_users(){    mysql $MYSQL_CONN_OPTION -B -N $@ -e "SELECT CONCAT('SHOW CREATE USER ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql $MYSQL_CONN_OPTION $@ | sed 's/\(CREATE .*\)/\1;/;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'}#=======================================================================# 执行导出#=======================================================================function exp_user_info(){mysql $MYSQL_CONN_OPTION  -e 'select @@hostname as MY_Host;'if [ $? -eq 0 ];then     echo -e "$MY_IP开始导出帐号和权限信息"    VERSTON=`mysql $MYSQL_CONN_OPTION  -Bse "select @@version" |cut -b 1-3`    if [ $VERSTON = "5.7" ];then        exp_grants > grants.sql        GRANTS=`grep -iwE "Grants" grants.sql |wc -l`        if [ $GRANTS -gt 0 ];then          echo -e "$MY_IP成功导出 $GRANTS 个用户权限"        else          echo -e "$MY_IP导出用户帐号异常结束."          echo -e "$MY_IP请检查帐号权限."          return 1        fi        exp_users > users.sql        USERS=`grep -iwE "IDENTIFIED" users.sql |wc -l`        if [ $USERS -gt 0 ];then          echo -e "$MY_IP成功导出 $USERS 个用户帐号"        else          echo -e "$MY_IP导出用户帐号异常结束."          echo -e "$MY_IP请检查帐号权限."          return 1        fi    else        exp_grants > grants.sql        GRANTS=`grep -iwE "Grants" grants.sql |wc -l`        if [ $GRANTS -gt 0 ];then          echo -e "$MY_IP成功导出 $GRANTS 个用户权限"        else          echo -e "$MY_IP导出用户帐号异常结束."          echo -e "$MY_IP请检查帐号权限."          return 1        fi    fielse    echo -e "$MY_IP连接异常,请检查帐号密码和主机名/IP......"fi}exp_user_info