良许Linux教程网 干货合集 MySQL通过UDF调用shell脚本

MySQL通过UDF调用shell脚本

前言

在最近的项目中,由于需要使用MySQL的UDF(user defined function),这个特性从未使用过,而且个人觉得这个特性以后应该会经常使用,所以写下博文,记录和分享这个特性的用法。

MySQL通过UDF调用shell脚本

UDF是mysql的一个拓展接口,UDF(Userdefined function)可翻译为用户自定义函数,这个是用来拓展Mysql的技术手段。

1. 下载

https://github.com/mysqludf/lib_mysqludf_sys

2. 安装

#安装mysql的两个依赖包
[root@dtadmin apollo ~]# rpm -ivh mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
[root@dtadmin apollo ~]# rpm -ivh mysql-community-devel-5.7.17-1.el7.x86_64.rpm
#安装gcc gcc-c++编译器
[root@dtadmin apollo ~]# yum install gcc gcc-c++

3. 解压

[root@dtadmin apollo ~]# unzip lib_mysqludf_sys-master.zip

4. 执行命令

#进入目录
[root@dtadmin apollo ~]# cd lib_mysqludf_sys-master
# 在目录lib_mysqludf_sys-master执行:
[root@dtadmin lib_mysqludf_sys-master ~]# gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so

5. 把编译后的 lib_mysqludf_sys.so 拷到 /usr/lib64/mysql/plugin/

[root@dtadmin lib_mysqludf_sys-master ~]# cp lib_mysqludf_sys.so /usr/lib64/mysql/plugin/
  • 1

6. 在mysql中执行

[root@dtadmin lib_mysqludf_sys-master ~]# mysql -u root -p --default-character-set=utf8

执行以下脚本:

Drop FUNCTION IF EXISTS lib_mysqludf_sys_info;
Drop FUNCTION IF EXISTS sys_get;
Drop FUNCTION IF EXISTS sys_set;
Drop FUNCTION IF EXISTS sys_exec;
Drop FUNCTION IF EXISTS sys_eval;

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';

7.在/var/lib/mysql-files目录下新建脚本

[root@dtadmin lib_mysqludf_sys-master ~]# cd /var/lib/mysql-files
[root@dtadmin mysql-files ~]# load_data_infile.sh

脚本内容如下:

#!/bin/bash
HOSTNAME="192.168.56.101" #mysql hostname
PORT="3306" #mysql port
USERNAME="root" # the username for DBNAME
PASSWORD="Love88me=-.," # the password for USERNAME and DBNAME

DBNAME="subs" #DBNAME


cmd_load_data_infile="LOAD DATA INFILE '$1' REPLACE INTO TABLE $2 FIELDS TERMINATED BY '\t\t\t';"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -D${DBNAME}  -e "${cmd_load_data_infile}"

8.改变文件的执行权限

[root@dtadmin mysql-files ~]# chmod u+x load_data_infile.sh

9.示例,定时备份数据表数据到另一张表

9.1.创建存储过程

use DBNAME;

drop table if exists sbux_nc_request_hist;      
drop table if exists sbux_nc_edm_message_hist;  
drop table if exists sbux_nc_mail_message_hist;  
drop table if exists sbux_nc_push_message_hist;  
drop table if exists sbux_nc_sms_message_hist;  
drop table if exists sbux_nc_wechat_message_hist;
drop table if exists sbux_nc_sys_log_hist;      
drop table if exists sbux_nc_sms_log_hist;      
drop table if exists sbux_nc_push_log_hist;          
drop table if exists sbux_nc_wechat_log_hist;    
drop table if exists sbux_nc_edm_log_hist;      
drop table if exists sbux_nc_mail_log_hist;      


create table sbux_nc_request_hist        like sbux_nc_request;
create table sbux_nc_edm_message_hist    like sbux_nc_edm_message;
create table sbux_nc_mail_message_hist   like sbux_nc_mail_message;
create table sbux_nc_push_message_hist   like sbux_nc_push_message;
create table sbux_nc_sms_message_hist    like sbux_nc_sms_message;
create table sbux_nc_wechat_message_hist like sbux_nc_wechat_message;
create table sbux_nc_sys_log_hist        like sbux_nc_sys_log;
create table sbux_nc_edm_log_hist        like sbux_nc_edm_log;
create table sbux_nc_mail_log_hist       like sbux_nc_mail_log;
create table sbux_nc_push_log_hist       like sbux_nc_push_log;
create table sbux_nc_sms_log_hist        like sbux_nc_sms_log;
create table sbux_nc_wechat_log_hist     like sbux_nc_wechat_log;


