Use MySQL to store NoSQL and SQL data in the same database u_MySQL

Use MySQL to store NoSQL and SQL data in the same database using memcached and InnoDB

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

---

On the Mac, it takes a few more steps (I tested this with Mac OS 10.9 – Mavericks). To installlibevent, you will need to installXcode, the Xcode command line tools, and thenHomebrew. You may install Xcode via the Apple App Store (and this is the most time-consuming part). Once Xcode is installed, from a command line type:

# xcode-select --install

This will prompt you to install the command-line tools. Then, you can easily install Homebrew via this command:

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

Then installlibeventvia:

# 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 
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:

• name: This is the name that is like the primary key for the memcache data collection. If you have a value of default for name, then this will be the default entry that is used. Otherwise it uses the first entry in the container table. You can also specify this name value in the NoSQL statement.
• db_schema: The InnoDB database name that you will use to store the data.
• db_table: The InnoDB database table name that you will use to store the data.
• key_columns: The column that you will use for the key value lookup. This field only contains one column (despite the plural name of key_columns).
• value_columns: Data will be pulled from and/or stored to these column/columns of data. You use a separator value (such as a pipe "|" symbol) to separate the columns. In the example database that is installed, you can store first name | last name which would pull data from both a firstname and lastname column.
• flags: This column stores memcache flags, which is an integer that is used to mark rows for memcache operations.
• cas_columnandexpire_time_column: These two columns are used for storing memcache compare-and-swap and expiration values. You can ignore these for now.
• unique_idx_name_on_key: This is the name of the unique index that you will use for the key column, and you should use the primary key for the table. You should not use an auto-incrementing index, as you can’t insert into an auto-incrementing key.

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:

• set – this is the command to store a value
• XX – this is the value key
• # – this is a reference to the flags that we will use
• # – this is the expiration TTL (ime to live)
• # – the length of the string that we will insert/store
• ABCDEF – the value to insert/store

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

---

Let’s create a new table, create the relationship in theinnodb_memcache.containersand insert (set) and read (get) some new data. Here is theCREATE TABLEstatement for our newuserstable:

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.