Use init-connect and binlog in MySQL to track user operations, init-connectbinlog

Use init-connect and binlog in MySQL to track user operations, init-connectbinlog

Preface:
In the test environment, several important data records are deleted. Because only the public account is deleted in the binlog, you cannot query who logged on to the database during that time period, consider how to record the login information of each MYSQL account. In MYSQL, each connection will first execute init-connect to initialize the connection, here we can get the user's login name and thread ID value. Then, with binlog, you can track the operation time and operator of each operation statement. Implement Audit.


1. Create a separate database for recording access information in the mysql server database
Set names utf8;
Create databaseaccess_log;
Create table 'Access _ Log'
(
'Id' int (11) not null AUTO_INCREMENT,
'Thread _ id' int (11) default null, -- thread id. This value is very important.
'Log _ time' timestamp not null def ault CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- Logon time
'Localname' varchar (30) default null, -- Logon Name
'Matchname' varchar (30) default null, -- logon user
PRIMARYKEY ('id ')
) ENGINE = InnoDBAUTO_INCREMENT = 1 default charset = utf8 comment 'enter User Logon information ';



2. Configure the init-connect parameter in the configuration file. Insert a log table at login. If this parameter is an incorrect SQL statement, logon fails.
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 common user. the user cannot have the super permission and must have the insert permission on the access_log table of the access_log database. Otherwise, the logon will fail.
Grant the insert permission to the login user, but do not grant the insert and select permissions of access_log,
GRANTINSERT, DELETE, UPDATE, select on test. * TO audit_user @ '%' identified by 'cacti _ user1603 ';
Mysql> GRANTCREATE, DROP, ALTER, INSERT, DELETE, UPDATE, select on test. * TO audit_user @ '%' identified by 'cacti _ user1603 ';
Query OK, 0 rowsaffected (0.00 sec)
Mysql> exit
Then log on with the new audit_user.
[Root @ db_server ~] #/Usr/local/mysql/bin/mysql-uaudit_user-p-S/usr/local/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) 2000,201 3, Oracle and/or its affiliates. All rights reserved.

Oracle is aregistered trademark of Oracle Corporation and/or its
Affiliates. Othernames may be 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 26 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
Mysql>
Check the error message (init_connect command failed) and verify the error log again:
Tail-fn 5/usr/local/mysql/mysqld. log
16: 03: 3123743 [Warning] Aborted connection 25 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect 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 26 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 04: 04 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'
You must have the insert permission on the access_log table of the access_log database.


4. Grant the insert and select permissions of access_log to the user, and then re-grant the permissions:
GRANTSELECT, insert on access_log. * TO audit_user @ '% ';
Mysql>
Mysql> GRANTSELECT, insert on access_log. * TO audit_user @ '% ';
Query OK, 0 rowsaffected (0.00 sec)

Mysql> exit
Bye

Log on again and report the following error:
[Root @ db_server ~] #/Usr/local/mysql/bin/mysql-uaudit_user-p-S/usr/local/mysql. sock
Enter password:
ERROR 1045 (28000): Access denied for user 'audit _ user' @ 'localhost' (using password: YES)
[Root @ db_server ~] #

View the error 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 37 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect 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 38 to db: 'unconnected' user: 'audit _ user' host: 'localhost' (init_connect command failed)
2014-07-2816: 15: 50 23743 [Warning] INSERT command denied to user ''@ 'localhost' for table 'Access _ Log'

You need to log in with the root user to clear the user records whose user is.
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 |
+ ---------------- + ----------- + ------------------------------------------- +
15 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>

Log On with the audit_user assigned the Insert permission for the access_log table.
Mysql> select * from access_log.access_log;
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
| Id | thread_id | log_time | localname | matchname |
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
| 4 | 41 | 16:19:37 | audit_user @ localhost | audit_user @ % |
| 5 | 42 | 16:20:32 | audit_user @ localhost | audit_user @ % |
| 6 | 45 | 16:21:11 | audit_user @ localhost | audit_user @ % |
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
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. log on to the table with another user and enter the test data.
Create Table and input data records
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 user behavior records.
Mysql> select * from access_log.access_log;
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
| Id | thread_id | log_time | localname | matchname |
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
| 4 | 41 | 16:19:37 | audit_user @ localhost | audit_user @ % |
| 5 | 42 | 16:20:32 | audit_user @ localhost | audit_user @ % |
| 6 | 45 | 16:21:11 | audit_user @ localhost | audit_user @ % |
| 7 | 48 | 16:30:42 | audit_user@192.168.3.62 | audit_user @ % |
| 8 | 50 | 16:46:11 | audit_user@192.168.3.62 | audit_user @ % |
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
8 rows in set (0.00 sec)

