需求背景
- 某公司出现内鬼,有人对外出售内部数据。运营同学帮忙想办法定位,咨询是否查询都会留下痕迹
解决思路
- 通过sql语句定位到账号的服务器地址
- 再通过jumpserver的记录定位到这个时间点操作这台数据库的人
查看正在执行的语句
show processlist
output:
+-------+------+-----------------+----------------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------------+----------------+---------+------+-------+------------------+----------+
| 4 | root | localhost:45070 | db_miaobo_data | Sleep | 2 | | NULL | 0.000 |
| 7 | root | localhost:45076 | db_miaobo_data | Sleep | 2 | | NULL | 0.000 |
| 9 | root | localhost:45080 | db_miaobo_data | Sleep | 2 | | NULL | 0.000 |
| 12 | root | localhost:45086 | db_miaobo_data | Sleep | 2 | | NULL | 0.000 || 15 | root | localhost:45092 | db_miaobo_data | Sleep | 2 | | NULL | 0.000 |
- 好处:不用设置,直接就可以查看
- 坏处:无法查看历史记录
查看历史执行记录
-- SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON'; //日志开启
-- SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'OFF'; //日志关闭
mysql> SELECT * from mysql.general_log ORDER BY event_time DESC limit 5;
+----------------------------+------------------------------+-----------+-----------+--------------+------------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------+-----------+-----------+--------------+------------------------------------------------------------------+
| 2020-07-02 10:15:21.028918 | root[root] @ localhost [::1] | 10 | 1 | Query | SELECT * from mysql.general_log ORDER BY event_time DESC limit 5 |
| 2020-07-02 10:15:11.313425 | root[root] @ localhost [::1] | 10 | 1 | Query | SELECT * from mysql.general_log ORDER BY event_time DESC limit 2 |
| 2020-07-02 10:14:57.505774 | root[root] @ localhost [::1] | 10 | 1 | Query | SELECT * from mysql.general_log ORDER BY event_time DESC |
| 2020-07-02 10:14:49.994994 | root[root] @ localhost [::1] | 10 | 1 | Query | select * from zhuanshu_spider limit 10 |
| 2020-07-02 10:14:38.714966 | root[root] @ localhost [::1] | 10 | 1 | Query | select * from phone_project_1 limit 10 |
+----------------------------+------------------------------+-----------+-----------+--------------+------------------------------------------------------------------+
- 删除(delete对于这个表,不允许使用,只能用truncate)
truncate table mysql.general_log;