Shulou(Shulou.com)06/01 Report--

This article mainly introduces the binlog+ audit log handout, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Recently, online data is inexplicably lost, so sql positioning is carried out to locate why online data is lost, and finally to a specific development program.

Audit log: record all the information in the database, so there will be a huge log, and the parameters set the audit log size, and the audit log will be rotated and divided.

1. Based on the characteristics of the audit log, after the business puts forward the problem, it is necessary to find and solve the problem in time.

Online business log is 512m a log, a total of 10. About 8 hours of database access information can be recorded.

2.binlog:binlog is to record the information of changes in the mysql database, record additions, deletions, changes and other information.

Combine the above two points: the problem sql can be located by binlog, and the IP of the operation DB can be located through the audit log, so that the user can locate a specific person.

(if each developer has a separate database operation user / permission, the location will be more accurate)

For example, the business to a field id=11223344 record is deleted.

When the log volume is small, it can be located directly through the audit log. It may be difficult to locate when the daily mass is large.

Experiment:

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), (2)), (3)), (3)), (11223344)

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)

Through binlog analysis:

Determine the current binlog

Show master status

+-+

| | File |

+-+

| | mysql-bin.000023 |

+-+

/ usr/local/mysql/bin/mysqlbinlog-- start-datetime='2018-02-26 15 stop-datetime='2018 00'-- stop-datetime='2018-02-26 15V-- 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.gtidhands seqipment 1280 accounting accounts /

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

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

# @ 2roomdddd' / * VARSTRING meta=120 nullable=1 is_null=0 * /

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

# at 871

two。 Audit log analysis

20180226 15:09:46,XHY005116,catr1192.168.5.116,7125,0,DISCONNECT,0

20180226 15 09 48 where id=11223344',0 XHY005116 localhost7085 and 51 where id=11223344',0 QUERYTESTRATION delete QUERY

20180226 15:09:49,XHY005116,catr1192.168.5.117,7127,0,FAILED_CONNECT,1049

Use two-part logs to locate user, IP, table, sql, and so on.

After reading the above lecture notes on binlog+ audit log, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.