建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL通過UDF調用shell腳本
前言
在最近的項目中，由于需要使用MySQL的UDF（user defined function），這個特性從未使用過，而且個人覺得這個特性以后應該會經(jīng)常使用，所以寫下博文，記錄和分享這個特性的用法。
UDF是mysql的一個拓展接口，UDF（Userdefined function）可翻譯為用戶自定義函數(shù)，這個是用來拓展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. 執(zhí)行命令
#進入目錄
[root@dtadmin apollo ~]# cd lib_mysqludf_sys-master
# 在目錄lib_mysqludf_sys-master執(zhí)行：
[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中執(zhí)行
[root@dtadmin lib_mysqludf_sys-master ~]# mysql -u root -p --default-character-set=utf8
執(zhí)行以下腳本：
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.改變文件的執(zhí)行權限
[root@dtadmin mysql-files ~]# chmod u+x load_data_infile.sh
9.示例，定時備份數(shù)據(jù)表數(shù)據(jù)到另一張表
9.1.創(chuàng)建存儲過程
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); # 創(chuàng)建表的選項(是否是分區(qū)表)
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; # 執(zhí)行文件的結果標記
DECLARE v_flag int DEFAULT 0; # 執(zhí)行shell腳本的標記
# 聲明游標：查詢出要做數(shù)據(jù)備份的表的列表
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
# 存儲原表中所有數(shù)據(jù)到文件
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;
# 保存最近一個月的數(shù)據(jù)到文件
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;
# 刪除原來數(shù)據(jù)
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;
# 加載數(shù)據(jù)到歷史數(shù)據(jù)表中
# 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;
# 設置之前導出的表的數(shù)據(jù)文件（路徑+文件名）
SET v_file = concat(v_file_path,v_table_name,'_hist.dat');
# 執(zhí)行mysql UFF函數(shù)調用shell腳本把數(shù)據(jù)導入到歷史表中
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;
# 把最近一個月的數(shù)據(jù)保存到原表中
# 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;
# 設置之前導出的表的近一個月數(shù)據(jù)文件（路徑+文件名）
SET v_file = concat(v_file_path,v_table_name,'.dat');
# 執(zhí)行mysql UFF函數(shù)調用shell腳本把數(shù)據(jù)導入到原表中
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;
# 收集（分析）表的數(shù)據(jù)
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,用來定時執(zhí)行
/*======================================================================================== */
-- Name: sbux_nc_auto_migrate_evt
-- Purpose: 定時任務，自動備份表數(shù)據(jù)
-- 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; # 在創(chuàng)建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 ; //
本文標題：MySQL通過UDF調用shell腳本
分享URL：http://www.dlmjj.cn/article/cociiei.html
