Use Init-connect and Binlog in MySQL for user action tracking records

Preface:
Test environment inexplicably have a few important data is deleted, because in Binlog only see is the public account deleted, unable to query is that who logged in that time period, consider how to record every MySQL account login information, in MySQL, Each connection executes the Init-connect first and the connection is initialized, where we can get the user's login name and thread ID value. Then with Binlog, we can trace the operation time of each operation statement, operator, etc. Implement audits.


1. Create a separate library of log access information in MySQL server DB
Set names UTF8;
Create Databaseaccess_log;
CREATE TABLE ' Access_log '
(
' id ' int (one) not NULL auto_increment,
' thread_id ' int (one) default NULL,--thread ID, this value is important
' Log_time ' timestamp not NULL DEF ault current_timestamp on UPDATE current_timestamp,--Logon time
' LocalName ' varchar (+) DEFAULT NULL,--Login name
' MatchName ' varchar (+) DEFAULT NULL,--Login user
PRIMARYKEY (' id ')
) engine=innodbauto_increment=1 DEFAULT Charset=utf8 comment ' Input user login information ';



2, configure the Init-connect parameter in the configuration file. Insert Log table when logging in. If this parameter is an incorrect SQL statement, the login will fail.
Vim/usr/local/mysql/my.cnf
Init-connect= ' Insertinto access_log.access_logvalues (null,connection_id (), now (), USER (), Current_User ()); '
Then restart the database

3, create a normal user, cannot have super privilege, and the user must have the Insert permission to the Access_log table of the Access_log library, or the login will fail.
Give the login user insert permission, but do not give Access_log Insert, select permissions,
Grantinsert,delete,update,select on test.* to [email protected] '% ' identified by ' cacti_user1603 ';
Mysql> Grantcreate,drop,alter,insert,delete,update,select on test.* to [email protected] '% ' identified by ' cacti_ user1603 ';
Query OK, 0 rowsaffected (0.00 sec)
Mysql> exit
and then go with the newAudit_user Login Operation
[Email protected]_server~]#/usr/local/mysql/bin/mysql-uaudit_user-p-s/usr/local/mysql/mysql.sock
Enter Password:
Welcome to Themysql Monitor. Commands End With; or \g.
Your MySQL ConnectionID is 25
Server Version:5.6.12-log

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is aregistered trademark of the Oracle Corporation and/or its
Affiliates. Othernames may trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> lect *from Access_log.access_log;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection id:26
Current database:*** NONE * * *


ERROR 1184 (08S01): Aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_connect command FA iled)
Mysql>
See the error message(Init_connect command failed), then go to the error log to verify:
TAIL-FN 5/usr/local/mysql/mysqld.log
2014-07-28 16:03:3123743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_co Nnect command failed)
2014-07-2816:03:31 23743 [Warning] INSERT command denied to user "@ ' localhost ' for table ' Access_log '
2014-07-2816:04:04 23743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_co Nnect command failed)
2014-07-2816:04:04 23743 [Warning] INSERT command denied to user "@ ' localhost ' for table ' Access_log '
To see that there must be a rightThe Insert permission for the Access_log table of the Access_log library is not OK.


4, give the user access_log Insert, select permissions, and then re-assign permissions:
Grantselect,insert on access_log.* to [email protected] '% ';
Mysql>
Mysql> Grantselect,insert on access_log.* to [email protected] '% ';
Query OK, 0 rowsaffected (0.00 sec)

Mysql> exit
Bye

re-login, error is as follows:
[Email protected]_server~]#/usr/local/mysql/bin/mysql-uaudit_user-p-s/usr/local/mysql/mysql.sock
Enter Password:
ERROR 1045 (28000): Access denied for user ' audit_user ' @ ' localhost ' (using Password:yes)
[[Email protected]_server~]#

to seeError log:
2014-07-2816:15:29 23743 [Warning] INSERT command denied to user "@ ' localhost ' for table ' Access_log '
2014-07-2816:15:41 23743 [Warning] aborted connection Notoginseng to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_co Nnect command failed)
2014-07-2816:15:41 23743 [Warning] INSERT command denied to user "@ ' localhost ' for table ' Access_log '
2014-07-2816:15:50 23743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_co Nnect command failed)
2014-07-2816:15:50 23743 [Warning] INSERT command denied to user "@ ' localhost ' for table ' Access_log '

