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

MySQL log audit helps you find out the profile of a bad guy.

Part1: write at the front

MySQL itself doesn't provide auditing capabilities like MariaDB and Percona, but what if we want to audit the database to see who deleted my database data? We mainly use the init-connect parameter to let each logged-in user log into our database, grab its connection_id (), and then find out who did the crap according to binlog.

How to audit MariaDB can be moved to:

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

Prepare for

Part1: create the required libraries

[root@HE3 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 required tables

[root@HE3] # 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 (10) UNSIGNED NOT NULL PRIMARY KEY auto_increment,-> ConnectionID INT (10) UNSIGNED,-> ConnUser VARCHAR (30) NOT NULL DEFAULT'',-> MatchUser VARCHAR (30) NOT NULL DEFAULT'',-> LoginTime datetime->); Query OK, 0 rows affected (0.02 sec)

Part3: adding in my.cnf

Init-connect='Insert into auditdb.accesslog (ConnectionID, ConnUser, MatchUser, LoginTime) values (connection_id (), user (), current_user (), now ());'

And restart the database

[root@HE3 ~] # / etc/init.d/mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!

test

Part1: environment

[root@HE3] # 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: login operation with different users

[root@HE3 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 = 2position query OK, 1 row affected (0.00 sec) mysql > delete from T1 where id = 4 Query OK, 1 row affected (0.00 sec) [root@HE3 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 row affected query OK, 1 row affected (0.00 sec)

Part3: viewing user ID

Mysql > select * from accesslog +-+ | ID | ConnectionID | ConnUser | MatchUser | LoginTime | +-+-- -+ | 1 | 10 | helei@localhost | helei@% | 19:07:49, 2016-12-08 | 2 | 19 | helei@localhost | helei@% | 2016-12-08 19:08:44 | 3 | 125 | helei@localhost | helei@% | 2016- 12-08 19:24:46 | 4 | 12-08 | yuhao@localhost | yuhao@% | 2016-12-08 19:25:01 | 5 | 182 | helei@localhost | helei@% | 2016-12-08 19:33:02 | 6 | 185 | yuhao@localhost | yuhao@% | 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 server id / 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 # 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 server id end_log_pos 5579 161208 19:33:49 server id 1250 end_log_pos 5630 CRC32 0x71feeadc Rows_query# delete from T1 where id= 3

Reference:

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

-- Summary.

Audit will more or less affect the performance of the database, can not be opened as far as possible. In addition, the users of the audit database should be real-name system or one-to-one, so as the author's level is limited and the writing time is very short, there will inevitably be some errors or inaccuracies in the article. Readers are urged to criticize and correct the inadequacies.

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.