python导出mysql授权语句

vi grants.py

import sys
import getopt
import pymysql

opts, args = getopt.getopt(sys.argv[1:], "m:")
for op, value in opts:
    if op == '-m':
        mm = value
    else:
        sys.exit()


def get_conn_master():
    conn = pymysql.connect(
        host=mm,
        port=3306, #端口
        user='root',  #账号
        passwd='password', #密码
        charset='utf8'
    )
    return conn


def select_all(conn, sql):
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        results = cursor.fetchall()
        conn.commit()
        conn.close()
        return results
    except Exception as ex:
        raise ex


def select_privileges():
    sql1 = "select user,host from mysql.user where user not in\
    ('zabbix','root') and host not in('localhost')"  #过滤不想导出的账号
    conn1 = get_conn_master()
    user = select_all(conn1, sql1)
    return user


def print_privileges():
    user = select_privileges()
    for user_info in user:
        sql1 = "show create user '%s'@'%s'" % (user_info[0], user_info[1])
        sql2 = "show grants for '%s'@'%s'" % (user_info[0], user_info[1])
        conn1 = get_conn_master()
        privileges = select_all(conn1, sql1)[0][0]
        grants = select_all(conn1, sql2)
        print(privileges, ';')
        for i in grants:
            print(i[0], ';')


print_privileges()

python grants.py -m 数据库IP
脚本很简单，执行脚本可以导出create user和grant语句，导入权限时直接导入sql即可，另外mysql5.5没有show create user语法，所以只能在5.6及以上版本使用。