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=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=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

python管理mysql，请叫我雷锋,古老的榕树,5-wow.com