Python 自动化管理Mysql数据库
#!/usr/local/bin/python2.7#-*- coding:utf-8 -*-
from os import path
from optparse import OptionParser
from subprocess import PIPE, Popen
import MySQLdb
import glob
import os
import sys
import time
import datetime
import re
DIRNAME = path.dirname(__file__)
OPSTOOLS_DIR = path.abspath(path.join(DIRNAME, '..'))
sys.path.append(OPSTOOLS_DIR)
from library.mysql import MySQLDConfig, getMyVariables
REPLICATION_USER = 'repl'
REPLICATION_PASS = '123qwe'
MYSQL_DATA_DIR = '/home/david/data'
MYSQL_CONF_DIR = '/home/david/cnfs'
MYSQL_BACK_DIR = '/home/david/backup'
def opts():
parser = OptionParser(usage="usage: %prog arg1 arg2")
parser.add_option("-c","--cmd",
dest="cmd",
action="store",
default="check",
help="Check the configuration file and database configuration parameters are different.[%options]"
)
parser.add_option("-n","--name",
dest="name",
action="store",
default="mysqlinstance",
help="Create Examples."
)
parser.add_option("-p","--port",
dest="port",
action="store",
default="3306",
help="Examples of port."
)
return parser.parse_args()
def checkPort(d, p):
'''实例端口检测'''
for m in d:
if p == m.mysqld_vars['port']:
return True
return False
def setReplMaster(cur):
'''设置slave数据库同步用户的授权'''
sql = "GRANT REPLICATION SLAVE ON *.* TO %s@'localhost' IDENTIFIED BY '%s'" % (REPLICATION_USER, REPLICATION_PASS)
cur.execute(sql)
def connMySQLd(mc):
'''连接数据库'''
host = '127.0.0.1'
user = 'root'
port = int(mc.mysqld_vars['port'])
conn = MySQLdb.connect(host, port=port, user=user)
cur = conn.cursor()
return cur
def run_mysql(cnf):
'''运行数据库'''
cmd = "mysqld_safe --defaults-file=%s &" % cnf
p = Popen(cmd, stdout=PIPE, shell=True)
time.sleep(5)
return p.returncode
def setOwner(p, user):
'''设置目录权限'''
os.system("chown -R %s:%s %s" % (user, user, p))
def mysql_install_db(cnf):
'''数据库初始化'''
p = Popen("mysql_install_db --defaults-file=%s" % cnf, stdout=PIPE, shell=True)
#p = Popen("mysql_install_db --user=mysql --datadir=%s " % MYSQL_DATA_DIR, stdout=PIPE, shell=True)
stdout, stderr = p.communicate()
return p.returncode
def _genDict(name, port):
'''设置文件存储目录及监听端口'''
return {
'pid-file': path.join(MYSQL_DATA_DIR, name, "%s.pid" % name),
'socket': '/tmp/%s.sock' % name,
'port': port,
'datadir': path.join(MYSQL_DATA_DIR, name)+'/',
'log_error': path.join(MYSQL_DATA_DIR, name)
}
def readConfs():
'''读取配置文件，如果配置文件不存在，使用默认配置生成配置文件'''
confs = glob.glob(path.join(MYSQL_CONF_DIR, '*.cnf'))
return
def getCNF(name):
'''获取配置文件完整路径'''
return path.join(MYSQL_CONF_DIR, "%s.cnf" % name)
def runMySQLdump(cmd):
'''启动Mysql命令'''
p = Popen(cmd, stdout=PIPE, shell=True)
stdout, stderr = p.communicate()
return p.returncode
def getBinlogPOS(f):
'''获取binlog'''
with open(f) as fd:
f, p = findLogPos(l)
if f and p:
return f,p
def findLogPos(s):
rlog = re.compile(r"MASTER_LOG_FILE='(\S+)',", re.IGNORECASE)
rpos = re.compile(r"MASTER_LOG_POS=(\d+),?", re.IGNORECASE)
log = rlog.search(s)
pos = rpos.search(s)
if log and pos:
return log.group(1), int(pos.group(1))
else:
return (None, None)
def changeMaster(cur, host, port, user, mpass, mf, p):
sql = '''CHANGE MASTER TO
MASTER_HOST='%s',
MASTER_PORT='%s',
MASTER_USER='%s',
MASTER_PASSWORD='%s',
MASTER_LOG_FILE='%s',
MASTER_LOG_POS=%s;''' % (host, port, user, mpass, mf, p)
cur.execute(sql)
def createInstance(name, port, dbtype="master", **kw):
'''创建数据库实例'''
cnf = path.join(MYSQL_CONF_DIR, "%s.cnf" % name)
datadir = path.join(MYSQL_DATA_DIR, name)
exists_cnfs = readConfs()
if checkPort(exists_cnfs, port):
print >> sys.stderr, "port exist."
sys.exit(-1)
if not path.exists(cnf):
c = _genDict(name, port)
c.update(kw)
mc = MySQLDConfig(cnf, **c)
mc.save()
else:
mc = MySQLDConfig(cnf, **kw)
if not path.exists(datadir):
mysql_install_db(cnf)
setOwner(datadir, mc.mysqld_vars['user'])
run_mysql(cnf)
time.sleep(3)
cur = connMySQLd(mc)
setReplMaster(cur)
def diffVariables(instance_name):
'''查询数据库配置文件和数据库配置的差异'''
cnf = getCNF(instance_name)
if path.exists(cnf):
mc = MySQLDConfig(cnf)
print mc
cur = connMySQLd(mc)
vars = getMyVariables(cur)
for k, v in mc.mysqld_vars.items():
k = k.replace('-', '_')
if k in vars and vars != v:
print k, v, vars
def setVariable(instance_name, variable, value):
'''重新加载配置'''
cnf = getCNF(instance_name)
if path.exists(cnf):
mc = MySQLDConfig(cnf)
cur = connMySQLd(mc)
cur.execute('set global %s = %s' % (variable, value))
mc.set_var(variable, value)
mc.save()
def backupMySQL(instance_name):
'''备份数据库'''
cnf = getCNF(instance_name)
if path.exists(cnf):
mc = MySQLDConfig(cnf)
now = datetime.datetime.now()
timestamp = now.strftime('%Y-%m-%d-%H%M%S')
backup_file = path.join(MYSQL_BACK_DIR, instance_name, timestamp+'.sql')
_dir = path.dirname(backup_file)
if not path.exists(_dir):
os.makedirs(_dir)
cmd = 'mysqldump -A -x -F --master-data=1 --host=127.0.0.1 --user=root --port=%s > %s' % (mc.mysqld_vars['port'], backup_file)
runMySQLdump(cmd)
def restoreMySQL(instance_name, instance_port, sqlfile, **kw):
createInstance(instance_name, instance_port, **kw)
cnf = getCNF(instance_name)
if path.exists(cnf):
mc = MySQLDConfig(cnf)
cur = connMySQLd(mc)
cmd = "mysql -h 127.0.0.1 -P %s -u root < %s" % (mc.mysqld_vars['port'], sqlfile)
f, p = getBinlogPOS(sqlfile)
runMySQLdump(cmd)
changeMaster(cur,
host=kw['master-host'],
port=kw['master-port'],
user=REPLICATION_USER,
mpass=REPLICATION_PASS,
mf=f,
p=p)
def _init():
'''查询mysql几个目录是否存在，如果不存在，自动创建'''
if not path.exists(MYSQL_DATA_DIR):
os.makedirs(MYSQL_DATA_DIR)
if not path.exists(MYSQL_CONF_DIR):
os.makedirs(MYSQL_CONF_DIR)
if not path.exists(MYSQL_BACK_DIR):
os.makedirs(MYSQL_BACK_DIR)
def main():
opt, args = opts()
instance_name = opt.name
instance_port = opt.port
command = opt.cmd
if command == "create":
if not args:
createInstance(instance_name, instance_port)
else:
dbtype = args
serverid = args
mysqld_options = {'server-id':serverid}
if dbtype == 'master':
mysqld_options['log-bin'] = 'mysql-bin'
elif dbtype == 'slave':
master_host = args
master_port = args
mysqld_options['master-host'] = master_host
mysqld_options['master-port'] = master_port
mysqld_options['master-user'] = REPLICATION_USER
mysqld_options['master-password'] = REPLICATION_PASS
mysqld_options['skip-slave-start'] = None
mysqld_options['replicate-ignore-db'] = 'mysql'
mysqld_options['read-only'] = None
createInstance(instance_name, instance_port, dbtype=dbtype, **mysqld_options)
elif command == 'check':
diffVariables(instance_name)
elif command == 'adjust':
variable = args
value = args
setVariable(instance_name, variable, value)
elif command == 'backup':
backupMySQL(instance_name)
elif command == 'restore':
serverid == args
mhost = args
mport = args
sqlfile = args
mysqld_options = {
"master-host":mhost,
"master-port":mport,
"server-id":serverid,
"skip-slave-start":None,
}
restoreMySQL(instance_name, instance_port, sqlfile, **mysqld_options)
if __name__ == "__main__":
print main()
页:
[1]