binlog+ Audit log location mariadb (mysql) database-specific SQL

Recent online data inexplicable lost data, so the SQL location, the location of the data lost on the line, and finally targeted to a specific development program.

Audit log: Record all the information of the database, there will be a huge log, and the parameters set audit log size, audit log rotation split.

1. Based on audit log this characteristic, after the business raises the question, must promptly discover the problem solves the problem.

The online business log is 512M a log, a total of 10. Approximately 8 hours of database access information can be recorded.

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

Combined with the above two points: you can binlog location problem sql, through the audit log location operation DB IP, the user to locate a specific person.

(positioning is more accurate if each development has a separate database operations user/permission)

such as business to a field id=11223344 record is deleted.

When the log volume is small, it can be directly located through the audit log. When the quality of the day is large, it may be more difficult to locate.

Experiment:

MariaDB [test]> CREATE TABLE t111 (ID int not NULL, name varchar (+), 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. Through Binlog analysis:

• Determine current 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 (+) meta=120 nullable=1 is_null=0 * *

# # @3=null * * varstring (meta=120 nullable=1 is_null=1) * *

# at 871

2. Audit log Analysis

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

Locate users, IP, tables, SQL, and more through two sections of logs.

binlog+ Audit log location mariadb (mysql) database-specific SQL