首页 > 代码库 > python管理mysql，请叫我雷锋
python管理mysql，请叫我雷锋
#!/usr/bin/env python
#coding:utf8
import os,sys,glob,time,MySQLdb,re
DIRNAME = os.path.dirname(__file__)
OPSTOOLS_DIR = os.path.abspath(os.path.join(DIRNAME,‘..‘))
sys.path.append(OPSTOOLS_DIR)
from library.mysqlconfig import MySQLDConfig,getMyVariables
from optparse import OptionParser
from subprocess import Popen
from subprocess import PIPE
from mysql_config import comain
MYSQL_DATA_DIR = "/var/mysqlmanager/data"
MYSQL_CONF_DIR = "/var/mysqlmanager/conf"
MYSQL_BACK_DIR = "/var/mysqlmanager/backup"
EPLICATION_USER="username"
EPLICATION_PASS="passwd"
def opts():
parser = OptionParser(usage="usage %prog options")
parser.add_option("-n","--name",
dest="name",
default="mysqlinstance",
action="store",
)
parser.add_option("-p","--port",
dest="port",
default="3306",
action="store",
)
parser.add_option("-c","--cmd",
dest="cmd",
default="check",
action="store",
)
"""
parser.add_option("-s","--socket",
dest="socket",
default="socket",
action="store",
)
"""
return parser.parse_args()
def _init():
if not os.path.exists(MYSQL_DATA_DIR):
os.makedirs(MYSQL_DATA_DIR)
if not os.path.exists(MYSQL_CONF_DIR):
os.makedirs(MYSQL_CONF_DIR)
def readconf():
confs=glob.glob(os.path.join(MYSQL_CONF_DIR,"*.cnf"))
return [MySQLDConfig(i) for i in confs]
def checkPort(d,p):
for m in d:
if p == m.mysqld_vars["port"]:
return True
return False
def _genDict(name,port):
return{
‘pid-file‘:os.path.join(MYSQL_DATA_DIR,name,"%s.pid" %name),
‘socket‘:"/tmp/%s.socket" %name,
"port":port,
‘datadir‘:os.path.join(MYSQL_DATA_DIR,name),
"log-error":os.path.join(MYSQL_DATA_DIR,name,"%s.error" %name),
}
def mysql_install_db(cnf):
cmd=[‘mysql_install_db‘,‘--defaults-file=%s‘%cnf]
p=Popen(cmd,stdout=PIPE)
#stdout,stderr=p.communicate()
return p.returncode
def setOwner(datadir):
os.system("chown -R mysql.mysql %s" %datadir)
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 startInstance(name,port,dbtype="master",**kw):
cnf = os.path.join(MYSQL_CONF_DIR,"%s.cnf" %name)
datadir = os.path.join(MYSQL_DATA_DIR,name)
if not os.path.exists(cnf):
c=_genDict(name,port)
c.update(kw)
mc=MySQLDConfig(cnf,**c)
mc.save()
else:
mc=MySQLDConfig(cnf)
mc.save()
if not os.path.exists(datadir):
mysql_install_db(cnf)
time.sleep(1)
setOwner(datadir)
run_mysql(cnf)
cur=connMySQL(name)
setReplMaster(cur)
else:
run_mysql(cnf)
cur=connMySQL(name)
setReplMaster(cur)
def setReplMaster(cur):
sql = "grant replication slave on *.* to %s@‘%%‘ identified by ‘%s‘" %(EPLICATION_USER,EPLICATION_PASS)
cur.execute(sql)
def stop_mysql(name):#,socket):
socket="/tmp/%s.socket" %name
cmd=[‘mysqladmin‘,‘shutdown‘,‘-S‘,‘%s‘ %socket]
p=Popen(cmd,stdout=PIPE)
return p.returncode
def getCNF(name):
return os.path.join(MYSQL_CONF_DIR,"%s.cnf"%name)
def connMySQL(name):
cnf = getCNF(name)
#print cnf
if os.path.exists(cnf):
mc=MySQLDConfig(cnf)
# print mc
host=‘127.0.0.1‘
port=int(mc.mysqld_vars[‘port‘])
user=‘root‘
conn=MySQLdb.connect(host=host,port=port,user=user)
cur=conn.cursor()
return cur
def diffvariavles(name):
cnf = getCNF(name)
cur = connMySQL(name)
vars = getMyVariables(cur)
#conf_dic=comain()
if os.path.exists(cnf):
k=k.replace(‘-‘,‘_ ‘)
if k in vars and vars[k] !=v:
print k,v,vars[k]
def setVariable(name,variable,value):
cnf=getCNF(name)
if os.path.exists(cnf):
mc=MySQLDConfig(cnf)
mc.set_var(variable,value)
mc.save()
def findLogPos(s):
rlog=re.compile(r"MASTER_LOG_FILE=‘(\S+)‘,",re.I)
rpos=rpos=re.compile(r"MASTER_LOG_POS=(\d+);$",re.I)
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 getLogPos(f):
with open(f) as fd:
for l in fd:
f,p=findLogPos(l)
if f and p:
return f,p
def find_log_pos(s):
pass
def get_log_pos(f):
with open(f) as fd:
while True:
fr=fd.readline()
if "CHANGE MASTER TO MASTER_LOG_FILE" == fr.split("=")[0]:
log_file=fr.split("=")[1].split(",")[0].split("‘")[1]
log_pos=fr.split("=")[2].split(";")[0]
break
else:
pass
return log_file,int(log_pos)
#def chageMaster(cur,host,port,user,passwd,log_file,log_pos):
# 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,passwd)
def ChangeMaster(cur,host,port,user,passwd):
sql = "change master to master_host=‘%s‘,master_port=%s,master_user=‘%s‘,master_password=‘%s‘" %(host,port,user,passwd)
cur.execute(sql)
def changeMaster(cur,host,port,user,passwd,log_file,log_pos):
cur.execute(sql)
def backupMySQL(name):
cnf=getCNF(name)
if os.path.exists(cnf):
mc=MySQLDConfig(cnf)
import datetime
now = datetime.datetime.now()
timestamp=now.strftime("%Y-%m-%d.%H.%M,%S")
backup_file=os.path.join(MYSQL_BACK_DIR,name,timestamp+‘.sql‘)
_dir=os.path.dirname(backup_file)
if not os.path.exists(_dir):
os.makedirs(_dir)
cmd="/usr/local/mysql/bin/mysqldump -A -x -F --master-data=http://www.mamicode.com/1 --host=127.0.0.1 --user=root --port=%s > %s" %(mc.mysqld_vars[‘port‘],backup_file)
runMySQLCmd(cmd)
def runMySQLCmd(cmd):
p=Popen(cmd,stdout=PIPE,shell=True)
stdou,stderr=p.communicate()
return p.returncode
def restoreMySQL(name,port,sqlfile,**kw):
startInstance(name,port,**kw)
cnf=getCNF(name)
if os.path.exists(cnf):
mc=MySQLDConfig(cnf)
cmd = "mysql --host=127.0.0.1 --user=root --port=%s < %s" %(mc.mysqld_vars["port"],sqlfile)
runMySQLCmd(cmd)
def main():
_init()
opt,args=opts()
instance_name=opt.name
instance_port=opt.port
command=opt.cmd
#socket=opt.socket
#print instance_name,instance_port,command
comm_list=["create","start","stop","status","check","abjust","backup","restore"]
if command in comm_list:
if command == "create":
if not args:
exists_conf=readconf()
if checkPort(exists_conf,instance_port):
print >> sys.stderr,"Port exists"
sys.exit(-1)
else:
startInstance(instance_name,instance_port)
else:
dbtype_list=["master","slave"]
if args[0] in dbtype_list:
dbtype=args[0]
serverid=args[1]
mysqld_options={"server-id":serverid}
if dbtype==‘master‘:
mysqld_options["logbin"]=‘mysql-bin‘
startInstance(instance_name,instance_port,dbtype,**mysqld_options)
elif dbtype=="slave" :
host=args[2]
port=args[3]
user=EPLICATION_USER
passwd=EPLICATION_PASS
"""
mysqld_options["master-host"]=master_host
mysqld_options["master-port"]=master_port
mysqld_options["master-user"]=EPLICATION_USER
mysqld_options["master-password"]=EPLICATION_PASS
"""
mysqld_options["replicate-ignore-db"]="mysql"
mysqld_options["skip-slave-start"]=None
startInstance(instance_name,instance_port,dbtype,**mysqld_options)
cur=connMySQL(instance_name)
ChangeMaster(cur,host,port,user,passwd)
else:
print >> sys.stderr,"\033[31;1minput dbtype error! please ‘master/slave‘\033[0m"
elif command == "start":
startInstance(instance_name,instance_port)
elif command == "stop":
stop_mysql(instance_name)#,socket)
elif command == "status":
socket="/tmp/%s.socket" %instance_name
if os.path.exists(socket):
print "\033[32;1mmysql--%s is runing!!\033[0m" %instance_name
else:
print "\033[31;1mmysql--%s is not runing!!\033[0m" %instance_name
elif command == "check":
diffvariavles(instance_name)
elif command == "abjust":
variable=args[0]
value=http://www.mamicode.com/args[1]
setVariable(instance_name,variable,value)
elif command == "backup":
backupMySQL(instance_name)
elif command == "restore":
serverid=args[0]
master_host=args[1]
master_port=args[2]
master_user=EPLICATION_USER
master_password=EPLICATION_PASS
sqlfile=args[3]
#master_log_file,master_log_pos=getLogPos(sqlfile)
master_log_file,master_log_pos=get_log_pos(sqlfile)
mysqld_options={"server-id":serverid}
mysqld_options["replicate-ignore-db"]="mysql"
mysqld_options["skip-slave-start"]=None
restoreMySQL(instance_name,instance_port,sqlfile,**mysqld_options)
cur=connMySQL(instance_name)
changeMaster(cur,master_host,master_port,master_user,master_password,master_log_file,master_log_pos)
else:
print >> sys.stderr,"\033[31;1mcommand is not found! please ‘create/start/stop/status/check/abjust/backup/restore‘\033[0m"
if __name__ =="__main__":
main()
本文出自 “原创python发布区” 博客，请务必保留此出处http://cuihailong.blog.51cto.com/2923450/1409576