Mysqlmariadbnone_MySQL之MariaDB启用审计插件配置详解

为了让我们运维朋友不背一些不要的黑锅&＃xff0c;今天我们就一起来看一篇关于MySQL之MariaDB启用审计插件配置详解&＃xff0c;希望此文章能够帮助到各位朋友。

对于MySQL Percona MariaDB三家都有自己的审计插件&＃xff0c;但是呢&＃xff0c;MySQL的审计插件是只有企业版才有的&＃xff0c;同时也有很多第三方的的MySQL的审计插件&＃xff0c;而Percona和MariaDB都是GPL的审计插件

先来看看MariaDB的审计插件使用&＃xff0c;启用插件和调整参数

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 24

Server version: 10.0.25-MariaDB-wsrep MariaDB Server, wsrep_25.13.raf7f02e

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.

MariaDB [(none)]> show variables like &＃39;%audit%&＃39;;

Empty set (0.00 sec)

MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME &＃39;server_audit&＃39;;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_file_rotate_size&＃61;1024*1024*1024;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_events&＃61;&＃39;query,table&＃39;;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_excl_users&＃61;&＃39;101023161&＃39;;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_file_rotate_now&＃61;on;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global server_audit_logging&＃61;&＃39;ON&＃39;;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like &＃39;server_audit%&＃39;;

------------------------------- -----------------------

| Variable_name                 | Value                 |

------------------------------- -----------------------

| server_audit_events           | QUERY,TABLE           |

| server_audit_excl_users       | 101023161             |

| server_audit_file_path        | server_audit.log      |

| server_audit_file_rotate_now  | OFF                   |

| server_audit_file_rotate_size | 1073741824            |

| server_audit_file_rotations   | 9                     |

| server_audit_incl_users       |                       |

| server_audit_loc_info         |                       |

| server_audit_logging          | ON                    |

| server_audit_mode             | 0                     |

| server_audit_output_type      | file                  |

| server_audit_query_log_limit  | 1024                  |

| server_audit_syslog_facility  | LOG_USER              |

| server_audit_syslog_ident     | mysql-server_auditing |

| server_audit_syslog_info      |                       |

| server_audit_syslog_priority  | LOG_INFO              |

------------------------------- -----------------------

16 rows in set (0.00 sec)

MariaDB [(none)]>\q

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "show variables like &＃39;%audit%&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "INSTALL PLUGIN server_audit SONAME &＃39;server_audit&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "set global server_audit_file_rotate_size&＃61;1024*1024*1024;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "set global server_audit_events&＃61;&＃39;query,table&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "set global server_audit_excl_users&＃61;&＃39;101023161&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "set global server_audit_file_rotate_now&＃61;ON;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "set global server_audit_logging&＃61;&＃39;ON&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# mysql -uroot -pZjUxZGI2ZjcwMmEy -e "show variables like &＃39;server_audit%&＃39;;"

[root&＃64;DS-VM-Node127 /data/mariadb]# tail -f /data/mariadb/server_audit.log

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,434,QUERY,mysql,&＃39;SELECT GROUP_CONCAT(CONCAT(user, "&＃64;", host) SEPARATOR ", ") AS user FROM mysql.user WHERE user !&＃61; "root" AND grant_priv &＃61; "Y"&＃39;,0

##时间&＃xff0c;节点&＃xff0c;用户&＃xff0c;来源&＃xff0c;事件类型&＃xff0c;库&＃xff0c;语句&＃xff0c;影响行数

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,435,READ,mysql,user,

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,435,QUERY,mysql,&＃39;SELECT GROUP_CONCAT(CONCAT(user, "&＃64;", host) SEPARATOR ", ") AS user FROM mysql.user WHERE user !&＃61; "root" AND super_priv &＃61; "Y"&＃39;,0

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,436,READ,mysql,user,

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,436,QUERY,mysql,&＃39;SELECT GROUP_CONCAT(CONCAT(user, "&＃64;", host) SEPARATOR ", ") AS user FROM mysql.user WHERE user !&＃61; "root" AND create_user_priv &＃61; "Y"&＃39;,0

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,437,QUERY,mysql,&＃39;SET SESSION wsrep_causal_reads&＃61;0&＃39;,0

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,438,QUERY,mysql,&＃39;SHOW GLOBAL VARIABLES&＃39;,0

20160825 19:05:03,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,439,QUERY,mysql,&＃39;SHOW /*!50000 GLOBAL */ STATUS&＃39;,0

20160825 19:05:04,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,440,QUERY,mysql,&＃39;SHOW GLOBAL VARIABLES&＃39;,0

20160825 19:05:04,DS-VM-Node127.cluster.com,fpmmm,127.0.0.1,36,441,QUERY,mysql,&＃39;SHOW /*!50000 GLOBAL */ STATUS&＃39;,0

^C

[root&＃64;DS-VM-Node127 /data/mariadb]#

再来试试第三方审计插件在MariaDB上用法&＃xff0c;这里需要注意你的MariaDB版本&＃xff0c;和MCAFEE MYSQL AUDIT的版本&＃xff0c;版本不支持是不行的&＃xff0c;我开始就遇到了这样的错。

[root&＃64;iZ62rkcotqvZ ~]# https://dl.bintray.com/mcafee/mysql-audit-plugin/:audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip

[root&＃64;iZ62rx97xj1Z ~]# unzip audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip

Archive:  audit-plugin-mariadb-10.0-1.1.0-625-linux-x86_64.zip

creating: audit-plugin-mariadb-10.0-1.1.0-625/

creating: audit-plugin-mariadb-10.0-1.1.0-625/lib/

inflating: audit-plugin-mariadb-10.0-1.1.0-625/lib/libaudit_plugin.so

inflating: audit-plugin-mariadb-10.0-1.1.0-625/COPYING

inflating: audit-plugin-mariadb-10.0-1.1.0-625/THIRDPARTY.txt

inflating: audit-plugin-mariadb-10.0-1.1.0-625/README.txt

[root&＃64;iZ62rx97xj1Z ~]# cd audit-plugin-mariadb-10.0-1.1.0-625/lib/

[root&＃64;iZ62rx97xj1Z ~/audit-plugin-mariadb-10.0-1.1.0-625/lib]# mysql -uroot -p -e "show global variables like &＃39;plugin_dir&＃39;;"

--------------- --------------------------------

| Variable_name | Value |

--------------- --------------------------------

| plugin_dir | /usr/local/mariadb/lib/plugin/ |

--------------- --------------------------------

[root&＃64;iZ62rx97xj1Z ~/audit-plugin-mariadb-10.0-1.1.0-625/lib]# cp libaudit_plugin.so /usr/local/mariadb/lib/plugin/

[root&＃64;iZ62rx97xj1Z ~/audit-plugin-mariadb-10.0-1.1.0-625/lib]# mysql -uroot -p -e "INSTALL PLUGIN AUDIT SONAME &＃39;libaudit_plugin.so&＃39;;"