how to use mysql_How to use NoSQL in MySQL

How to use NoSQL in MySQL?

Louis Hust

1   Introduction

MySQL5.6 has already supported memcached, so we can say MySQL is a mixture of RDBMS and NoSQL. But there is few materials about how to install memcached in MySQL and how to use it, and that is the reason i write this article. In this article, i will show you how to install memcached plugin with MySQL source code and how to use it with InnoDB engine. After that, you will have a general comprehension about it. So let’s begin.

2   Architecture

At first, i have to mention the architecture in Figure 1which is used all the time when you search the keywords ‘Nosql’ && ‘MySQL’.

Figure Figure 1: Memcached with InnoDB Architecture

3   Get MySQL source code

MySQL with memcached was just in MySQL Labs before, now it’s contained in trunk. I just get the trunk tree with bazzar as below:

bzr branch  http://bazaar.launchpad.net/~mysql/mysql-server/trunk/ trunk

This will take much time, so you can read left and get ready for install.

4   Build server

As you know, MySQL use cmake to compile the code, which is a cross-platform build system. Many people are used to use cmake at the source code directory. I’d like to make a new directory for cmake. Have a glance at my source directory.

Shell>pwd

/home/loushuai/src/mysql-server/trunk

Shell>ls

bld              cscope.out           libmysqld         sql-bench

bld_memcached    cscope.po.out        libservices       sql-common

BUILD            dbug                 man               storage

BUILD-CMAKE      Docs                 mysql-test        strings

client           Doxyfile-perfschema  mysys             support-files

cmake            extra                packaging         tags

CMakeLists.txt   files                plugin            tests

cmd-line-utils   include              README            unittest

config.h.cmake   INSTALL-SOURCE       regex             VERSION

configure.cmake  INSTALL-WIN-SOURCE   scripts           vio

COPYING          libevent             source_downloads  win

cscope.in.out    libmysql             sql               zlib

As you see, i have two directories: bld && bld_memcached, bld was used for general purpose and bld_memcached is used for memcached. Just follow my code:

Shell>pwd

/home/loushuai/src/mysql-server/trunk

Shell>mkdir bld_memcached

Shell>cd bld_memcached

Shell>cmake .. -DWITH_DEBUG=1 -DCMAKE_INSTALL_PREFIX=./mysql  \

-DMYSQL_DATADIR=./mysql/data  -DWITH_INNODB_MEMCACHED=ON -DENABLE_DOWNLOADS=1

Shell>make

Shell>make install

Now let’s check if the memcached plugin is built.

Shell>pwd

/home/loushuai/src/mysql-server/trunk/bld_memcached/mysql/lib/plugin

Shell>ls

adt_null.so          daemon_example.ini    mypluglib.so          semisync_master.so

auth.so              innodb_engine.so      qa_auth_client.so     semisync_slave.so

auth_socket.so       libdaemon_example.so  qa_auth_interface.so

auth_test_plugin.so  libmemcached.so       qa_auth_server.so

As seen above, what we need is two dynamic link library files:emphlibmemcached.so && emphinnodb_engine.so.

5   Start server

Before we start sever, we should following things:

Initialize database Cause I install MySQL from source, so we need initialize the database manually.

Shell>pwd

/home/loushuai/src/mysql-server/trunk/bld_memcached/mysql/scripts

Shell>./mysql_install_db --datadir=../data --basedir=../ --user=loushuai

Cause I install MySQL with user ‘loushuai’, so i need to add -user, the same to you.

Config my.cnf

[mysqld]

gdb

datadir=../data

socket=../data/mysql.sock

autocommit=1

#back_log=200

log-bin=mysql-bin

pid-file=../data/a.pid

#wait_timeout=31536000

#interactive_timeout=31536000

server-id=1

log_slave_updates

binlog-format=STATEMENT

general-log

Start MySQL server

Shell>pwd

/home/loushuai/src/mysql-server/trunk/bld_memcached/mysql/bin

