真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

MySQL回滾工具binlog2sql使用介紹

參數(shù)介紹：
參考官網(wǎng)地址：
https://github.com/danfengcao/binlog2sql

成都創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)與策劃設(shè)計(jì),靖安網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:靖安等地區(qū)。靖安做網(wǎng)站價(jià)格咨詢:13518219792

直接從官網(wǎng)下載軟件包，照著github上給的方法，雖然可以安裝成功，但是執(zhí)行如下命了報(bào)錯(cuò)，由于此工具源代碼是是別人所寫，目前暫時(shí)沒(méi)找到是代碼的哪個(gè)地方導(dǎo)致的語(yǔ)法錯(cuò)誤。有知道的網(wǎng)友朋友可以友情提醒下，謝謝。

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='MySQL-bin.000006' -B  |awk -F '[;]' '{print $1 ";"}'|grep 'INSERT'
  File "binlog2sql.py", line 73
    with temp_open(tmp_file, "w") as f_tmp, self.connection as cursor:
                                          ^
SyntaxError: invalid syntax
[root@git-server binlog2sql]# pwd
/root/binlog2sql
[root@git-server binlog2sql]#

于是采用去年在https://github.com上下載的binlog2sql代碼包，安裝正常，而且也沒(méi)有報(bào)錯(cuò)。
查看此工具的參數(shù)說(shuō)明介紹請(qǐng)參考：
https://github.com/danfengcao/binlog2sql

于是才有下面的測(cè)試：
查看當(dāng)前的記錄到日志的binlog文件

MySQL [zixun3]> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 686
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

為了方便測(cè)試，刷新binlog，生成 新的binlog日志文件來(lái)記錄mysql的操作的sql

MySQL [zixun3]> flush logs;
Query OK, 0 rows affected (0.07 sec)

MySQL [zixun3]> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

MySQL [zixun3]> 

MySQL [zixun3]> update zixun3.zx_scores set titles='張三' where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [zixun3]> select * from zx_scores;
+----+--------+------+----------+-----------+
| id | titles | icon | integral | isdefault |
+----+--------+------+----------+-----------+
|  2 | 列兵   |    1 |        0 |         1 |
|  3 | 班長(zhǎng)   |    2 |     1000 |         1 |
|  4 | 少尉   |    3 |     2000 |         1 |
|  5 | 中尉   |    4 |     3000 |         1 |
|  6 | 上尉   |    5 |     4000 |         1 |
|  7 | 少校   |    6 |     5000 |         1 |
|  8 | 中校   |    7 |     6000 |         1 |
|  9 | 上校   |    8 |     9000 |         1 |
| 10 | 少將   |    9 |    14000 |         1 |
| 11 | 中將   |   10 |    19000 |         1 |
| 12 | 張三   |   11 |    24000 |         1 |
| 15 | 大將   |   12 |    29000 |         1 |
+----+--------+------+----------+-----------+
12 rows in set (0.00 sec)

python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  -B|more
UPDATE `zixun3`.`zx_scores` SET `titles`='上將', `integral`=24000, `id`=12, `isdefault`=1, `icon`=11 WHERE `titles`='張三' AND `integral`=24000 AND `id`=12 AND `isdefault`=1 AND `icon`=11 LIM
IT 1; #start 4 end 328 time 2018-05-23 14:55:23

-B參數(shù)使用是生成回滾的語(yǔ)句

binlog內(nèi)容格式簡(jiǎn)單說(shuō)明如下：

[root@git-server vhost]# mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v  /data/mysql/data/mysql-bin.000006
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#at 4
#180523 14:51:19 server id 1  end_log_pos 120 CRC32 0xda6d72fe  Start: binlog v 4, server v 5.6.36-log created 180523 14:51:19
#Warning: this binlog is either in use or was not closed properly.
#at 120
#180523 14:55:23 server id 1  end_log_pos 194 CRC32 0x84ca9830  Query   thread_id=77    exec_time=0 error_code=0
SET TIMESTAMP=1527058523/*!*/;
SET @@session.pseudo_thread_id=77/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
#at 194
#180523 14:55:23 server id 1  end_log_pos 254 CRC32 0xf289bf8a  Table_map: `zixun3`.`zx_scores` mapped to number 70
#at 254
#180523 14:55:23 server id 1  end_log_pos 328 CRC32 0x1f932ec9  Update_rows: table id 70 flags: STMT_END_F
###UPDATE `zixun3`.`zx_scores`
###WHERE
###@1=12
###@2='上將'
###@3=11
###@4=24000
###@5=1
###SET
###@1=12
###@2='張三'
###@3=11
###@4=24000
###@5=1
#at 328
#180523 14:55:23 server id 1  end_log_pos 403 CRC32 0xab4af087  Query   thread_id=77    exec_time=0 error_code=0
SET TIMESTAMP=1527058523/*!*/;
COMMIT
/*!*/;
DELIMITER ;
#End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

