MySQL使用UDF调用shell脚本

栏目: 数据库 · 发布时间: 6年前

内容简介：MySQL使用UDF调用shell脚本

前言

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

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

官方介绍请点击
有关MySQL5.7在CentOS7.0上的安装配置请参考 http://www.linuxidc.com/Linux/2017-05/144363.htm

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/

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(flag<>1) 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 ; //

本文永久更新链接地址 ： http://www.linuxidc.com/Linux/2017-06/144947.htm

以上所述就是小编给大家介绍的《MySQL使用UDF调用shell脚本》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对码农网的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

Google's PageRank and Beyond

Amy N. Langville、Carl D. Meyer / Princeton University Press / 2006-7-23 / USD 57.50

Why doesn't your home page appear on the first page of search results, even when you query your own name? How do other web pages always appear at the top? What creates these powerful rankings? And how......一起来看看《Google's PageRank and Beyond》这本书的介绍吧!

码农工具