Go to the mysqldb server to view the binlog content. After parsing, there is no insert statement. What's going on? Check 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
Originally, binlog filtering settings were set for the test database, all of which were commented out. Restart the mysql database and repeat it. You can see the binlog
Run the command again 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 | 15:41:04 | cacti_user@192.168.171.71 | cacti_user @ % |
| 2 | 18 | 15:41:05 | cacti_user@192.168.171.71 | cacti_user @ % |
| 3 | 19 | 15:41:05 | cacti_user@192.168.171.71 | cacti_user @ % |
| 4 | 41 | 16:19:37 | audit_user @ localhost | audit_user @ % |
| 5 | 42 | 16:20:32 | audit_user @ localhost | audit_user @ % |
| 6 | 45 | 16:21:11 | audit_user @ localhost | audit_user @ % |
| 7 | 48 | 16:30:42 | audit_user@192.168.3.62 | audit_user @ % |
| 8 | 50 | 16:46:11 | audit_user@192.168.3.62 | audit_user @ % |
| 9 | 56 | 19:32:12 | audit_user@192.168.1.12 | audit_user @ % |
| 10 | 1 | 20:02:56 | audit_user@192.168.3.62 | audit_user @ % |
+ ---- + ----------- + --------------------- + --------------------------- + -------------- +
10 rows in set (0.00 sec)
The thread_id is 1.


6. How to view and track user behavior records.
Check the binlog on the mysql database server. The binlog record with thread_id = 1 should be returned.
[Root @ db_serverbinlog] #/usr/local/mysql/bin/mysqlbinlog -- base64-output = DECODE-ROWS mysql-bin.000018-v> 3.log
[Root @ db_serverbinlog] # vim 3.log
# At 1103
#140728 20: 12: 48 server id 72 end_log_pos 1175 CRC32 0xa323c00e Query thread_id = 1 exec_time = 0 error_code = 0
SETTIMESTAMP = 1406549568 /*! */;
BEGIN
/*! */;
# At 1175
#140728 20: 12: 48 server id 72 end_log_pos 1229 CRC32 0xbb8ca914 Table_map: 'test'. 't1' mapped to number 72
# At 1229
#140728 20: 12: 48 server id 72 end_log_pos 1272 CRC32 0x8eed1450 Write_rows: table id 72 flags: STMT_END_F
### Insert into 'test'. 't1'
### SET
###@ 1 = 5
###@ 2 = 't5'
# At 1272
#140728 20: 12: 48 server id 72 end_log_pos 1303 CRC32 0x72b26336 Xid = 14
COMMIT /*! */;


We can see that thread_id = 1. Then, we can judge the source for executing this insert command based on thread_id = 1. We can also execute show full processlist on the mysql server; to obtain 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>
The thread with Id 1 is displayed, and the port is44657.

We switch back to the mysql client to check what process the port is on, as shown below:
[Tim @ db_client ~] $ Netstat-antlp | grep 44657
(Not allprocesses cocould be identified, non-owned process info
Will not beshown, you wowould have to be root to see it all .)
Tcp 0 0 192.168.3.62: 44657 192.168.1.12: 3307 ESTABLISHED 6335/mysql
[Tim @ db_client ~] $
Obtain the PID of the process, and then run ps-eaf to obtain the Command executed by the process, as shown below:
[Tim @ db_client ~] $ Ps-eaf| grep 6335
Tim 633525497 0 00:00:00 pts/1 mysql-uaudit_user-p-h192.168.1.12-P3307
Tim 6993 6906 0 00:00:00 pts/2 grep 6335
[Tim @ db_client ~] $
Finally, we found the connection was logged on through the mysql client. If you join this 6335 project in a web project, you can query the process information of the web project according to the ps-eaf command.


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

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

Mysql uses mysqlbinlog to view logs

Mysqlbinlog mysql-bin.00005 -- startdatetime = 'time' -- stopdatetime = 'time' -- database = DB_name | grep-I-A 6 'id = 100'

Use grep ..

Mysql binlog log Switch

This is set in the mysql configuration file ~~~ Set to enable binary log ~~~