MySQL-Entschlüsselung -> So zeigen Sie die originellste SQL-Anweisung im Binärprotokoll-ROW-Modus an

MySQLsBinlogsROWMuster Parsen

Nach MySQL5.6 Master-Slave-Datenkonsistenz Die Anforderungen sind gestiegen und das Anweisungsformat ist zunehmend weniger für Geschäftsanforderungen geeignet. Daher übernimmt jeder in der Produktionsumgebung den Zeilenmodus, der die geringsten Datenänderungen überträgt, um Master-Slave-Daten zu übertragen Was ist in binlog der Unterschied zum normalen Anweisungsmodus? Können Sie die ursprüngliche SQL-Anweisung sehen?

1, bereiten Sie sich auf die Dateneingabe vor

mysql> create table test1(id int,c1 varchar(20),type int,address varchar(20),create_time datetime);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into test1 select 1,'zhangsan','1','zhangsan road No 870,floor 602',now();
ERROR 1406 (22001): Data too long for column 'address' at row 1
mysql>
mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(20) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> alter table test1 modify  `address` varchar(200) DEFAULT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> insert into test1 select 1,'zhangsan','1','zhangsan road No 870,floor 602',now();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> insert into test1 select 2,'lisi','1','zhangsan road No 870,floor 602',now();
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql>

 

mysql>  show binlog events in 'mysql-bin.000216';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                             |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| mysql-bin.000216 |   4 | Format_desc |        63 |         120 | Server ver: 5.6.12-log, Binlog ver: 4                                            |
| mysql-bin.000216 | 120 | Query       |        63 |         200 | BEGIN                                                                            || mysql-bin.000216 | 200 | Rows_query  |        63 |         302 | # insert into test1 select 3,'wanger','3','zhangsan road No 870,floor 603',now() || mysql-bin.000216 | 302 | Table_map   |        63 |         359 | table_id: 74 (test.test1)                                                        |
| mysql-bin.000216 | 359 | Write_rows  |        63 |         447 | table_id: 74 flags: STMT_END_F                                                   |
| mysql-bin.000216 | 447 | Xid         |        63 |         478 | COMMIT /* xid=208 */                                                             |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
 
mysql>

2, ReiheModus Binlog ist verstümmelt

Im Zeilenmodus wird die DDL-Anweisung im Binlog normal angezeigt, aber Das DML ist verstümmelt, wie unten gezeigt:

[root@hch_test_dbm1_121_63 binlog]# / usr /local/mysql/bin/mysqlbinlog mysql-bin.000215

…… # Das Vorhergehende wird hier weggelassen

#160722 17 :02: 38 Server-ID 62 end_log_pos 4291 CRC32 0x369e3244 Abfrage thread_id=60 exec_time=4294967271 error_code=0

SET TIMESTAMP=1469178158/*!*/;

DATENBANK ERSTELLEN, WENN NICHT EXISTIERT `percona ` /* pt-table-checksum */

/*!*/;

# at 4291

#160722 17:02:38 Server-ID 62 end_log_pos 5079 CRC32 0x8abc6e67 Query thread_id =60 exec_time=4294967271 error_code=0

use `percona`/*!*/;

SET TIMESTAMP=1469178158/*!*/;

CREATE TABLE IF NOT Exists `Percona`.`checksums` (

DB Char (64) Not Null,

TBL CHAR (64) Not null,

chunk int not null ,

chunk_time float NULL,

chunk_index varchar(200) NULL,

Lower_boundary text NULL,

Upper_boundary text NULL,

this_crc char( 40) NOT NULL,

this_cnt int NOT NULL,

master_crc char(40) NULL,

master_cnt int NULL,

ts timestamp NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP,

PRIMÄRSCHLÜSSEL (db, tbl, chunk),

INDEX ts_db_tbl (ts, db, tbl)

) ENGINE=InnoDB

/*!*/;

# bei 5079

#160820 10:21:10 Server-ID 63 end_log_pos 5280 CRC32 0xd147bd8e Abfrage thread_id=16 exec_time=0​ error_code= 0

SET TIMESTAMP=1471659670/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

GEWÄHREN SIE ALLE PRIVILEGIEN AUF *.* TO 'tim' @'192.168%' IDENTIFIED BY PASSWORD '*2976819BD2CCD13612E03F812A2CD297C1A18B23'

/*!*/;

# bei 5280

#160820 10: 22:40 Server-ID 63 end_log_pos 5445 CRC32 0x85811be7 Abfrage thread_id=18 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1471659760/*!*/;

create table test1(id int,c1 varchar(20),type int,address varchar(20),create_time datetime)

/*!*/;

# at 5445