drop procedure if exists sbux_nc_data_migrate_p;

delimiter // ;

CREATE PROCEDURE sbux_nc_data_migrate_p()
BEGIN
   DECLARE v_same_date_last_period date;   # 上月同一天
   DECLARE v_max_hist_date date;           # 历史表中的最大时间
   DECLARE v_current_date date;            # 当前日期
   DECLARE v_table_name varchar(50);       # 变量,用来存储游标遍历中的要备份表的表名
   DECLARE v_engine varchar(50);           # 表的存储引擎
   DECLARE v_create_options varchar(50);   # 创建表的选项(是否是分区表)
   DECLARE FLAG int default 0;             # 游标的标记
   DECLARE v_auto_increment int default 0;     # 设置下个自增长列

   DECLARE v_file_path varchar(200) default '/var/lib/mysql-files/';               # MySQL中的存储datafile的路径
   DECLARE v_separator varchar(200) default " fields terminated  by '\\t\\t\\t'";  # 导出文件的分隔符

   DECLARE v_file varchar(500);            # 导出文件的全名(路径+文件名)
   DECLARE v_result int default 0;         # 执行文件的结果标记
   DECLARE v_flag int DEFAULT 0;           # 执行shell脚本的标记





   # 声明游标:查询出要做数据备份的表的列表
   DECLARE cur_table_list CURSOR FOR
   SELECT table_name, engine, create_options
   FROM information_schema.TABLES
   WHERE table_schema='DBMAME'
   AND table_name in
   (
   'sbux_nc_request' ,
   'sbux_nc_edm_message',
   'sbux_nc_mail_message',
   'sbux_nc_push_message',
   'sbux_nc_sms_message',
   'sbux_nc_wechat_message',
   'sbux_nc_sys_log',
   'sbux_nc_edm_log',
   'sbux_nc_mail_log',
   'sbux_nc_push_log',
   'sbux_nc_sms_log',
   'sbux_nc_wechat_log'
   );

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=1;      #设置游标退出标记

   SELECT date_sub(current_date(),interval 1 month) INTO v_same_date_last_period; # 查询上月同一天的日期
   SELECT current_date() INTO v_current_date;                                     # 查询当前日期

   # 清空目录下面的文件
   select sys_exec('rm -rf /var/lib/mysql-files/sbux_nc_*') into v_result;

   # 遍历游标
   OPEN cur_table_list;
   FETCH cur_table_list INTO v_table_name,v_engine,v_create_options;
   WHILE(flag1) DO

       # 存储原表中所有数据到文件
       SET @stmt = CONCAT('select * into outfile ',"'",v_file_path, v_table_name,'_hist.dat'"'",' ', v_separator, ' from ',v_table_name,';');
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;

       # 设置要备份表的时间段
       SET @condition =
                   case v_table_name
                   when 'sbux_nc_request'          then concat('where request_time between ',"'",v_same_date_last_period,"'"' and ',"'", v_current_date,"'")
                   when 'sbux_nc_edm_message'      then concat('where send_time between ',"'",v_same_date_last_period,"'"' and ',"'", v_current_date,"'")
                   when 'sbux_nc_mail_message'     then concat('where send_time between ',"'",v_same_date_last_period, "'",' and ',"'", v_current_date,"'")
                   when 'sbux_nc_push_message'     then concat('where send_time between ',"'",v_same_date_last_period, "'",' and ',"'", v_current_date,"'")
                   when 'sbux_nc_sms_message'      then concat('where send_time between ',"'",v_same_date_last_period, "'",' and ',"'", v_current_date,"'")
                   when 'sbux_nc_wechat_message'   then concat('where send_time between ',"'",v_same_date_last_period, "'",' and ',"'", v_current_date,"'")
                   when 'sbux_nc_sys_log'          then concat('where log_time between ',"'",v_same_date_last_period,"'"' and ',"'", v_current_date,"'")
                   when 'sbux_nc_edm_log'          then concat('where log_time between ',UNIX_TIMESTAMP(v_same_date_last_period), ' and ', UNIX_TIMESTAMP(v_current_date))
                   when 'sbux_nc_mail_log'         then concat('where log_time between ',UNIX_TIMESTAMP(v_same_date_last_period), ' and ', UNIX_TIMESTAMP(v_current_date))
                   when 'sbux_nc_push_log'         then concat('where log_time between ',UNIX_TIMESTAMP(v_same_date_last_period), ' and ', UNIX_TIMESTAMP(v_current_date))
                   when 'sbux_nc_sms_log'          then concat('where log_time between ',UNIX_TIMESTAMP(v_same_date_last_period), ' and ', UNIX_TIMESTAMP(v_current_date))
                   when 'sbux_nc_wechat_log'       then concat('where log_time between ',UNIX_TIMESTAMP(v_same_date_last_period), ' and ', UNIX_TIMESTAMP(v_current_date))
                   else NULL
               end;

       # 保存最近一个月的数据到文件
       SET @stmt = CONCAT('select * into outfile ',"'", v_file_path, v_table_name,'.dat '"'"' ', v_separator, ' from ',v_table_name, ' ', @condition,';');
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;

       #获取表的最大增增长列并
       select auto_increment into  v_auto_increment
           from information_schema.TABLES
           where table_schema='starbucks'
           and table_name=v_table_name;

       # 删除原来数据
       SET @stmt = CONCAT('truncate table ',v_table_name,';');
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;

       #设置下一个增量值
       SET @stmt = CONCAT('alter table ',v_table_name,' auto_increment=',v_auto_increment,';');
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;

       # 加载数据到历史数据表中
       # SET @stmt = CONCAT('LOAD DATA INFILE ',"'", v_file_path, v_table_name,'_hist.dat', "'", ' INTO TABLE ', v_table_name,'_hist',' ', v_separator);
       # PREPARE stmt FROM @stmt;
       # EXECUTE stmt;
       # DEALLOCATE PREPARE stmt;    

       # 设置之前导出的表的数据文件(路径+文件名)
       SET v_file = concat(v_file_path,v_table_name,'_hist.dat');  
       # 执行mysql UFF函数调用shell脚本把数据导入到历史表中
       set @v_load_str = concat('sh /var/lib/mysql-files/load_data_infile.sh',' ',v_file,' ',CONCAT(v_table_name,'_hist'));
       select sys_exec(@v_load_str) into v_flag;

       # 把最近一个月的数据保存到原表中
       # SET @stmt = CONCAT('LOAD DATA INFILE ',"'", v_file_path, v_table_name,'.dat ',"'",' INTO TABLE ',v_table_name,' ', v_separator);
       # PREPARE stmt FROM @stmt;
       # EXECUTE stmt;
       # DEALLOCATE PREPARE stmt;

       # 设置之前导出的表的近一个月数据文件(路径+文件名)
       SET v_file = concat(v_file_path,v_table_name,'.dat');
       # 执行mysql UFF函数调用shell脚本把数据导入到原表中
       set @v_load_str = concat('sh /var/lib/mysql-files/load_data_infile.sh',' ',v_file,' ',v_table_name);
       select sys_exec(@v_load_str) into v_flag;




       # 收集(分析)表的数据
       SET @stmt = CONCAT('analyze table ',v_table_name,';');
       PREPARE stmt FROM @stmt;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;

   FETCH cur_table_list INTO v_table_name,v_engine,v_create_options;

   END WHILE;
   CLOSE cur_table_list;
