很多企业网中都部署了日志中心,集中收集、分析和处理各种设备产生的日志。
但一些应用系统的日志是保存在数据库表中,如果需要提供syslog格式日志,除了进行代码改造外,一种快速的解决方法是使用SQL触发器,在写入日志记录的INSERT语句后面自动记录syslog日志。本例以MySQL和postgreSQL为例介绍实际项目中的一个案例,分别将MySQL和postgreSQL(瀚高)应用的日志表转成syslog格式记录或者发送,涉及到的技术包括MySQL/postgreSQL触发器、MySQL自定义函数/postgreSQL pl/sh扩展、syslog日志操作和生成rpm包等环节。
注:本文中管理和操作数据库主要使用DBeaver工具(比navicat等商用软件更好用),如何使用DBeaver访问各种开源或者信创数据库参考本博客《信创环境下数据库的远程管理与DBeaver工具》。
一、MySQL 触发器中执行shell命令
1. MySQL触发器
MySQL从5.0.2版本就开始支持触发器的功能, 触发器(trigger)与表事件相关的特殊的存储过程,触发器由事件来触发,例如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器四要素:
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,OLD是只读的,而NEW则可以在触发器中使用 SET 赋值,来引用触发器中发生变化的记录内容,格式如:NEW.columnName (columnName为相应数据表某一列名)
mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END$$
mysql> delimiter ;
2. 编译及部署libmysqludf
对于Mysql,SQL语句包括触发器中执行外部程序需要使用libmysqludf用户自定义函数。libmysqludf网站是:https://github.com/mysqludf/lib_mysqludf_sys
(一)编译
网站上的lib_mysqludf_sys.so文件是32位的,一般现在Linux都是64为系统,需要重新编译(编译的系统上要安装mariadb-devel或者mysql-devel包, yum install mariadb-devel),完整编译命令如下。
gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so
(二)复制so文件到mysql插件目录
编译生成的lib_mysqludf_sys.so复制到mysql/mariadb的plugin目录,一般是/usr/lib64/mysql/plugin/,但中科方德NFS4中,是/usr/lib64/mariadb/plugin。
可以用#rpm -ql mariadb-server检查一下mysql库的安装位置。
(三)初始化lib_mysqludf
执行初始化SQL脚本(源文件目录中的lib_mysqludf_sys.sql文件),内容大致是:
CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';
导入上面的SQL初始化脚本
mysql -u root -p<source lib_mysqludf_sys.sql
如果正确的安装了udf插件,source命令会正常执行,在msyql中func表中看到新增的函数。
![](https://i-blog.csdnimg.cn/blog_migrate/31c6726bbf1955fcf5a4775af3e8a548.png)
(四)测试
lib_mysqludf提供了几个函数:
- sys_exec: 调用系统命令,执行外部程序,返回成功失败代码
- sys_eval:调用系统命令,执行外部程序,返回stdout
- sys_get: 返回系统环境变量的值
- sys_set: 设置系统环境变量
在mysql命令行下可以测试函数用法,如下面的例子返回的是当前用户ID:
use mysql
select sys_eval('id');
如不能执行,检查一下lib_mysqludf_sys.so权限,chmod 777 lib_mysqludf_sys.so试下!
3. MySQL触发器中使用UDF函数调用系统Shell命令完成syslog日志记录
syslog日志用法这里不详细介绍,可以用logger命令测试syslog日志生成,例如:
logger -p local3.notice "hello1"
logger -n 56.18.2.2 -p local3.notice "hello1" #发送到远程syslog日志服务器
生成的日志在/var/log/messages中,tail -n 100 /var/log/messages查看最近的记录。
将SQL日志转换成syslog日志的思路是显而易见的,就是建一个触发器,插入日志表的时候,调用logger系统命令生成一个相应的日志记录。
例如,下面的oc_user表有三个地段,uid,diaplayname,password,触发器基本代码如下:
create trigger log after insert on oc_user
for each row
begin
set @info=strcat('logger local4.info ',NEW.uid,NEW.displayname,NEW.password);
set @ret=sys_exec(info);
end
下图是用dbeaver工具(连接要使用advance视图,simple视图不会显示存储过程、触发器等特性)和navicat工具分别测试的截图
![](https://i-blog.csdnimg.cn/blog_migrate/a3b917c44aa20df1d866ec0a94578896.png)
插入一条记录:insert into oc_users(uid,displayname,password ) values('testid', 'mcwolf' , '5465jdgft8948')
![](https://i-blog.csdnimg.cn/blog_migrate/e79f43352b37ef37dabc4ab71bef5941.png)
这时,在/var/log/messages中可以看到生成的日志。
4. 实际例子
在表【task】建一个insert触发器log,所做的就是将需要发送的字段进行拼接(@message),然后和logger命令参数进行拼接(@logger),这里是将日志发送到日志服务器56.8.2.2,准备完成后调用logger命令。
CREATE DEFINER=`root`@`%` TRIGGER log
AFTER INSERT
ON task FOR EACH ROW
begin
set @sep=',';
set @PRI=' local4.info ';
set @logtable=' [task] ';
set @logserver=' -n 192.168.2.27 ' ;
/*fields:taskID,taskName,serviceType,userName,deptName,useraccount,computerID,commitTime,status*/
set @message=concat_ws(@sep,@logtable,NEW.taskID,NEW.taskName,NEW.serviceType,NEW.userName,NEW.deptName,NEW.useraccount,NEW.computerID,NEW.committime,NEW.status);
set @logger=concat('logger -p ',@PRI,@logserver,@message);
set @ret=sys_exec(@logger);
end
关于MySQL代码的几个技术说明:
(1)变量使用参考《MySQL存储过程与各种变量》
(2)构造信息串,使用字符连接函数,但是MySQL的concat函数如果遇到有一个参数是 NULL,整个会返回NULL,所以要么使用IFNULL函数如果是NULL将其置为''空字符串。要么用CONCAT_WS函数代替,指定有分隔符的字符串连接,在执行的时候,不会因为NULL值而返回NULL。
SELECT CONCAT('1,',IFNULL(NULL,''),'2');
执行结果:1,2
CONCAT_WS(分隔符,参数1,参数2,...参数n)
(3)处理时间格式也是比较常见的操作,例如syslog时间格式,《获取当前日期及格式化》
set @logtime=date_format(now(),'%b %e %T');
【注】这里logger -n 192.168.2.27是日志服务器地址,替代方法是还是先进行本地处理,在/etc/rsyslog.con中配置local4.info转发到日志服务器192.158.2.27,更合理一些。
二、PostgreSQL触发器执行shell命令
1. Postgre触发器机制
Postgre只有函数,它自身就可以实现Oracle的存储过程的功能。
Postgre触发器和MySQL方式有一些不同,一般是创建一个(返回trigger类型)处理函数,然后创建触发器调用这个函数。
CREATE FUNCTION dosomthing()
returns trigger as
begin
......
end
CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE dosomthin();
2. PL/sh-PostgreSQL中执行Shell命令
postgresql中使用的缺省是pqsql语言,但编写存储过程等的时候也可以使用其它的语言扩展插件,如pl/java、pl/sql、pl/perl、pl/python等,pl/sh扩展是可以用shell脚本来编写postgresql函数,网站是:https://github.com/petere/plsh。
CREATE FUNCTION concat(text, text) RETURNS text AS '
#!/bin/sh
echo "$1$2"
' LANGUAGE plsh;
第一行必须是#!/ 开始,可以用别的shell!
注意所用sh环境变量PATH的影响,如果不能执行命令,可以用绝对路径命令试下,这也是很难调试的一个问题!
由于没有在shell中的postgresql存取接口,如果要在shell脚本中操作数据库,需要使用psql命令
CREATE FUNCTION query (x int) RETURNS text
LANGUAGE plsh
AS $$
#!/bin/sh
psql -At -c "select b from pbar where a = $1"
$$;
安装比较简单,解压缩后
make
make install
如果提示找不到pg_config,可以在make中带参数(例如,瀚高数据库的pg_config路径就不是标准路径,也可以执行/opt/HighGoDB-5.0.0-lite/bin/pg_config命令查看瀚高数据库的配置)
make PG_CONFIG=/opt/HighGoDB-5.0.0-lite/bin/pg_config
make install PG_CONFIG=/opt/HighGoDB-5.0.0-lite/bin/pg_config
在数据库工具DBeaver中,如果需要编写PL/SH函数之前,需要先加入这个语言扩展,如下图:
![](https://i-blog.csdnimg.cn/blog_migrate/a16f469e9c30b4cc352b0d60338deccc.png)
3. 实例
和上面MySQL例子类似,在postgreSQL的表【devicelog】建一个insert触发器log,所做的就是将需要发送的字段进行拼接(message),然后和logger命令参数进行拼接(logger),这里是将日志发送到日志服务器56.8.2.2,准备完成后调用logger命令。
![](https://i-blog.csdnimg.cn/blog_migrate/6a5941faa8c20470620cf8fcb639a1ca.png)
调用过程是上面的1->2->3,编写过程是3->2->1
核心是logger函数,调用shell命令logger,使用PL/sh语言。
CREATE OR REPLACE FUNCTION logger(logger character varying)
RETURNS integer
LANGUAGE plsh
AS $function$
#!/bin/sh
/usr/bin/logger $1
$function$
;
实际上的触发器函数是buildsyslog_device(RETUNRS trigger),使用PL/pgsql语句,主要是完成命令的拼接,然后调用logger函数执行shell命令
CREATE OR REPLACE FUNCTION htt.buildsyslog_devicelog()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
declare
sep varchar:=',';
PRI varchar:=' local4.info ';
logtable varchar=' [devicelog] ';
logserver varchar:=' -n 56.18.2.2 ' ;
message varchar;
logger varchar;
begin
message:=concat_ws(sep,logtable,NEW.departname,NEW.Respuser,NEW.operUser,NEW.pcname,NEW.mac,NEW.ip,NEW.devname,NEW.devinfo,NEW.illtime);
logger:=concat(' -p ',PRI,logserver,message);
return htt.logger(logger);
END;
$function$
;
创建触发器,简单调用触发器函数即可
CREATE TRIGGER logtable AFTER INSERT ON devicelog
FOR EACH ROW EXECUTE PROCEDURE buildsyslog_devicelog();
几个技术细节:
1. PostgreSQL字符串连接和MySQL比较类似,参考《PostgreSQL字符串的两种连接方式》 。
2. 由于不是专门开发PostgreSQL的工具,在DBeaver中调试函数/存储过程不太方便,主要使用raise函数在输出窗口打印字符串(以及抛出错误),类似于Java中的System.out.println(),Oracle中的dbms_output.put_line()。
RAISE level 'format' [, expression [, ...]];
#例如:
raise notice 'My name is %, I am a %.', param1, param2;
raise后面的level是级别,一共有debug/log/info/notice/warning/exception这些级别。
三、其它
1. 直接生成syslog报文发送到日志服务器
上面是logger程序生成syslog格式日志,保存在本地日志文件。还可以logger -n 发送到远程syslog日志服务器。
此外,在上文中还有一个udp的高性能方案,如果发现日志过于频繁影响性能,可以考虑直接生成UDP报文到日志服务器。
logger -p user.info "test from loongson"
logger -n 192.168.10.58 -p user.info "test from loongson"
echo “test from loongson” > /dev/udp/192.168.10.58/514
区别是logger会处理时间和PRI的一些底层问题,直接udp协议构造需要自己解决很多细节格式问题。
2. 制作RPM安装包
在某些特殊场景下,需要使用安装包才能安装这个udf扩展,这就需要使用打包工具,下面以rpm包为例。
简单的rpm包参考《制作一个简单的rpm包:hello world》过程就不详细介绍,因为中标麒麟v7和中科方德v10的mysql lib路径不相同,就制作了两个SPEC文件,对应生成两个rpm包。
目录结构记录如下:
rpmbuild/
├── BUILD
│ └── mysqludf-1.0
│ ├── debugfiles.list
│ ├── debuglinks.list
│ ├── debugsources.list
│ ├── elfbins.list
│ └── lib_mysqludf_sys.so
├── BUILDROOT
├── RPMS
│ └── x86_64
│ ├── mysqludf-neokylin.x86_64.rpm
│ ├── mysqludf-debuginfo-1.0-1.el7.centos.x86_64.rpm
│ └── mysqludf-nfs.x86_64.rpm
├── SOURCES
│ ├── mysqludf-1.0
│ │ └── lib_mysqludf_sys.so
│ └── mysqludf-1.0.tar.gz
├── SPECS
│ ├── mysqludf-neokylin.spec
│ └── mysqludf-nfs4.spec
└── SRPMS
└── mysqludf-1.0-beta.src.rpm
SPEC文件如下(两个SPEC区别就是lib64路径的不同):
[root@mailserver SPECS]# cat mysqludf-neokylin.spec
NAME:mysqludf
Version:1.0
Release:1%{?dist}
License:GPL
Summary:mysql-udf plugin
Source0:mysqludf-1.0.tar.gz
%description
MysqlUDF plugin
%prep
%setup -q
%build
%install
mkdir -p $RPM_BUILD_ROOT/usr/lib64/mysql/plugin
cp $RPM_BUILD_DIR/mysqludf-1.0/lib_mysqludf_sys.so $RPM_BUILD_ROOT/usr/lib64/mysql/plugin
%clean
rm -r $RPM_BUILD_ROOT
%files
%defattr(-,root,root)
%doc
/usr/lib64/mysql/plugin/lib_mysqludf_sys.so
在rpmbuild目录下执行:rpmbuild -ba SPECS/mysqludf-neokylin.spec,生成rpm包。
参考文章及资源下载:
- mysql触发器内执行shell脚本,mysql触发器内执行shell脚本,shell脚本用curl访问php网页_qq845454748的专栏-CSDN博客
- 《Ubuntu18.04之mysqludf函数sys_exec/sys_eval函数执行不成功的解决方案 》 文中提到需要提权,没有遇到这种情况
- postgresql执行shell脚本,使用PL/sh扩展遇见 ,该扩展参考https://github.com/petere/plsh,在PostgreSQL函数中使用参数运行系统命令 - IT屋-程序员软件开发技术分享社区文章提到了postgresql执行系统命令的方式
- https://github.com/sqlmapproject/sqlmap/issues/2965 中讨论windows下的mysqludf_sys ,64位版本的下载链接 https://github.com/sqlmapproject/sqlmap/files/1789515/lib_mysqludf_sys_64.zip
- x86_64和龙芯下编译的lib_mysqludf_sys.so(海光CPU),中标麒麟/中科方德等X86_64下的rpm包等见本博客资源https://download.csdn.net/download/McwoLF/13758875。
- 龙芯MIPS64下的PL/SH扩展二进制文件见本博客资源:PL/sh扩展库(MIPS64)-其它文档类资源-CSDN下载