MySQL管理工具MySQL Utilities — mysqlauditadmin(14)

默北
MySQLMySQL管理工具MySQL Utilities — mysqlauditadmin(14)已关闭评论12,8941字数 5381阅读17分56秒阅读模式

MySQL 企业版本有日志审计的功能，社区版本是没有这功能的，并且启用了审计日志的插件。下面介绍的两款工具是针对审计日志的。

mysqlauditadmin 数据库管理员对审计日志的维护，监控审计日志的增长和控制日志轮滚。轮滚就是对当前日志进行归档整理。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/

可用的动作有：

  • copy复制审计日志。将 --audit-log-name 选项指定的审计日志复制到 --copy-to 选项指定的目录下。  --remote-login 选项可以用来从远程服务器复制日志。注意：当前用户需要有目的目录的写入权限。Windows平台不支持该选项的。
  • policy改变审计日志策略。使用 --value 选项指定策略值，可选值有：
    注意：该指令需要--server选项。从mysql5.6.20和 5.7.5 版本，该值仅从audit_log_policy变量读取。现在的策略结合下面两个变量audit_log_connection_policy和audit_log_statement_policy。这种改变从MySQL Utilities 1.5.2版本支持。
    • ALL: 所有事件日志
    • NONE: 没有日志
    • LOGINS:只有登录事件日志
    • QUERIES: 只有查询事件日志
    • DEFAULT: 设置默认日志策略
  • rotate_on_size设置审计日志文件自动轮转的大小阀值。 (audit_log_rotate_on_size 变量值). 该值通过--value选项设置，值范围在(0, 4294967295)之间。该指令也需要--server选项。注意：如果该变量值不是4096的倍数，则截断到最近的倍数值。
  • rotate按需执行审计日志轮转，只需要指定--server 选项。注意：如果审计日志大小小于4096该命令无效的。也就是 audit_log_rotate_on_size 变量的最小值。

选项：

Usage: mysqlauditadmin --server=user:pass@host:port --show-options 

mysqlauditadmin - audit log maintenance utility

Options:
  --version             show program's version number and exit
  --help                display this help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --audit-log-name=LOG_NAME
                        full path and file name for the audit log file. Used
                        for stats and copy options.
  --show-options        display the audit log system variables.
  --remote-login=RLOGIN
                        user name and host to be used for remote login for
                        copying log files. Format: <user>:<host_or_ip>
                        Password will be prompted.
  --file-stats          display the audit log file statistics.
  --copy-to=COPY_LOCATION
                        the location to copy the audit log file specified. The
                        path must be locally accessible for the current user.
  --value=VALUE         value used to set variables based on the command
                        specified. See --help for list per command.
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug

Available Commands:

  copy             - copy the audit log to a locally accessible path
  policy           - set the audit log policy
                     Values = ALL, NONE, LOGINS, QUERIES, DEFAULT
  rotate           - perform audit log rotation
  rotate_on_size   - set the rotate log size limit for auto rotation
                     Values = 0, 4294967295

实例：

显示审计日志的系统变量文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --show-options --server=root@localhost:3310

  #
  # Audit Log Variables and Options
  #
  +---------------------------+---------------+
  | Variable_name             | Value         |
  +---------------------------+---------------+
  | audit_log_buffer_size     | 1048576       |
  | audit_log_file            | audit.log     |
  | audit_log_flush           | OFF           |
  | audit_log_policy          | ALL           |
  | audit_log_rotate_on_size  | 0             |
  | audit_log_strategy        | ASYNCHRONOUS  |
  +---------------------------+---------------+

手动轮转审计日志文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --server=root@localhost:3310 rotate

#
# Executing ROTATE command.
#

显示审计日志统计信息文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --file-stats --audit-log-name=../SERVER/data/audit.log

  +------------------------------+--------+---------------------------+---------------------------+
  | File                         | Size   | Created                   | Last Modified             |
  +------------------------------+--------+---------------------------+---------------------------+
  | audit.log                    | 3258   | Wed Sep 26 11:07:43 2012  | Wed Sep 26 11:07:43 2012  |
  | audit.log.13486539046497235  | 47317  | Wed Sep 26 11:05:04 2012  | Wed Sep 26 11:05:04 2012  |
  +------------------------------+--------+---------------------------+---------------------------+

改变审计日志策略，仅记录查询事件，并显示文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --show-options --server=root@localhost:3310 policy \
       --value=QUERIES

  #
  # Showing options before command.
  #
  # Audit Log Variables and Options
  #
  +---------------------------+---------------+
  | Variable_name             | Value         |
  +---------------------------+---------------+
  | audit_log_buffer_size     | 1048576       |
  | audit_log_file            | audit.log     |
  | audit_log_flush           | OFF           |
  | audit_log_policy          | ALL           |
  | audit_log_rotate_on_size  | 0             |
  | audit_log_strategy        | ASYNCHRONOUS  |
  +---------------------------+---------------+
  
  #
  # Executing POLICY command.
  #
  
  #
  # Showing options after command.
  #
  # Audit Log Variables and Options
  #
  +---------------------------+---------------+
  | Variable_name             | Value         |
  +---------------------------+---------------+
  | audit_log_buffer_size     | 1048576       |
  | audit_log_file            | audit.log     |
  | audit_log_flush           | OFF           |
  | audit_log_policy          | QUERIES       |
  | audit_log_rotate_on_size  | 0             |
  | audit_log_strategy        | ASYNCHRONOUS  |
  +---------------------------+---------------+

改变审计日志轮转大小，并显示执行rotate_on_size指令的前后系统变量文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --show-options --server=root@localhost:3310 rotate_on_size \
       --value=32535

  #
  # Showing options before command.
  #
  # Audit Log Variables and Options
  #
  +---------------------------+---------------+
  | Variable_name             | Value         |
  +---------------------------+---------------+
  | audit_log_buffer_size     | 1048576       |
  | audit_log_file            | audit.log     |
  | audit_log_flush           | OFF           |
  | audit_log_policy          | ALL           |
  | audit_log_rotate_on_size  | 0             |
  | audit_log_strategy        | ASYNCHRONOUS  |
  +---------------------------+---------------+
  
  #
  # Executing ROTATE_ON_SIZE command.
  #
  
  #
  # Showing options after command.
  #
  # Audit Log Variables and Options
  #
  +---------------------------+---------------+
  | Variable_name             | Value         |
  +---------------------------+---------------+
  | audit_log_buffer_size     | 1048576       |
  | audit_log_file            | audit.log     |
  | audit_log_flush           | OFF           |
  | audit_log_policy          | ALL           |
  | audit_log_rotate_on_size  | 28672         |
  | audit_log_strategy        | ASYNCHRONOUS  |
  +---------------------------+---------------+

将审计日志复制到另一个地方去文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --audit-log-name=../SERVER/data/audit.log.13486539046497235 \
       copy --copy-to=/BACKUP/Audit_Logs

将远程审计日志拷贝到当前位置文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 

shell> mysqlauditadmin --audit-log-name=audit.log.13486539046497235 \
       copy --remote-login=user:host --copy-to=.

权限：

执行命令的用户必须要有从磁盘读取日志的权限和写入到目的目录的权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
默北
  • 本文由 发表于 11/02/2015 01:00:03
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/mysql-utilities-mysqlauditadmin/
  • MySQL Utilities
  • mysqlauditadmin