MySQL user accidentally delete the from table, how to query the user information?

CodePudding user response:

MySQL enterprise audit audit module, open after the audit, the user can query to each operation, in addition, if you have a backup file, also can be used to backup files and binlog accidentally delete table, the hope can help you,

CodePudding user response:

MySQL community edition did not bring the design of the function or plug-ins, the survey found the audit MariaDB plugin also applies to MySQL, support more granular audit, such as audit only DDL operations, to meet our requirements, because recently one table structure regular test environment and the data has been cleared by the change of situation, so the introduction of MariaDB plug-in audit DDL
MariaDB audit the plugin's website
MariaDB audit the plugin download address
http://dl.bintray.com/mcafee/mysql-audit-plugin/
See the MySQL plugin path


Mysql> Show global variables like '% plugin %';
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
Value | | Variable_name |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| plugin_dir |/usr/local/mysql/lib/plugins/|
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.00 SEC)

Mysql> Select the version ();
+ -- -- -- -- -- -- -- -- -- -- -- -- +
(1) | | version
+ -- -- -- -- -- -- -- -- -- -- -- -- +
| 5.6.33 - log |
+ -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.00 SEC)

I choose to download the plug-in version of the file for server_audit - 1.4.0. Tar. Gz
After decompression plugin file server_audit. So copy to MySQL plugin file directory
Install
Mysql> INSTALL the PLUGIN server_audit SONAME 'server_audit. So;
Query OK, 0 rows affected (0.02 SEC)
# loading plug-ins will restart fail online installation, can be configured in the configuration file
[mysqld]
.
Plugin_load=server_audit=server_audit. So
Configuration audit item

# after installation related configuration items have
SHOW GLOBAL VARIABLES LIKE 'server_audit %';

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
Value | | Variable_name |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| server_audit_events | CONNECT, QUERY, TABLE |
| server_audit_excl_users | |
| server_audit_file_path | server_audit. Log |
| server_audit_file_rotate_now | OFF |
1000000 | | server_audit_file_rotate_size |
| | server_audit_file_rotations | 9
| server_audit_incl_users | |
ON | server_audit_logging | |
| | server_audit_mode | 0
| server_audit_output_type | file |
1024 | | server_audit_query_log_limit |
| server_audit_syslog_facility | LOG_USER |
| server_audit_syslog_ident | mysql - server_auditing |
| server_audit_syslog_info | |
| server_audit_syslog_priority | LOG_INFO |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

According to our requirements set
Mysql> The set global server_audit_events='query_ddl, table;
Query OK, 0 rows affected (0.00 SEC)

Mysql> The set global server_audit_logging=on;
Query OK, 0 rows affected (0.00 SEC)
After a set of DDL audit log, such as
20180416 11:25:22, mysql 5.6. Dev. Yz, root, localhost, 34950852215, 54, QUERY, test, 'truncate table t1, 0
About server_audit_events optional parameters have connect: will record all connections, including failed and the closing of the connection logs, such as log, but for us, don't care
[[email protected] 3306 _develop] # tailf server_audit. Log
20180416 11:22:42, mysql 5.6. Dev. Yz, root, 10.211.253.104, 34950731, 0, 0, test, CONNECT,
20180416 11:22:48, mysql 5.6. Dev. Yz, admin, 10.211.253.153, 34950655, 0, 0 DISCONNECT, test,,
20180416 11:22:48, mysql 5.6. Dev. Yz, admin, 10.211.253.153, 34950732, 0, 0, test, CONNECT,
20180416 11:22:49, mysql 5.6. Dev. Yz, admin, 10.211.253.101, 34950664, 0, 0 DISCONNECT, test,,








The set global server_audit_events='CONNECT, QUERY, table, QUERY_DDL, QUERY_DML, QUERY_DCL';



Key parameters:
Server_audit_file_path: if server_audit_output_type=file, set to log path
Server_audit_logging: must be open to log
Server_audit_events: has the following options, multiple use commas
CONNECT: Logs connects and disconnects the and failed connects (o the error code).
QUERY: the Queries issued and their results in plain text), o failed the Queries due to syntax or permission errors.
The TABLE, Which tables were affected by the query execution.
QUERY_DDL: Works as the 'QUERY' value, but filters only DDL -type queries (CREATE, ALTER, etc.)
QUERY_DML: Works as the 'QUERY' value, but filters only DML - type queries (INSERT, UPDATE, etc.)
QUERY_DCL: Works as the 'QUERY' value, but filters only DCL -type queries (GRANT, REVOKE, etc.)