基于位置點(diǎn)的恢復(fù)：

BEGIN 和COMMIT之間的才是需要回滾的sql語(yǔ)句。回滾的位置點(diǎn)是：194---328

BEGIN
/*!*/;
#at 194
#180523 14:55:23 server id 1  end_log_pos 254 CRC32 0xf289bf8a  Table_map: `zixun3`.`zx_scores` mapped to number 70
#at 254
#180523 14:55:23 server id 1  end_log_pos 328 CRC32 0x1f932ec9  Update_rows: table id 70 flags: STMT_END_F
### UPDATE `zixun3`.`zx_scores`
### WHERE
###   @1=12
###   @2='上將'
###   @3=11
###   @4=24000
###   @5=1
### SET
###   @1=12
###   @2='張三'
###   @3=11
###   @4=24000
###   @5=1
#at 328
#180523 14:55:23 server id 1  end_log_pos 403 CRC32 0xab4af087  Query   thread_id=77    exec_time=0 error_code=0
SET TIMESTAMP=1527058523/*!*/;
COMMIT
/*!*/;

直接登陸MySQL執(zhí)行 上面的回滾sql語(yǔ)句就可以了
MySQL [zixun3]> delete from zx_scores;
Query OK, 12 rows affected (0.00 sec)
MySQL [zixun3]> select * from zx_scores;
Empty set (0.00 sec)

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  --start-position=760 --stop-position=1083  -B
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大將', 29000, 15, 1, 12); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上將', 24000, 12, 1, 11); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中將', 19000, 11, 1, 10); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少將', 14000, 10, 1, 9); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班長(zhǎng)', 1000, 3, 1, 2); #start 760 end 1083 time 2018-05-23 15:35:59
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1); #start 760 end 1083 time 2018-05-23 15:35:59

此sql格式還不能直接用來(lái)恢復(fù)到數(shù)據(jù)庫(kù)中，需要簡(jiǎn)單的處理下：

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006'  --start-position=760 --stop-position=1083  -B|awk -F '[;]' '{print $1 ";"}'
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大將', 29000, 15, 1, 12);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上將', 24000, 12, 1, 11);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中將', 19000, 11, 1, 10);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少將', 14000, 10, 1, 9);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班長(zhǎng)', 1000, 3, 1, 2);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1);

基于時(shí)間點(diǎn)的恢復(fù)介紹：

按照時(shí)間點(diǎn)提取sql日志說(shuō)明：誤刪除sql語(yǔ)句delete from zx_scores;的binlog日志的生成時(shí)間點(diǎn)到下一次DML操作 delete from zx_vote ;記錄到binlog日志的時(shí)間點(diǎn)。這個(gè)時(shí)間段的時(shí)間點(diǎn)才可以找到需要恢復(fù)的sql語(yǔ)句

[root@git-server binlog2sql]# python binlog2sql.py -h227.0.0.1 -P3306 -uadmin -p'admin'  -dzixun3 -tzx_scores --start-file='mysql-bin.000006' --start-datetime='2018-05-23 15:35:59' --stop-datetime='2018-05-23 16:09:59'  -B|awk -F '[;]' '{print $1 ";"}'
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('大將', 29000, 15, 1, 12);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上將', 24000, 12, 1, 11);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中將', 19000, 11, 1, 10);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少將', 14000, 10, 1, 9);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上校', 9000, 9, 1, 8);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中校', 6000, 8, 1, 7);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少校', 5000, 7, 1, 6);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('上尉', 4000, 6, 1, 5);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('中尉', 3000, 5, 1, 4);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('少尉', 2000, 4, 1, 3);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('班長(zhǎng)', 1000, 3, 1, 2);
INSERT INTO `zixun3`.`zx_scores`(`titles`, `integral`, `id`, `isdefault`, `icon`) VALUES ('列兵', 0, 2, 1, 1);

直接在mysql中恢復(fù)就可以了


文章標(biāo)題：MySQL回滾工具binlog2sql使用介紹
當(dāng)前URL：http://weahome.cn/article/jjcjoj.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部