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.