Part1:写在最前
MySQL本身并不像MariaDB和Percona一样提供审计功能，但如果我们想对数据库进行审计，去看是谁把我的数据库数据给删了，该怎么办呢？我们主要利用init-connect参数，让每个登录的用户都记录到我们的数据库中，并抓取其connection_id()，再根据binlog就能够找出谁干了那些破事儿。
MariaDB如何审计，可移步：
http://suifu.blog.51cto.com/9167728/1857594
准备
Part1:创建所需库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@HE3 telegraf]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 859
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
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:创建所需表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@HE3 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 266
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> use auditdb;
Database changed
mysql> 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:在my.cnf中添加
|
1
|
init-connect=
'Insert into auditdb.accesslog(ConnectionID ,ConnUser ,MatchUser ,LoginTime)values(connection_id(),user(),current_user(),now());'
|
并重启数据库
|
1
2
3
|
[root@HE3 ~]
# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
|
测试
Part1:环境
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[root@HE3 ~]
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 266
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> use auditdb;
mysql> use helei;
Database changed
mysql>
select
* from t1;
+----+
|
id
|
+----+
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
8 rows
in
set
(0.00 sec)
|
Part2:用不同用户登录操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
[root@HE3 telegraf]
# mysql -uhelei -pMANAGER
mysql: [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 185
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> use helei;
Database changed
mysql>
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)
[root@HE3 telegraf]
# mysql -uyuhao -pMANAGER
mysql: [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 185
Server 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
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> use helei;
Database changed
mysql>
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:查看用户ID
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql>
select
* from accesslog;
+----+--------------+-----------------+-----------+---------------------+
| ID | ConnectionID | ConnUser | MatchUser | LoginTime |
+----+--------------+-----------------+-----------+---------------------+
| 1 | 10 | helei@localhost | helei@% | 2016-12-08 19:07:49 |
| 2 | 19 | helei@localhost | helei@% | 2016-12-08 19:08:44 |
| 3 | 125 | helei@localhost | helei@% | 2016-12-08 19:24:46 |
| 4 | 128 | 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日志对比
这里可以看到t1表的id=2和id=4列是由thread_id=182用户删掉的，也就是helei用户
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
#161208 19:33:39 server id 1250 end_log_pos 5275 CRC32 0x2ae798a9 Query thread_id=182 exec_time=0 error_code=0
SET 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=0
SET 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
|
这里可以看到t1表的id=3列是由thread_id=185用户删掉的，也就是yuhao用户
|
1
2
3
4
5
6
7
|
#161208 19:33:49 server id 1250 end_log_pos 5579 CRC32 0x5f8d9879 Query thread_id=185 exec_time=0 error_code=0
SET TIMESTAMP=1481254429/*!*/
BEGIN
/*!*/;
# at 5579
#161208 19:33:49 server id 1250 end_log_pos 5630 CRC32 0x71feeadc Rows_query
# delete from t1 where id = 3
|
参考资料：
http://dbspace.blog.51cto.com/6873717/1881053
——总结——
审计多多少少会影响数据库的性能，能不开尽量不开。另外开启审计数据库用户要实名制或者一对一，以免干了坏事儿的人赖账~由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1881116，如需转载请自行联系原作者
