Ideonella-sakaiensis/lib_mysqludf_redis

Provides Mysql UDF commands to synchronize data from Mysql to Redis.

lib_mysqludf_redis

Provides UDF commands to access Redis from Mysql/MariaDB.

English | 繁體中文 | 日本語

Table of Contents

• Synopsis
• System Requirements
• Compilation and Install Plugin Library
  • Compilation Argumnets
  • Compilation Variable
• Install and Uninstall UDF
• Usage
• TODO
• Copyright and License
• See Also

Synopsis

figure01 digraph G {

rankdir = "LR";
size ="8,8";
edge [
    fontname = "Consolas"
    fontsize = 10
];
MariaDB [
    label = "MariaDB\n(presistence)"
    shape = "box"
];
Redis [
    label = "Redis\n(cached)"
    shape = "box"
];
edge [
    fontcolor = "blue"
    color = "blue"
];
writer;
writer:e -> MariaDB [
    label="INSERT\nUPDATE\nDELETE"
];
MariaDB -> Redis [
    label = "SET"
];
edge [
    fontcolor = "red"
    color = "red"
];
reader;
reader:e -> MariaDB [
    label="SELECT"
];
MariaDB -> Redis [
    label = "GET"
];
edge [
    fontcolor = "default"
    color = "default"
    dir ="none"
    arrowhead="none"
    arrowtail="none"
    penwidth = 0.5
    style="dashed"
];
node [
    fontname = "Consolas"
    fontsize = 10
    penwidth = 0.5
    color    = "gray"
    shape = "record"
    style = "rounded"
];
MariaDB_Data [
  label = <<TABLE border="0" cellspacing="0" cellborder="1"><TR><TD COLSPAN="2">MariaDB data</TD></TR><TR><TD>item</TD><TD>qty</TD></TR><TR><TD>shoes</TD><TD>35</TD></TR><TR><TD>books</TD><TD>158</TD></TR></TABLE>>
];
{
  rank = "same";
  MariaDB:n -> MariaDB_Data:s;
}
Redis_Data [
  label = <<TABLE border="0" cellspacing="0" cellborder="1"><TR><TD COLSPAN="2">Redis data</TD></TR><TR><TD>item</TD><TD>qty</TD></TR><TR><TD>shoes</TD><TD>35</TD></TR><TR><TD>books</TD><TD>158</TD></TR></TABLE>>
];
{
  rank = "same";
  Redis:n -> Redis_Data:s;
}

} figure01

Back to TOC

System Requirements

• Architectures: Linux 64-bit(x64)
• Compilers: GCC 4.1.2+
• MariaDB 5.5+
• Redis 1.2+
• Dependencies:
  • MariaDB development library 5.5+
  • hiredis 0.13.3+
  • cJSON 1.6+

Back to TOC

Compilation and Install Plugin Library

Installing compilation tools

CentOS

# install tools
$ yum install -y make wget gcc git

# install mariadb development tool
$ yum install -y mariadb-devel

Debian

# install tools
$ apt-get install -y make wget gcc git

# install mariadb development tool
$ apt-get install -y libmariadb-dev

FreeBSD

# install tools
$ pkg install -y gmake wget gcc git-lite

To compile the plugin library just simply type make and make install. -or- gmake and gmake install on FreeBSD.

$ make

# install plugin library to plugin directory
$ make install

# install UDF to Mysql/MariaDB server
$ make installdb

NOTE: If the Mysql/MariaDB is an earlier version or installed from source code, the default include path might be invalid; use make INCLUDE_PATH=`mysql_config --variable=pkgincludedir` to assign INCLUDE_PATH variable for compilation.

Compilation Argumnets

• install

  Install the plugin library to Mysql plugin directory.

• installdb

  Install UDFs to Mysql/MariaDB server.

• uninstalldb

  Uninstall UDFs from Mysql/MariaDB server.

• clean

  Clear the compiled files and resources.

• distclean

  Like the clean and also remove the dependencies resources.

Compilation Variable

The compilation variable can be use in make:

• HIREDIS_MODULE_VER

  The hiredis version to be compiled. If it is not specified, the default value is 0.13.3.

• CJSON_MODULE_VER

  The cJSON version to be compiled. If it is not specified, the default value is 1.6.0

