binlog2sql 回滚误操作

缺乏、安全感 2021-09-20 12:00 379阅读 0赞

*  参考过在资料:  
    https://github.com/wuyongshenghub/mysqlbinlog2sql  
    https://www.cnblogs.com/xuanzhi201111/p/6602489.html
 *  下载及安装

#git clone https://github.com/danfengcao/binlog2sql.git
    Cloning into 'binlog2sql'...
    remote: Counting objects: 294, done.
    remote: Total 294 (delta 0), reused 0 (delta 0), pack-reused 294
    Receiving objects: 100% (294/294), 142.05 KiB | 109.00 KiB/s, done.
    Resolving deltas: 100% (152/152), done.
    [root@db210_15:21:00 /opt]  
    #cd binlog2sql/
    [root@db210_15:21:06 /opt/binlog2sql]  
    #ll
    total 52
    drwxr-xr-x 2 root root    69 Aug  2 15:21 binlog2sql
    drwxr-xr-x 2 root root    53 Aug  2 15:21 example
    -rw-r--r-- 1 root root 35141 Aug  2 15:21 LICENSE
    -rw-r--r-- 1 root root  9517 Aug  2 15:21 README.md
    -rw-r--r-- 1 root root    54 Aug  2 15:21 requirements.txt
    drwxr-xr-x 2 root root    36 Aug  2 15:21 tests
    [root@db210_15:21:07 /opt/binlog2sql]  
    #pip install -r requirements.txt       // 安装依赖包
    Collecting PyMySQL==0.7.11 (from -r requirements.txt (line 1))
      Downloading https://files.pythonhosted.org/packages/c6/42/c54c280d8418039bd2f61284f99cb6d9e0eae80383fc72ceb6eac67855fe/PyMySQL-0.7.11-py2.py3-none-any.whl (78kB)
        100% |████████████████████████████████| 81kB 444kB/s 
    Collecting wheel==0.29.0 (from -r requirements.txt (line 2))
      Downloading https://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66kB)
        100% |████████████████████████████████| 71kB 1.9MB/s 
    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
    Installing collected packages: PyMySQL, wheel, mysql-replication
      Found existing installation: PyMySQL 0.8.1
        Uninstalling PyMySQL-0.8.1:
          Successfully uninstalled PyMySQL-0.8.1
      Running setup.py install for mysql-replication ... done
    Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0
    You are using pip version 8.1.2, however version 18.0 is available.
    You should consider upgrading via the 'pip install --upgrade pip' command.
    [root@db210_15:21:31 /opt/binlog2sql]  
    #pip install --upgrade pip
    Collecting pip
      Downloading https://files.pythonhosted.org/packages/5f/25/e52d3f31441505a5f3af41213346e5b6c221c9e086a166f3703d2ddaf940/pip-18.0-py2.py3-none-any.whl (1.3MB)
        100% |████████████████████████████████| 1.3MB 675kB/s 
    Installing collected packages: pip
      Found existing installation: pip 8.1.2
        Uninstalling pip-8.1.2:
          Successfully uninstalled pip-8.1.2
    Successfully installed pip-18.0
    
    #pip install -r requirements.txt
    Requirement already satisfied: PyMySQL==0.7.11 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 1)) (0.7.11)
    Requirement already satisfied: wheel==0.29.0 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 2)) (0.29.0)
    Requirement already satisfied: mysql-replication==0.13 in /usr/lib/python2.7/site-packages (from -r requirements.txt (line 3)) (0.13)
    [root@db210_17:16:44 /opt/binlog2sql]

*  授权mysql 执行账号:

root@localhost [wenyz]>grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT on *.* to wyz@'%'; Query OK, 0 rows affected (0.00 sec) root@localhost [wenyz]>show grants for wyz@'%'; +-------------------------------------------------------------------------+ | Grants for wyz@% | +-------------------------------------------------------------------------+ | GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'wyz'@'%' | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

*  模拟删除数据.

root@localhost [wenyz]>select count(*) from t2; +----------+ | count(*) | +----------+ | 163798 | +----------+ 1 row in set (0.08 sec) root@localhost [wenyz]>delete from t2 limit 20; Query OK, 20 rows affected (0.00 sec) root@localhost [wenyz]>flush logs; Query OK, 0 rows affected (0.01 sec) root@localhost [wenyz]>show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000236 | 268440480 | | mysql-bin.000237 | 268436866 | | mysql-bin.000238 | 268438786 | | mysql-bin.000239 | 268438976 | | mysql-bin.000240 | 55243968 | | mysql-bin.000241 | 1310 | | mysql-bin.000242 | 444 | | mysql-bin.000243 | 2986 | //删除记录在此文件中 | mysql-bin.000244 | 194 | +------------------+-----------+ 244 rows in set (0.00 sec)

*  执行mysqlbinlog2sql

[root@db210_17:25:54 /opt/binlog2sql/binlog2sql]  
    #python binlog2sql.py -uwyz -h127.0.0.1 -P3506 -pzstzst -dwenyz --start-file='mysql-bin.000243' > delsql.sql
    Traceback (most recent call last):
      File "binlog2sql.py", line 6, in <module>
        import pymysql
    ModuleNotFoundError: No module named 'pymysql'
    [root@db210_17:26:00 /opt/binlog2sql/binlog2sql]  
    #vi binlog2sql.py
    #!/usr/bin/env python2   //把python 为python2
    # -*- coding: utf-8 -*-
    
    
    结果还报错,后来是因为我环境中python指向的是3.5
    #python binlog2sql.py -uwyz -h127.0.0.1 -P3506 -pzstzst -dwenyz --start-file='mysql-bin.000243' > delsql.sql
    Traceback (most recent call last):
      File "binlog2sql.py", line 6, in <module>
        import pymysql
    ModuleNotFoundError: No module named 'pymysql'
    
    
    #python2 binlog2sql.py -uwyz -h127.0.0.1 -P3506 -pzstzst -dwenyz --start-file='mysql-bin.000243' > delsql.sql
    [root@db210_17:26:56 /opt/binlog2sql/binlog2sql]  
    #ll
    #cat delsql.sql limit 20
    USE wenyz;
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'wyz'@'%';
    DELETE FROM `wenyz`.`t2` WHERE `date`='2018-07-29' AND `id`=3866964 AND `ti`='ffddssoie' LIMIT 1; #start 2370 end 2908 time 2018-08-02 17:13:25
    ... ...

*  通过以上操作可以确定我们删除数据操作的事件位置,并执行以下命令恢复数据.

[root@db210_21:17:12 /opt/binlog2sql/binlog2sql]  
    #python2 binlog2sql.py -uwyz -h127.0.0.1 -P3506 -pzstzst -dwenyz --start-file='mysql-bin.000243' -B --start-pos=2370 --end-pos=2908 |mysql -S /tmp/mysql3506.sock -p
    Enter password: 
    [root@db210_21:17:45 /opt/binlog2sql/binlog2sql]

mysql --login-path=p3506 (wd: /tmp) root@localhost [wenyz]>select count(*) from t2; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 32 Current database: wenyz +----------+ | count(*) | +----------+ | 163798 | +----------+ 1 row in set (0.04 sec) root@localhost [wenyz]>

转载于:https://www.cnblogs.com/2woods/p/9398643.html