您好,登錄后才能下訂單哦!
環(huán)境:MySQL 5.6 主從環(huán)境(Keepalived架構(gòu))
4000W行大表進(jìn)行歷史數(shù)據(jù)歸檔。
方案:為盡量降低對(duì)業(yè)務(wù)影響,決定采取下列方案。
1、在主庫(kù)建立 2016、2017、2018、2019的4個(gè)歷史表結(jié)構(gòu)。
2、在從庫(kù)建立test庫(kù),并建立 2016、2017、2018、2019的4個(gè)歷史表結(jié)構(gòu),在從庫(kù)的主表上用insert into語(yǔ)句根據(jù)時(shí)間字段把數(shù)據(jù)插入test庫(kù)的2016、2017、2018、2019的歷史表里面。分拆為2016、2017、2018、2019。
3、用Navicat把 2016、2017、2018、2019導(dǎo)出為SQL文件,并生成主表的DELETE語(yǔ)句的TXT文件。
4、用Python腳本把 SQL文件和 TXT文件進(jìn)行處理,分批導(dǎo)入到 2016、2017、2018、2019的4個(gè)歷史表,并刪除主表的歷史數(shù)據(jù)。
5、對(duì)主表進(jìn)行收縮。
完成歸檔。
1、在主庫(kù)建立歷史表的表結(jié)構(gòu)。
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、從庫(kù)建立test庫(kù),同樣建立 歷史表的表結(jié)構(gòu)。
在從庫(kù)上用insert into語(yǔ)句把2016年的歷史數(shù)據(jù)插入test庫(kù)的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導(dǎo)出為SQL文件,舉例:
導(dǎo)出的是純insert的SQL腳本。
導(dǎo)出Delete語(yǔ)句:
4、使用Python腳本批量運(yùn)行上述腳本。
先insert到目標(biāo)主庫(kù)的歷史表里,再delete目標(biāo)主庫(kù)的歷史數(shù)據(jù)。
Python腳本如下:
load_sql_v1.py:
# coding:utf8
"""
1、更新數(shù)據(jù)庫(kù)配置
2、變更待執(zhí)行文件文件名為SQL.sql
3、執(zhí)行文件
"""
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()
# 打開數(shù)據(jù)庫(kù)連接
db = pymysql.connect(DB_IP, DB_USER, DB_PWD, DB_DATABASE, charset='utf8')
# 使用cursor()方法獲取操作游標(biāo)
cursor = db.cursor()
# 使用execute方法執(zhí)行SQL語(yǔ)句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法獲取一條數(shù)據(jù)
data = cursor.fetchone()
print("Database version : %s " % data)
for index, sql in enumerate(content):
if index % 10000 == 0:
print('已執(zhí)行 %d'%index)
if index % 20000 == 0:
time.sleep(WaitTime)
try:
# 執(zhí)行sql語(yǔ)句
db.ping(reconnect=True)
cursor.execute(sql)
# 提交到數(shù)據(jù)庫(kù)執(zhí)行
db.commit()
except Exception as e:
# Rollback in case there is any error
print(sql)
print(e)
## db.rollback()
f.close()
# 關(guān)閉數(shù)據(jù)庫(kù)連接
db.close()
5、對(duì)主表進(jìn)行收縮。
用pt-osc工具做。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。