python mysql導庫加入主從同步叢集實例介紹

腳本可以在任意機器上執行（需要安裝mysql，至少是mysql客戶端，mysql只能版本為5.6及以上），先輸入來源ip，偵測來源ip上的mysql是否正常執行，再在本機dump mysql資料庫，然後將dump檔案傳輸到目的伺服器，在目的伺服器上匯入資料庫，最後把從庫加入到現用叢集中。

[root@master test]# cat finaly_mysql.py

#!/usr/bin/env python

#-*- coding: utf-8 -*-

import MySQLdb, socket, paramiko,sys, os,datetime, time

sour_db=raw_input('please input the source mysql database ip:')

dest_db=raw_input ('please input the destination mysql database ip:')

password=raw_input('please input the sour_db root users password :')

def check_port(ip,port):

print "test whether source mysql db is running!"

res=socket.socket(socket.AF_INET, socket.SOCK_STREAM)

# res.settimeout(3)

try:

res.connect((ip,port))

print 'Server port 3306 OK!'

print("\033[41;36m  Server port 3306 OK!   \033[0m")

except Exception,e:  

print Exception,":",e

print "break this program"

# sys.exit()

res.close()

#查看來源庫的3306埠是否正常

def begin_dump():

print "begin dump remote mysql,please waiting...!"

print("\033[41;36m  begin dump remote mysql,please waiting...!   \033[0m")

        hostname=sour_db

        hostname=sour_db

        username='root'

        dump='mysqldump -uroot -pXp29at5F37 -h 192037 -h.32037 -h.p. echo $ ? '

if os.popen(dump).read().strip() == '0':

print "dump result is 0,means dump success"

# print("\033[41;36m  dump result is 0,means dump success   \033[0m")

else:

print "\033[1;31;40m% s\033[0m"  % "dump error,exit python file"

sys.exit()

#從本機dump資料庫檔案

def trans_dump():

print "   "

local_dir='/tmp' 

        remote_dir='/tmp'

  #      dest_dir='/#cmp' print " "

print "begin transfer  mysql dump file from local server to destination mysql database server ,please waiting...!"

try:

          Transport((dest_db,22))

            t.connect(username='root',password=password)

         》 files='dump.sql'

            print "\033[1;32;40m%s\033[0m" % "transfer back file,Please wait ...."

##Oo#  問題'    Beginning to transfer file  to  %s  %s ' % (dest_db,datetime.datetime.now())

            print ' :    files)

            sftp.put(os.path.join(local_dir,files),os.path.join(dest_dir,files))

 #            print '    transfer All dump file success %s ' % datetime.datetime.now()

        except Exception,e:

#        0 %s \033[0m" % ":", "\033[1;31;40m%s\033[0m" % e

            sys.exit()

#將資料庫檔案從本地傳送到目的伺服器，即dest_db

def import_dump():

conn=MySQLdb.connect(host=dest_db,user='root',passwd='Xp29at5F37',db='test ')

        cur1=conn.cursor()

        cur1.execute("stop slave;")

        cur1.close;")

     conn.cursor()

        cur2.execute("reset master;")

        cur2.close()

        cur. #        cur3.execute("reset slave all;")

        cur3.close()

        conn.close

# print

# "begin to import mysql  dump file   ,please waiting...!"

        local_dir='/tmp'

       remote_dir='/tmp'

       remote_dir='/tmp'

        dest_dir='/tmp'

import_command = "mysql -uroot -pleyou  < /tmp/dump.sql"

print  ' begin import dump ake ,

## print  ' longbegin import dump ake , may tay tlonga may time, please be patient !!!'

## try:

ssh =paramiko.SSHClient ()

ssh.load_system_host_keys ()

ssh.connect (hostname =dest_db,username ='root',password =password)

stdin, stdout, stderr = ssh.exec_command (import_command) 

      print stderr。 # ssh.close ()

        except Exception,e:

                print Exception,":",e

## print "import #over先將dest_db的slave 都停掉，因為不知道dest_db的狀態，可能之前是個主函式庫，也可能是其他機子的從函式庫。

def final_check_mysql ():

print "   "

print "finally check mysql service   "

        status = True

## 

        status = True

## 

     

                conn=MySQLdb.connect(host=dest_db,user='root',passwd='Xp29at5F37',db='test 

                cur1.execute("CHANGE MASTER TO MASTER_HOST='192.168.3.10', MASTER_USER='o', MASTER_PASSWORD='Xp29557STER_USER='          cur1.close()

                cur3=conn.cursor()

                cur3. 

print "sleep 10 seconds; "

time.sleep(10)

                cur2=conn.cursor()

             result = cur2.fetchall()

                io_thread= result[0][10]

             print io_thread,sql_thread

        if io_thread == 「    else:

                   print ' MySQL Master/Slave replication fail,Please check it'

                cur2.close()

     

# print Exception,"\033[1;31;40m%s\033[0m" % ":", "\033[1;31;40m%s\033[0m" % e

           

        return status

#最後檢查新從庫的運作狀態，同步是否正常

if __name__ == "__main__":

a=check_port (sour_db,3306)

b=begin_dump()

# c=trans_dump()

d=import_dump()

e=final_check_mysql()

print "dump file ok!!!!!!!!!!!"

以上是python mysql導庫加入主從同步叢集實例介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！