UseMySQLtostoreNoSQLandSQLdatainthesamedatabaseu_MySQL

May 1, 2014Leave a comment

MySQLis a great relational database, but at some point someone (management) in your company is probably going to say that they need to use NoSQL to store their data. After all, NoSQL is one of the latest buzzwords, so it must be good (correct?). Basically, NoSQL allows you to store data without all of the characteristics of a relational database. A very simple explanation is that you are storing all of a data set with just one primary key, and the primary key is how you also retrieve the data. While NoSQL may be good in some cases, it is hard to beat “old-fashioned” SQL relational databases – especially if that is what you know. But, with MySQL and InnoDB, you can have the best of both worlds.

With MySQL version 5.6(and above), you have the ability to store and retrieve NoSQL data, using NoSQL commands, while keeping the data inside a MySQL InnoDB database. So, you can use NoSQL and SQL at the same time, on the same data, stored in the same database. And the beauty is that it takes just a few minutes to setup. This post will provide you with a quick lesson on how to setup NoSQL on a MySQL InnoDb database.

I would suggest that you read this MySQL web page to get started –Getting Started with InnoDB Memcached Plugin. You should be able to follow this guide and have your NoSQL database up and running in no time at all.

NoSQL on MySQL usesmemcached– a distributed memory object caching system. Currently, the memcached daemon plugin is only supported on Linux, Solaris, and Mac OS X platforms.

There are a few prerequisites. You must have thelibevent 1.4.3(or greater) libraries installed (it is not installed if you used a MySQL installer to install MySQL). Depending upon your operating system, you can useapt-get, yum, or port install. For example, on Ubuntu Linux:

sudo apt-get install libevent-dev

# xcode-select --install

# ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

# brew install libevent

The libraries for memcached and the InnoDB plugin for memcached are put into the correct place by the MySQL installer. For a typical operation, the fileslib/plugin/libmemcached.soandlib/plugin/innodb_engine.soare used.

You may check to make sure you have the libraries:(substitute $MYSQL_HOME for your mysql home directory)

# ls -l $MYSQL_HOME/lib/plugin/libmemcached.so-rwxr-xr-x1 mysqlwheel195664 Mar 14 15:23 lib/plugin/libmemcached.so# ls -l $MYSQL_HOME/lib/plugin/innodb_engine.so-rwxr-xr-x1 mysqlwheel109056 Mar 14 15:23 lib/plugin/innodb_engine.so

To be able to use memcached so that it can interact with InnoDB tables, you will need to run a configuration script to install the tables necessary for use with memcached. This script is namedinnodb_memcached_config.sql, and it should be in your$MYSQL_HOME/sharedirectory.

# cd $MYSQL_HOME# ls -l share/innodb_memcached_config.sql-rwxr-xr-x1 mysqlwheel3963 Mar 14 15:02 share/innodb_memcached_config.sql

To install the script, from the command line run:

# mysql -uroot -p < $MYSQL_HOME/share/innodb_memcached_config.sql

This script will install the three tables (cache_policies, config_options and containers) that it needs for the InnoDB/memcached mapping relationship, along with a sample table nameddemo_test, which is installed in thetestdatabase.

mysql> use innodb_memcacheDatabase changedmysql> show tables;+---------------------------+| Tables_in_innodb_memcache |+---------------------------+| cache_policies|| config_options|| containers|+---------------------------+3 rows in set (0.01 sec)mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| demo_test|+----------------+1 row in set (0.00 sec)

The table that we need to use for the InnoDB mapping is thecontainerstable. Here is theDESCRIBEstatement for thecontainerstable:

mysql> DESCRIBE innodb_memcache.containers;+------------------------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+------------------------+--------------+------+-----+---------+-------+| name | varchar(50)| NO | PRI | NULL| || db_schema| varchar(250) | NO | | NULL| || db_table | varchar(250) | NO | | NULL| || key_columns| varchar(250) | NO | | NULL| || value_columns| varchar(250) | YES| | NULL| || flags| varchar(250) | NO | | 0 | || cas_column | varchar(250) | YES| | NULL| || expire_time_column | varchar(250) | YES| | NULL| || unique_idx_name_on_key | varchar(250) | NO | | NULL| |+------------------------+--------------+------+-----+---------+-------+9 rows in set (0.00 sec)

Here is some information about the container columns:

NOTE: If you make any changes to theinnodb_memcache.containers table, you will need to restart the plugin or restart mysqld.

Theinnodb_memcached_config.sqlscript inserted one line of data for us in theinnodb_memcache.containerstable:

mysql> select * from innodb_memcache.containers;+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+| name | db_schema | db_table| key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+| aaa| test| demo_test | c1| c2| c3| c4 | c5 | PRIMARY|+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+1 row in set (0.00 sec)

You can see the nameaaais mapped to thedb_table(InnoDB table)demo_test. And theinnodb_memcached_config.sqlscript also created thisdemo_testtable for us to use, and it inserted one row of data. Here is theDESCRIBEstatement for thedemo_testtable:

