#!/usr/bin/env bash
# 盈尺（Surinch）MySQL 误删找回演示脚本 v1.0（2026-10-23，在 MySQL 8.0.46 测试实例上跑通）
# 演示：clone 本地物理备份 -> 业务写入 -> 误删 -> 误删后继续写入 -> 起一个恢复实例 -> mysqlbinlog --exclude-gtids 跳过误删事务重放
#       -> 另一条路：从 binlog 行镜像把删掉的行拼成 INSERT 直接插回源库 -> 两条路 CHECKSUM 对比
# !!! 只能在测试实例上跑：会 DROP/CREATE 数据库 pitr_lab、在 WORK_DIR 下建目录、启动第二个 mysqld，最后把误删的行插回源库。
# 前提：源实例 log_bin=ON、binlog_format=ROW、binlog_row_image=FULL、gtid_mode=ON、已加载 clone 插件（plugin-load-add=mysql_clone.so），
#       本机能直接读源实例 datadir 下的 binlog 文件，当前账号有 BACKUP_ADMIN、CLONE_ADMIN 等权限（测试用 root）。
# 用法：MYSQL_BASE=/opt/mysql SRC_CNF=/opt/mysql/my.cnf WORK_DIR=/tmp/pitr-demo ROWS=200000 bash mysql-binlog-pitr-demo-v1.0.sh
# 相关文章：https://surinch.com/resources/mysql-binlog-pitr-clone-restore-deleted-rows
set -euo pipefail
MYSQL_BASE=${MYSQL_BASE:-/opt/mysql}
SRC_CNF=${SRC_CNF:-/opt/mysql/my.cnf}
WORK_DIR=${WORK_DIR:-/tmp/pitr-demo}
ROWS=${ROWS:-200000}
RESTORE_PORT=${RESTORE_PORT:-3399}
M="$MYSQL_BASE/bin/mysql --defaults-file=$SRC_CNF"
BINLOG="$MYSQL_BASE/bin/mysqlbinlog"
ms(){ echo $(( ($(date +%s%N) - $1) / 1000000 )); }
log(){ echo "[$(date '+%F %T')] $*"; }

DATADIR=$($M -N -e "SELECT @@datadir")
UUID=$($M -N -e "SELECT @@server_uuid")
log "源实例 $($M -N -e 'SELECT @@version') datadir=$DATADIR uuid=$UUID"
[ "$($M -N -e 'SELECT @@gtid_mode')" = "ON" ] || { echo "需要 gtid_mode=ON"; exit 1; }
[ "$($M -N -e 'SELECT @@binlog_row_image')" = "FULL" ] || echo "警告：binlog_row_image 不是 FULL，第 6 步拼回 INSERT 会缺列"

log "1) 建 pitr_lab，orders $ROWS 行（自造数据）"
$M <<SQL
DROP DATABASE IF EXISTS pitr_lab; CREATE DATABASE pitr_lab; USE pitr_lab;
CREATE TABLE orders(id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, note VARCHAR(40) NOT NULL, KEY idx_created(created_at));
CREATE TABLE payments(id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, paid DECIMAL(10,2) NOT NULL, paid_at DATETIME NOT NULL, KEY(order_id));
CREATE TABLE digits(d INT PRIMARY KEY); INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO orders(user_id,status,amount,created_at,note)
SELECT 1+MOD(n*7919,50000), IF(MOD(n,10)=0,9,1+MOD(n,4)), ROUND(10+MOD(n*31,9000)/10,2), TIMESTAMP('2026-01-01') + INTERVAL MOD(n*17,25920000) SECOND, CONCAT('n',n)
FROM (SELECT a.d+b.d*10+c.d*100+e.d*1000+f.d*10000+g.d*100000 n FROM digits a,digits b,digits c,digits e,digits f,digits g) t WHERE n < $ROWS ORDER BY n;
INSERT INTO payments(order_id,paid,paid_at) SELECT id,amount,created_at + INTERVAL 5 MINUTE FROM orders WHERE status IN (2,3) LIMIT $((ROWS/5));
SQL

log "2) clone 本地物理备份"
mkdir -p "$WORK_DIR"; rm -rf "$WORK_DIR/clone" "$WORK_DIR/restore"
t=$(date +%s%N); $M -e "CLONE LOCAL DATA DIRECTORY = '$WORK_DIR/clone';"; echo "clone_ms=$(ms $t) size=$(du -sh $WORK_DIR/clone | cut -f1)"
$M -e "SELECT state, binlog_file, binlog_position, gtid_executed FROM performance_schema.clone_status\G"

log "3) 备份后的正常写入：50 个事务各插 100 单 + 改 2000 单状态 + 30 个事务插支付"
for i in $(seq 1 50); do $M pitr_lab -e "INSERT INTO orders(user_id,status,amount,created_at,note) SELECT 1+MOD(id,50000),1,99.00,NOW(),CONCAT('after-backup-',$i) FROM orders WHERE id<=100;"; done
$M pitr_lab -e "UPDATE orders SET status=3 WHERE id BETWEEN $((ROWS/2+1)) AND $((ROWS/2+2000));"
for i in $(seq 1 30); do $M pitr_lab -e "INSERT INTO payments(order_id,paid,paid_at) SELECT id,amount,NOW() FROM orders WHERE note='after-backup-$i';"; done

