pythonmysql导库加入主从同步集群实例介绍

脚本可以在任意机器上执行（需要安装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(&＃39;please input the source mysql database ip:&＃39;)

dest_db=raw_input(&＃39;please input the destination mysql database ip:&＃39;)

password=raw_input(&＃39;please input the sour_db root users password :&＃39;)

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 &＃39;Server port 3306 OK!&＃39;

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

username=&＃39;root&＃39;

dump=&＃39;mysqldump -uroot -pXp29at5F37 -h 192.168.3.10 -A -B > /tmp/dump.sql && echo $? &＃39;

if os.popen(dump).read().strip() == &＃39;0&＃39;:

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=&＃39;/tmp&＃39;

remote_dir=&＃39;/tmp&＃39;

dest_dir=&＃39;/tmp&＃39;

print " "

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

try:

t=paramiko.Transport((dest_db,22))

t.connect(username=&＃39;root&＃39;,password=password)

sftp=paramiko.SFTPClient.from_transport(t)

files=&＃39;dump.sql&＃39;

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

print &＃39; Beginning to transfer file to %s %s &＃39; % (dest_db,datetime.datetime.now())

print &＃39; Transfering file:&＃39;,dest_db + &＃39;:&＃39; + os.path.join(local_dir,files)

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

t.close()

print &＃39; transfer All dump file success %s &＃39; % datetime.datetime.now()

except Exception,e:

print Exception,"\033[1;31;40m%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=&＃39;root&＃39;,passwd=&＃39;Xp29at5F37&＃39;,db=&＃39;test&＃39;)

cur1=conn.cursor()

cur1.execute("stop slave;")

cur1.close()

cur2=conn.cursor()

cur2.execute("reset master;")

cur2.close()

cur3=conn.cursor()

cur3.execute("reset slave all;")

cur3.close()

conn.close()

print " "

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

local_dir=&＃39;/tmp&＃39;

remote_dir=&＃39;/tmp&＃39;

dest_dir=&＃39;/tmp&＃39;

import_command = "mysql -uroot -pleyou

print &＃39; begin import dump file ,it may take a long time, please be patient !!!&＃39;

try:

ssh =paramiko.SSHClient ()

ssh.load_system_host_keys ()

ssh.connect (hostname =dest_db,username =&＃39;root&＃39;,password =password)

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

print stderr.read ()

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

try:

cOnn=MySQLdb.connect(host=dest_db,user=&＃39;root&＃39;,passwd=&＃39;Xp29at5F37&＃39;,db=&＃39;test&＃39;)

cur1=conn.cursor()

cur1.execute("CHANGE MASTER TO MASTER_HOST=&＃39;192.168.3.10&＃39;, MASTER_USER=&＃39;root&＃39;, MASTER_PASSWORD=&＃39;Xp29at5F37&＃39;, MASTER_AUTO_POSITION=1;")

cur1.close()

cur3=conn.cursor()

cur3.execute("start slave;")

cur3.close()

print "sleep 10 seconds;"

time.sleep(10)

cur2=conn.cursor()

cur2.execute("show slave status;")

result = cur2.fetchall()

io_thread= result[0][10]

sql_thread= result[0][11]

print io_thread,sql_thread

if io_thread == "Yes" and sql_thread == "Yes":

print &＃39;MySQL master/slave replication status is successfully&＃39;

else:

print &＃39;MySQL Master/Slave replication fail,Please check it&＃39;

cur2.close()

conn.close()

except Exception,e:

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

status = True

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社区 其它相关文章！