binlog+審計日誌 定位mariadb(mysql)資料庫特定sql

標籤：mysql   DML定位   

  最近線上資料莫名的丟資料，故進行sql定位，定位線上資料為何丟失，最後定位到具體某開發程式。

  審計日誌：記錄資料庫所有資訊，故會有巨大的日誌，而且參數設定審計日誌大小，會進行審計日誌輪換分割。

1.基於審計日誌這個特點，業務提出問題後，要及時發現問題解決問題。

線上業務日誌為512M一個日誌，共10個。大約能記錄8個小時左右的資料庫訪問資訊。

2.binlog：binlog是記錄mysql資料庫變化的資訊，記錄增刪改等資訊。

結合以上兩點：可以binlog定位問題sql，通過審計日誌定位操作DB的IP，使用者從而定位到具體某人。

（如果每個開發有單獨的資料庫操作使用者/許可權，定位會更加準確）

比如業務給一個欄位 id=11223344 記錄被刪除。

日誌量小的時候，可通過審計日誌直接定位。日品質大的時候可能比較困難定位。

實驗：

MariaDB [test]> create table t111 ( id int not null, name varchar(30), city varchar(30));

Query OK, 0 rows affected (0.08 sec)

MariaDB [test]> insert into t111 (id,name) values (1,'aaa'),(2,'bbb'),(3,'ccc'),(11223344,'dddd');

Query OK, 4 rows affected (0.02 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> delete from t111 where id=11223344;

Query OK, 1 row affected (0.04 sec)

1. 通過binlog分析：

• 確定當前binlog

  show master status;

  
  

  +------------------+

  | File             |

  +------------------+

  | mysql-bin.000023 |

  +------------------+

/usr/local/mysql/bin/mysqlbinlog --start-datetime='2018-02-26 15:00:00' --stop-datetime='2018-02-26 15:13:00' -v --base64-output=decode-rows mysql-bin.000023 >/data/bin.log

#180226 15:09:48 server id 3306116  end_log_pos 775 CRC32 0x27b288a6    GTID 0-3306116-1280 trans

/*!100001 SET @@session.gtid_seq_no=1280*//*!*/;

BEGIN

/*!*/;

# at 775

#180226 15:09:48 server id 3306116  end_log_pos 828 CRC32 0x636faceb    Table_map: `test`.`t111` mapped to number 609

# at 828

#180226 15:09:48 server id 3306116  end_log_pos 871 CRC32 0xc6b380c3    Delete_rows: table id 609 flags: STMT_END_F

### DELETE FROM `test`.`t111`

### WHERE

###   @1=11223344 /* INT meta=0 nullable=0 is_null=0 */

###   @2='dddd' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */

###   @3=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */

# at 871

2.審計日誌分析

20180226 15:09:46,XHY005116,catr1,192.168.5.116,7125,0,DISCONNECT,,,0

20180226 15:09:48,XHY005116,root,localhost,7085,51,QUERY,test,'delete from t111 where id=11223344',0

20180226 15:09:49,XHY005116,catr1,192.168.5.117,7127,0,FAILED_CONNECT,,,1049

通過兩部分日誌定位使用者，IP，表，sql等等資訊。

binlog+審計日誌 定位mariadb(mysql)資料庫特定sql