需求

如今有個這么一個需求。mysql中有個表。數據增長的非常快。可是呢這個數據有效期也就是1個月，一個月曾經的記錄不太重要了，可是又不能刪除。為了保證這個表的查詢速度，須要一個簡單的備份表，把數據倒進去。

代碼

于是我寫了一個小腳本，用來做定時任務。把這個表某段時間的數據備份到備份表中。核心就是個簡單的sql。

原始表radius 備份的表為 radius2015

#!/usr/bin/python2.7 # -*- coding: utf-8 -*- #python2.7x #authror: orangleliu #備份radius中的上網記錄表，每一個月備份一次，原始表中保留一份數據 #使用同一個數據庫中的一個不同表名的表備份import time import datetime import logging from datetime import timedeltaimport MySQLdb import 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 AND acctstarttime < UNIX_TIMESTAMP(STR_TO_DATE('{1}', '%Y-%m-%d') ) AND acctstarttime >= UNIX_TIMESTAMP(STR_TO_DATE('{2}', '%Y-%m-%d') ) """#date tools def get_year(month):#month like 201505return 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.monthreturn "{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 = connconfigelse:connname = connname or 'default'self.connconfig = self.__CONFIG.get(connname, 'default')self.conn = Nonedef __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.connexcept Exception, e:print str(e)return Nonedef __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 = connself.cursor = conn.cursor()self.month = monthself.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 Falsedef 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 tableif not self.check_table_exist():self.create_backup_table()if self.check_table_exist() == False:logger.error(u"無法找到備份表 exit")return flag#check datasif self.check_datas_count(self.tablename) > 0:return flagelse:return Truedef 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, Trueelse:return -1, Falsedef 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 採用署名-非商業性使用-同樣方式共享協議

轉載于:https://www.cnblogs.com/yfceshi/p/7142672.html

總結

以上是生活随笔為你收集整理的[Mysql]备份同库中一张表的历史记录 insert into ..select的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。