• INCLUDE_PATH

  The MariaDB or Mysql C header path. If it is not specified, the default will be the Mysql variable pkgincludedir. The value can be displayed by executing the following command:

  $ echo `mysql_config --variable=pkgincludedir`/server

• PLUGIN_PATH

  The MariaDB or Mysql plugin path. The value can be obtained via running the sql statement SHOW VARIABLES LIKE '%plugin_dir%'; in MariaDB/Mysql server. If it is not specified, the default will be Mysql variable plugindir. The value can be displayed by executing the following command:

  $ mysql_config --plugindir

example:

# specify the plugin install path with /opt/mysql/plugin
$ make PLUGIN_PATH=/opt/mysql/plugin
$ make install

Back to TOC

Install and Uninstall UDF

To install UDF from make:

$ make installdb

or executing the following sql statement on Mysql/MariaDB server:

mysql>  CREATE FUNCTION `redis` RETURNS STRING SONAME 'lib_mysqludf_redis.so';

To uninstall UDF with make uninstalldb -or- executing the following sql statement on Mysql/MariaDB server:

mysql>  DROP FUNCTION IF EXISTS `redis`;

Back to TOC

Usage

redis($connection_string, $command, [$args...])

Call a Redis command by specified $connection_string, $command, and individual arguments.

• $connection_string - represent the Redis server to be connected, the value is a DSN connection string, must be one of following type:
  • redis://:<password>@<host>:<port>/<database>/
  • redis://:<password>@<host>/<database>/
  • redis://@<host>:<port>/<database>/
  • redis://@<host>/<database>/
• $command, $args... - the Redis command and arguments. See also https://redis.io/commands for further details.

The function returns a JSON string indicating success or failure of the operation.

the success output:

{
   "out": "OK"
}

the failure output:

{
   "err": "Connection refused"
}

The following examples illustrate how to use the function contrast with redis-cli utility.

/*
  the following statement likes:

    $ redis-cli -h 127.0.0.1 -n 8 PING
    PONG
*/
mysql>  SELECT `redis`('redis://@127.0.0.1/8/', 'PING')\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/8/', 'PING'): {
        "out":  "PONG"
}
1 row in set (0.00 sec)



/*
  the following statement likes:

    $ redis-cli -h 127.0.0.1 -a foobared -n 8 PING
    PONG
*/
mysql>  SELECT `redis`('redis://:foobared@127.0.0.1/8/', 'PING')\G
*************************** 1. row ***************************
`redis`('redis://:foobared@127.0.0.1/8/', 'PING'): {
        "out":  "PONG"
}
1 row in set (0.00 sec)



/*
    $ redis-cli -h 127.0.0.1 -p 80 -n 8 PING
    Could not connect to Redis at 127.0.0.1:80: Connection refused
*/
mysql>  SELECT `redis`('redis://@127.0.0.1:80/8/', 'PING')\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1:80/8/', 'PING'): {
        "err":  "Connection refused"
}
1 row in set (0.00 sec)



/*
    $ redis-cli -h 127.0.0.1 -n 8 HMSET myhash field1 Hello field2 World
    OK
*/
mysql>  SELECT `redis`('redis://@127.0.0.1/8/', 'HMSET', 'myhash', 'field1', 'Hello', 'field2', 'World')\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/8/', 'HMSET', 'myhash', 'field1', 'Hello', 'field2', 'World'): {
        "out":  "OK"
}
1 row in set (0.00 sec)



/*
    $ redis-cli -h 127.0.0.1 -n 8 HGET myhash field1
    "Hello"
*/
mysql>  SELECT `redis`('redis://@127.0.0.1/8/', 'HGET', 'myhash', 'field1')\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/8/', 'HGET', 'myhash', 'field1'): {
        "out":  "Hello"
}
1 row in set (0.00 sec)



-- redis-cli -h 127.0.0.1 -n 0 SET foo bar
mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'SET', 'foo', 'bar')

-- redis-cli -h 127.0.0.1 -n 0 SCAN 0 MATCH prefix*
mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'SCAN', '0', 'MATCH', 'prefix*')

Back to TOC

TODO

• implement Redis Authentication (2017-12-30)
• add redis DSN string builder function

Back to TOC

Copyright and License

See LICENSE for further details.

Back to TOC

See Also

Back to TOC