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;

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

| Name                          | Status   | Type               | Library         | License |

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

| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |

| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL            | GPL     |

| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| XTRADB_READ_VIEW              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| XTRADB_INTERNAL_HASH_TABLES   | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| XTRADB_RSEG                   | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_CHANGED_PAGES          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL            | BSD     |

| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL            | BSD     |

| CHANGED_PAGE_BITMAPS          | ACTIVE   | INFORMATION SCHEMA | NULL            | GPL     |

| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL            | GPL     |

| partition                     | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |

| TokuDB                        | ACTIVE   | STORAGE ENGINE     | ha_tokudb.so    | GPL     |

| TokuDB_trx                    | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| TokuDB_lock_waits             | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| TokuDB_locks                  | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| TokuDB_file_map               | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| TokuDB_fractal_tree_info      | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| TokuDB_fractal_tree_block_map | ACTIVE   | INFORMATION SCHEMA | ha_tokudb.so    | GPL     |

| CONNECT                       | ACTIVE   | STORAGE ENGINE     | ha_connect.so   | GPL     |

| SERVER_AUDIT                  | ACTIVE   | AUDIT              | server_audit.so | GPL     |

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

 

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