Syslog 使用广泛，对日志进行独立存储。而且有很多的工具可对 syslog 数据进行聚合、监控、查询和分析。Syslog 数据可以当成是一个中央资料库。
你也可以在 MariaDB 中将日志写到 Syslog 中，步骤很简单：
首先下载 MariaDB 的 audit 插件
然后将 server_audit.so 文件拷贝到你的 MySQL/MariaDB 下的 lib/plugin 目录，并通过如下命令激活该插件：
view source
print?
1 |
MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME'server_audit.so'; |
系统默认的日志是写到文件，我们需要改为 Syslog：
view source
print?
1 |
MariaDB [test]> SETGLOBAL server_audit_output_type=SYSLOG; |
2 |
MariaDB [test]> SETGLOBAL server_audit_events='CONNECT,QUERY'; |
3 |
MariaDB [test]> SETGLOBAL server_audit_logging=on; |
相关的配置项如下：
view source
print?
01 |
MariaDB [test]> show variables like '%audit%'; |
02 |
+-------------------------------------+-----------------------+ |
03 |
| Variable_name | Value | |
04 |
+-------------------------------------+-----------------------+ |
05 |
| server_audit_events |CONNECT,QUERY | |
06 |
| server_audit_excl_users | | | |
07 |
| server_audit_file_path | server_audit.log | |
08 |
| server_audit_file_rotate_now |OFF | |
09 |
| server_audit_file_rotate_size | 1000000 | |
10 |
| server_audit_file_rotations | 9 | |
11 |
| server_audit_incl_users | | |
12 |
| server_audit_logging |ON | |
13 |
| server_audit_mode | 0 | |
14 |
| server_audit_output_type | syslog | |
15 |
| server_audit_syslog_facility | LOG_USER | |
16 |
| server_audit_syslog_ident | mysql-server_auditing | |
17 |
| server_audit_syslog_info | | |
18 |
| server_audit_syslog_priority | LOG_INFO | |
19 |
+-------------------------------------+-----------------------+ |
20 |
14 rowsin set (0.00 sec) |
运行状态信息：
view source
print?
01 |
MariaDB [test]> show status like'%audit%'; |
02 |
+------------------------------ +--------------+ |
03 |
| Variable_name | Value | |
04 |
+----------------------------- -+--------------+ |
05 |
| server_audit_active | ON | |
06 |
| server_audit_current_log | [SYSLOG] | |
07 |
| server_audit_last_error | | |
08 |
| server_audit_writes_failed | 0 | |
09 |
+-------------------------------+--------------+ |
10 |
4 rowsin set (0.00 sec) |
确保 rsyslog 在运行：
view source
print?
1 |
[root@centos1 log]# service rsyslog restart |
2 |
Shutting down system logger: [ OK ] |
3 |
Starting system logger: [ OK ] |
现在所有连接和查询 MariaDB 的动作都会写到 syslog 日志里：
view source
print?
1 |
[root@centos1 log]# tail -f /var/log/messages |
2 |
Sep 21 00:07:07 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,10,QUERY,,'set global server_audit_logging=on',0 |
3 |
Sep 21 00:07:11 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,11,QUERY,,'show status like \'%audit%\'',0 |
4 |
Sep 21 00:07:21 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,12,QUERY,,'show variables like \'%audit%\'',0 |
5 |
Sep 21 00:10:06 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,13,QUERY,,'set global server_audit_events=\'CONNECT,QUERY\'',0 |
6 |
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,14,QUERY,,'SELECT DATABASE()',0 |
7 |
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,16,QUERY,test,'show databases',0 |
8 |
Sep 21 00:13:09 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,17,QUERY,test,'show tables',0 |
9 |
Sep 21 00:13:14 centos1 mysql-server_auditing: centos1.localdomain,root,localhost,1,18,QUERY,test,'show tables',0 |
日志写到 /var/log/messages 文件是由 /etc/rsyslog.conf 进行配置的：
view source
print?
1 |
*.info;mail.none;authpriv.none;cron.none /var/log/messages |
如果你想要将日志写到不同的文件，可以：
view source
print?
1 |
if $programname == 'mysql-server_auditing' then /var/log/mariadbaudit1 |
使用 MariaDB Audit Plugin 对安全和系统管理来说是一个好的选择。
英文原文：http://serge.frezefond.com/2013/09/mariadb-audit-plugin-logging-to-syslog/
