MySQL 5.6对大表做归档
环境：MySQL 5.6 主从环境（Keepalived架构）
4000W行大表进行历史数据归档。
方案：为尽量降低对业务影响，决定采取下列方案。
1、在主库建立 2016、2017、2018、2019的4个历史表结构。
2、在从库建立test库，并建立 2016、2017、2018、2019的4个历史表结构，在从库的主表上用insert into语句根据时间字段把数据插入test库的2016、2017、2018、2019的历史表里面。分拆为2016、2017、2018、2019。
3、用Navicat把 2016、2017、2018、2019导出为SQL文件，并生成主表的DELETE语句的TXT文件。
4、用Python脚本把 SQL文件和 TXT文件进行处理，分批导入到 2016、2017、2018、2019的4个历史表，并删除主表的历史数据。
5、对主表进行收缩。
完成归档。
1、在主库建立历史表的表结构。
CREATE TABLE `upload_order_header_2016` (
`id` bigint(22) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`company` varchar(25) DEFAULT NULL COMMENT '货主',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2、从库建立test库，同样建立 历史表的表结构。
在从库上用insert into语句把2016年的历史数据插入test库的2016年的历史表。
insert into test.upload_order_header_2016 select * from log_db.upload_order_header
where add_time < unix_timestamp('2017-01-01 00:00:00');
insert into test.upload_order_header_2017 select * from log_db.upload_order_header
where add_time >= unix_timestamp('2017-01-01 00:00:00') and
add_time < unix_timestamp('2018-01-01 00:00:00');
3、用 Navicat把 2016导出为SQL文件，举例：
导出的是纯insert的SQL脚本。
导出Delete语句：
4、使用Python脚本批量运行上述脚本。
先insert到目标主库的历史表里，再delete目标主库的历史数据。
Python脚本如下：
load_sql_v1.py：
# coding:utf8
"""
1、更新数据库配置
2、变更待执行文件文件名为SQL.sql
3、执行文件
"""
import pymysql
import time
DB_IP = "192.168.22.10"
DB_USER = "DBA"
DB_PWD = "XXXXXX"
DB_DATABASE = "log_db"
WaitTime = 10
FilePath = [
'2016.sql',
]
for file in FilePath:
f = open(file, mode='r')
print(file)
content=f.readlines()
# 打开数据库连接
db = pymysql.connect(DB_IP, DB_USER, DB_PWD, DB_DATABASE, charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print("Database version : %s " % data)
for index, sql in enumerate(content):
if index % 10000 == 0:
print('已执行 %d'%index)
if index % 20000 == 0:
time.sleep(WaitTime)
try:
# 执行sql语句
db.ping(reconnect=True)
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except Exception as e:
# Rollback in case there is any error
print(sql)
print(e)
## db.rollback()
f.close()
# 关闭数据库连接
db.close()
5、对主表进行收缩。
用pt-osc工具做。