END;
//
delimiter ; //

9.2.新建event,用来定时执行

/*======================================================================================== */
-- Name:  sbux_nc_auto_migrate_evt
-- Purpose: 定时任务,自动备份表数据
-- Interval: Monthly
-- AT: 每个月1号凌晨2点
-- Invoker: sbux_nc_data_migrate_p
/*======================================================================================== */
set GLOBAL event_scheduler=1; # MySQL启动event scheduler
drop event if exists sbux_nc_auto_migrate_evt; # 在创建event scheduler前删除已存在的同名的event scheduler.
delimiter // ;
create event sbux_nc_auto_migrate_evt
ON  SCHEDULE  EVERY  1  DAY STARTS date_add(date(curdate() + 1),interval 2 hour) #MONTH  STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 2 HOUR) -- 每个月的一号凌晨2点
ON completion preserve
ENABLE
DO
call sbux_nc_data_migrate_p();
//
delimiter ; //

以上就是良许教程网为各位朋友分享的Linu系统相关内容。想要了解更多Linux相关知识记得关注公众号“良许Linux”,或扫描下方二维码进行关注,更多干货等着你 !

img
本文由 良许Linux教程网 发布,可自由转载、引用,但需署名作者且注明文章出处。如转载至微信公众号,请在文末添加作者公众号二维码。
良许

作者: 良许

良许,世界500强企业Linux开发工程师,公众号【良许Linux】的作者,全网拥有超30W粉丝。个人标签:创业者,CSDN学院讲师,副业达人,流量玩家,摄影爱好者。
上一篇
下一篇

发表评论

邮箱地址不会被公开。 必填项已用*标注

联系我们

联系我们

公众号:良许Linux

在线咨询: QQ交谈

邮箱: yychuyu@163.com

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部