percona和mariadb的审计插件

折腾了2个mysql优秀分支的审计插件，个人觉得mariadb的不错，比较直观，而percona的是类似于json格式的，可能对那些擅长文本处理的人比较合适，以下是2个插件的大概配置，其中没有对各个参数做出详细解释。如需更加详细内容，请参考官方文档。

Percona audit plugin:

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

mysql> show variables like '%audit%';

+---------------------------+---------------+

|Variable_name             | Value         |

+---------------------------+---------------+

|audit_log_buffer_size     | 1048576       |

|audit_log_file            |audit.log     |

|audit_log_flush           | OFF           |

|audit_log_format          | OLD           |

|audit_log_handler         | FILE          |

|audit_log_policy          | LOGINS        |

|audit_log_rotate_on_size  | 0             |

|audit_log_rotations       | 0             |

|audit_log_strategy        |ASYNCHRONOUS  |

|audit_log_syslog_facility | LOG_USER     |

|audit_log_syslog_ident    | percona-audit|

|audit_log_syslog_priority | LOG_INFO     |

+---------------------------+---------------+

12 rowsin set (0.00 sec)

 

值	含义
ASYNCHRONOUS	异步日志，等待输出缓冲区空间
PERFORMANCE	异步日志，如果在输出缓冲区空间不足则降低请求
SEMISYBCHRONOUS	同步日志，使用操作系统缓存
SYNCHRONOUS	同步日志，每个请求过后都会调用sync()方法

Mariadb audit plugin:

mysql> install plugin server_audit soname 'server_audit.so';

mysql> show variables like '%audit%';

+-------------------------------+-----------------------+

|Variable_name                 |Value                 |

+-------------------------------+-----------------------+

|server_audit_events           |                       |

|server_audit_excl_users       |                       |

|server_audit_file_path        |server_audit.log      |

|server_audit_file_rotate_now  | OFF                   |

|server_audit_file_rotate_size | 1000000               |

|server_audit_file_rotations   | 9                     |

|server_audit_incl_users       |                       |

|server_audit_loc_info         |                       |

|server_audit_logging          | OFF                   |

|server_audit_mode             | 1                     |

|server_audit_output_type      | file                  |

|server_audit_query_log_limit  | 1024                  |

|server_audit_syslog_facility  |LOG_USER              |

|server_audit_syslog_ident     |mysql-server_auditing |

|server_audit_syslog_info      |                       |

|server_audit_syslog_priority  |LOG_INFO              |

+-------------------------------+-----------------------+

16 rowsin set (0.00 sec)

 

mysql> set global server_audit_events='connect,query_ddl';

也可写在配置文件中：

[mysqld]

server_audit_events=connect,query

Type	Description	Introduced
CONNECT	Logs connects, disconnects and failed connects (including the error code).
QUERY	Queries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLE	Which tables were affected by query execution.
QUERY_DDL	Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).	MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML	Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).	MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DCL	Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.)	MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5

 

 

mysql> set global server_audit_logging=1;

timestamp	serverhost	username	host	connectionid	queryid	operation	database	object	retcode
20160516 15:31:18	centos-demo.ledo.com	thunder	10.237.13.148	21		QUERY		SHOW GLOBAL VARIABLES	0

2016051615:31:18,centos-demo.ledo.com,thunder,10.237.13.148,21,193,QUERY,,'SHOW GLOBALVARIABLES',0

2016051616:03:27,centos-demo.ledo.com,root,localhost,15,840,QUERY,thunder,'showtables',0

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/#installation