MySQL历史数据归档及表空间整理

#!/usr/bin/python
#encoding=utf-8
import sys,time
import MySQLdb
from __builtin__ import str

reload(sys)
sys.setdefaultencoding('utf-8')

def restore_call_record_data():
    list_id = find_min_max_id('call_record')
    print list_id
    start_value=list_id[0]
    max_value=list_id[1]
    step=1000

    while (start_value < max_value):

        end_value = start_value + step
        if (start_value+step)>max_value :
                end_value = max_value

        conn=MySQLdb.connect(host="172.16.3.88",user="restore",passwd="pwd4mysql",db="institute_call_record",charset="utf8")
        cursor =conn.cursor()
        count_sql="select count(1) from call_record t where created_time<date_sub(now(),interval 10 MINUTE) and id<="+str(end_value)+" and id>="+str(start_value)+";"
        sql ="select * from call_record t where created_time<date_sub(now(), interval 10 MINUTE) and id<="+str(end_value)+" and id>="+str(start_value)+";"
        print sql
        cursor.execute(count_sql)
        print cursor.fetchone()
        cursor.execute(sql)
        rows=cursor.fetchall()
        if rows:
            sql_ids=''
            flag=1
            for row in rows:
                if flag==1:
                    sql_ids=str(row[0])
                else:
                    sql_ids=sql_ids+','+str(row[0])
                flag=flag+1

            sql_insert="replace into call_record_history select * from call_record where id in("+sql_ids+");"
            #print sql_insert
            sql_delete="delete from call_record where id in("+sql_ids+");"
            #print sql_delete
            try:
                cursor.execute(sql_insert)
                cursor.execute(sql_delete)
                cursor.execute("commit;")
                time.sleep(0.5)
                #print "call_record-->start_value="+str(start_value)+"end_value="+str(end_value)
            except:
                print "call_record id="+str(row[0])+"!!!"
                cursor.execute("rollback;")
                cursor.close()
                conn.close()
                sys.exit(0)


            start_value = end_value+1
        else:
            start_value = end_value+1
            continue
            time.sleep(0.01)
        cursor.close()
        conn.close()

def find_min_max_id(table_name):
    find_sql="select ifnull(min(id),0),ifnull(max(id),0) from "+table_name+" where created_time<date_sub(now(),interval 10 MINUTE);"
    print find_sql
    conn=MySQLdb.connect(host="172.16.3.88",user="restore",passwd="pwd4mysql",db="institute",port=3306,charset="utf8")
	cursor =conn.cursor()
    cursor.execute(find_sql)
    row=cursor.fetchone()

    list_id = [0,0]
    list_id[0]=int(row[0])
    list_id[1]=int(row[1])

    cursor.close()
    conn.close()
    return list_id


if __name__ == '__main__':
    print "call_record_data---------"
    ISOTIMEFORMAT='%Y-%m-%d %X'
    print time.strftime( ISOTIMEFORMAT, time.localtime( time.time() ) )+"-------------------开始restore-----------------------"
    restore_call_record_data()
    print time.strftime( ISOTIMEFORMAT, time.localtime( time.time() ) )+"restory执行完毕。。。。。。。。。。。。。。。。。。。。"