社群 學習 工具庫 休閒
搜尋
繁体中文
首頁 > 資料庫 > mysql教程 > 主體

MySQL 解密 --> 如何查看二進位日誌ROW模式下最原始的SQL語句

黄舟
發布： 2017-02-16 11:43:25
原創
2450 人瀏覽過

MySQL的binlog的ROW模式解析

ROW

      在mysql5.6以後，主從資料一致性要求變高了，statement格式逐漸不太適合業務的需求了，所以生產環境大家都採用了row模式，row模式是傳輸最底層的數據變化的insert的模組來進行主從數據的傳輸，那麼在binlog裡面就和普通的statement模式有何差別？能否看到最原始的sql語句呢？

 

1、準備輸入資料

 

 

 




 

 

 

 

2、Row Row模式下面，binlog裡面的ddl語句是正常顯示的，但是dml是亂碼，如下圖： 

[root@hch_test_dbm1_121_63 binlog]sql# 3/usr/local /binr/local …… #

這裡前面的省略

#160722 17:02:38 server id 62  end_log_pos 4291 CRC32 0x369e3244     Query    271 error_code=0

SET TIMESTAMP=1469178158/*!*/;CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */

/*!*/;

# at 4291#160722 17:02:38 server id 62  end_log_pos 5079 32:38 server read_id=60         exec_time=4294967271 error_code=0 use `percona`/*!*/;

SET TIMESTAMP=1469178158/*!*/;

CREATE TABLE IF NOT EXISTS  `percona`.`checksums` (🠎🠟  char(64)     NOT NULL,

      tbl char(64)     NOT NULL,

      chunk          int                NULL,

      chunk_index    varchar(200)     NULL,🎠  

      upper_boundary text              NULL,

    ,

      this_cnt       int          NOT NULL,

      master_crc     char(40)         NULL,

      master_cnt     int              NULL,

      ts             timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON  UPDATE CURRENT_TIMESTAMP,

      PRIMARY KEY (db, tbl, chunk),

      INDEX ts_db_tbl (ts, db, tbl)

  )  ENGINE=InnoDB

/*!*/;

# at 5079

#160820 10:21:1050 8e     Query       thread_id=16         exec_time=0 error_code=0

SET TIMESTAMP=1471659670/*!*/;

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

GRANT ALL PRIVWE ON. BY PASSWORD  '*2976819BD2CCD13612E03F812A2CD297C1A18B23'

/*!*/;

# at 5280

#16088 CRC32 0x85811be7     Query       thread_id=18         exec_time=0    error_code=0

test

test `/*!*/;

SET TIMESTAMP=1471659760/*!*/;

create table test1(id int,c1  varchar(20),type int,address varchar(20),create_time date)* !*/;

# at 5445

#160820 10:24:34 server id 63  end_log_pos 5580 CRC   exec_time=0    error_code=0

SET TIMESTAMP=1471659874/*!*/;

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

/*!*/;

# at 5580

#160820 10:24:36 伺服器id 63  end_log_pos 5660 CRC32 0x7b7c645f      查詢       thread_id=1800time

SET TIMESTAMP=1471659876/*!*/ ;

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

BEGIN

/*!*/;

# 在5660

# 在5764

#160820 10:24:36 CRCID 63 58  Table_map : `測試`.`測試1 ` 對應編號74

#，位於5821

#160820 10:24:36 伺服器id 63  end_log_pos 5911 CRC32 0x2f577f52 STM  標記： 

 

BINLOG '

ZL+3VxM/AAAAOQAAAL0WAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB/ DlLwI

ZL+3Vx4/AAAAWgAAABcXAAAAAEoAAAAAAAEAAgAF/+ABAAAACHpoYW5nc2FuAQAAAB4Aemhhbmdz

YW4gcm9hZCBObyA4NemhhAmmA4NN;

# 在5911

#160820 10:24:36 伺服器ID 63  end_log_pos 5942 CRC32 0xb26af81b      Xid = 19999926 /* !*/;

# 在5942

#160820 10:24:48 伺服器id 63  end_log_pos 6022 CRC   exec_time=0    error_code=0

設定時間戳=1471659888/*!*/;

開始

/*!*/;

# at 6022

# at 6122

#160820 10:24:48 伺服器ID 63 ：`test`.`test1` 映射到數字7 4

#在6179

#160820 10:24:48 伺服器id 63  end_log_pos 6265 CRC32 0x5f7ad700   '

cL+3VxM /AAAAOQAAACMYAAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB9Lw23c

cL+3Vx4/AAAAVgAAAHAAAAAAAAAAAAAAAAAEA /+ACAAAABGxpc2kBAAAAHgB6aGFuZ3NhbiBy

b2FkIE5vIDg3MCxmbG9vciA2MDKZmiimMADXel8=

'/*!*/;  end_log_pos 6296 CRC32 0xf6833d28   Xid = 200

COMMIT/*!*/;

# at 6296

#160820 10:31:30 伺服器id 63  end_log_pos 6343 CRC32 0xcfcdd344    日誌檔案結束

ROLLBACK /*由mysqlbinlog 新增*/ ;

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

/*!50530 SET @@SESSION.PSEUDO_SL*/;

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

_test

 

 

 

 

3、透過--base64-output  執行指令是：/usr /local/mysql/bin/mysqlbinlog  --base64-output=decode-rows -v  mysql-bin.000215 

可以看到以下效果，不過都是最原始的d

[root@hch_test_dbm1_121_63 binlog]#  /usr/local/mysql/bin/mysqlbinlog   --base64-output=decode-rows -v  binlog   --base64-output=decode-rows -v   mylog-bin EUDO_SLAVE_MODE= 1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET  @OLD_COMPLETION_TYPE=@COMPLETION_TYPE,COMPLETIONS888/LIS/E:202;

……#160820 10:24:36 server id 63  end_log_pos 5821 CRC32 0x08bc94c3      Table_map: `test`.`157775 月#160820 10:24:36 server id 63 end_log_pos 5911 CRC32 0x2f577f52       寫

