Mysql Logs user actions

Mysql connection is initialized via Init_connect, website description:

The string that the server executes for each connected client. The string consists of one or more SQL statements separated by a semicolon character. For example, the auto-commit mode is enabled by default for each client session. For older servers (before MySQL 5.5.8), there is no global auto-commit system variable to specify that autocommit should be disabled by default, but as a workaround, init_connect can be used to achieve the same effect: Set global Init_connect = ' Set autocommit = 0 ';

1. Creating databases and Tables

To create a database:

Create DATABASE DBA;

To create a table:

CREATE TABLE Accesslog (' thread_id ' int primary key auto_increment, ' time ' timestamp, ' localname ' varchar (+), ' Machine_na Me ' varchar (40));

thread_id: Log MySQL thread ID

Time: Record operation times

LocalName: Logging Operations Remote IP

Machine_Name: Record User

2. Variable Configuration

View Init_connect

+---------------+-------+| variable_name | value |                                                                                                                     |+---------------+-- -----+| init_connect  |       | |  init_file     |       | |  init_slave    |       |+---------------+-------+3 rows in set  (0.00 sec) 

Configuration variables

Set global init_connect= ' INSERT INTO Dba.accesslog (thread_id,time,localname,machine_name) VALUES (connection_id (), Now (), User (), current_user ());

View Init_connect again

+---------------+---------------------------------------------------------------------------------------------- -------------------------+| 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. Assigning Logging permissions to users

Grant Select,insert,update on Dba.accesslog to ' zhaohongming '% ';

4. Simulate User actions Add Delete

[Email protected]:(none) 11:34:49 >use sdlcqw; [Email protected]:(none) 11:34:49 >crate table haha (cc int); [Email protected]:(none) 11:34:49 >drop table haha;

5. View Binlog Records

Export Binlog content:

# Mysqlbinlog mysql-bin.000079 >/root/9.txt

Query the Log table:

[email protected]:(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 changed[email protected]:d ba 11:39:50 >select *  from accesslog limit 1;+-----------+---------------------+------------------------+------------ ----+| thread_id | time                 | localname               | machine_name   |+-----------+---------------------+------------------- -----+----------------+|    279950 | 2018-05-24 08:51:37 | [email  protected] | [email protected]% |+-----------+---------------------+------------------------+----------------+1 row in set  (0.00  SEC)

6. View Binlog Content

The thread above is 279950

# Cat 9.txt | grep-b 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/*!*/;D rop table  ' haha '  /* generated by  server */

Mysql Logs user actions