环境:mariadb-10.1.13
Audit
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)
[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