阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL使用UDF调用shell脚本

200次阅读
没有评论

共计 9028 个字符，预计需要花费 23 分钟才能阅读完成。

前言

在最近的项目中，由于需要使用 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/
  • 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(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

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL使用UDF调用shell脚本 MySQL脚本 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL UDF调用shell脚本 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计9028字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
Zabbix自定义监控MySQL性能状态
Oracle启动脚本及开机自启动设置
Oracle里常见的执行计划
CockroachDB介绍及安装使用
Redis集群redis主从自动切换Sentinel（哨兵模式）
Ubuntu 16.04下实现MySQL主从复制
MySQL 报 ERROR 1030 Got error 28 from 解决
在 Azure 虚拟机上快速搭建 MongoDB 集群
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3593244
评论数
4
文章搜索
热门文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

实验环境：CentOS Linux release 7.3.1611 (Core) 内核版本：Linux ve...
介绍ansible的Ad-hoc与commands模块

介绍ansible的Ad-hoc与commands模块

Ad-Hoc 是指 ansible 下临时执行的一条命令，并且不需要保存的命令，对于复杂的命令后面会说 pla...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
CentOS+OpenVZ+Vtonf实现Linux虚拟化

CentOS+OpenVZ+Vtonf实现Linux虚拟化

导读 OpenVZ 是基于 Linux 内核和作业系统的操作系统级虚拟化技术。OpenVZ 允许物理服务器运行...
重做日志中如何分析日志容量及切换频率？

重做日志中如何分析日志容量及切换频率？

导读 在 Oracle 数据库的世界里，Redo Log 是一个非常核心的存在，通过 Redo 日志，Orac...
使用 BTFS 在线观看种子视频

使用 BTFS 在线观看种子视频

Bittorrent 已经存在了很长时间，它可以从互联网上共享和下载数据。市场上有大量的 GUI 和 CLI ...
大神教你:ownCloud的双因素身份验证教程

大神教你:ownCloud的双因素身份验证教程

导读 ownCloud 是一种系统，用于存储您自己或您公司的数据，而不是在别人的计算机（也称为“云”）上，而在...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中