内容简介: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脚本》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 在新的,干净的PowerShell实例中调用PowerShell脚本(在另一个脚本中)
- Nodejs 调用 R 脚本 / Nodejs Call R Script
- Go调用企业微信API发送zabbix告警信息脚本
- 新姿势!Redis中调用Lua脚本以实现原子性操作
- 使用Python和Java调用Shell脚本时的死锁陷阱
- sysbench测试Oracle时写一个lua脚本调用sqlload快速造数据
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。