MariaDB Audit & Statistics

MariaDB Audit & Statistics

环境:mariadb-10.1.13

Audit

一.安装Audit Plugin

https://mariadb.com/kb/en/mariadb-audit- plugin/

比较新的mariadb版本audit插件直接内嵌在版本里，可以直接安装

(jlive)[isfdb]>INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Query OK, 0 rows affected (0.16 sec)

(jlive)[isfdb]>SHOW PLUGINS;

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

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

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

64 rows in set (0.00 sec)

(jlive)[isfdb]>SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

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

| Variable_name | Value |

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

| plugin_dir | /opt/mariadb/lib/plugin/ |

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

1 row in set (0.15 sec)

如果plugin目录没有server_audit.so，就需要到http://www.skysql.com/ downloads/ 下载并copy到plugin目录

卸载请使用UNINSTALL PLUGIN 'server_audit';

如果想永久加载，只需在配置文件里指定如下行即可，此时卸载该插件时会报错，插件将无法卸载，直到配置文件变更

[mysqld]

plugin-load=server_audit=server_audit.so

server_audit=FORCE_PLUS_PERMANENT

二.使用Audit

查看

(jlive)[isfdb]>SHOW GLOBAL VARIABLES LIKE 'server_audit%'G

*************************** 1. row ***************************

Variable_name: server_audit_events

Value:

*************************** 2. row ***************************

Variable_name: server_audit_excl_users

Value:

*************************** 3. row ***************************

Variable_name: server_audit_file_path

Value: server_audit.log

*************************** 4. row ***************************

Variable_name: server_audit_file_rotate_now

Value: OFF

*************************** 5. row ***************************

Variable_name: server_audit_file_rotate_size

Value: 1000000

*************************** 6. row ***************************

Variable_name: server_audit_file_rotations

Value: 9

*************************** 7. row ***************************

Variable_name: server_audit_incl_users

Value:

*************************** 8. row ***************************

Variable_name: server_audit_loc_info

Value: OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

*************************** 9. row ***************************

Variable_name: server_audit_logging

Value: OFF

*************************** 10. row ***************************

Variable_name: server_audit_mode

Value: 0

*************************** 11. row ***************************

Variable_name: server_audit_output_type

Value: file

*************************** 12. row ***************************

Variable_name: server_audit_query_log_limit

Value: 1024

*************************** 13. row ***************************

Variable_name: server_audit_syslog_facility

Value: LOG_USER

*************************** 14. row ***************************

Variable_name: server_audit_syslog_ident

Value: mysql-server_auditing

*************************** 15. row ***************************

Variable_name: server_audit_syslog_info

Value:

*************************** 16. row ***************************

Variable_name: server_audit_syslog_priority

Value: LOG_INFO

16 rows in set (0.00 sec)

启用audit

(jlive)[isfdb]>SET GLOBAL server_audit_logging=ON;

Query OK, 0 rows affected (0.15 sec)

(jlive)[isfdb]>SET GLOBAL server_audit_file_rotate_now=ON;

Query OK, 0 rows affected (0.00 sec)

(jlive)[isfdb]>SHOW GLOBAL STATUS LIKE 'server_audit%';

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

| Variable_name | Value |

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

| Server_audit_active | ON |

| Server_audit_current_log | server_audit.log |

| Server_audit_last_error | |

| Server_audit_writes_failed | 0 |

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

4 rows in set (0.00 sec)

配置需要(或不需要)track的用户

SET GLOBAL server_audit_incl_users = 'untrusted_user';

SET GLOBAL server_audit_incl_users = CONCAT(@@global.server_audit_incl_users,',untrusted_user2');

SHOW GLOBAL VARIABLES LIKE 'server_audit_incl_users';

SET GLOBAL server_audit_excl_users = 'trusted_user';

SET GLOBAL server_audit_excl_users = CONCAT(@@global.server_audit_excl_users, ',trusted_user2');

SHOW GLOBAL VARIABLES LIKE 'server_audit_excl_users';

说明:默认情况下，audit会track所有用户，可以通过server_audit_incl_users来指定要track的用户，也可以通过server_audit_excl_users来排除不需要track的用户，作限制和排除时会忽略主机名，如下用户都会include到track用户下，同时incl的优先级高于excl，也就是说，如果一个用户既在incl又在excl，则这个用户会被track

untrusted_user@'localhost'

untrusted_user@'192.168.1.%'

