Detailed details of how MySQL is logged

Use Init-connect and Binlog in MySQL to implement user action tracking records 2014-07-28 20:55:38

Category: MySQL

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 track the operation time of each operation statement, the operator and the client's connection process information, etc. Implement audits.


1, inMySQLServerDBCreate a separate library of record access information in the
Set names UTF8;
Create Database Access_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
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=1 DEFAULT charset=utf8 comment ' Input user login information ';



2, configure in the configuration fileInit-connectparameters. Insert Log table when logging in. If this parameter is an error,SQLstatement, the login will fail.
Vim/usr/local/mysql/my.cnf
Init-connect= ' INSERT into Access_log.access_log VALUES (null,connection_id (), now (), USER (), Current_User ()); '
Then restart the database

3, create a normal user, cannot haveSuperuser must have access to theAccess_logLibrary'sAccess_logof the tableInsertpermissions, or the login fails.
Give the login user insert permission, but do not give Access_log Insert, select permissions,
GRANT Insert,delete,update,select on test.* to [e-mail protected] '% ' identified by ' cacti_user1603 ';
Mysql> GRANT Create,drop,alter,insert,delete,update,select on test.* to [email protected] '% ' identified by ' cacti_ user1603 ';
Query OK, 0 rows Affected (0.00 sec)
Mysql> exit
And then go with the new Audit_user login operation
[Email protected]_server ~]#/usr/local/mysql/bin/mysql-uaudit_user-p-s/usr/local/mysql/mysql.sock
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 25
Server Version:5.6.12-log

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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 Failed
Mysql>
See the error message (Init_connect command failed), and then go to the Errors log to verify:
TAIL-FN 5/usr/local/mysql/mysqld.log
2014-07-28 16:03:31 23743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_ Connect command failed)
2014-07-28 16:03:31 23743 [Warning] INSERT command denied to user ' @ ' localhost ' for table ' Access_log '
2014-07-28 16:04:04 23743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_ Connect command failed)
2014-07-28 16:04:04 23743 [Warning] INSERT command denied to user ' @ ' localhost ' for table ' Access_log '
See that you must have insert permission on the Access_log table for the Access_log library.


4, giving usersAccess_logof theInsert,Selectpermissions, and then re-assign permissions:
GRANT Select,insert on access_log.* to [email protected] '% ';
Mysql>
Mysql> GRANT Select,insert on access_log.* to [email protected] '% ';
Query OK, 0 rows Affected (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 view the error log:
2014-07-28 16:15:29 23743 [Warning] INSERT command denied to user ' @ ' localhost ' for table ' Access_log '
2014-07-28 16:15:41 23743 [Warning] aborted connection Notoginseng to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_ Connect command failed)
2014-07-28 16:15:41 23743 [Warning] INSERT command denied to user ' @ ' localhost ' for table ' Access_log '
2014-07-28 16:15:50 23743 [Warning] aborted connection to DB: ' unconnected ' User: ' Audit_user ' host: ' localhost ' (init_ Connect command failed)
2014-07-28 16:15:50 23743 [Warning] INSERT command denied to user ' @ ' localhost ' for table ' Access_log '

You need to log in with the root user and empty 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> drop user ' @ ' localhost ';
Query OK, 0 rows Affected (0.00 sec)

mysql> drop user ' @ ' db_server ';
Query OK, 0 rows Affected (0.00 sec)

Mysql>

Audit_user Login with insert permission assigned to the Access_log 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> Show full processlist;
+----+------------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------+------+---------+------+-------+-----------------------+
| 45 | Audit_user | localhost | NULL |    Query | 0 | init | Show Full Processlist |
+----+------------+-----------+------+---------+------+-------+-----------------------+
1 row in Set (0.00 sec)

Mysql>

5, and then use another user login to build the table, enter the test data.
Data record of building table input
mysql> use test;
Database changed
mysql> CREATE TABLE T1 Select 1 as a, ' WA ' as B;
Query OK, 1 row affected (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 to MySQL DB server to 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

The original is the test library has binlog filter settings, all commented out. Restart the MySQL library, again, and you can see the Binlog
Re-executed on the MySQL client.
mysql> use test;
Database changed
mysql> INSERT INTO TEST.T1 Select 5, ' T5 ';
Query OK, 1 row Affected (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)
See thread_id as 1


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

When you see thread_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> Show full 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>
See the thread with ID 1, the port is44657。

We switched back to the MySQL client and went to see if the port was44657What the process is, as follows:
[[Email protected]_client ~]$ NETSTAT-ANTLP |grep 44657
(Not all processes could is identified, non-owned process info
Would is not being shown, 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 ~]$

Gets the PID to the process6335, and then get the command executed by the process through Ps-eaf, as follows:
[[Email protected]_client ~]$ ps-eaf|grep 6335
Tim 6335 25497 0 19:59 pts/1 00:00:00 mysql-uaudit_user-p-H 192.168.1.12-p3307
Tim 6993 6906 0 20:16 pts/2 00:00:00 grep 6335
[[Email Protected]_client ~]$

Finally, we found out that throughMySQLthe client logs on the connection. Join this6335is aWebEngineering, then, can also be based onPs-eafcommand Query getsWebprocess information for the project.

Http://blog.chinaunix.net/uid-24086995-id-168445.html

Http://blog.itpub.net/26230597/viewspace-1240386

Detailed details of how MySQL is logged