mysql 同步中历史记录_[Mysql]备份同库中一张表的历史记录 insert into ..select

#!/usr/bin/python2.7

# -*- coding: utf-8 -*-

#python2.7x

#authror: orangleliu

#备份radius中的上网记录表，每一个月备份一次，原始表中保留一份数据

#使用同一个数据库中的一个不同表名的表备份

import time

import datetime

import logging

from datetime import timedelta

import MySQLdb

import MySQLdb.cursors

logging.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 201505

return datetime.datetime.strptime(month, "%Y%m").year

def 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")