处理MySQL连接异常终止的方法

介绍

MySQL连接异常终止在使用中经常出现，方法针对宕机、网络波动等状况。

方法一

使用try_except代码块控制MySQL连接异常中止

import MySQLdb
import time

while True:
    try:
        conn=MySQLdb.connect(host='localhost',user='user_root',passwd='root',db='test',port=3306)
        cursor = conn.cursor()
        cursor.execute("select * from t_user")
        print cursor.fetchone()
        cursor.close()
        conn.close()
    except Exception,e:
        print 'MySQL error msg:',e
        time.sleep(5) 

方法二

使用MySQL次连接保持，当连接中止后会自动重连。当然，MySQL数据连接池也可以实现类似的功能，此处不再详细介绍。

import MySQLdb
import sys
import time
import threading

class MySQLConnection:
    def __init__(self, **conf):
        self.conf = conf
        self.connect()

    def connect(self):
        try:
            self.conn = MySQLdb.connect(**self.conf)
            self.cursor = self.conn.cursor()
        except Exception as e:
            print >> sys.stderr, e
            return False
        return True

    def close(self):
        self.cursor.close()
        self.conn.close()
        del self.cursor
        del self.conn

class MySQLConnectionKeeper(threading.Thread):
    def __init__(self, **conf):
        threading.Thread.__init__(self)

        self.conf = conf

        self.keep_running = True
        self.last_activity = time.time()

        self.sleep_time = 0.500 # seconds
        self.reconnect_wait_time = 5.0 # seconds

        self.conn = MySQLConnection(**self.conf)

    def run(self):
        while self.keep_running:
            try:
                self.conn.cursor.execute("SELECT 1")
                self.last_activity = time.time()

                time.sleep(self.sleep_time)

                if time.time() - self.last_activity > self.reconnect_wait_time:
                    self.conn.close()
                    self.conn.connect()
            except Exception as e:
                print >> sys.stderr, e
                self.conn.close()
                self.conn.connect()

    def shutdown(self):
        self.keep_running = False

if __name__ == '__main__':
    conn_keeper = MySQLConnectionKeeper(host='localhost',user='user_root',passwd='root',db='test',port=3306)
    conn_keeper.start()

方法三

使用pymsql进行连接操作，pymsql支持自动重连机制，可以自动重新连接到MySQL服务器。

import pymysql
import time

while True:
    try:
        conn = pymysql.connect(host = 'localhost',
                                   user = 'user_root',
                                   password = 'root',
                                   database = 'test',
                                    port=3306)
        cursor = conn.cursor()
        cursor.execute("select * from t_user")
        print cursor.fetchone()
        cursor.close()
        conn.close()
    except Exception as e:
        print 'MySQL error msg:',e
        time.sleep(5)

总结

以上三种方式可以很好地控制MySQL连接异常终止，防止程序因此中止。