Last week I heard about a company that has a concern with their NoSQL solution and the replication of their data. I, of course, thought of the new MySQL 5.6 release as
a possible solution for their problem.
You can combine all your data across the same nodes and replicate it very easily. All the while the data is accessible via SQL and NOSQL. MySQL Cluster proved this in
MySQL Cluster 7.2 already, so now it will also be available for
MySQL 5.6.
Some great blogs and documentation already exist on this topic but
they are a little older:
http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/
http://blogs.innodb.com/wp/2011/04/nosql-to-innodb-with-memcached/
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-replication.html
This feature can be tested now via the mysql-5.6.6 linux download --
mysql-5.6.6-m9-linux2.6-x86_64.tar.gz. Below is my quick example on how to set this all up.
Get the release:
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.6-m9-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
tar -vxzf mysql-5.6.6-m9-linux2.6-x86_64.tar.gz
cd mysql-5.6.6-m9-linux2.6-x86_64
# if you want more info
more INSTALL-BINARY
./scripts/mysql_install_db --user=mysql
/etc/init.d/mysql start
Make sure your system is set up for memcached.
Libevent is required for memcached. I have the following for example:
# rpm -qa | grep libevent
libevent-headers-1.4.13-4.el6.noarch
libevent-1.4.13-4.el6.x86_64
libevent-devel-1.4.13-4.el6.x86_64
libevent-doc-1.4.13-4.el6.noarch
You will need to also keep in mind that your SELINUX could deny access during a plugin install:
mysql> install plugin daemon_memcached soname 'libmemcached.so';
ERROR 1126 (HY000): Can't open shared library '/var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/libmemcached.so' (errno: 2 /var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/libmemcached.so: cannot open shared object file: Permission denied)
You can solve this a few ways, and it is up to you how you handle SELINUX.
First edit the my.cnf file and add the location of your plugins. I also added a server_id since I will be using replication later.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
plugin_dir=/var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin
innodb_api_enable_binlog=1
server_id = 1
Confirm this :
mysql> select @@plugin_dir;
+-----------------------------------------------------+
| @@plugin_dir |
+-----------------------------------------------------+
| /var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/ |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Install the configuration files:
cd mysql-5.6.6-m9-linux2.6-x86_64/lib/scripts/
mysql -p < innodb_memcached_config.sql
Now you are able to install the plugin:
mysql> install plugin daemon_memcached soname 'libmemcached.so';
So... lets test this all out..
We are going to test with the demo_test table in the test database. This is set via the innodb_memcached_config.sql file referenced earlier.
First SQL:
# mysql -p
Server version: 5.6.6-m9-log MySQL Community Server (GPL)
mysql> use test
mysql> truncate demo_test;
mysql> INSERT INTO demo_test VALUES ('AA','TEST VIA SQL',1,1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
+----+--------------+------+------+------+
1 row in set (0.01 sec)
Now via memcached:
# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set memc 10 0 9
memcached
STORED
get memc
VALUE memc 10 9
memcached
END
mysql> select * from demo_test;
+------+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
| memc | memcached | 10 | 5 | 0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)
Jimmy's blog post goes into more details on the table and etc.if your curious about the table structures.
If you add replication to this architecture you will now have a NOSQL & MySQL replicated system. Below is a quick example showing this...
This is our data at the start :
MySQL_Master >select * from demo_test;
+------+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
| memc | memcached | 10 | 5 | 0 |
+------+--------------+------+------+------+
Access memcached and update the memc key/values
# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set memc 12 0 10
replicated
STORED
get memc
VALUE memc 12 10
replicated
END
We can see this change on the master.
MySQL_Master >select * from demo_test;
+------+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
| memc | replicated | 12 | 2 | 0 |
+------+--------------+------+------+------+
Now lets check the slave....
MySQL Slave > select * from demo_test;
+------+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
| memc | memcached | 10 | 5 | 0 |
+------+--------------+------+------+------+
Ouch a problem!
Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
I normally would have been in Mixed or Row based but this was a new virtual image for this example. So this is a simple quick adjustment.
MySQL Slave > SET GLOBAL binlog_format = 'MIXED';
After a stop and start of slave.....
MySQL Slave > select * from demo_test;
+------+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+------+--------------+------+------+------+
| AA | TEST VIA SQL | 1 | 1 | 1 |
| memc | replicated | 12 | 2 | 0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)
With MySQL 5.6 you get NoSQL, SQL, and replication. This allows you to take advantage of your data and not have to store it via separate data systems.
Learn more about MySQL and InnoDB and our NoSQL options at MySQL Connect.
CON8815 - A Journey into NoSQLand: MySQL’s NoSQL Implementation
Lig Turmelle - Web Database Administrator, Kaplan Professional
CON9343 - Developing High-Throughput Services with NoSQL APIs to InnoDB and MySQL Cluster
Andrew Morgan - MySQL Senior Product Manager, Oracle
John Duncan - Software Engineer, MySQL, Oracle
