需求现在有个这么一个需求，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")
|