MySQL执行delete误删除数据恢复
binlog格式必须为row,且binlog_row_image=full（表示记录的二进制日志将包含所有列的值，包括旧值和新值）
只能回滚DML， 不能回滚DDL 8.0 用户密码认证必须是mysql_native_password才能解析
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);
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`;
mkdir /data/backup/rollback -p cd /data/backup/rollback wget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql
3 模拟误操作
delete from d_recover.del_t1;
mkdir /data/backup/rollbackcp /data/mysql/binlog/mysql-bin.000012 /data/backup/rollback
cd /data/backup/rollback mysqlbinlog mysql-bin.000012 --start-datetime='2023-06-21 01:20:00' --stop-datetime='2023-06-21 01:40:00' --base64-output=decode-rows -v > operation.sql
找到误操作的Binlog，并确定开始和结束的位点
/data/backup/rollback ./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.000012 -start-pos 3556 -stop-pos 3719 -output-dir recover_01
cat recover_01/rollback.12.sql
mysql -uroot -p <recover_01/rollback.12.sql
select * from d_recover.del_t1;
感谢分享 有用
发表评论