-- 先刪除舊表（若存在），避免沖突
drop table if exists t1;

-- 創(chuàng)建業(yè)務(wù)表t1（InnoDB引擎，含自增主鍵和時(shí)間字段）
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,  -- 業(yè)務(wù)字段1
  `b` int DEFAULT NULL,          -- 業(yè)務(wù)字段2
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 自動(dòng)時(shí)間戳
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

alter table t1 rename t1_bak_20231114;

drop table t1_bak_20231114;

use martin;

-- 重建表t1
drop table if exists t1;
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` int DEFAULT NULL,
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

-- 創(chuàng)建存儲(chǔ)過程，插入10萬行數(shù)據(jù)
drop procedure if exists insert_t1;
delimiter ;;  -- 臨時(shí)修改語(yǔ)句結(jié)束符，避免與存儲(chǔ)過程內(nèi);沖突
create procedure insert_t1()
begin
  declare i int;
  set i=1;
  while(i<=100000)do  -- 循環(huán)插入10萬行
    insert into t1(a,b) values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

-- 執(zhí)行存儲(chǔ)過程，初始化數(shù)據(jù)
call insert_t1();

# 進(jìn)入MySQL數(shù)據(jù)目錄（需根據(jù)實(shí)際路徑調(diào)整，此處為/data/mysql/data/martin）
cd /data/mysql/data/martin
# 查看t1.ibd大?。?h表示人性化顯示，如KB/MB）
ll -h t1.ibd

-- delete全表數(shù)據(jù)
delete from t1;

-- 先重建表并插入數(shù)據(jù)（恢復(fù)到步驟2狀態(tài)）
call insert_t1();
-- truncate清空表
truncate table t1;

use martin;

drop table if exists t2;
CREATE TABLE `t2` (        
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (id),  -- 主鍵索引
  key idx_b(b)       -- 為查詢條件b創(chuàng)建索引，加速刪除
) ENGINE=InnoDB CHARSET=utf8mb4 ;

-- 存儲(chǔ)過程插入10萬行數(shù)據(jù)
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()       
begin
  declare i int;                 
  set i=1;                     
  while(i<=100000)do                 
    insert into t2(a,b) values(i,i); 
    set i=i+1;                     
  end while;
end;;
delimiter ;
call insert_t2();

# 用mysqldump備份t2表
cd /data/backup
mysqldump -uroot -p martin t2 > t2_bak.sql

# 或創(chuàng)建備份表，復(fù)制數(shù)據(jù)（更快速）
create table t2_bak_1114 like t2;  -- 復(fù)制表結(jié)構(gòu)
insert into t2_bak_1114 select * from t2;  -- 復(fù)制數(shù)據(jù)

DELIMITER //

CREATE PROCEDURE delete_t2()
BEGIN
    REPEAT
        DELETE FROM t2 WHERE b < 50000 LIMIT 1000;
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    SELECT '刪除完成' AS result;
END //

DELIMITER ;

-- 確認(rèn)刪除效果（應(yīng)返回0）
select count(*) from t2 where b < 50000;
-- 剩余數(shù)據(jù)量（應(yīng)返回50000）
select count(*) from t2 where b >= 50000;

# 安裝依賴
yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL

# 下載并安裝Percona Toolkit
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.1/binary/redhat/8/x86_64/percona-toolkit-3.5.1-1.el8.x86_64.rpm
rpm -ivh percona-toolkit-3.5.1-1.el8.x86_64.rpm

# 驗(yàn)證安裝（查看版本）
pt-archiver --version

-- 創(chuàng)建dba用戶，允許192.168網(wǎng)段訪問
CREATE USER 'dba'@'192.168.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Id81Gdac_a';
-- 授予全庫(kù)權(quán)限（生產(chǎn)環(huán)境可縮小權(quán)限范圍，僅授予martin庫(kù)權(quán)限）
GRANT all ON *.* TO 'dba'@'192.168.%';

pt-archiver \
--source h=192.168.184.151,u=dba,p='Id81Gdac_a',D=martin,t=t2 \  # 源表信息
--where "b<50000" \  # 刪除條件
--progress 10000 \   # 每處理10000行顯示進(jìn)度
--limit=1000 \       # 每次處理1000行
--txn-size 10000 \   # 每10000行提交一次事務(wù)
--no-safe-auto-increment \  # 不修改自增主鍵（避免影響后續(xù)插入）
--statistics \       # 輸出統(tǒng)計(jì)信息（如處理時(shí)間、行數(shù)）
--purge              # 僅刪除，不歸檔（核心參數(shù)）

-- 登錄歸檔庫(kù)（192.168.184.152）
mysql -uroot -p

-- 創(chuàng)建歸檔數(shù)據(jù)庫(kù)
create database archiver_db;
use archiver_db;

-- 創(chuàng)建與源表結(jié)構(gòu)一致的歸檔表
CREATE TABLE `t2_archiver` (        
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (id),
  key idx_b(b)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

-- 授予dba用戶歸檔庫(kù)權(quán)限
CREATE USER 'dba'@'192.168.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Id81Gdac_a';
GRANT all ON *.* TO 'dba'@'192.168.%';

# 關(guān)閉歸檔庫(kù)的防火墻（避免連接失?。?
iptables -F  # 僅測(cè)試環(huán)境，生產(chǎn)環(huán)境需配置白名單

# 執(zhí)行歸檔+刪除
pt-archiver \
--source h=192.168.184.151,u=dba,p='Id81Gdac_a',D=martin,t=t2 \  # 源表
--dest h=192.168.184.152,u=dba,p='Id81Gdac_a',D=archiver_db,t=t2_archiver \  # 歸檔表
--where "b<50000" \  # 歸檔條件
--progress 10000 \   # 進(jìn)度顯示
--limit=1000 \       # 每次處理1000行
--txn-size 10000 \   # 事務(wù)大小
--no-safe-auto-increment \
--statistics \
--purge              # 歸檔后刪除源表數(shù)據(jù)

select count(*) from martin.t2 where b<50000;  -- 應(yīng)返回0
select count(*) from martin.t2;  -- 應(yīng)返回50001

select count(*) from archiver_db.t2_archiver;  -- 應(yīng)返回49999
select min(b),max(b) from archiver_db.t2_archiver;  -- 應(yīng)返回1和49999

-- 方法1：alter table重建表（推薦，InnoDB會(huì)整理空間）
alter table martin.t2 engine=InnoDB;

-- 方法2：optimize table（效果同上，僅支持InnoDB和MyISAM）
optimize table martin.t2;

-- 驗(yàn)證空間變化
ll -h /data/mysql/data/martin/t2.ibd  # 空間應(yīng)明顯減少

use martin;

drop table if exists t3_log ;

-- 創(chuàng)建按年份分區(qū)的日志表（2016、2017、2018三個(gè)分區(qū)）
CREATE TABLE t3_log (
    id INT,
    log_info VARCHAR (100),  -- 日志內(nèi)容
    date datetime            -- 分區(qū)鍵（按年份分區(qū)）
) ENGINE = INNODB 
PARTITION BY RANGE (YEAR(date))(  -- RANGE分區(qū)，按YEAR(date)的值分區(qū)
    PARTITION p2016 VALUES less THAN (2017),  -- 2016年數(shù)據(jù)（<2017）
    PARTITION p2017 VALUES less THAN (2018),  -- 2017年數(shù)據(jù)（<2018）
    PARTITION p2018 VALUES less THAN (2019)   -- 2018年數(shù)據(jù)（<2019）
);

insert into t3_log values 
(1,'aaa','2016-01-01'),  -- 進(jìn)入p2016分區(qū)
(2,'bbb','2016-06-01'),  -- 進(jìn)入p2016分區(qū)
(3,'ccc','2017-01-01'),  -- 進(jìn)入p2017分區(qū)
(4,'ddd','2018-01-01');  -- 進(jìn)入p2018分區(qū)

select 
  TABLE_SCHEMA,  -- 數(shù)據(jù)庫(kù)名
  TABLE_NAME,    -- 表名
  PARTITION_NAME,-- 分區(qū)名
  TABLE_ROWS     -- 分區(qū)行數(shù)
from information_schema.partitions 
where table_schema='martin' and table_name='t3_log';

-- 刪除p2016分區(qū)（即刪除2016年所有數(shù)據(jù)）
alter table t3_log drop partition p2016;

-- 查看分區(qū)列表（p2016已消失）
select PARTITION_NAME from information_schema.partitions where table_schema='martin' and table_name='t3_log';

-- 查詢?nèi)頂?shù)據(jù)（2016年數(shù)據(jù)已刪除）
select * from t3_log;  -- 僅返回2017、2018年數(shù)據(jù)