log "4) 误删：本想删 1 月已取消订单（status=9），漏了 status 条件"
$M pitr_lab -e "SELECT COUNT(*) will_delete, SUM(status=9) intended FROM orders WHERE created_at < '2026-02-01';"
t=$(date +%s%N); $M pitr_lab -e "DELETE FROM orders WHERE created_at < '2026-02-01';"; echo "delete_ms=$(ms $t)"
sleep 1
log "   误删后业务继续：20 个事务插订单 + 支付"
for i in $(seq 1 20); do $M pitr_lab -e "INSERT INTO orders(user_id,status,amount,created_at,note) SELECT 1+MOD(id,50000),2,59.00,NOW(),CONCAT('after-accident-',$i) FROM orders ORDER BY id DESC LIMIT 100; INSERT INTO payments(order_id,paid,paid_at) SELECT id,amount,NOW() FROM orders WHERE note='after-accident-$i';"; done
$M -e "FLUSH BINARY LOGS;"

log "5) 定位误删事务：解码 binlog，按 GTID 统计 DELETE 行数"
FILES=$($M -N -e "SHOW BINARY LOGS" | awk '{print $1}')
t=$(date +%s%N)
BAD=$(cd "$DATADIR" && $BINLOG -v --base64-output=decode-rows $FILES | awk -v u="$UUID" '/GTID_NEXT/{match($0,/:[0-9]+/); g=substr($0,RSTART+1,RLENGTH-1)} /^### DELETE FROM `pitr_lab`.`orders`/{c[g]++} END{for(k in c) print c[k], k}' | sort -rn | head -1)
echo "find_ms=$(ms $t) 最多 DELETE 的事务: 行数 GTID序号 = $BAD"
BADNO=$(echo "$BAD" | awk '{print $2}')
BADGTID="$UUID:$BADNO"

log "6a) 路线 A：从 clone 起恢复实例（端口 $RESTORE_PORT），重放 binlog 时排除 $BADGTID"
t=$(date +%s%N); cp -a "$WORK_DIR/clone" "$WORK_DIR/restore"; echo "copy_ms=$(ms $t)"
cat > "$WORK_DIR/restore.cnf" <<CNF
[mysqld]
basedir=$MYSQL_BASE
datadir=$WORK_DIR/restore
port=$RESTORE_PORT
socket=$WORK_DIR/restore.sock
pid-file=$WORK_DIR/restore.pid
log-error=$WORK_DIR/restore.err
server_id=$RESTORE_PORT
log_bin=binlog
gtid_mode=ON
enforce_gtid_consistency=ON
skip_replica_start=ON
mysqlx=OFF
[client]
socket=$WORK_DIR/restore.sock
user=root
CNF
R="$MYSQL_BASE/bin/mysql --defaults-file=$WORK_DIR/restore.cnf"
t=$(date +%s%N); "$MYSQL_BASE/bin/mysqld" --defaults-file="$WORK_DIR/restore.cnf" >/dev/null 2>&1 &
until $R -e "SELECT 1" >/dev/null 2>&1; do sleep 0.2; done; echo "startup_ms=$(ms $t)"
echo "恢复实例 gtid_executed（= 备份点）: $($R -N -e 'SELECT @@gtid_executed')"
t=$(date +%s%N); (cd "$DATADIR" && $BINLOG --exclude-gtids="$BADGTID" $FILES) | $R; echo "replay_ms=$(ms $t)"
$R -e "SELECT @@gtid_executed AS gtid_after_replay; SELECT COUNT(*) orders FROM pitr_lab.orders; SELECT COUNT(*) payments FROM pitr_lab.payments;"
echo "   给 $BADGTID 补空事务，避免以后挂成从库时被重新拉回误删："
$R -e "SET GTID_NEXT='$BADGTID'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC'; SELECT @@gtid_executed;"

log "6b) 路线 B：从 binlog 行镜像拼 INSERT，直接插回源库"
t=$(date +%s%N)
(cd "$DATADIR" && $BINLOG --include-gtids="$BADGTID" -v --base64-output=decode-rows $FILES) | python3 -c '
import sys,re
hdr=re.compile(r"^### DELETE FROM `pitr_lab`\.`orders`"); col=re.compile(r"^###   @(\d+)=(.*)$")
rows=[];cur=None
for line in sys.stdin:
    line=line.rstrip("\n")
    if hdr.match(line):
        if cur: rows.append(cur)
        cur={};continue
    if cur is not None:
        m=col.match(line)
        if m: cur[int(m.group(1))]=m.group(2)
        elif not line.startswith("### WHERE"): rows.append(cur); cur=None
if cur: rows.append(cur)
for i in range(0,len(rows),1000):
    print("INSERT INTO pitr_lab.orders VALUES "+",".join("("+",".join(v for _,v in sorted(r.items()))+")" for r in rows[i:i+1000])+";")
sys.stderr.write("rows=%d\n"%len(rows))' > "$WORK_DIR/undo.sql"
echo "extract_ms=$(ms $t) size=$(du -h $WORK_DIR/undo.sql | cut -f1)"
t=$(date +%s%N); $M < "$WORK_DIR/undo.sql"; echo "apply_ms=$(ms $t)"

log "7) 对比：源库（路线 B 之后） vs 恢复实例（路线 A）"
$M -e "SELECT COUNT(*) src_orders FROM pitr_lab.orders; CHECKSUM TABLE pitr_lab.orders, pitr_lab.payments;"
$R -e "CHECKSUM TABLE pitr_lab.orders, pitr_lab.payments;"
log "完成。恢复实例仍在运行：$MYSQL_BASE/bin/mysqladmin --defaults-file=$WORK_DIR/restore.cnf shutdown ；清理：DROP DATABASE pitr_lab; rm -rf $WORK_DIR"