mysql資料誤刪後的資料回滾

guixiang發表於2024-11-02
MySql

mysql資料誤刪後的資料回滾

第一步：下載MyFlash工具

# 建立資料夾
mkdir  /back_data
cd /back_data

# 下載壓縮包
wget https://codeload.github.com/Meituan-Dianping/MyFlash/zip/master 

# 安裝編譯相關軟體
yum install gcc -y
yum install glib2 glib2-devel -y

# 解壓縮包
yum -y install unzip
unzip master

# 進入軟體目錄
cd /back_data/MyFlash-master

# 編譯
sh build.sh

#驗證
cd binary
./flashback --help

如果顯示

[root@localhost binary]# ll
總用量 7380
-rwxr-xr-x. 1 root root   87648 10月 31 14:20 flashback
-rwxr-xr-x. 1 root root 7463125 11月  5 2020 mysqlbinlog20160408
[root@localhost binary]# ./flashback --help
Usage:
  flashback [OPTION?]

Help Options:
  -h, --help                  Show help options

Application Options:
  --databaseNames             databaseName to apply. if multiple, seperate by comma(,)
  --tableNames                tableName to apply. if multiple, seperate by comma(,)
  --tableNames-file           tableName to apply. if multiple, seperate by comma(,)
  --start-position            start position
  --stop-position             stop position
  --start-datetime            start time (format %Y-%m-%d %H:%M:%S)
  --stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
  --sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
  --maxSplitSize              max file size after split, the uint is M
  --binlogFileNames           binlog files to process. if multiple, seperate by comma(,)
  --outBinlogFileNameBase     output binlog file name base
  --logLevel                  log level, available option is debug,warning,error
  --include-gtids             gtids to process. if multiple, seperate by comma(,)
  --include-gtids-file        gtids to process. if multiple, seperate by comma(,)
  --exclude-gtids             gtids to skip. if multiple, seperate by comma(,)
  --exclude-gtids-file        gtids to skip. if multiple, seperate by comma(,)

[root@localhost binary]#

即安裝成功!

第二步：開啟binlog日誌

#登入資料庫 （一般或是mysql -uroot -p123456）
[root@localhost binary]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

#查詢binlog日誌是否開啟

 mysql> show variables%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE '%binlog_row_image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

#開啟binlog日誌
#第一個：binlog_format=row 

[root@localhost ~]# cd /etc/
[root@localhost etc]# vim my.cnf

[mysqld]
log-bin=mysql-bin
binlog_format=row
server_id=1


#第二個：binlog_row_image=FULL。
#預設開啟

#重啟mysqld
[root@localhost etc]# systemctl restart mysqld

第三步：檢查第二步

[root@localhost etc]# systemctl restart mysqld
[root@localhost etc]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%binlog_row_image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /application/mysql/data/mysql-bin       |
| log_bin_index                   | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql>

第四步：開始測試

基礎用法

# 檢視所有binglog日誌
SHOW MASTER LOGS;


# 當前使用的日誌
show master status;

# 檢視日誌記錄
show binlog events in '日誌檔名';

建立測試表

create database if EXISTS itcast;

use itcast;

create table tb_user(
	id int(11) not null,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

insert into tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');

第五步：誤刪資料庫

檢視刪庫後的binlog

第六步：新建binglog，減少外來日誌影響

之後立即flush logs; 生成新的binlog

由於我們執行 flush logs 命令新生了一個檔案，所以我們執行的刪除的命令應該在 binlog.000001 檔案裡面

檢視具體的命令列

show binlog events in 'mysql-bin.000001';

得到資料恢復的起始位置為 120，結束位置為 1166，

mysqlbinlog輔助恢復

接下來使用 mysqlbinlog 命令執行 binlog 檔案，恢復資料，命令如下：

mysqlbinlog -v /application/mysql-5.6.40/data/mysql-bin.000001 --start-position=120 --stop-position=1166 | mysql -uroot -p123456

相關文章