mysql5.6 Nosql, innodb 安装和配置以及主从镜像模式下可能产生的问题

一些可以参考的文章：

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

本文使用 mysql-5.6.6-m9-linux2.6-x86_64.tar.gz.

下面是简要的安装过程：
最好是把SELINUX给关了，否则下面都有可能会报错。

安装mysql5.6.6

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

可以看一下安装文档：
more INSTALL-BINARY
./scripts/mysql_install_db --user=mysql

/etc/init.d/mysql START
memcached需要安装Libevent

# 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
接下来安装mysql memcached的plugin：

修改my.cnf,顺便添加一个server_id:

[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


确认一下：

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)
运行必须的sql文件，会创建 test.demo_test表。

cd mysql-5.6.6-m9-linux2.6-x86_64/lib/scripts/
mysql -p < innodb_memcached_config.SQL
安装插件：

mysql> install plugin daemon_memcached SONAME 'libmemcached.so';
安装完成，来测试一下。

用SQL语句向demo_test中写入数据：
# 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)
使用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)
假设已经做好了主从镜像，测试一下nosql在主从的环境中是否有问题：

主服务器的初始数据如下：


MySQL_Master >SELECT * FROM demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | memcached    |   10 |    5 |    0 |
+------+--------------+------+------+------+
使用memcached协议来更新一下数据：

# 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
主服务器的数据正常，已经被更新了：

MySQL_Master >SELECT * FROM demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | replicated   |   12 |    2 |    0 |
+------+--------------+------+------+------+
从服务器还是旧的数据。。。

MySQL Slave > SELECT * FROM demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | memcached    |   10 |    5 |    0 |
+------+--------------+------+------+------+
Error executing row event: ‘Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.’

看来只能把binlog_formaty调整为MIXED了。。。:

MySQL Slave > SET GLOBAL binlog_format = 'MIXED';
这点要注意到。