加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程 > 正文

MySQL使用binlog2sql闪回误删掉数据

发布时间:2022-03-29 18:11:37 所属栏目:编程 来源:互联网
导读:查询数据库相关配置参数 root [test] show global variables like binlog%format%; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) root [tes
      查询数据库相关配置参数
 
root [test]> show global variables like 'binlog%format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
root [test]> show global variables like 'binlog%row%image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
 
     安装binlog2sql
     正克隆到 'binlog2sql'...
remote: Counting objects: 298, done.
remote: Compressing objects: 100% (4/4), done.
remote: Total 298 (delta 0), reused 1 (delta 0), pack-reused 294
接收对象中: 100% (298/298), 147.01 KiB | 49.00 KiB/s, done.
处理 delta 中: 100% (151/151), done.
[root@mysql-server binlog2sql]# source ../venv4archer/bin/activate
(venv4archer) [root@mysql-server binlog2sql]# pip install -r requirements.txt
Requirement already satisfied: PyMySQL==0.7.11 in /data1/venv4archer/lib/python3.6/site-packages (from -r requirements.txt (line 1)) (0.7.11)
Collecting wheel==0.29.0 (from -r requirements.txt (line 2))
  Cache entry deserialization failed, entry ignored
  Downloading https://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66kB)
 
Collecting mysql-replication==0.13 (from -r requirements.txt (line 3))
  Downloading https://files.pythonhosted.org/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2/mysql-replication-0.13.tar.gz
Building wheels for collected packages: mysql-replication
  Running setup.py bdist_wheel for mysql-replication ... done
  Stored in directory: /root/.cache/pip/wheels/91/33/05/32b16ccadd4fc566ff38af96afdeb5d57d49c2f1eff0402164
Successfully built mysql-replication
Installing collected packages: wheel, mysql-replication
  Found existing installation: wheel 0.31.1
    Uninstalling wheel-0.31.1:
      Successfully uninstalled wheel-0.31.1
      Successfully installed mysql-replication-0.13 wheel-0.29.0
 
      查看二进制日志文件
 
root [test]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |      2236 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |      1218 |
+------------------+-----------+
3 rows in set (0.00 sec)
 
      解析出回滚SQL
 
(venv4archer) [root@mysql-server binlog2sql]# python binlog2sql/binlog2sql.py -uroot -p'root' -h 127.0.0.1 -P 23306 -dtest -tuser --start-file='mysql-bin.000004' --start-datetime='2018-09-14 17:00:00' --stop-datetime='2018-09-14 18:25:00' -B > /tmp/20180914_rollback.sql
(venv4archer) [root@mysql-server binlog2sql]# cat //tmp/20180914_rollback.sql
INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (2, 'trinity', '2018-09-02 00:00:00'); #start 855 end 1187 time 2018-09-14 18:23:31
INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (1, 'neo', '2018-09-01 00:00:00'); #start 855 end 1187 time 2018-09-14 18:23:31
DELETE FROM `test`.`user` WHERE `id`=3 AND `name`='jason' AND `add_time`='2018-09-05 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='trinity' AND `add_time`='2018-09-02 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='neo' AND `add_time`='2018-09-01 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33

(编辑:核心网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读