[Mysql] Back up the history of a table in the same library insert into. Select

Demand

There is one such demand now. There is a table in MySQL. The data grew very fast. However, this data is valid for 1 months, the record of one months is not very important, but can not be deleted. In order to ensure the query speed of this table, a simple backup table is needed to pour the data in.

Code

So I wrote a little script to do the timed tasks. Back up data from this table for a certain period of time to the backup table. The core is a simple SQL.

radiusthe table backed up by the original table isradius2015

#!/usr/bin/python2.7#-*-Coding:utf-8-*-#python2.7x#authror: Orangleliu#备份radius中的上网记录表, backed up once every one months, leaving a copy of the data in the original table#使用同一个数据库中的一个不同表名的表备份ImportTimeImportDatetimeImportLogging fromDatetimeImportTimedeltaImportMySQLdbImportMySQLdb.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 to back up. Data check control record number, one months about 100w data#radacct2015#检查表, check for retransmission, backup. ChecksumCreate_table_sql =' CREATE TABLE ' {0} ' (' Radacctid ' bigint (+) NOT null auto_increment, ' acctsessionid ' varchar (+) ' NOT null DEFAULT ' ', ' Acctuniqueid ' varchar (+) NOT null default ' ', ' username ' varchar (+) ' NOT null default ' ', ' groupname ' varchar (UP) NO T null default ' ', ' realm ' varchar (+) Default ', ' nasipaddress ' varchar () ' NOT null ' default ' ', ' nasportid ' varchar ( Default NULL, ' Nasporttype ' varchar (+) default NULL, ' Acctstarttime ' int (one-by-one) default null, ' Acctupdatetime ' int (11 Default NULL, ' Acctstoptime ' int (one) default null, ' Acctinterval ' int () default null, ' Acctsessiontime ' int () uns igned default NULL, ' acctauthentic ' varchar (+) default NULL, ' Connectinfo_start ' varchar () default NULL, ' Connectinf   O_stop ' varchar ' default null, ' Acctinputoctets ' bigint (default null, ' Acctoutputoctets ' bigint () default NULL, ' Calledstationid ' varchar (NOT null default ' ', ' callingstationid ' varchar ') ' is not null default ' ', ' Acctterminatec Ause ' varchar (+) not NULL default ', ' servicetype ' varchar (+) default NULL, ' Framedprotocol ' varchar (+) default NULL, ' Framedipaddress ' var char (+) not NULL DEFAULT ' ', PRIMARY key (' Radacctid '), UNIQUE key ' Acctuniqueid ' (' Acctuniqueid '), key ' username ' (' u Sername '), key ' framedipaddress ' (' framedipaddress '), key ' Acctsessionid ' (' Acctsessionid '), key ' Acctsessiontime ' (' AC Ctsessiontime '), key ' Acctstarttime ' (' acctstarttime '), key ' Acctinterval ' (' Acctinterval '), key ' Acctstoptime ' (' Accts Toptime '), 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 accts Tarttime >= 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 Acct StartTime >= Unix_timestamp (Str_to_date (' {2} ', '%y-%m-%d ')) "" "#date Tools def get_year(month):    #month like 201505    returnDatetime.datetime.strptime (Month,"%y%m"). Year def get_month_firstday_str(month):    returnDatetime.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.yearifMonthnum < A ElseMonth_firstday.year +1, Monthnum +1 ifMonthnum < A Else 1,1) class dbconn(object):__config = {' Default ': {' Host ':"",' user ':"",' database ':"",' Password ':"",' CharSet ':"",        }    } def __init__(self, connname=", connconfig={}):        ifConnconfig:self.connconfig = ConnconfigElse: Connname = Connnameor ' 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)returnSelf.connexceptException, E:PrintSTR (e)return None     def __exit__(self, exe_type, Exe_value, Exe_traceback):        ifExe_type andExe_value:Print '%s:%s '% (Exe_type, Exe_value)ifSelf.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) se        Lf.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.F Etchall ()return True ifLen (res) >0 Else False     def create_backup_table(self):sql = Create_table_sql.format (self.tablename) self.cursor.execute (SQL) Logger.info (u "Start creating Backup Table {0}". Format (Self.tablename)) def check_datas_count(self, tablename):sql = Count_sql.format (tablename, Self.next_month_firstday, Self.month_firstday) logger.debug (sq L) self.cursor.execute (sql) res = Self.cursor.fetchone ()returnres[0] def check_before(self):Flag =False        #check Table        if  notSelf.check_table_exist (): Self.create_backup_table ()ifSelf.check_table_exist () = =False: Logger.error (U "Cannot find backup table Exit")returnFlag#check datas        ifSelf.check_datas_count (Self.tablename) >0:returnFlagElse:return True     def backup_datas(self):sql = Back_sql.format (self.tablename, self.stable, Self.next_month_firstday, Self.month_firstday) lo Gger.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)ifSnum >0  and(Snum = = Bnum): Logger.info (U "Backup succeeded")returnSnum,True        Else:return-1,False     def backup_handler(self):        ifSelf.check_before (): Logger.info (u "Check complete, start Backup data") Self.backup_datas () Logger.info (u "Start Backup"NUM, flag = Self.check_after () logger.info (U "This backup {0} data {1} bar". Format (self.month, num))Else: Logger.info (u "Data has been backed up, please check")if__name__ = ="__main__": month ="201504"     withDbconn (Connconfig=dbparams) asDbconn:ifDbconn:backup = Radiusbackup (month, dbconn) Backup.backup_handler ()Else: Logger.error ("Can not connect to db")

This article is from the "Orangleliu Notebook" blog, reproduced please be sure to keep this source http://blog.csdn.net/orangleliu/article/details/46650875 author Orangleliu Attribution-NonCommercial use-share agreement in the same way

[Mysql] Back up the history of a table in the same library insert into. Select