mysql> DESCRIBE test.demo_test;+-------+---------------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| c1| varchar(32) | NO | PRI | | || c2| varchar(1024) | YES| | NULL| || c3| int(11) | YES| | NULL| || c4| bigint(20) unsigned | YES| | NULL| || c5| int(11) | YES| | NULL| |+-------+---------------------+------+-----+---------+-------+5 rows in set (0.00 sec)

And here is the row that was inserted:

mysql> select * from demo_test;+----+--------------+------+------+------+| c1 | c2 | c3 | c4 | c5 |+----+--------------+------+------+------+| AA | HELLO, HELLO |8 |0 |0 |+----+--------------+------+------+------+1 row in set (0.00 sec)

Next you will need to install thememcachedplugin. From a mysql prompt:

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

Once the plugin is installed this way, it is automatically activated each time the MySQL server is booted or restarted.

To turn off the plugin, use this statement:

mysql> uninstall plugin daemon_memcached;

NOTE: You might need to restart mysqld before continuing.

Now we can start testing memcached with InnoDB. MySQL is now listening to the memcached port 11211. We could try writing some code, but the easiest way is to just telnet to the port 11211 and issue some NoSQL commands. We only have one row in theinnodb_memcache.containerstable, and only one row in thetest.demo_testtable. For this example, we are only going to use a few NoSQL commands –getandset. Here is a link to thefull list of commands.

Let’s telnet and use NoSQL to retrieve the data from the InnoDBdemo_testtable, which contained one row of data. The key for this one row isAA. We simply need to typeget AAto retrieve the data:

# telnet localhost 11211Trying ::1...Connected to localhost.Escape character is '^]'.get AAVALUE AA 8 12HELLO, HELLOEND

We can now insert a line of data. This is done with thesetcommand. The syntax for inserting data is:

In your telnet session, type this:

set BB 0 0 16Goodbye, GoodbyeSTORED

Now, let’s take a look at the InnoDB table,test.demo_test– and we can see our data:

mysql> select * from test.demo_test;+----+------------------+------+------+------+| c1 | c2 | c3 | c4 | c5 |+----+------------------+------+------+------+| AA | HELLO, HELLO |8 |0 |0 || BB | Goodbye, Goodbye |0 |1 |0 |+----+------------------+------+------+------+2 rows in set (0.00 sec)

If we go back to the telnet session, we can also get the value forBB, which we inserted earlier via NoSQL:

get BBVALUE BB 0 16Goodbye, GoodbyeEND

use test;CREATE TABLE `users` ( `user_id` varchar(32) NOT NULL DEFAULT '', `first` varchar(100) DEFAULT NULL, `last` varchar(100) DEFAULT NULL, `flags` int(11) DEFAULT '0', `cas` int(11) DEFAULT '0', `expiry` int(11) DEFAULT '0', PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here is the SQLINSERTforinnodb_memcache.containerstable, to establish our relationship with memcached and InnoDB:

INSERT INTO `innodb_memcache`.`containers` (`name`, `db_schema`, `db_table`, `key_columns`, `value_columns`, `flags`, `cas_column`, `expire_time_column`, `unique_idx_name_on_key`)VALUES ('default', 'test', 'users', 'user_id', 'first|last', 'flags','cas','expiry','PRIMARY');

NOTE: Since we changed theinnodb_memcache.containerstable, we will need to either restart mysqld or disable/enable the plugin (as shown above).

Now that we have restarted mysqld or the plugin, let’s insert some data into our new InnoDB table via NoSQL.

# telnet localhost 11211Trying ::1...Connected to localhost.Escape character is '^]'.set jj1 0 0 15James|JohnsonSTOREDset jj2 0 0 14John|JacksonSTORED

We can now use SQL to query thetest.userstable to see the InnoDB data we just stored via NoSQL:

mysql> select * from users;+---------+-------+-----------+-------+------+--------+| user_id | first | last| flags | cas| expiry |+---------+-------+-----------+-------+------+--------+| jj1 | James | Johnson | 0 |1 |0 || jj2 | John| Jackson | 0 |2 |0 |+---------+-------+-----------+-------+------+--------+2 rows in set (0.00 sec)

Let’s insert some data into thetemp.userstable via mysql, and then retrieve the data via NoSQL.

mysql> INSERT INTO test.users (user_id, first, last, flags, cas, expiry) VALUES ('bb1', 'Beth', 'Brown', '0', '3', '0');Query OK, 1 row affected (0.00 sec)

Retrieve the data via NoSQL:

# telnet localhost 11211Trying ::1...Connected to localhost.Escape character is '^]'.get bb1VALUE bb1 0 10Beth|BrownEND

We now have a way to use NoSQL via memcached and at the same time use good old-fashioned SQL statements on the same data via InnoDB and MySQL. It is the best of both worlds!

Tony Darnell is a Principal Sales Consultant forMySQL, a division ofOracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and onLinkedIn.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com