MySQL 5.6对大表做归档
发布时间：2022-03-28 13:11:13 所属栏目：MySql教程 来源：互联网
导读：环境：MySQL 5.6 主从环境（Keepalived架构） 4000W行大表进行历史数据归档。 方案：为尽量降低对业务影响，决定采取下列方案。 1、在主库建立 2016、2017、2018、2019的4个历史表结构。 2、在从库建立test库，并建立 2016、2017、2018、2019的4个历史表结构
|
环境：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脚本。 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工具做。 （编辑：瑞安网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
