ITPub博客

首页 > 数据库 > MySQL > MySQL 5.6对大表做归档

MySQL 5.6对大表做归档

原创 MySQL 作者:ywxj_001 时间:2020-05-29 20:50:38 0 删除 编辑

环境: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工具做。








来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2688085/,如需转载,请注明出处,否则将追究法律责任。

全部评论
在零售、金融、互联网行业从事数据库相关工作10余年,有丰富的数据库管理和架构的相关经验。 涉及SQLServer、Oracle、MySQL、PostgreSQL等多种数据库。 目前在一家互联网公司担任首席DBA岗位。 负责整个集团数据库的架构设计和管理工作。

注册时间:2010-01-19

  • 博文量
    145
  • 访问量
    155070