mysql清理用户权限
1、mysql数据库地址文件格式
db:test 10.10.10.10 10.10.10.11
db:profile 10.10.10.12:3306 10.10.10.13:3307
2、脚本内容
#!/usr/bin/env python
#_*_coding:utf-8_*_
#user = liuzilong
#email = liuzilong@jiayuan.com
#time = 2019-04-10
import sys,os
import MySQLdb
import re
import time
import logging
#需要排除的db组
db_name_list = ['db:master','db:lvs']
#数据库配置文件列表
file_list = ['/root/cron/db_list.txt','/root/cron/baihe_db_list.txt']
#数据库清理权限备份文件
d_time = time.strftime('%Y%m%d_%H_%M_%S',time.localtime(time.time()))
bk_file = '/home/mysql/bk/priv_%s.txt' % (d_time)
#执行日志记录
# 第一步，创建一个logger
logger = logging.getLogger('priv_delete')
logger.setLevel(logging.DEBUG) # Log等级总开关
# 第二步，创建一个handler，用于写入日志文件
logfile = '/home/mysql/bk/db_priv_delete.log'
fh = logging.FileHandler(logfile)
fh.setLevel(logging.DEBUG) # 输出到file的log等级的开关
# 第三步，定义handler的输出格式
formatter = logging.Formatter("%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
fh.setFormatter(formatter)
# 第四步，将logger添加到handler里面
logger.addHandler(fh)
#定义清理权限类
class Priv_Delete(object):
#初始化，获取数据库ip和端口
def __init__(self):
ip_port = {}
db_list = []
for file in file_list:
try:
f = open(file,'r')
db_list += f.readlines()
f.close()
except Exception as e:
logger.error("No such file or directory: %s " % (file))
for ip in db_list:
db_name = ip.split()[0]
if db_name not in db_name_list:
for i in range(1,len(ip.split())):
try:
ip_port[ip.split()[i].split(':')[0]] = int(ip.split()[i].split(':')[1])
except Exception as e :
ip_port[ip.split()[i].split(':')[0]] = 3306
self.ip_port = ip_port
#创建数据库连接
def conn_db(self,ip,port):
ret = {}
db_host = ip
db_port = port
db_user = 'sg'
db_passwd = 'sg109504'
db_name = ''
try:
conn = MySQLdb.connect(host=db_host,port=db_port,user=db_user,passwd=db_passwd,db=db_name,charset='utf8',connect_timeout=10)
return conn
except Exception as e:
logger.error("attempt connection database %s faild！" % (ip))
return None
#定义清理权限方法
def priv_delete(self,hostlist):
#打开权限备份文件
bk_f = open(bk_file,'a+')
for k,v in self.ip_port.items():
db_ip = k
db_port = v
logger.info("Current operational database address is %s:%s" % (db_ip,db_port))
conn = self.conn_db(db_ip,db_port)
if conn:
cursor = conn.cursor()
ip_str = '"' + '","'.join(hostlist) + '"'
valid_s = "select user,host from mysql.user where host in (%s);" % (ip_str)
cursor.execute(valid_s)
rs = cursor.fetchall()
if len(rs) > 0:
bk_f.write(db_ip + ':' + str(db_port) + '\n')
cursor.execute("set sql_log_bin=0;")
for host_ip in hostlist:
logger.info('current remove privilege ip is %s' % (host_ip))
get_priv_cmd = 'select user from mysql.user where host="%s" ' % (host_ip)
cursor.execute(get_priv_cmd)
result = cursor.fetchall()
for row in result:
name = row[0]
logger.info('current remove privilege user is %s@%s' % (name,host_ip))
if name:
get_db_version = 'select version();'
cursor.execute(get_db_version)
result = cursor.fetchone()[0]
st_list = []
if result.startswith('5.7'):
get_priv = 'show grants for %s@%s' % (name,host_ip)
get_pwd = "select authentication_string from mysql.user where user='%s' and host='%s'" % (name,host_ip)
cursor.execute(get_pwd)
pwd = cursor.fetchone()[0]
cursor.execute(get_priv)
result = cursor.fetchall()
for row in result:
s = " IDENTIFIED BY PASSWORD %s" % (pwd)
s = row[0] + s
st_list.append(s)
st = ';\n'.join(st_list) + ';\n'
bk_f.write(st)
else:
get_priv = 'show grants for %s@%s' % (name,host_ip)
cursor.execute(get_priv)
result = cursor.fetchall()
for row in result:
st_list.append(row[0])
st = ';\n'.join(st_list) + ';\n'
bk_f.write(st)
try:
#remove_priv_cmd = 'drop user %s@%s ' % (name,host_ip)
#cursor.execute(remove_priv_cmd)
logger.info('current user %s@%s remove privilege complete!' % (name,host_ip))
except Exception as e:
logger.error('current user %s@%s remove privilege faild!' % (name,host_ip))
cursor.close()
else:
cursor.close()
bk_f.close()
def usage(self):
print "Usage:"
print ""
print "删除主机授权"
print ""
print " remove_priv ip <ip address>"
print ""
print " eg: remove_priv ip 10.10.10.10 10.10.10.11"
if __name__ == "__main__":
privobj = Priv_Delete()
if len(sys.argv) < 2:
privobj.usage()
else:
hostlist = list(sys.argv[1:])
privobj.priv_delete(hostlist)
