Python MySQL guide library, join master-Slave synchronization cluster

Scripts can be executed on any machine (need to install MySQL, at least MySQL client, MySQL only version 5.6 and above), first enter the source IP, detect the source IP on the normal operation of MySQL, and then in the local dump MySQL database, It then transfers the dump file to the destination server, imports the database on the destination server, and finally joins the library to the active cluster.

[email protected] 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

Username= ' Root '

Dump= ' mysqldump-uroot-pxp29at5f37-h 192.168.3.10-a-B >/tmp/dump.sql && 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 Database File

Def trans_dump ():

Print ""

Local_dir= '/tmp '

Remote_dir= '/tmp '

Dest_dir= '/tmp '

Print ""

Print "Begin transfer MySQL dump file from the local server to destination MySQL database server, please waiting...!"

Try

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

T.connect (username= ' root ', Password=password)

Sftp=paramiko. Sftpclient.from_transport (t)

files= ' Dump.sql '

Print "\033[1;32;40m%s\033[0m"% "transfer back File,please wait ..."

print ' Beginning to transfer file to%s '% (Dest_db,datetime.datetime.now ())

print ' transfering file: ', dest_db + ': ' + os.path.join (local_dir,files)

Sftp.put (Os.path.join (Local_dir,files), Os.path.join (Dest_dir,files))

T.close ()

print ' Transfer all dump file success%s '% 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 ()

#将数据库文件从本地传输到目的服务器, namely 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 ()

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, waiting...!"

Local_dir= '/tmp '

Remote_dir= '/tmp '

Dest_dir= '/tmp '

Import_command = "Mysql-uroot-pleyou </tmp/dump.sql"

print ' Begin import dump file, it may take a long 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.read ()

Ssh.close ()

Except Exception,e:

Print Exception, ":", E

Print "Import Over"

#导入之前先将dest_db的slave all stop because you don't know the state of the dest_db, it could be a main library, or it could be from a library of other machines.

Def final_check_mysql ():

Print ""

print "Finally check MySQL service"

Status = True

Try

Conn=mysqldb.connect (host=dest_db,user= ' root ', passwd= ' xp29at5f37 ', db= ' test ')

Cur1=conn.cursor ()

Cur1.execute ("Change MASTER to master_host= ' 192.168.3.10 ', master_user= ' root ', master_password= ' xp29at5f37 ', master_ Auto_position=1; ")

Cur1.close ()

Cur3=conn.cursor ()

Cur3.execute ("Start slave;")

Cur3.close ()

Print "Sleep ten 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 ' MySQL master/slave replication status is successfully '

Else

print ' MySQL master/slave replication fail,please Check it '

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

#最后检查新从库的运行状态, synchronization is normal

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 guide library, join master-Slave synchronization cluster