Mysql 连接通过init_connect来初始化，官网说明：
服务器为每个连接的客户端执行的字符串。该字符串由一个或多个SQL语句组成，用分号字符分隔。例如，默认情况下每个客户端会话都启用自动提交模式。对于较旧的服务器（在MySQL 5.5.8之前），没有全局自动提交系统变量来指定默认情况下应禁用自动提交，但作为解决方法，init_connect可用于实现相同的效果：SET GLOBAL init_connect ='SET autocommit = 0';
1.创建数据库及表
创建数据库：
create database dba;
创建表：
create table accesslog(`thread_id` int primary key auto_increment, `time` timestamp, `localname` varchar(40), `machine_name` varchar(40));
thread_id : 记录mysql 线程ID
time：记录操作时间
localname：记录操作远程IP
machine_name：记录用户
2.变量配置
查看init_connect
+---------------+-------+| Variable_name | Value | |+---------------+-------+| init_connect | || init_file | || init_slave | |+---------------+-------+3 rows in set (0.00 sec)
配置变量
set global init_connect='insert into dba.accesslog(thread_id,time,localname,machine_name) values(connection_id(),now(),user(),current_user());';
再次查看init_connect
+---------------+-----------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+---------------+-----------------------------------------------------------------------------------------------------------------------+| init_connect | insert into dba.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.为用户赋记录日志权限
grant select,insert,update on dba.accesslog to 'zhaohongming'@'%';
4.模拟用户操作添加删除
root@localhost:(none) 11:34:49 >use sdlcqw;root@localhost:(none) 11:34:49 >crate table haha(cc int);root@localhost:(none) 11:34:49 >drop table haha;
5.查看binlog记录
导出binlog内容：
# mysqlbinlog mysql-bin.000079 > /root/9.txt
查询log表：
root@localhost:(none) 11:39:41 >use dba;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedroot@localhost:dba 11:39:50 >select * from accesslog limit 1;+-----------+---------------------+------------------------+----------------+| thread_id | time | localname | machine_name |+-----------+---------------------+------------------------+----------------+| 279950 | 2018-05-24 08:51:37 | zhaohongming@localhost | zhaohongming@% |+-----------+---------------------+------------------------+----------------+1 row in set (0.00 sec)
6.查看binlog内容
上面的线程是279950
# cat 9.txt | grep -B 10 haha
# at 267289752#180524 8:52:35 server id 1 end_log_pos 267289794 CRC32 0x542b2211 GTID 0-1-2743823 ddl/*!100001 SET @@session.gtid_seq_no=2743823*//*!*/;# at 267289794#180524 8:52:35 server id 1 end_log_pos 267289888 CRC32 0x6d9ec74d Query thread_id=279950 exec_time=0 error_code=0use `sdlcqw`/*!*/;SET TIMESTAMP=1527123155/*!*/;SET @@session.sql_auto_is_null=0, @@session.check_constraint_checks=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;create table haha(cc int)--#180524 8:52:42 server id 1 end_log_pos 267296661 CRC32 0x4552d77e Xid = 105731850COMMIT/*!*/;# at 267296661#180524 8:52:42 server id 1 end_log_pos 267296703 CRC32 0x42549f0e GTID 0-1-2743846 ddl/*!100001 SET @@session.gtid_seq_no=2743846*//*!*/;# at 267296703#180524 8:52:42 server id 1 end_log_pos 267296815 CRC32 0x8b715e13 Query thread_id=279950 exec_time=0 error_code=0use `sdlcqw`/*!*/;SET TIMESTAMP=1527123162/*!*/;SET @@session.sql_mode=1342177280/*!*/;DROP TABLE `haha` /* generated by server */