mysql 数据同步 代码脚本.doc

#!/bin/env python# encoding:utf-8import MySQLdbimport tracebackimport timeimport winsound#报警提示声音文件mp3=rC:/Documents and Settings/Administrator/桌面/replication/master alarm1.wav#主从机数据库信息字典rep_master = host:7, user : repacct-1872172, passwd:a17a3b495d9c23cedee6380aad87f25f, db: test, port:3306 ;rep_slave = host:13, user : repacct-1872172, passwd:a17a3b495d9c23cedee6380aad87f25f, db: test, port:3307 ;#状态显示域master_status_fields = File:0, Position:1,slave_status_fields = Slave_IO_State: 0, Master_Host: 1, Master_Port: 3, Connect_Retry:4, Master_Log_File: 5, Read_Master_Log_Pos:6, Slave_IO_Running: 10, Slave_SQL_Running: 11, Last_IO_Errno: 34, Last_Errno:18#这个errno应该不能作为判断依据，因为网络中断的时候，他的number号还是0 ;#获取数据库链接 def get_conn(host_info): conn = MySQLdb.Connect(host=host_infohost, user=host_infouser, passwd=host_infopasswd, db=host_infodb, port=host_infoport) if 0: conn.set_character_set(gb2312) cur = conn.cursor() cur.execute(set names gb2312;) #conn.ping() #print conn,conn return conn Ping:Checks whether the connection to the server is working. If the connection has gone down, an attempt to reconnect is made unless auto-reconnect is disabled. This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary. #是否ping通？ def conn_ping(conn): conn.ping()#获取链接的游标？ def get_cur(conn): return conn.cursor() #数据查询def query_sql(conn, sql):#有conn情况下的查询 for i in range(1): try: cur = conn.cursor() cur.execute(sql) return cur.fetchall() except: #flog.flog(数据库连接错误: %s % traceback.format_exc() print DB error: %s % traceback.format_exc() #conn = get_conn(True) #end of GetOneData #暂时还没用到2011年7月5日10:21:36 def exec_sql(conn, sql): for i in range(1): try: cur = conn.cursor() cur.execute(sql) if sql.count(update)0 or sql.count(insert)0: mit() else: print alert in call exec sql, sql break #返回 except: #flog.flog(exec_sql 错误: sql=%sn %s % (sql, traceback.format_exc() print exec sql retry error %s % traceback.format_exc() #conn = get_conn(True) #end of exec sql#主机信息 def get_masterinfo(conn):#有连接了，才开始调用这函数，不然报错 sql = SHOW MASTER STATUS rows = query_sql(conn, sql) return rows #从机信息def get_slaveinfo(conn): sql = SHOW SLAVE STATUS rows = query_sql(conn, sql) return rows #从机slave重启def restart_slave(conn): sql = stop slave query_sql(conn, sql) sql = start slave query_sql(conn, sql) return 1 #slave机同步信息 def slave_status(): try: slave_conn = get_conn(rep_slave) print slave server is connected. myfile.write(slave_conn:connected+n) rows = get_slaveinfo(slave_conn) # /doc/refman/5.0/en/replication-administration-status.html row_slave = rows0 myfile.write(n) myfile.write(Slave status+n) for k in slave_status_fields.keys(): print k, row_slaveslave_status_fieldsk myfile.write(k+:) myfile.write(str(row_slaveslave_status_fieldsk) + n) except: myfile.write(slave_conn:losed.need reconnect) print slave server is not connected. please make sure the connector is ok #这里最好设一个声音报警! #winsound.PlaySound(mp3, winsound.SND_NODEFAULT) #设一段延时，slave机有充分的时间，确保conn连接正常。 time.sleep(10) #再次运行slave所有代码 slave_status() #master机同步信息 def master_status(): global restart_num try:#尝试连接master master_conn = get_conn(rep_master) print master server is connected. myfile.write(master_conn:connected+n) myfile.write(n) myfile.write(Master status+n) rows = get_masterinfo(master_conn) row_master = rows0 #print row_master for k in master_status_fields.keys(): print k, row_mastermaster_status_fieldsk myfile.write(k+:) myfile.write(str(row_mastermaster_status_fieldsk) + n) except:#如果连接不上master myfile.write(master_conn:losed.need reconnect) print master server is not connected. please make sure the connector is ok #这里最好设一个声音报警! #设一段延时，master机有充分的时间，确保conn连接正常。 winsound.PlaySound(mp3, winsound.SND_NODEFAULT) time.sleep(10) #如果master机的网络出现过中断，那么我们将这个restart_num置为1，准备在master连接上后，重启slave restart_num = 1 #再次运行master所有代码 master_status() #判断slave和master机的Log_Pos位置是否一致；如果不一致，则准备重启。 def check_log_pos(): slave_conn = get_conn(rep_slave) rows = get_slaveinfo(slave_conn) row_slave = rows0 #print Read_Master_Log_Pos,row_slaveslave_status_fieldsRead_Master_Log_Pos master_conn = get_conn(rep_master) rows = get_masterinfo(master_conn) row_master = rows0 #print Position,row_mastermaster_status_fieldsPosition #print time:,time.strftime(%Y-%m-%d %X, time.localtime( time.time() ) ) #下面这种if情况只有在，master机网线掉落，连接中断，在中断的时间中，master机更新了数据。 #我们为了确保数据同步，我们比较log日志的位置是否一致；如果不一致，则重启slave if row_slaveslave_status_fieldsRead_Master_Log_Pos /var/camiant/db/mysql/databases.in/doc/refman/5.0/en/replication-administration-status.html Slave_IO_State: Waiting for master to send event Master_Host: master1 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 931 Relay_Log_File: slave1-relay-bin.000056 Relay_Log_Pos: 950 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 931 Relay_Log_Space: 1365 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0#下面是在命令窗中打印出来的信息Master status: 7 3306 =(mysql-bin.000001, 597L, , ),)Slave status 13 3307Master_Log_File my