#160820 10:24:34 Server -ID 63 END_LOG_POS 5580 CRC32 0x26220c Query Thread_id = 18 exec_time = 0 error_code = 0

Timestamp = 1471659874/*!

alter table test1 changes `address` varchar(200) DEFAULT NULL

/*!*/;

# at 5580

#160820 10:24:36 Server-ID 63  end_log_pos 5660 CRC32 0x7b7c645f      Abfrage       thread_id=18         exec_time=0    error_code=0

SET. TIMESTAMP=1471659876 /*!*/;

SET @@session.time_zone='SYSTEM'/*!*/;

BEGIN

/*!*/;

# bei 5660

# bei 5764

#160820 10:24:36 Server-ID 63  end_log_pos 5821 CRC32 0x08bc94c3      Table_map: `test`.`test1` zugeordnet zu  Nummer 74

# bei 5821

#160820 10:24:36 Server-ID 63  end_log_pos 5911 CRC32 0x2f577f52       Write_rows: Tabellen-ID 74 Flags:  STMT_END_F

 

BINLOG '

ZL+3VxM/ AAAAOQAAAL0WAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB/DlLwI

ZL+3Vx4/AAAAWgAAABcXAAAAAEoAAAAAAAEAAgAF/+ABAAAACHpoYW5nc2FuAQAAAB4Aemhhbmdz

YW4gcm9hZCBObyA4NzAsZmxvb3IgNjAymZoopiRSf1cv

'/*!*/;

# bei 5911

#160820 10:24:36 Server-ID 63  end_log_pos 5942 CRC32 0xb26af81b     Server-ID 63  end_log_pos 6022 CRC32 0x09eab31d     Abfrage       thread_id=18         exec_time=0    error_code=0

SET TIMESTAMP=1471659888/*!*/;

BEGIN

/*!* /;

# bei 6022

# bei 6122

#160820 10:24:48 Server-ID 63  end_log_pos 6179 CRC32 0xdc6dc34b      Table_map: `test`.`test1` zugeordnet zu  Nummer 74

# bei 6179

#160820 10:24:48 Server-ID 63  end_log_pos 6265 CRC32 0x5f7ad700      Write_rows: Tabellen-ID 74 Flags: STMT_END_F

 

BINLOG '

cL+3VxM/AAAAOQAAACMYAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB9Lw23c

cL+3Vx4/AAAAVgAAAHkYAAAAAEoAAAAAAAEAAgAF/+ACAAA ABGxpc2kBAAAAHgB6aGFuZ3NhbiBy

b2FkIE5vIDg3MCxmbG9vciA2MDKZmiimMADXel8=

'/*!*/;

# bei 6265

#160820 10:24:48 Server-ID 63  end_log_pos 6296 CRC32 0xf6833d28      Xid = 200

COMMIT/*!*/;

# bei 6296

# 160820 10:31:30 Server-ID 63 END_LOG_POS 6343 CRC32 0xcfcdd344 Drehen der Protokolldatei

ROLLBACK /* hinzugefügt von mysqlbinlog */;

/*!50003 SET  COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET  @@SESSION .PSEUDO_SLAVE_MODE=0*/;

[root@hch_test_dbm1_121_63 binlog]#

3, bis --base64-output =decode-rows -v zum Anzeigen der dml-Anweisung

Ausführungsbefehl ist : /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000215

Sie können Folgendes sehen Wirkung, aber es sind alles die ursprünglichen DML-Block-SQL-Anweisungen:

[root@hch_test_dbm1_121_63 binlog]#  /usr/local/mysql/bin/mysqlbinlog   --base64-output=decode-rows -v   mysql-bin.000215

/*!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 /*!*/;

……

#160820 10:24:36 server id 63  end_log_pos 5821 CRC32 0x08bc94c3      Table_map: `test`.`test1` mapped to  number 74

# at 5821

#160820 10:24:36 server id 63  end_log_pos 5911 CRC32 0x2f577f52       Write_rows: table id 74 flags:  STMT_END_F

### INSERT INTO `test`.`test1`

### SET

###   @1=1

###    @2='zhangsan'

###   @3=1

###    @4='zhangsan road No 870,floor 602'

###    @5='2016-08-20 10:24:36'

# at 5911

#160820 10:24:36 server id 63  end_log_pos 5942 CRC32 0xb26af81b      Xid = 199

COMMIT/*!*/;

# at 5942

#160820 10:24:48 server id 63  end_log_pos 6022 CRC32 0x09eab31d     Query       thread_id=18         exec_time=0    error_code=0

SET TIMESTAMP=1471659888/*!*/;

BEGIN

/*!*/;

# at 6022

# at 6122

