mysqlauditgrep 与前面介绍的 mysqlauditadmin 工具一样，也是针对MySQL企业版本的。该工具提供对当前的或已经归档的审计日志进行搜索，根据不同的搜索标准搜索过滤显示结果，也可以对输出结果进行不同的格式化，如GRID (默认), TAB, CSV, VERTICAL, 和 RAW (原始的 XML 格式)。
mysqlauditgrep 可以根据下面的选项进行搜索和过滤审计日志：用户 (--users)、日期和时间范围 (--start-date and --end-date)、SQL查询类型(--query-type)、日志事件和记录类型 (--event-type)、状态 (--status)、匹配模式(--pattern)。 这些选项可以随意组合以便检索到最需要的记录。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
匹配模式(--pattern)支持两种模式匹配：标准的SQL，也就是使用LIKE匹配(SQL模式)。另一种REGEXP(POSIX正则表达式的模式)。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
输出格式
- GRID (default)
网格化输出，类似于MySQL客户端命令行工具显示输出。
- CSV
以逗号分隔值的格式输出.
- VERTICAL
类似于MySQL客户端命令行工具\G显示输出。
- RAW
原始RAW格式输出，写入到XML。
标准的SQL模式匹配
通过SQL标准定义的简单模式允进行匹配，% 和 _ 。默认不区分大小写。也就是like匹配。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
正则表达式模式匹配（POSIX）
标准的正则表达式模式比在SQL标准定义的简单模式更强大。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
关于正则表达式的语法定义这里就不累述了，本博客中有相关文章的介绍。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
这里是使用到Python的re模块，可以看看re模块文档：https://docs.python.org/2/library/re.html文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
选项
Usage: mysqlauditgrep [options] AUDIT_LOG_FILE
Options:
--version show program's version number and exit
--help display this help message and exit
--license display program's license and exit
-f FORMAT, --format=FORMAT
display the output in either GRID (default), TAB, CSV,
VERTICAL and RAW format
-u USERS, --users=USERS
find log entries by user name. Accepts a comma-
separated list of user names, for example:
joe,sally,nick
--file-stats display the audit log statistics.
--start-date=START_DATE
retrieve log entries starting from the specified
date/time. If not specified or the value is 0, all
entries from the start of the log are displayed.
Accepted formats: yyyy-mm-ddThh:mm:ss or yyyy-mm-dd.
--end-date=END_DATE retrieve log entries until the specified date/time. If
not specified or the value is 0, all entries to the
end of the log are displayed. Accepted formats: yyyy-
mm-ddThh:mm:ss or yyyy-mm-dd.
-e PATTERN, --pattern=PATTERN
search pattern to retrieve matching entries.
--query-type=QUERY_TYPE
search for all SQL statements/commands from the given
list of commands. Accepts a comma-separated list of
commands. Supported values: CREATE, ALTER, DROP,
TRUNCATE, RENAME, GRANT, REVOKE, SELECT, INSERT,
UPDATE, DELETE, COMMIT, SHOW, SET, CALL, PREPARE,
EXECUTE, DEALLOCATE
--event-type=EVENT_TYPE
search for all recorded event types from the given
list of supported log events. Accepts a comma-
separated list of event types. Supported values:
Audit, Binlog Dump, Change user, Close stmt, Connect
Out, Connect, Create DB, Daemon, Debug, Delayed
insert, Drop DB, Execute, Fetch, Field List, Init DB,
Kill, Long Data, NoAudit, Ping, Prepare, Processlist,
Query, Quit, Refresh, Register Slave, Reset stmt, Set
option, Shutdown, Sleep, Statistics, Table Dump, Time
--status=STATUS search for all entries with the specified status
values. Accepts a comma-separated list of non-negative
integers (corresponding to MySQL error codes) or
intervals marked with a dash. For example:
1051,1068-1075,1109,1146.
-G, --basic-regexp, --regexp
use 'REGEXP' operator to match pattern. Default is to
use 'LIKE'.
-v, --verbose control how much information is displayed. e.g., -v =
verbose, -vv = more verbose, -vvv = debug
该工具使用前需要先启用审计日志插件。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
实例
显示审计日志统计并以CSV格式输出文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --file-stats --format=CSV /SERVER/data/audit.log # # Audit Log File Statistics: # File,Size,Created,Last Modified audit.log,9101,Thu Sep 27 13:33:11 2012,Thu Oct 11 17:40:35 2012 # # Audit Log Startup Entries: # SERVER_ID,STARTUP_OPTIONS,NAME,TIMESTAMP,MYSQL_VERSION,OS_VERSION,VERSION 1,/SERVER/sql/mysqld --defaults-file=/SERVER/my.cnf,Audit,2012-09-27T13:33:11,5.5.29-log,x86_64-Linux,1
显示特定用户的审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log
显示审计日志统计文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --users=tester1,tester2 /SERVER/data/audit.log +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+ | STATUS | SERVER_ID | NAME | TIMESTAMP | CONNECTION_ID | HOST | USER | PRIV_USER | IP | SQLTEXT | +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+ | 0 | 1 | Connect | 2012-09-28T11:26:50 | 9 | localhost | root | tester1 | 127.0.0.1 | None | | 0 | 1 | Query | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | SET @@session.autocommit = OFF | | 0 | 1 | Ping | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | None | | 0 | 1 | Query | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | SHOW VARIABLES LIKE 'READ_ONLY' | | 0 | 1 | Query | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | COMMIT | | 0 | 1 | Ping | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | None | | 0 | 1 | Query | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | COMMIT | | 0 | 1 | Quit | 2012-09-28T11:26:50 | 9 | None | root | tester1 | None | None | | 0 | 1 | Connect | 2012-10-10T15:55:55 | 11 | localhost | tester2 | root | 127.0.0.1 | None | | 0 | 1 | Query | 2012-10-10T15:55:55 | 11 | None | tester2 | root | None | select @@version_comment limit 1 | | 0 | 1 | Query | 2012-10-10T15:56:10 | 11 | None | tester2 | root | None | show databases | | 1046 | 1 | Query | 2012-10-10T15:57:26 | 11 | None | tester2 | root | None | show tables test | | 1046 | 1 | Query | 2012-10-10T15:57:36 | 11 | None | tester2 | root | None | show tables test | | 0 | 1 | Query | 2012-10-10T15:57:51 | 11 | None | tester2 | root | None | show tables in test | | 0 | 1 | Quit | 2012-10-10T15:57:59 | 11 | None | tester2 | root | None | None | | 0 | 1 | Connect | 2012-10-10T17:35:42 | 12 | localhost | tester2 | root | 127.0.0.1 | None | | 0 | 1 | Query | 2012-10-10T17:35:42 | 12 | None | tester2 | root | None | select @@version_comment limit 1 | | 0 | 1 | Quit | 2012-10-10T17:47:22 | 12 | None | tester2 | root | None | None | +---------+------------+----------+----------------------+----------------+------------+----------+------------+------------+----------------------------------+
显示一个特定时间范围的审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --start-date=2012-09-27T13:33:47 --end-date=2012-09-28 /SERVER/data/audit.log +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+ | STATUS | TIMESTAMP | NAME | CONNECTION_ID | SQLTEXT | +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+ | 0 | 2012-09-27T13:33:47 | Ping | 7 | None | | 0 | 2012-09-27T13:33:47 | Query | 7 | SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%' | | 0 | 2012-09-27T13:33:47 | Query | 7 | COMMIT | | 0 | 2012-09-27T13:34:48 | Quit | 7 | None | | 0 | 2012-09-27T13:34:48 | Quit | 8 | None | +---------+----------------------+--------+----------------+---------------------------------------------------------------------------+
显示特定的SQL模式文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --pattern="% = ___"; /SERVER/data/audit.log +---------+----------------------+--------+---------------------------------+----------------+ | STATUS | TIMESTAMP | NAME | SQLTEXT | CONNECTION_ID | +---------+----------------------+--------+---------------------------------+----------------+ | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 7 | | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 8 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 9 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 10 | +---------+----------------------+--------+---------------------------------+----------------+
显示特定的正则表达式文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --pattern=".* = ..." --regexp /SERVER/data/audit.log +---------+----------------------+--------+---------------------------------+----------------+ | STATUS | TIMESTAMP | NAME | SQLTEXT | CONNECTION_ID | +---------+----------------------+--------+---------------------------------+----------------+ | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 7 | | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 8 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 9 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 10 | +---------+----------------------+--------+---------------------------------+----------------+
显示特定查询类型审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --query-type=show,SET /SERVER/data/audit.log +---------+----------------------+--------+-------------------------------------------------+----------------+ | STATUS | TIMESTAMP | NAME | SQLTEXT | CONNECTION_ID | +---------+----------------------+--------+-------------------------------------------------+----------------+ | 0 | 2012-09-27T13:33:39 | Query | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci' | 7 | | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 7 | | 0 | 2012-09-27T13:33:39 | Query | SHOW VARIABLES LIKE 'READ_ONLY' | 7 | | 0 | 2012-09-27T13:33:39 | Query | SHOW VARIABLES LIKE 'datadir' | 7 | | 0 | 2012-09-27T13:33:39 | Query | SHOW VARIABLES LIKE 'basedir' | 7 | | 0 | 2012-09-27T13:33:39 | Query | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci' | 8 | | 0 | 2012-09-27T13:33:39 | Query | SET @@session.autocommit = OFF | 8 | | 0 | 2012-09-27T13:33:39 | Query | SHOW VARIABLES LIKE 'READ_ONLY' | 8 | | 0 | 2012-09-27T13:33:39 | Query | SHOW VARIABLES LIKE 'basedir' | 8 | | 0 | 2012-09-28T11:26:50 | Query | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci' | 9 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 9 | | 0 | 2012-09-28T11:26:50 | Query | SHOW VARIABLES LIKE 'READ_ONLY' | 9 | | 0 | 2012-09-28T11:26:50 | Query | SET NAMES 'latin1' COLLATE 'latin1_swedish_ci' | 10 | | 0 | 2012-09-28T11:26:50 | Query | SET @@session.autocommit = OFF | 10 | | 0 | 2012-09-28T11:26:50 | Query | SHOW VARIABLES LIKE 'READ_ONLY' | 10 | | 0 | 2012-09-28T11:26:50 | Query | SET @@GLOBAL.audit_log_flush = ON | 10 | | 0 | 2012-09-28T11:26:50 | Query | SHOW VARIABLES LIKE 'audit_log_policy' | 10 | | 0 | 2012-09-28T11:26:50 | Query | SHOW VARIABLES LIKE 'audit_log_rotate_on_size' | 10 | | 0 | 2012-10-10T15:56:10 | Query | show databases | 11 | | 1046 | 2012-10-10T15:57:26 | Query | show tables test | 11 | | 1046 | 2012-10-10T15:57:36 | Query | show tables test | 11 | | 0 | 2012-10-10T15:57:51 | Query | show tables in test | 11 | +---------+----------------------+--------+-------------------------------------------------+----------------+
显示特定事件类型的审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --event-type="Ping,Connect" /SERVER/data/audit.log +---------+----------+----------------------+----------------+------------+---------+------------+------------+ | STATUS | NAME | TIMESTAMP | CONNECTION_ID | HOST | USER | PRIV_USER | IP | +---------+----------+----------------------+----------------+------------+---------+------------+------------+ | 0 | Connect | 2012-09-27T13:33:39 | 7 | localhost | root | root | 127.0.0.1 | | 0 | Ping | 2012-09-27T13:33:39 | 7 | None | None | None | None | | 0 | Ping | 2012-09-27T13:33:39 | 7 | None | None | None | None | | 0 | Ping | 2012-09-27T13:33:39 | 7 | None | None | None | None | | 0 | Ping | 2012-09-27T13:33:39 | 7 | None | None | None | None | | 0 | Connect | 2012-09-27T13:33:39 | 8 | localhost | root | root | 127.0.0.1 | | 0 | Ping | 2012-09-27T13:33:39 | 8 | None | None | None | None | | 0 | Ping | 2012-09-27T13:33:39 | 8 | None | None | None | None | | 0 | Ping | 2012-09-27T13:33:47 | 7 | None | None | None | None | | 0 | Connect | 2012-09-28T11:26:50 | 9 | localhost | root | tester | 127.0.0.1 | | 0 | Ping | 2012-09-28T11:26:50 | 9 | None | None | None | None | | 0 | Ping | 2012-09-28T11:26:50 | 9 | None | None | None | None | | 0 | Connect | 2012-09-28T11:26:50 | 10 | localhost | root | root | 127.0.0.1 | | 0 | Ping | 2012-09-28T11:26:50 | 10 | None | None | None | None | | 0 | Ping | 2012-09-28T11:26:50 | 10 | None | None | None | None | | 0 | Ping | 2012-09-28T11:26:50 | 10 | None | None | None | None | | 0 | Ping | 2012-09-28T11:26:50 | 10 | None | None | None | None | | 0 | Ping | 2012-09-28T11:26:50 | 10 | None | None | None | None | | 0 | Connect | 2012-10-10T15:55:55 | 11 | localhost | tester | root | 127.0.0.1 | | 0 | Connect | 2012-10-10T17:35:42 | 12 | localhost | tester | root | 127.0.0.1 | +---------+----------+----------------------+----------------+------------+---------+------------+------------+
显示特定状态的审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --status=1100-1199,1046 /SERVER/data/audit.log +---------+----------------------+--------+---------------------------------------------------------------------+----------------+ | STATUS | TIMESTAMP | NAME | SQLTEXT | CONNECTION_ID | +---------+----------------------+--------+---------------------------------------------------------------------+----------------+ | 1046 | 2012-10-10T15:57:26 | Query | show tables test | 11 | | 1046 | 2012-10-10T15:57:36 | Query | show tables test | 11 | | 1146 | 2012-10-10T17:44:55 | Query | select * from teste.employees where salary > 500 and salary < 1000 | 12 | | 1046 | 2012-10-10T17:47:17 | Query | select * from test_encoding where value = '<>"&' | 12 | +---------+----------------------+--------+---------------------------------------------------------------------+----------------+
所有成功的状态--status=0，所有失败的状态--status=1-9999文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
多个搜索条件文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
shell> mysqlauditgrep --users=root --start-date=0 --end-date=2012-10-10 --event-type=Query \
--query-type=SET --status=0 --pattern="%audit_log%" /SERVER/data/audit.log
+---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
| STATUS | SERVER_ID | NAME | TIMESTAMP | CONNECTION_ID | USER | PRIV_USER | SQLTEXT |
+---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
| 0 | 1 | Query | 2012-09-28T11:26:50 | 10 | root | root | SET @@GLOBAL.audit_log_flush = ON |
+---------+------------+--------+----------------------+----------------+-------+------------+------------------------------------+
权限
用户需要有读取磁盘上的审计日志权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditgrep/
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
评论