MySQL log audit to help you find out what's going on inside a bad guy.

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M02/8B/59/wKiom1hKKK3C1V0tAAA4eVx2Dz8749.jpg "title=" icon 2. JPG "alt=" Wkiom1hkkk3c1v0taaa4evx2dz8749.jpg "/>

MySQL Log audit help you find out what's wrong with you. Profile

Part1 : write at the top

MySQL itself does not provide the audit function like MARIADB and Percona, but what if we want to audit the database to see who has deleted my database data? We mainly use the init-connect parameter, so that each logged-in user is logged into our database, and crawl its connection_id (), and then according to Binlog can find out who did the broken things.

Mariadb How to audit, can shift:

http://suifu.blog.51cto.com/9167728/1857594

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/8B/56/wKioL1hKLi_TWfJmAABXHI-nFdM367.jpg "title=" 8694a4c27d1ed21b95b52e58ae6eddc451da3f0a.jpg "alt=" Wkiol1hkli_twfjmaabxhi-nfdm367.jpg "/>

Get ready

part1: Create required library

[[email protected] telegraf]# mysql -uroot - Penter password: welcome to the mysql monitor.  commands end  with ; or \g.your mysql connection id is 859server version:  5.7.16-log MySQL Community Server  (GPL) copyright  (c)  2000, 2016,  oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> create database auditdb; query ok, 1 row affected  (0.00 sec) 

Part2: Create the desired table

[[email protected] ~]# mysql -uroot -penter password: welcome to  The mysql monitor.  commands end with ; or \g.your mysql  connection id is 266Server version: 5.7.16-log MySQL Community  server  (GPL) copyright  (c)  2000, 2016, oracle and/or its affiliates.  all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> use auditdb;Database changedmysql> CREATE TABLE  accesslog  (    -> ID INT )  unsigned not null primary key auto_increment,    ->  connectionid int  (Ten)  UNSIGNED,    -> ConnUser VARCHAR  (  NOT NULL DEFAULT  ',    -> matchuser varchar  (30 )  NOT NULL DEFAULT  ',    -> logintime datetime     -> ); query ok, 0 rows affected  (0.02 SEC)

Part3: Add in My.cnf

Init-connect= ' Insert into Auditdb.accesslog (ConnectionID, Connuser, Matchuser, Logintime) VALUES (connection_id (), User (), Current_User (), now ()); '

Restart the database

[Email protected] ~]#/etc/init.d/mysqld restartshutting down MySQL .... success! Starting MySQL. success!

Test

part1: environment

[[email protected] ~]# mysql -uroot -penter password: welcome to  The mysql monitor.  commands end with ; or \g.your mysql  connection id is 266Server version: 5.7.16-log MySQL Community  server  (GPL) copyright  (c)  2000, 2016, oracle and/or its affiliates.  all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> use auditdb;mysql> use helei;Database changedmysql>  select * from t1;+----+| id |+----+|  2 | |   3 | |   4 | |   5 | |   6 | |   7 | |   8 | |   9 |+----+8 rows in set  (0.00 sec)

Part2: log in with different users

[[Email protected] telegraf]# mysql -uhelei -pmanagermysql: [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 185server version: 5.7.16-log mysql  Community Server  (GPL) copyright  (c)  2000, 2016, Oracle and/or  Its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> use helei;database changedmysql> select * from t1;+----+| id |+----+|  2 | |   3 | |   4 | |   5 | |   6 | |   7 | |   8 | |   9 |+----+8 rows in set  (0.00 sec) mysql> delete from  t1 where id = 2; query ok, 1 row affected  (0.00 sec) mysql> delete from t1  where id = 4; query ok, 1 row affected  (0.00 sec) [[email protected] telegraf]#  mysql -uyuhao -pmanagermysql: [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 185server version: 5.7.16-log mysql community server  (GPL) copyright  (c)  2000, 2016, oracle  and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> use helei;database changedmysql> select * from  t1;+----+| id |+----+|  3 | |   5 | |   6 | |   7 | |   8 | |   9 |+----+8 rows in set  (0.00 sec) mysql> delete from  t1 where id = 3; query ok, 1 row affected  (0.00 SEC)

Part3: View User ID

mysql> select * from accesslog;+----+--------------+-----------------+-----------+----- ----------------+| id | connectionid | connuser         | MatchUser | LoginTime            |+----+--------------+-----------------+-----------+---------------------+|  1 |            10 | [email protected] |  [email protected]%   | 2016-12-08 19:07:49 | |   2 |           19 | [email  protected] | [email protected]%   | 2016-12-08 19:08:44 | |   3 |          125 | [email  protected] | [email protected]%    | 2016-12-08 19:24:46 | |   4 |          128 | [email  protected] | [email protected]%   | 2016-12-08 19:25:01 | |   5 |          182 | [email  protected] | [email protected]%   | 2016-12-08 19:33:02 | |   6 |          185 | [email  protected] | [email protected]%   | 2016-12-08 19:33:20 |+----+-- ------------+-----------------+-----------+---------------------+6 rows in set  (0.00  Sec

PART4: binlog Log comparison

Here you can see that the id=2 and id=4 columns of the T1 table are deleted by the thread_id=182 user, that is, the Helei user

#161208  19:33:39 server id 1250  end_log_pos 5275 CRC32  0x2ae798a9      query   thread_id=182   exec_time= 0     error_code=0set timestamp=1481254419/*!*/; begin/*!*/;# at 5275#161208 19:33:39 server id 1250  end_log_pos  5324 crc32 0x2cf42817      rows_query# delete from t1  where id=2#161208 19:34:07 server id 1250  end_log_pos 5885  CRC32 0x947106d4      Query   thread_id=182    exec_time=0     error_code=0SET TIMESTAMP=1481254447/*!*/; begin/*!*/;# at 5885#161208 19:34:07 server id 1250  end_log_pos  5934 crc32 0xfe1eb7fc      rows_qUery# delete from t1 where id=4 

Here you can see that the id=3 column of the T1 table is deleted by the thread_id=185 user, that is, the Yuhao user

#161208 19:33:49 Server ID 1250 end_log_pos 5579 CRC32 0x5f8d9879 Query thread_id=185 exec_time=0 error_code =0set timestamp=1481254429/*!*/; begin/*!*/;# at 5579#161208 19:33:49 server ID 1250 end_log_pos 5630 CRC32 0x71feeadc rows_query# Delete from T1 whe Re id = 3

Resources:

http://dbspace.blog.51cto.com/6873717/1881053

-- summary --

Audit will affect the performance of the database more or less, can not open as far as possible. In addition to open the audit database users to real-name or one-on, so as not to do bad people deadbeat ~ due to the author's level is limited, writing time is also very hasty, the text will inevitably appear some errors or inaccurate places, inappropriate place to ask readers to criticize correct.

This article is from the "He Lei Technology Blog" blog, make sure to keep this source http://suifu.blog.51cto.com/9167728/1881116

MySQL log audit to help you find out what's going on inside a bad guy.