[Mysql]備份同庫中一張錶的歷史記錄 insert into ..select

標籤：資料   python   mysql   備份   backup   

需求

現在有個這麼一個需求，mysql中有個表，資料增長的很快，但是呢這個資料有效期間也就是1個月，一個月以前的記錄不太重要了，但是又不能刪除。為了保證這個表的查詢速度，需要一個簡單的備份表，把資料倒進去。

代碼

於是我寫了一個小指令碼，用來做定時任務，把這個表某段時間的資料備份到備份表中，核心就是個簡單的sql。

原始表radius 備份的表為 radius2015

#!/usr/bin/python2.7# -*- coding: utf-8 -*-#python2.7x#authror: orangleliu#備份radius中的上網記錄表，每個月備份一次，原始表中保留一份資料#使用同一個資料庫中的一個不同表名的表備份import timeimport datetimeimport loggingfrom datetime import timedeltaimport MySQLdbimport MySQLdb.cursorslogging.basicConfig(format=‘%(asctime)s %(levelname)s -     %(message)s‘)logger = logging.getLogger(‘backup‘)logger.setLevel(logging.DEBUG)#資料庫配置DBPARAMS = {    "host":"127.0.0.1",    "user":"root",    "password":"",    "database":"test",    "charset": ""}#這裡使用select into 來備份，資料校正對比記錄數，一個月大概100w條資料#radacct2015#檢查表，檢查重傳，備份，校正create_table_sql = ‘‘‘CREATE TABLE `{0}` (  `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,  `acctsessionid` varchar(64) NOT NULL DEFAULT ‘‘,  `acctuniqueid` varchar(32) NOT NULL DEFAULT ‘‘,  `username` varchar(64) NOT NULL DEFAULT ‘‘,  `groupname` varchar(64) NOT NULL DEFAULT ‘‘,  `realm` varchar(64) DEFAULT ‘‘,  `nasipaddress` varchar(15) NOT NULL DEFAULT ‘‘,  `nasportid` varchar(15) DEFAULT NULL,  `nasporttype` varchar(32) DEFAULT NULL,  `acctstarttime` int(11) DEFAULT NULL,  `acctupdatetime` int(11) DEFAULT NULL,  `acctstoptime` int(11) DEFAULT NULL,  `acctinterval` int(12) DEFAULT NULL,  `acctsessiontime` int(12) unsigned DEFAULT NULL,  `acctauthentic` varchar(32) DEFAULT NULL,  `connectinfo_start` varchar(50) DEFAULT NULL,  `connectinfo_stop` varchar(50) DEFAULT NULL,  `acctinputoctets` bigint(20) DEFAULT NULL,  `acctoutputoctets` bigint(20) DEFAULT NULL,  `calledstationid` varchar(50) NOT NULL DEFAULT ‘‘,  `callingstationid` varchar(50) NOT NULL DEFAULT ‘‘,  `acctterminatecause` varchar(32) NOT NULL DEFAULT ‘‘,  `servicetype` varchar(32) DEFAULT NULL,  `framedprotocol` varchar(32) DEFAULT NULL,  `framedipaddress` varchar(15) NOT NULL DEFAULT ‘‘,  PRIMARY KEY (`radacctid`),  UNIQUE KEY `acctuniqueid` (`acctuniqueid`),  KEY `username` (`username`),  KEY `framedipaddress` (`framedipaddress`),  KEY `acctsessionid` (`acctsessionid`),  KEY `acctsessiontime` (`acctsessiontime`),  KEY `acctstarttime` (`acctstarttime`),  KEY `acctinterval` (`acctinterval`),  KEY `acctstoptime` (`acctstoptime`),  KEY `nasipaddress` (`nasipaddress`)) ENGINE=InnoDB DEFAULT CHARSET=utf8‘‘‘back_sql = ‘‘‘INSERT INTO {0}SELECT *FROM {1}WHERE acctstarttime < UNIX_TIMESTAMP(   STR_TO_DATE(‘{2}‘, ‘%Y-%m-%d‘)) AND acctstarttime >= UNIX_TIMESTAMP(   STR_TO_DATE(‘{3}‘, ‘%Y-%m-%d‘))‘‘‘count_sql = """SELECT count(*) FROM {0} WHERE 1=1 ANDacctstarttime < UNIX_TIMESTAMP(   STR_TO_DATE(‘{1}‘, ‘%Y-%m-%d‘)) AND acctstarttime >= UNIX_TIMESTAMP(   STR_TO_DATE(‘{2}‘, ‘%Y-%m-%d‘))"""#date toolsdef get_year(month):    #month like 201505    return datetime.datetime.strptime(month, "%Y%m").yeardef get_month_firstday_str(month):    return datetime.datetime.strptime(month,"%Y%m").                                        strftime("%Y-%m-%d")def get_next_month_firstday_str(month):    month_firstday = datetime.datetime.strptime(month,"%Y%m")    monthnum = month_firstday.month    return "{0}-{1}-{2}".format(            month_firstday.year if monthnum < 12 else                                  month_firstday.year + 1,            monthnum + 1 if monthnum < 12 else 1, 1)class DBConn(object):    __CONFIG = {        ‘default‘: {            ‘host‘: "",            ‘user‘: "",            ‘database‘: "",            ‘password‘: "",            ‘charset‘: "",        }    }    def __init__(self, connname=‘‘, connconfig={}):        if connconfig:            self.connconfig = connconfig        else:            connname = connname or ‘default‘            self.connconfig = self.__CONFIG.get(connname, ‘default‘)        self.conn = None    def __enter__(self):        try:            self.conn = MySQLdb.connect(                user=self.connconfig[‘user‘],                db=self.connconfig[‘database‘],                passwd=self.connconfig[‘password‘],                host=self.connconfig[‘host‘],                use_unicode=True,                charset=self.connconfig[‘charset‘] or "utf8",                #cursorclass=MySQLdb.cursors.DictCursor                )            return self.conn        except Exception, e:            print str(e)            return None    def __exit__(self, exe_type, exe_value, exe_traceback):        if exe_type and exe_value:            print ‘%s: %s‘ % (exe_type, exe_value)        if self.conn:            self.conn.close()class RadiusBackup(object):    def __init__(self, month, conn):        self.conn = conn        self.cursor = conn.cursor()        self.month = month        self.year = get_year(month)        self.month_firstday = get_month_firstday_str(month)        self.next_month_firstday = get_next_month_firstday_str(month)        self.tablename = "radacct{0}".format(self.year)        self.stable = "radacct"    def check_table_exist(self):        check_table_sql = "SHOW TABLES LIKE ‘{0}‘".format(                            self.tablename)        self.cursor.execute(check_table_sql)        res = self.cursor.fetchall()        return True if len(res) > 0 else False    def create_backup_table(self):        sql = create_table_sql.format(self.tablename)        self.cursor.execute(sql)        logger.info(u"開始建立備份表 {0}".format(self.tablename))    def check_datas_count(self, tablename):        sql = count_sql.format(tablename, self.next_month_firstday,                    self.month_firstday)        logger.debug(sql)        self.cursor.execute(sql)        res = self.cursor.fetchone()        return res[0]    def check_before(self):        flag = False        #check table        if not self.check_table_exist():            self.create_backup_table()            if self.check_table_exist() == False:                logger.error(u"無法找到備份表 exit")                return flag        #check datas        if self.check_datas_count(self.tablename) > 0:            return flag        else:            return True    def backup_datas(self):        sql = back_sql.format(self.tablename, self.stable,                self.next_month_firstday, self.month_firstday)        logger.debug(sql)        self.cursor.execute(sql)        self.conn.commit()    def check_after(self):        snum = self.check_datas_count(self.stable)        bnum = self.check_datas_count(self.tablename)        if snum > 0 and (snum == bnum):            logger.info(u"備份成功")            return snum, True        else:            return -1, False    def backup_handler(self):        if self.check_before():            logger.info(u"檢查完畢，開始備份資料")            self.backup_datas()            logger.info(u"開始備份")            num, flag = self.check_after()            logger.info(u"本次備份{0} 資料 {1}條".format(self.month, num))        else:            logger.info(u"資料已經有備份，請檢查")if __name__ == "__main__":    month = "201504"    with DBConn(connconfig=DBPARAMS) as dbconn:        if dbconn:            backup = RadiusBackup(month, dbconn)            backup.backup_handler()        else:            logger.error("can not connect to db")

本文出自 “orangleliu筆記本” 部落格，轉載請務必保留此出處http://blog.csdn.net/orangleliu/article/details/46650875 作者orangleliu 採用署名-非商業性使用-相同方式共用協議

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

[Mysql]備份同庫中一張錶的歷史記錄 insert into ..select