通过定时任务执行mysql的定期删除和新建分区，此处是按日分区

使用python脚本作为命令脚本，linux的定时任务来每天定时执行

# !/usr/bin/python # -*- coding: utf8 -*- import pymysql import datetime import calendar # 要分区的表 table_name = ' my_table ' # 连接数据库的信息 host,user,passwd,db = ( ' 127.0.0.1 ' , ' root ' , ' 123456 ' , ' test ' ) # 保留数据的天数 days = 2 # =================================================== ''' #根据月分区,计算出要删除的上月分区名称和下月新增的分区名称 def add_months(dt,months): month = dt.month - 1 + months year = dt.year + month / 12 month = month % 12 + 1 day = min(dt.day,calendar.monthrange(year,month)[1]) return dt.replace(year=year, month=month, day=day) today = datetime.datetime.today() last_date = add_months(today,-1) next_date = add_months(today,1) next_next_date = add_months(next_date,1) last_p = 'p%s' % last_date.strftime('%Y%m') next_p = 'p%s' % next_date.strftime('%Y%m') ''' # =================================================== # 根据天分区，根据保留的天数计算出要删除的分区名称和要新增的分区名称 today = datetime.datetime.today() last_date = today - datetime.timedelta(days) next_date = today + datetime.timedelta( 1 ) next_next_date = today + datetime.timedelta( 2 ) last_p = ' p%s ' % last_date.strftime( ' %Y%m%d ' ) next_p = ' p%s ' % next_date.strftime( ' %Y%m%d ' ) sql_add_p = ' alter table %s add partition (partition %s values less than (to_days(\'%s\'))) ' % (table_name,next_p,next_next_date.strftime( ' %Y-%m-%d ' )) sql_del_p = ' alter table %s drop partition %s ' % (table_name,last_p) # =================================================== # 连接数据库，执行删除和新增分区的sql语句 # print sql_add_p # print sql_del_p print ' ==============================================\nstart work:\n%s\n%s ' % (sql_add_p,sql_del_p) conn = pymysql.connect(host,user, passwd, db) cur = conn.cursor() cur.execute(sql_add_p) cur.execute(sql_del_p) conn.close()

转载于:https://www.cnblogs.com/sunny5156/archive/2012/10/27/2743033.html