[root@wallet01 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@wallet01 ~]# python -V Python 2.7.5 [root@wallet01 ~]# yum install -y python-pip [root@wallet01 ~]# pip -V pip 8.1.2 from /usr/lib/python2.7/site-packages (python 2.7) [root@wallet01 ~]# git clone https://github.com/danfengcao/binlog2sql.git Initialized empty Git repository in /root/binlog2sql/.git/ remote: Enumerating objects: 323, done. remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323 Receiving objects: 100% (323/323), 151.51 KiB | 245 KiB/s, done. Resolving deltas: 100% (170/170), done. [root@wallet01 ~]# cd binlog2sql [root@wallet01 binlog2sql]# pip install -r requirements.txt [root@wallet01 binlog2sql]# cd binlog2sql/ [root@wallet01 binlog2sql]# python binlog2sql.py --help usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS] [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]] [-t [TABLES [TABLES ...]]] [--only-dml] [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B] [--back-interval BACK_INTERVAL] --start-file --起始解析文件 --stop-file --终止解析文件 --start-position --起始解析位置 --stop-position --终止解析位置 --start-datetime --起始解析时间,格式'%Y-%m-%d %H:%M:%S'。 --stop-datetime --终止解析时间,格式'%Y-%m-%d %H:%M:%S'。 -d --仅解析目标db的sql -t --仅解析目标table的sql --only-dml --仅解析dml,忽略ddl。 --sql-type --仅解析指定类型,支持insert,update,delete。 -B --生成回滚SQL [root@wallet01 ~]# mysql -uroot -p Enter password: mysql> grant select,replication client,replication slave on *.* to 'fb'@'%' identified by 'fb@2019'; Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.11 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-10-12 17:18:22 | +---------------------+ 1 row in set (0.06 sec) mysql> use gsoa mysql> select count(*) from bd_city; +----------+ | count(*) | +----------+ | 372 | +----------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from bd_city; Query OK, 372 rows affected (0.09 sec) mysql> commit; Query OK, 0 rows affected (0.04 sec) mysql> select count(*) from bd_city; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-10-12 17:22:26 | +---------------------+ 1 row in set (0.00 sec) 恢复已提交事务中删除的记录 [root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2019' -dgsoa -tbd_city \ --start-file='mysql-bin.000008' --only-dml --sql-type=delete \ --start-datetime='2019-10-12 17:18:22' --stop-datetime='2019-10-12 17:22:26' >fb.log [root@wallet01 binlog2sql]# more fb.log DELETE FROM `gsoa`.`bd_city` WHERE `status`='1' AND `parent_name`='中国' AND `code`='110000' AND `name`='北京市' AND `parent_code`='CN' LIMIT 1; #start 423 end 8756 time 2019-10-12 17:21:26 [root@wallet01 binlog2sql]# tail -n 1 fb.log DELETE FROM `gsoa`.`bd_city` WHERE `status`='1' AND `parent_name`='' AND `code`='CN' AND `name`='中国' AND `parent_code`='' LIMIT 1; #start 423 end 19319 time 2019-10-12 17:21:26 [root@wallet01 binlog2sql]# python binlog2sql.py -hlocalhost -P3306 -ufb -p'fb@2019' -dgsoa -tbd_city \ --start-file='mysql-bin.000008' --start-position=423 --stop-position=19319 -B > rollback.sql [root@wallet01 binlog2sql]# more rollback.sql | grep '北京' INSERT INTO `gsoa`.`bd_city`(`status`, `parent_name`, `code`, `name`, `parent_code`) VALUES ('1', '中国', '110000', '北京市', 'CN'); #start 423 end 8756 time 2019-10-12 17:21:26 [root@wallet01 binlog2sql]# mysql -uroot -p < rollback.sql Enter password: [root@wallet01 ~]# mysql -uroot -p Enter password: mysql> use gsoa mysql> select count(*) from bd_city; +----------+ | count(*) | +----------+ | 372 | +----------+ 1 row in set (0.00 sec)创新互联网站建设公司一直秉承“诚信做人,踏实做事”的原则,不欺瞒客户,是我们最起码的底线! 以服务为基础,以质量求生存,以技术求发展,成交一个客户多一个朋友!专注中小微企业官网定制,成都网站设计、做网站,塑造企业网络形象打造互联网企业效应。
另外有需要云服务器可以了解下创新互联scvps.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。