untrusted_user@'%'

(jlive)[isfdb]>SHOW GLOBAL VARIABLES LIKE 'server_audit_incl_users';

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

| Variable_name | Value |

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

| server_audit_incl_users | untrusted_user,untrusted_user2 |

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

1 row in set (0.00 sec)

(jlive)[isfdb]>SHOW GLOBAL VARIABLES LIKE 'server_audit_excl_users';

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

| Variable_name | Value |

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

| server_audit_excl_users | trusted_user,trusted_user2 |

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

1 row in set (0.00 sec)

Statistics

https://mariadb.com/kb/en/engine-independent-table-statistics/

不管使用哪种存储引擎，mariadb都能够对所有的表进行统计

启用表统计

SET GLOBAL use_stat_tables=complementary; #有三个合法的值(never不使用表统计;complementary如果存储引擎没有类似的统计功能，则使用表统计;preferably总是使用表统计)

ANALYZE TABLE table_name;

ANALYZE TABLE table_name PERSISTENT FOR COLUMNS (column_1,column_2,...) INDEXES (index_1,index_2,...);

查看统计信息

SELECT * FROM mysql.table_stats;

SELECT * FROM mysql.index_stats;

SELECT * FROM mysql.column_stats;

Extended Statistics

启用

SET GLOBAL userstat=1;

[mysqld]

userstat = 1

查看统计

SHOW CLIENT_STATISTICS;

SHOW INDEX_STATISTICS;

SHOW TABLE_STATISTICS;

SHOW USER_STATISTICS;

统计置零

FLUSH CLIENT_STATISTICS;

FLUSH INDEX_STATISTICS;

FLUSH TABLE_STATISTICS;

FLUSH USER_STATISTICS;

performance

https://mariadb.com/kb/en/performance-schema/

说明:performance_schema不能动态生效，任何对performance_schema的修改都需要写入配置文件后才能生效

启用performance_schema

在配置文件中添加如下行重启

[mysqld]

performance_schema

查看用户连接次数

(jlive)[isfdb]>SELECT * FROM performance_schema.users;

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

| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |

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

| zabbix | 3 | 3 |

| jlive | 1 | 1 |

| NULL | 22 | 26 |

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

3 rows in set (0.01 sec)

提示:禁用用户连接数统计,添加如下行后重启，默认值为-1,统计所有

[mysqld]

performance_schema_users_size=0

查看激活连接的线程

(jlive)[isfdb]>SELECT * FROM performance_schema.threads WHERE type="foreground"G

*************************** 1. row ***************************

THREAD_ID: 25

NAME: thread/sql/one_connection

TYPE: FOREGROUND

PROCESSLIST_ID: 3

PROCESSLIST_USER: zabbix

PROCESSLIST_HOST: localhost

PROCESSLIST_DB: zabbix

PROCESSLIST_COMMAND: Sleep

PROCESSLIST_TIME: 0

PROCESSLIST_STATE: NULL

PROCESSLIST_INFO: NULL

PARENT_THREAD_ID: 1

ROLE: NULL

INSTRUMENTED: YES

*************************** 2. row ***************************

THREAD_ID: 27

NAME: thread/sql/one_connection

TYPE: FOREGROUND

PROCESSLIST_ID: 5

PROCESSLIST_USER: jlive

PROCESSLIST_HOST: 192.168.130.1

PROCESSLIST_DB: isfdb

PROCESSLIST_COMMAND: Query

PROCESSLIST_TIME: 0

PROCESSLIST_STATE: Sending data

PROCESSLIST_INFO: SELECT * FROM performance_schema.threads WHERE type="foreground"

PARENT_THREAD_ID: NULL

ROLE: NULL

INSTRUMENTED: YES

相关阅读:
《银光志Silverlight 3.0开发详解与最佳实践》出版电子版——风云编著
Nigel Parker 40分钟视频演示了微软的 31 项技术（附下载）
《银光志Silverlight 3.0开发详解与最佳实践》书搞目录
Silverlight 2使用C#遍历XML(兼容Silverlight3)
为什么要把Silverlight归入Web 2.0?
Silverlight明年将占据互联网设备半壁江山
Silverlight Tools 3.0中文正式版发布（附下载地址）
银客帝国招聘Silverlight兼职开发人员
再说招聘：学开车一定要摸方向盘
Expression Blend 4 下载

原文地址：https://www.cnblogs.com/lixuebin/p/10814120.html