导出帐号和权限脚本

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 privileges

MY_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
    fi
else
    echo -e "$MY_IP连接异常,请检查帐号密码和主机名/IP......"
fi
}

exp_user_info