###   @1=1

###    @2='zhangsan'

###   @3=1

###    @4='zhangsan road No

###    @4='zhangsan road No 870, oor 6050 =' 2016-08-20 10:24:36'

# at 5911

#160820 10:24:36 server idaf3  end_log_pos 5942 26910bx MIT/*!*/;

# at 5942

#160820 10:24:48 server id 63  end_log_pos 6022 CRC32 0x09eab31d     Query    0

SET TIMESTAMP=1471659888/*!*/;

BEGIN

/*!*/;

# at 6022

# at 6122

#160820 10:24:48 server id 63  end_log_pos 6179 CRC32 0xdc634b test test34b test_app # at 6179

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

test_ ERT INTO ``test11``test INS`test_``test.

###   @1= 2

###   @2='lisi'

###   @3=11

###   @3=1

###   @3=1

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

# at 6265

#160820 10:24:48 server id 63 #160820 10:24:48 server id 63  69385 200

COMMIT/*!*/; # at 6296

#160820 10:31:30 server id 63  end_log_pos 6343 CRC32 0xcfcdd344      # End of log file

ROLLBACK /* added by mysqlbinlog */;/*!50003 SET  COMPLETION_TYPE=@OLD_COMPLETION_TYPE*​​/;

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

 

 

 

PS：這樣是可以看到了 塊，而看不到原始的過來的insert語句，很多時候排查問題需要原始的insert語句而不是底層的insert塊。所以問題來了，如何看到最原始的insert sql語句呢？     

4

、透過查看

 

（4.1）先在線上設置全域的binlog_rows_query_log_events參數，刷新日誌：

mysql>                           Query OK, 0 位logs;Query OK, 0 rows affected (0.01 sec) mysql>exit

 [root@hch_test_dbm1_121_63 ~]# mysql  -utim -ptimgood -h192.168.121.63facesql  -utim -ptimgood -h192.168.121.633faced inface faces.

 

 

（

4.2

）然後重新登錄錄入新的數據記錄：

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

Your MySQL connection id is 19

Server version: 5.6.12-log Source  distribution. served . Oracle is a registered trademark of  Oracle Corporation and/or itsaffiliates. Other names may be trademarks  of their respectiveiliates. Other names may be trademarks  of their respectiveiliates. Other names may be trademarks  of their respectiveiliates. help. Type 'c'  to clear the current input statement.

 mysql>

mysql> use test;

Database changed

my> sert

Database ,floor 603',now();

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

 

（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、試驗總結

基於以上的試驗，可以得出在row模式下，可以通過mysqlbinlog --base64-output=decode-rows –v查看最底層的insert資料模組，也可以透過命令列show binlog events in 'mysql-bin.000216';來即時查看最原始的insertsql記錄。

 

我們可以在搭建資料庫的時候，在啟動參數檔案my.cnf裡面事先設定好，如下圖所示：

#vim my..commcn =row  # binlog

日誌格式

binlog_rows_query_log_events = 1  #      

  以上是MySQL 解密--> 如何查看二進位日誌ROW模式下最原始的SQL語句的內容，更多相關內容請關注PHP中文網（www.php.cn）！

相關標籤：
MySQL，ROW模式，SQL语句
來源：php.cn
上一篇：MySQL 使用自增ID主鍵和UUID 作為主鍵的優劣比較詳細流程（500W單表） 下一篇：MySQL伺服器時間同步問題
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
作者最新文章
最新問題
function_exists()無法判定自訂函數 function test()    {        return true;    }    if (function_exists('TEST')) {        ech...
來自於 2024-04-29 11:01:01
0
2
1979
google 瀏覽器 手機版顯示的怎麼實現 老師您好，google 瀏覽器怎麼變成手機版樣式的？
來自於 2024-04-23 00:22:19
0
10
2143
子窗口操作父窗口，輸出沒反應 前兩句可執行，最後一句沒辦法應
來自於 2024-04-19 15:37:47
0
1
1805
父視窗沒有輸出 document.onclick = function(){ window.opener.document.write('我是子視窗的輸出');                  ...
來自於 2024-04-18 23:52:34
0
1
1703
關於CSS心智圖的課件在哪？ 課件
來自於 2024-04-16 10:10:18
0
0
1715
相關專題
更多>
熱門推薦
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板