need to useThe root user logs in and clears the user record.
Mysql>select User,host,password from Mysql.user;
+----------------+-----------+-------------------------------------------+
| user | Host | password |
+----------------+-----------+-------------------------------------------+
| Root |                                         localhost | |
| Root |                                         Db_server | |
| Root |                                         127.0.0.1 | |
| Root |                                         :: 1 | |
| |                                         localhost | |
| |                                         Db_server | |
| Cacti_user | % |*eb9e3195e443d577879101a35ef64a701b35f949 |
| Cacti_user | 1 |*d5ff9b53a78232da13d3643965a5961449b387db |
| Cacti_user | 2 | *d5ff9b53a78232da13d3643965a5961449b387db|
| Test_user | 192.% |*8a447777509932f0ed07adb033562027d95a0f17 |
| Test_user | 1 |*8A447777509932F0ED07ADB033562027D95A0F17 |
| weakpwd_user_1| 10.% | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| weakpwd_user_2| 10.% | *b1461c9c68afa1129a5f968c343636192a084adb |
| weakpwd_user_3| 10.% | *dcb7df5ffc82c441503300fff165257bc551a598 |
| Audit_user | % |*AEAB1915B137FAFDE9B949D67A9A42DDB68DD8A2 |
+----------------+-----------+-------------------------------------------+
Rows in Set (0.00 sec)

mysql> dropuser ' @ ' localhost ';
Query OK, 0 rowsaffected (0.00 sec)

mysql> dropuser ' @ ' db_server ';
Query OK, 0 rowsaffected (0.00 sec)

Mysql>

the re-use has been assignedAccess_log audit_user Login with insert permission for table
Mysql> select* from Access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| ID | thread_id| Log_time | LocalName | MatchName |
+----+-----------+---------------------+---------------------------+--------------+
|       4 | 41 | 2014-07-28 16:19:37 | [Email protected] | [Email protected]% |
|       5 | 42 | 2014-07-28 16:20:32 | [Email protected] | [Email protected]% |
|       6 | 45 | 2014-07-28 16:21:11 | [Email protected] | [Email protected]% |
+----+-----------+---------------------+---------------------------+--------------+
6 rows in Set (0.00 sec)

Mysql> Showfull processlist;
+----+------------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------+------+---------+------+-------+-----------------------+
| 45 | audit_user| localhost | NULL |    Query | 0 | init | Show Fullprocesslist |
+----+------------+-----------+------+---------+------+-------+-----------------------+
1 row in Set (0.00 sec)

Mysql>

5, then use another user login to build the table, input test data.
data record of building table input
Mysql> usetest;
Database changed
mysql> createtable T1 Select 1 as a, ' WA ' as B;
Query OK, 1 rowaffected (0.01 sec)
Records:1 duplicates:0 warnings:0
View Tracking user behavior records.
Mysql> select* from Access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| ID | thread_id| Log_time | LocalName | MatchName |
+----+-----------+---------------------+---------------------------+--------------+
|       4 | 41 | 2014-07-28 16:19:37 | [Email protected] | [Email protected]% |
|       5 | 42 | 2014-07-28 16:20:32 | [Email protected] | [Email protected]% |
|       6 | 45 | 2014-07-28 16:21:11 | [Email protected] | [Email protected]% |
|       7 | 48 | 2014-07-28 16:30:42 | [Email protected] | [Email protected]% |
|       8 | 50 | 2014-07-28 16:46:11 | [Email protected] | [Email protected]% |
+----+-----------+---------------------+---------------------------+--------------+
8 rows in Set (0.00 sec)

go toMySQLdb Server View Binlog content, after parsing, there is no insert statement, what's going on, to see MY.CNF
#binlog-ignore-db=mysql # No Sync databases
#binlog-ignore-db=test # No Sync databases
#binlog-ignore-db=information_schema # No Sync databases
#binlog-ignore-db=performance_schema
turns out to beThe test library has binlog filtering settings, all commented out. Restart the MySQL library, again, and you can see the Binlog
Re-executed on the MySQL client.
Mysql> usetest;
Database changed
Mysql> insertinto test.t1 Select 5, ' T5 ';
Query OK, 1 rowaffected (0.00 sec)
Records:1 duplicates:0 warnings:0

