mysql sql 一部分记录_小水玩转Mysql---Mysql跟踪sql记录

原因：binlog日志仅记录了操作日志，并没有记录是哪个用户操作的。 目的：需要对操作进行审计,记录用户操作(增删改)记录前提：需要先开启mysql的binlog

第一种方法(设置init_connect)

1.创建用于存放连接日志的数据库和表

mysql>create database accesslog;mysql>use accesslog;mysql>create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));

2.设置变量init_connect

#查询mysql> show variables like 'init%';+---------------+-------+| Variable_name | Value |+---------------+-------+| init_connect | || init_file | || init_slave | |+---------------+-------+3 rows in set (0.00 sec)#设置mysql>set global init_connect='insert into accesslog.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';Query OK, 0 rows affected (0.00 sec)#再次查询mysql> show variables like 'init%';+---------------+------------------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+---------------+------------------------------------------------------------------------------------------------------------------------------+| init_connect | insert into access_log.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()); || init_file | || init_slave | |+---------------+------------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)

3.设置用户权限

mysql> grant select,insert,update on accesslog.accesslog to myuser@'localhost' identified by 'mypassword';Query OK, 0 rows affected (0.00 sec)

4.实验

#给myuser赋予某个数据库权限

mysql> grant select,delete on test.* to myuser@'localhost';

Query OK, 0 rows affected (0.00 sec)

#使用myuser用户登录数据库

mysql -umyuser -p

mysql> use test

#删除表里的数据

mysql> delete from mytable;

#开始追踪是谁删除了数据

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| mysqld-bin.000015 | 120 | | | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

#查看binlog日志(可以记录下自己测试删除数据的时间)

mysqlbinlog /var/lib/mysql/mysqld-bin.000015 --start-datetime='2018-11-23 15:07:00'

#输出如下：

BEGIN

/*!*/;

# at 1217

#181123 15:07:16 server id 1 end_log_pos 1311 CRC32 0x9a9eee0e Query thread_id=4 exec_time=0 error_code=0

use `jpress`/*!*/;

SET TIMESTAMP=1542956836/*!*/;

delete from user

/*!*/;

# at 1311

#181123 15:07:16 server id 1 end_log_pos 1342 CRC32 0xae382b86 Xid = 78

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

#可以看到thread_id=4

#查询accesslog表

mysql> select * from accesslog.accesslog;

+-----------+---------------------+------------------+------------------+

| thread_id | time | localname | machine_name |

+-----------+---------------------+------------------+------------------+

| 4 | 2018-11-23 15:05:19 | myuser@localhost | myuser@localhost |

+-----------+---------------------+------------------+------------------+

1 row in set (0.00 sec)

#可以看到thread_id为4的连接为myuser用户，那么可以判断此操作就是myuser用户执行。

5.注意

对于所有的普通级别的用户，必须全部都要对日志表具有读写权限， 否则将导致，没有权限的用户无法使用数据库。init_connect 不会记录有超级管理员权限的用户连接信息 (原因:当init_connect设置有误时,超级管理员可进行修改)，因此，对于一般的用户，不能赋予all privileges权限。mysql重启后设置就失效了，需要重新设置init_connect即可。

第二种方法(开启general log)

注意：一般不会开启开功能，因为log的量会非常庞大。

1.查看现状

mysql> show variables like '%general%';+------------------+------------------------------+| Variable_name | Value |+------------------+------------------------------+| general_log | OFF || general_log_file | /var/lib/mysql/localhost.log |+------------------+------------------------------+2 rows in set (0.00 sec)#设置开启，默认输出到日志文件mysql> set global general_log = ON;#永久修改需要在my.cnf的【mysqld】中添加：general_log = 1#查看日志文件[root@localhost ~]# tail -f /var/lib/mysql/localhost.log/usr/sbin/mysqld, Version: 5.6.40-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument/usr/sbin/mysqld, Version: 5.6.40-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument181123 15:26:43 5 Query show variables like '%general%'181123 15:26:49 4 Quit181123 15:27:01 5 Query show databases181123 15:27:53 5 Query SELECT DATABASE() 5 Init DB mysql 5 Query show databases 5 Query show tables181123 15:31:44 5 Quit181123 15:31:55 6 Connect myuser@localhost on 6 Query insert into accesslog.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user()) 6 Query select @@version_comment limit 1181123 15:32:17 6 Query show databases#从日志文件中可以看到id为6的用户为myuser用户,id是递增的，重新连接就增加1#也可以设置日志输出到表,查看mysql数据库里的general_log表mysql> set global log_output='table';#登录后执行show databases;命令查看表里是否增加一条数据mysql> select * from mysql.general_log;| 2018-11-23 15:37:27 | root[root] @ localhost [] | 7 | 1 | Query | show databases || 2018-11-23 15:38:10 | root[root] @ localhost [] | 7 | 1 | Query | select * from mysql.general_log |+---------------------+-----------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+42 rows in set (0.00 sec)