MySQL User-defined function (UDF) for HTTP REST
Note: This is another fork of y-ken/mysql-udf-http
This fixes/changes the following:
- Plugin directory changed from
${exec_prefix}/lib/mysql/plugin/
to${exec_prefix}/lib/plugin/
. This matches the pluign directory provided by the official mysql:5.5 docker image. - Updates to README.md for official mysql:5.5 docker image
Note: It is a fork repository. Original Website is below.
https://code.google.com/p/mysql-udf-http
HTTP Method | CRUD Action | Description |
---|---|---|
POST | CREATE | Create a new resource |
GET | READ | Read a resource |
PUT | UPDATE | Update a resource |
DELETE | DELETE | Delete a resource |
Support MySQL version 5.1.x and 5.5.x on Linux systems.
make sure these depandencies are installed.
- mysql server
- mysql_config command
- development tools
For Debian, like this.
apt-get update
apt-get install make gcc default-libmysqlclient-dev pkg-config wget unzip -y
This sample is written for "/usr/local/mysql/" is your MySQL install path. (as per the mysql:5.5 docker image)
ulimit -SHn 65535
wget https://curl.haxx.se/download/curl-7.21.1.tar.gz
tar zxvf curl-7.21.1.tar.gz
cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../
echo '/usr/local/mysql/lib/' > /etc/ld.so.conf.d/mysql.conf
/sbin/ldconfig
wget https://github.com/tehmantra/mysql-udf-http/archive/master.zip
unzip mysql-udf-http-master.zip
cd mysql-udf-http-master/
chmod +x ./configure
./configure --prefix=/usr/local/mysql --with-mysql=/usr/local/mysql/bin/mysql_config
make && make install
cd ../
/usr/local/webserver/mysql/bin/mysql --socket=/tmp/mysql.sock --password=$MYSQL_ROOT_PASSWORD
mysql>
create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';
mysql>
SELECT http_get('<url>');
SELECT http_post('<url>', '<data>');
SELECT http_put('<url>', '<data>');
SELECT http_delete('<url>');
mysql>
/* Baidu Mobile Search */
SELECT http_get('https://m.baidu.com/s?word=xoyo&pn=0');
SELECT http_post('https://m.baidu.com/s','word=xoyo&pn=0');
/* Sina Weibo Open Platform */
SELECT http_get('https://api.t.sina.com.cn/statuses/user_timeline/103500.json?count=1&source=1561596835') AS data;
SELECT http_post('https://your_sina_uid:[email protected]/statuses/update.xml?source=1561596835', 'status=Thins is sina weibo test information');
/* Tokyo Tyrant */
SELECT http_put('https://192.168.8.34:1978/key', 'This is value');
SELECT http_get('https://192.168.8.34:1978/key');
SELECT http_delete('https://192.168.8.34:1978/key');
Use mysql-udf-http and lib_mysqludf_json synchronizes the update to Tokyo Tyrant with MySQL Trigger.
(1). Download and install lib_mysqludf_json:
On 32-Bit Linux:
wget https://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-i386.tar.gz
tar zxvf lib_mysqludf_json-i386.tar.gz
cd lib_mysqludf_json-i386/
# if your MySQL install path is not '/usr/local/webserver/mysql/', please modify the path.
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../
On 64-Bit Linux:
wget https://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-x86_64.tar.gz
tar zxvf lib_mysqludf_json-x86_64.tar.gz
cd lib_mysqludf_json-x86_64/
# if your MySQL install path is not '/usr/local/webserver/mysql/', please modify the path.
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../
Enter to the MySQL console:
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock
mysql>
create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so';
create function json_array returns string soname 'lib_mysqludf_json.so';
create function json_members returns string soname 'lib_mysqludf_json.so';
create function json_object returns string soname 'lib_mysqludf_json.so';
create function json_values returns string soname 'lib_mysqludf_json.so';
How to use lib_mysqludf_json, please visit: https://www.mysqludf.org/lib_mysqludf_json/
(2). Create table:
mysql>
SET NAMES UTF8;
USE test;
CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`addtime` int(10) NOT NULL,
`title` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
(3). Create trigger for table:
mysql>
/* TRIGGER for INSERT */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_insert;
CREATE TRIGGER mytable_insert
AFTER INSERT ON mytable
FOR EACH ROW BEGIN
SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = NEW.id LIMIT 1);
SET @tt_resu = (SELECT http_put(CONCAT('https://192.168.8.34:1978/', NEW.id), @tt_json));
END |
DELIMITER ;
/* TRIGGER for UPDATE */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_update;
CREATE TRIGGER mytable_update
AFTER UPDATE ON mytable
FOR EACH ROW BEGIN
SET @tt_json = (SELECT json_object(id,addtime,title) FROM mytable WHERE id = OLD.id LIMIT 1);
SET @tt_resu = (SELECT http_put(CONCAT('https://192.168.8.34:1978/', OLD.id), @tt_json));
END |
DELIMITER ;
/* TRIGGER for DELETE */
DELIMITER |
DROP TRIGGER IF EXISTS mytable_delete;
CREATE TRIGGER mytable_delete
AFTER DELETE ON mytable
FOR EACH ROW BEGIN
SET @tt_resu = (SELECT http_delete(CONCAT('https://192.168.8.34:1978/', OLD.id)));
END |
DELIMITER ;
(4). Select data from MySQL table and Tokyo Tyrant:
mysql>
SELECT id,addtime,title,http_get(CONCAT('https://192.168.8.34:1978/',id)) AS tt FROM mytable ORDER BY id DESC LIMIT 0,5;
mysql>
drop function http_get;
drop function http_post;
drop function http_put;
drop function http_delete;
Please check the Members
at the left side of menu.
https://code.google.com/p/mysql-udf-http/
This is free software, and you are welcome to modify and redistribute it under the New BSD License.