[Mysql] backing up the insertinto .. select history of a table in the same database

[Mysql] backing up the insertinto .. select history of a table in the same database
Requirement

Now there is such a requirement that mysql has a table and the data grows fast. However, this data is valid for one month, and the record a month ago is not very important, but cannot be deleted. To ensure the query speed of this table, a simple backup table is required to reverse the data.

Code

So I wrote a small script to back up the data in this table for a certain period of time to the backup table. The core is a simple SQL.

The original table radius backup table is radius2015

#! /Usr/bin/python2.7 #-*-coding: UTF-8-*-# python2.7x # authror: orangleliu # back up the Internet record table in the radius, which is backed up once every month, keep one copy of data in the original table # use a table with different table names in the same database to back up 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) # database Configuration DBPARAMS = {"host": "127.0.0.1", "user": "root", "password": "", "database": "test ", "charset": ""} # Here, select into is used for backup. The number of data verification and comparison records is about records per month # radacct2015 # Checklist, check retransmission, backup, check 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 '', 'napaddress' 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, 'info info _ 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 distinct T 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.exe cute (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.exe cute (SQL) logger.info (u "start to create 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 (SQL) self.cursor.exe cute (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 "cannot find backup table 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.exe cute (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 "backup successful") return snum, True else: return-1, False def backup_handler (self ): if self. check_before (): logger.info (u "Check finished, start backing up data") self. backup_datas () logger.info (u "Start backup") num, flag = self. check_after () logger.info (u "{0} data {1} records in this backup ". format (self. month, num) else: logger.info (u "data has been backed up. Check") 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 ")