#160820 10:24:48 server id 63  end_log_pos 6179 CRC32 0xdc6dc34b      Table_map: `test`.`test1` mapped to  number 74

# at 6179

#160820 10:24:48 server id 63  end_log_pos 6265 CRC32 0x5f7ad700      Write_rows: table id 74 flags: STMT_END_F

### INSERT INTO `test`.`test1`

### SET

###   @1=2

###   @2='lisi'

###   @3=1

###    @4='zhangsan road No 870,floor 602'

###    @5='2016-08-20 10:24:48'

# at 6265

#160820 10:24:48 server id 63  end_log_pos 6296 CRC32 0xf6833d28      Xid = 200

COMMIT/*!*/;

# at 6296

#160820 10:31:30 server id 63  end_log_pos 6343 CRC32 0xcfcdd344       Rotate to mysql-bin.000216  pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET  COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@hch_test_dbm1_121_63 binlog]#

[ root@ hch_test_dbm1_121_63 binlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000215/*!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 /*!*/;……#160820 10:24:36 Server-ID 63 end_log_pos 5821 CRC32 0x08bc94c3 Table_map: `test`.`test1` zugeordnet zu Nummer 74 # bei 5821#160820 10:24:36 Server-ID 63 end_log_pos 5911 CRC32 0x2f577f52 Write_rows: Tabellen-ID 74 Flags: STMT_END_F### INSERT INTO `test`.`test1`## # SET ### @1=1### @2='zhangsan'### @3=1 ### @4='zhangsan road No 870,floor 602'### @5='2016-08-20 10:24:36'# bei 5911# 160820 10:24:36 Server-ID 63 end_log_pos 5942 CRC32 0xb26af81b : 48 Server-ID 63 end_log_pos 6022 CRC32 0x09eab31d Abfrage thread_id=18 exec_time=0 error_code=0SET 1659888/*!*/; BEGIN/*! */;# bei 6022# bei 6122#160820 10:24:48 Server-ID 63 end_log_pos 6179 CRC32 0xdc6dc34b Table_map: `test`.`test1` zugeordnet zu Nummer 74# bei 6179#160820 10:24:48 Server-ID 63 end_log_pos 6265 CRC32 ad700 Write_rows : Tabellen-ID 74 Flags: STMT_END_F### INSERT INTO `test`.`test1` ### SET### @1 =2### @2='lisi'### @3 =1### @4=' Zhangsan Road Nr. 870, Etage 602'### @5='2016-08-20 10:24:48 '# bei 6265 #160820 10:24:48 Server-ID 63 end_log_pos 6296 CRC32 0xf6833d28 Xid = 200COMMIT/*!*/;# bei 6296#16082 0 10:31:30 Server-ID 63 end_log_pos 6343 CRC32 0xcfcdd344 Rotate to mysql-bin.000216 pos: 4DELIMITER ; # Ende der ProtokolldateiROLLBACK /* hinzugefügt von mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*! 50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@hch_test_dbm1_121_63 binlog]#

PS: So könnt ihr es sehen dmls insert-Anweisung, aber nur der letzte insert-Block wird gesehen, aber nicht die letzte Die ursprüngliche insert-Anweisung In vielen Fällen erfordert die Fehlerbehebung die ursprüngliche insert-Anweisung anstelle der zugrunde liegenden Block einfügen. Die Frage ist also, wie man die ursprüngliche insert sql-Anweisung sieht?

4, passbinlog_rows_query_log_events Parameter zum Anzeigen des Originals SQL einfügen

( 4.1) Stellen Sie zunächst die globalen binlog_rows_query_log_events Parameter online ein und aktualisieren Sie das Protokoll:

mysql> set  binlog_rows_query_log_events=1;                                         

Query OK, 0 rows affected (0.01 sec)

 

mysql>

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

 

mysql>exit

 

mysql> set binlog_rows_query_log_events=1; sec)

mysql>

mysql> Flush-Protokolle;Abfrage OK, 0 Zeilen betroffen (0,01 Sek.) mysql> ;exit

[root@hch_test_dbm1_121_63 ~]# mysql  -utim -ptimgood -h192.168.121.63

Warning: Using a password on the command  line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 19

Server version: 5.6.12-log Source  distribution

 

Copyright (c) 2000, 2013, 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>

mysql> use test;

Database changed

mysql> insert into test1 select  3,'wanger','3','zhangsan road No 870,floor 603',now();

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql>

 

(4.2) Dann Melden Sie sich erneut an, um neue Datensätze einzugeben:

