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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22996654/viewspace-2688085/，如需轉載，請註明出處，否則將追究法律責任。