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

-- 創(chuàng)建業(yè)務表t1（InnoDB引擎，含自增主鍵和時間字段）
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,  -- 業(yè)務字段1
  `b` int DEFAULT NULL,          -- 業(yè)務字段2
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 自動時間戳
  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)建存儲過程，插入10萬行數(shù)據(jù)
drop procedure if exists insert_t1;
delimiter ;;  -- 臨時修改語句結束符，避免與存儲過程內(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í)行存儲過程，初始化數(shù)據(jù)
call insert_t1();

# 進入MySQL數(shù)據(jù)目錄（需根據(jù)實際路徑調(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ù)（恢復到步驟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 ;

-- 存儲過程插入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)建備份表，復制數(shù)據(jù)（更快速）
create table t2_bak_1114 like t2;  -- 復制表結構
insert into t2_bak_1114 select * from t2;  -- 復制數(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 ;

-- 確認刪除效果（應返回0）
select count(*) from t2 where b < 50000;
-- 剩余數(shù)據(jù)量（應返回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

# 驗證安裝（查看版本）
pt-archiver --version

-- 創(chuàng)建dba用戶，允許192.168網(wǎng)段訪問
CREATE USER 'dba'@'192.168.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Id81Gdac_a';
-- 授予全庫權限（生產(chǎn)環(huán)境可縮小權限范圍，僅授予martin庫權限）
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行顯示進度
--limit=1000 \       # 每次處理1000行
--txn-size 10000 \   # 每10000行提交一次事務
--no-safe-auto-increment \  # 不修改自增主鍵（避免影響后續(xù)插入）
--statistics \       # 輸出統(tǒng)計信息（如處理時間、行數(shù)）
--purge              # 僅刪除，不歸檔（核心參數(shù)）

-- 登錄歸檔庫（192.168.184.152）
mysql -uroot -p

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

-- 創(chuàng)建與源表結構一致的歸檔表
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用戶歸檔庫權限
CREATE USER 'dba'@'192.168.%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Id81Gdac_a';
GRANT all ON *.* TO 'dba'@'192.168.%';

# 關閉歸檔庫的防火墻（避免連接失敗）
iptables -F  # 僅測試環(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 \   # 進度顯示
--limit=1000 \       # 每次處理1000行
--txn-size 10000 \   # 事務大小
--no-safe-auto-increment \
--statistics \
--purge              # 歸檔后刪除源表數(shù)據(jù)

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

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

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

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

-- 驗證空間變化
ll -h /data/mysql/data/martin/t2.ibd  # 空間應明顯減少

use martin;

drop table if exists t3_log ;

-- 創(chuàng)建按年份分區(qū)的日志表（2016、2017、2018三個分區(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'),  -- 進入p2016分區(qū)
(2,'bbb','2016-06-01'),  -- 進入p2016分區(qū)
(3,'ccc','2017-01-01'),  -- 進入p2017分區(qū)
(4,'ddd','2018-01-01');  -- 進入p2018分區(qū)

select 
  TABLE_SCHEMA,  -- 數(shù)據(jù)庫名
  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ù)