-- 1. 創(chuàng)建測(cè)試庫(kù)表與基礎(chǔ)數(shù)據(jù)
create database recover;
use recover;
CREATE TABLE test_recover (
 id int NOT NULL AUTO_INCREMENT,
 a int NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into test_recover values (1,1),(2,2);

-- 2. 創(chuàng)建備份專用用戶（遵循最小權(quán)限原則）
CREATE USER `u_backup`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Ujg8G_aUU';
GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES,BACKUP_ADMIN ON *.* TO `u_backup`@`localhost`;

-- 3. 執(zhí)行全量備份（使用xtrabackup工具）
cd /data/backup/xtrabackup_bak
xtrabackup --defaults-file=/data/mysql/conf/my.cnf -uu_backup -p'Ujg8G_aUU' --backup --stream=xbstream --target-dir=./ >/data/backup/xtrabackup_bak/xtrabackup.xbstream

-- 1. 插入全備后的增量數(shù)據(jù)
use recover;
insert into test_recover values (3,3);

-- 2. 驗(yàn)證數(shù)據(jù)（此時(shí)表中應(yīng)有3條數(shù)據(jù)）
select * from test_recover;

-- 3. 模擬誤刪表
drop table test_recover;

# 1. 傳輸備份文件到恢復(fù)實(shí)例（示例IP：192.168.184.152）
# 恢復(fù)實(shí)例操作：創(chuàng)建接收目錄
mkdir /data/backup/recover
# 源實(shí)例操作：傳輸文件
scp /data/backup/xtrabackup.xbstream 192.168.184.152:/data/backup/recover

# 2. 恢復(fù)實(shí)例清空環(huán)境（僅恢復(fù)實(shí)例執(zhí)行！）
/etc/init.d/mysql.server stop
rm /data/mysql/data/* -rf
rm /data/mysql/binlog/* -rf

# 3. 解壓并恢復(fù)全備數(shù)據(jù)
cd /data/backup/recover
xbstream -x backup.xbstream
xtrabackup --prepare --target-dir=./
xtrabackup --defaults-file=/data/mysql/conf/my.cnf --copy-back --target-dir=./
chown -R mysql.mysql /data/mysql
/etc/init.d/mysql.server start

# 4. 驗(yàn)證全備恢復(fù)結(jié)果（僅能看到基礎(chǔ)數(shù)據(jù)1,1和2,2）
mysql -uroot -p -e "select * from recover.test_recover;"

# 1. 查看全備對(duì)應(yīng)的Binlog起點(diǎn)（獲取文件名與位點(diǎn)）
cat /data/backup/recover/xtrabackup_binlog_info
# 輸出示例：mysql-bin.000026	196	dac2d0d1-d4cc-11f0-8896-000c295cba4b:1-3395053

# 2. 傳輸源實(shí)例Binlog文件到恢復(fù)實(shí)例
scp 192.168.184.151:/data/mysql/binlog/mysql-bin.000026/data/backup/binlog

# 3. 定位drop操作前的有效事務(wù)
cd /data/backup/binlog
mysqlbinlog mysql-bin.000026 --start-datetime='2026-02-11 10:20:00' --stop-datetime='2026-02-11 14:40:00' --base64-output=decode-rows -v > 1.sql
cat 1.sql | grep -A 5 -B 5 "DROP TABLE"
# 找到GTID范圍：dac2d0d1-d4cc-11f0-8896-000c295cba4b:3395054

# 4. 創(chuàng)建恢復(fù)專用用戶
mysql -uroot -p 
CREATE USER `u_recover`@`localhost` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'Ujg8G_aUU'; 
GRANT insert,SESSION_VARIABLES_ADMIN,REPLICATION_APPLIER ON *.* TO `u_recover`@`localhost`;"

# 5. 回放Binlog增量數(shù)據(jù)
mysqlbinlog --include-gtids='dac2d0d1-d4cc-11f0-8896-000c295cba4b:3395035-3395054' mysql-bin.000026 | mysql -uu_recover -p'Ujg8G_aUU'

# 6. 驗(yàn)證增量恢復(fù)（此時(shí)應(yīng)有3條完整數(shù)據(jù)）
mysql -uroot -p -e "select * from recover.test_recover;"

# 1. 恢復(fù)實(shí)例導(dǎo)出完整表
mysqldump -uroot -p --set-gtid-purged=off --skip-add-drop-table recover test_recover > recover_test_recover.sql

# 2. 傳輸?shù)皆磳?shí)例
scp recover_test_recover.sql 192.168.184.151:/data/backup

# 3. 源實(shí)例導(dǎo)入數(shù)據(jù)
mysql -uroot -p recover data/backup/recover_test_recover.sql

# 4. 最終驗(yàn)證
mysql -uroot -p -e "select * from recover.test_recover;"

cd /data/backup
wget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql
chmod +x my2sql
./my2sql -h  # 驗(yàn)證安裝

-- 1. 創(chuàng)建測(cè)試庫(kù)表與數(shù)據(jù)
create database d_recover;
use d_recover;
CREATE TABLE del_t1 (
  id int NOT NULL AUTO_INCREMENT,
  a int NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into del_t1 values (1,1),(2,2);

-- 2. 創(chuàng)建解析專用用戶
CREATE USER `u_rollback`@`127.0.0.1` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'IgdI8G_aUU';
GRANT SELECT, REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO `u_rollback`@`127.0.0.1`;

-- 1. 模擬delete誤刪
delete from d_recover.del_t1;

# 2. 復(fù)制目標(biāo)Binlog文件
mkdir /data/backup/rollback
cp /data/mysql/binlog/mysql-bin.000026 /data/backup/rollback

# 3. 定位delete操作位點(diǎn)
cd /data/backup/rollback
mysqlbinlog mysql-bin.000026 --start-datetime='2026-02-11 15:25:00' --stop-datetime='2026-02-11 15:32:00' --base64-output=decode-rows -v > operation.sql
cat operation.sql | grep -A 10 -B 10 "delete from d_recover.del_t1"
# 記錄start-pos（3511）與stop-pos（3674）

# 1. 生成回滾SQL
cd /data/backup
mkdir recover_01
./my2sql -user u_rollback -password 'IgdI8G_aUU' -host 127.0.0.1 -databases d_recover -tables del_t1 -work-type rollback -start-file mysql-bin.000023 -start-pos 3511 -stop-pos 3674 -output-dir recover_01

# 2. 驗(yàn)證回滾SQL（應(yīng)為insert語(yǔ)句）
cat recover_01/rollback.23.sql

# 3. 執(zhí)行回滾
mysql -uroot -p <recover_01/rollback.23.sql

# 4. 驗(yàn)證結(jié)果
mysql -uroot -p -e "select * from d_recover.del_t1;"