Mysql> select* from Access_log.access_log;
+----+-----------+---------------------+---------------------------+--------------+
| ID | thread_id| Log_time | LocalName | MatchName |
+----+-----------+---------------------+---------------------------+--------------+
|       1 | 17 | 2014-07-28 15:41:04 | [Email protected]| [Email protected]% |
|       2 | 18 | 2014-07-28 15:41:05 | [Email protected]| [Email protected]% |
|       3 | 19 | 2014-07-28 15:41:05 | [Email protected]| [Email protected]% |
|       4 | 41 | 2014-07-28 16:19:37 | [Email protected] | [Email protected]% |
|       5 | 42 | 2014-07-28 16:20:32 | [Email protected] | [Email protected]% |
|       6 | 45 | 2014-07-28 16:21:11 | [Email protected] | [Email protected]% |
|       7 | 48 | 2014-07-28 16:30:42 | [Email protected] | [Email protected]% |
|       8 | 50 | 2014-07-28 16:46:11 | [Email protected] | [Email protected]% |
|       9 | 56 | 2014-07-28 19:32:12 | [Email protected] | [Email protected]% |
| 10 |1| 2014-07-28 20:02:56 | [Email protected] | [Email protected]% |
+----+-----------+---------------------+---------------------------+--------------+
Rows in Set (0.00 sec)
SeeTHREAD_ID is 1


6, how to view what tracking user behavior records.
go toTo view Binlog on the MySQL database server, you should thread_id=1 the Binlog record.
[Email protected]_serverbinlog]#/usr/local/mysql/bin/mysqlbinlog--base64-output=decode-rows mysql-bin.000018-v >3.log
[[Email protected]_serverbinlog]# vim 3.log
# at 1103
#140728 20:12:48server ID end_log_pos 1175 CRC32 0xa323c00e Querythread_id=1Exec_time=0 error_code=0
settimestamp=1406549568/*!*/;
BEGIN
/*!*/;
# at 1175
#140728 20:12:48server ID end_log_pos 1229 CRC32 0xbb8ca914 table_map: ' Test '. ' T1 ' mapped to number 72
# at 1229
#140728 20:12:48server ID end_log_pos 1272 CRC32 0x8eed1450 write_rows:table ID flags:stmt_end_f
# # # INSERT into ' test '. ' T1 '
# # SET
# # @1=5
# # # @2= ' T5 '
# at 1272
#140728 20:12:48server ID end_log_pos 1303 CRC32 0x72b26336 Xid = 14
commit/*!*/;


SeeThread_id=1, you can then judge the source of the insert command according to Thread_id=1, and you can execute show full processlist on the MySQL server, to get the request port of the MySQL client.
Mysql> Showfull processlist;
+----+------------+-------------------+------+---------+------+-------+-----------------------+
| Id | User | Host |db | Command | Time | State | Info |
+----+------------+-------------------+------+---------+------+-------+-----------------------+
| 1 |audit_user | 192.168.3.62:44657 | Test |  Sleep |      162 | | NULL |
| 3 | Root | localhost | NULL |    Query | 0 | init | Show Full Processlist |
+----+------------+-------------------+------+---------+------+-------+-----------------------+
2 rows in Set (0.00 sec)


Mysql>
SeeA thread with an ID of 1, the port is44657。

We switch back toFor the MySQL client, go to see what process the port is 44657, as follows:
[[Email protected]_client~]$ NETSTAT-ANTLP |grep 44657
(not allprocesses could is identified, non-owned process info
Won't beshown, you would has to be the root to see it all.)
TCP 0 0 192.168.3.62:44657 192.168.1.12:3307 established 6335/mysql
[[Email protected]_client~]$
That gets to the process.PID, and then get the command executed by the process through Ps-eaf, as follows:
[[Email protected]_client~]$ ps-eaf|grep 6335
Tim 633525497 0 19:59 pts/1 00:00:00 mysql-uaudit_user-p-h192.168.1.12-p3307
Tim 6993 6906 0 20:16 pts/2 00:00:00 grep 6335
[[Email Protected]_client ~]$
finally , the connection is logged through the MySQL client. To join this 6335 is a Web project, you can also query the ps-eaf command to get the web Process information for the project.


From: http://blog.itpub.net/26230597/viewspace-1240386/

Reference article address: http://blog.chinaunix.net/uid-24086995-id-168445.html