mysql 清理host文件_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 "

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)