[root @hch_test_dbm1_121_63 ~]# mysql -utim -ptimgood -h192.168.121.63Warnung: Die Verwendung eines Passworts auf der Befehlszeilenschnittstelle kann unsicher sein.Willkommen am Ende der MySQL-Befehle mit ; ​​oder g.Ihre MySQL-Verbindungs-ID ist 19Serverversion: 5.6.12-log Quellverteilung Copyright (c) 2000 , 2013, Oracle und/oder seine verbundenen Unternehmen. Oracle ist eine eingetragene Marke der Oracle Corporation und/oder ihrerAndere Namen können sein Marken ihrer jeweiligen Eigentümer. Geben Sie „help;“ oder „h“ ein, um die aktuelle Eingabeanweisung zu löschen. mysql>use test 3,'wanger','3','zhangsan road No 870,floor 603',now() ;Abfrage OK, 1 Zeile betroffen (0,00 Sek.)Datensätze: 1 Duplikate: 0 Warnungen: 0 mysql>

 

 

（4.3）解析binlog，没有看到原始的insert语句

[root@hch_test_dbm1_121_63 binlog]#  /usr/local/mysql/bin/mysqlbinlog   --base64-output=decode-rows -v   mysql-bin.000216 >1.sql

[root@hch_test_dbm1_121_63 binlog]# more  1.sql

/*!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

#160820 10:31:30 server id 63  end_log_pos 120 CRC32 0x1e4d0366       Start: binlog v 4, server v 5.6.12-log  created 160820 10:31:30

# Warning: this binlog is either in use  or was not closed properly.

# at 120

#160820 10:32:04 server id 63  end_log_pos 200 CRC32 0xab0e625e       Query       thread_id=19   exec_time=0         error_code=0

SET TIMESTAMP=1471660324/*!*/;

SET @@session.pseudo_thread_id=19/*!*/;

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.time_zone='SYSTEM'/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET  @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 200

# at 302

#160820 10:32:04 server id 63  end_log_pos 359 CRC32 0x41bf2876        Table_map: `test`.`test1` mapped to  number 74

# at 359

#160820 10:32:04 server id 63  end_log_pos 447 CRC32 0x1a3ab3d8       Write_rows: table id 74 flags:  STMT_END_F

### INSERT INTO `test`.`test1`

### SET

###    @1=3

###    @2='wanger'

###    @3=3

###    @4='zhangsan road No 870,floor 603'

###    @5='2016-08-20 10:32:04'

# at 447

#160820 10:32:04 server id 63  end_log_pos 478 CRC32 0xc5081021        Xid = 208

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET  COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET  @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@hch_test_dbm1_121_63 binlog]#

 

 

 

（4.4）通过mysql的命令行查看最原始的insert sql语句，如下所示：

mysql>  show binlog events in 'mysql-bin.000216';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                             |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
| mysql-bin.000216 |   4 | Format_desc |        63 |         120 | Server ver: 5.6.12-log, Binlog ver: 4                                            |
| mysql-bin.000216 | 120 | Query       |        63 |         200 | BEGIN                                                                            || mysql-bin.000216 | 200 | Rows_query  |        63 |         302 | # insert into test1 select 3,'wanger','3','zhangsan road No 870,floor 603',now() || mysql-bin.000216 | 302 | Table_map   |        63 |         359 | table_id: 74 (test.test1)                                                        |
| mysql-bin.000216 | 359 | Write_rows  |        63 |         447 | table_id: 74 flags: STMT_END_F                                                   |
| mysql-bin.000216 | 447 | Xid         |        63 |         478 | COMMIT /* xid=208 */                                                             |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
 
mysql>

5, Testzusammenfassung

basiert Aus den obigen Experimenten kann geschlossen werden, dass Sie im Zeilenmodus das Einfügedatenmodul der untersten Ebene über mysqlbinlog --base64-output=decode-rows –v anzeigen können , oder über die Befehlszeile Binlog-Ereignisse in 'mysql-bin.000216' anzeigen;, um den ursprünglichen insertsql-Datensatz in Echtzeit anzuzeigen.

Wir können es beim Erstellen der Datenbank im Voraus in der Startparameterdatei my.cnf festlegen, wie unten gezeigt:

# vim my.cnf

[mysqld]

binlog_format=row  # binlog 日志格式

binlog_rows_query_log_events = 1  # 将原始的操作sql记录写入事件中              

# vim my.cnf

[mysqld]

binlog_format= row

# binlog

Protokollformat

binlog_rows_query_log_events = 1 # Konvertieren Sie den ursprünglichen Vorgang sql Schreibereignis aufzeichnen                                                             Anweisung im Binärprotokoll-ROW-Modus. Weitere verwandte Inhalte finden Sie im PHP Chinesische Website (www.php.cn)!