Shell>./mysqld --defaults-file=my.cnf

Create configure table for memcached Next we create the configure table and a demo table by running innodb_memcached_config.sql:

Shell>pwd

/home/loushuai/src/mysql-server/trunk/bld_memcached/mysql/bin

Shell>./mysql -uroot -h127.0.0.1 

Check tables are created:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| innodb_memcache    |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

mysql> use innodb_memcache

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+---------------------------+

| Tables_in_innodb_memcache |

+---------------------------+

| cache_policies            |

| config_options            |

| containers                |

+---------------------------+

3 rows in set (0.00 sec)

This configure script installs 3 tables needed by the InnoDB Memcached. These tables are created in a dedicated database ïnnodb_memcache”. We will go over these three tables in a bit more detail:   1) “containers” – This table is the most important table for “Memcached – InnoDB mapping”. It describes the table used to store the memcached values. Currently, we can only map memcached to one table at a time. So essentially, there will be only one row in the table. In the future, we would consider making this configuration more flexible and dynamic, or user can map memcached operations to multiple tables.   The mapping is done through specifying corresponding column values in the table:   “db_schema” and “db_table” columns describe the database and table name for storing the memcached value. “key_columns” describes the column (single column) name for the column being used as “key” for the memcached operation “value_columns” describes the columns (can be multiple) used as “values” for the memcached operation. User can specify multiple columns by separating them by comma (such as “col1, col2″ etc.) ünique_idx_name_on_key” is the name of the index on the “key” column. It must be a unique index. It can be primary or secondary. Above 5 column values (table name, key column, value column and index) must be supplied. Otherwise, the setup will fail.   Following are optional values, however, to fully comply with memcached protocol, you will need these column values supplied too.   “flags” describes the columns used as “flag” for memcached. It also used as “column specifier” for some operations (such as incr, prepend) if memcached “value” is mapped to multiple columns. So the operation would be done on specified column. For example, if you have mapped value to 3 columns, and only want the ïncrement” operation performed on one of these columns, you can use flags to specify which column will be used for these operations. “cas_column” and ëxp_column” are used specifically to store the “cas” and ëxp” value of memcached.   2) Table “cache_policies” specifies whether we’ll use InnoDB as the data store of memcached (innodb_only) or use memcached’s “default engine” as the backstore (cache-only) or both (caching). In the last case, only if the default engine operation fails, the operation will be forwarded to InnoDB (for example, we cannot find a key in the memory, then it will search InnoDB).   3) Table “config_options”, currently, we only support one config option through this table. It is the ßeparator” used to separate values of a long string into smaller values for multiple columns values. For example, if you defined “col1, col2″ as value columns. And you define ” as separate, you could issue following command in memcached to insert values into col1 and col2 respectively:

Install memcached plugin At last we need install the memcached daemon plugin:

mysql> install plugin daemon_memcached soname "libmemcached.so";

Query OK, 0 rows affected (0.01 sec)

6   How to use?

We can set and get value just telnet the memcached port:

Shell>telnet 127.0.0.1 11211

Trying 127.0.0.1...

Connected to 127.0.0.1.

Escape character is '^]'.

set all 0 0 9

123456789

STORED

get all

VALUE all 0 9

123456789

END

get AA

VALUE AA 8 12

HELLO, HELLO

END

quit

Connection closed by foreign host.

So we can check the value we just set into table with SQL:

mysql> select * from test.demo_test;

+-----+--------------+------+------+------+

| c1  | c2           | c3   | c4   | c5   |

+-----+--------------+------+------+------+

| AA  | HELLO, HELLO |    8 |    0 |    0 |

| all | 123456789    |    0 |    1 |    0 |

+-----+--------------+------+------+------+

2 rows in set (0.00 sec)

As you see, we set the value ‘123456789’ with the key ‘all’ which are stored in test.demo_test. And the mapping relation is define in the table innodb_memcache.containers.

References

File translated from TEX by TTH, version 4.03.

On 21 Nov 2012, 15:29.