• 博客访问: 613828
  • 博文数量: 290
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-01 11:01
  • 认证徽章:
个人简介

暂无介绍

文章分类

全部博文(290)

文章存档

2016年(5)

2013年(1)

2012年(6)

2011年(7)

2010年(1)

2009年(12)

2008年(67)

2007年(142)

2006年(21)

2005年(28)

我的朋友

分类: MySQL

2016-06-04 16:24:11

为什么mysql这么火,是因为mysql很强大吗?NO,作为oracle 的DBA,之前一直都感觉mysql比较low,尤其是在大数据量、大并发、锁读写环境下,mysql简直就是木鸡
但是不得不说的是mysql,刚好赶上了这个时代的需求,随着行业的越来越推进,虚拟化、X86化、敏捷、松耦合、低成品要求的不断推进,越来越多的人开始考虑将业务分割、切片、读写分离
当数据量进行横向、纵向拆分后,mysql这种简单、低成本的数据库就越来越有优势了
当然,也有很多内在因素,促使mysql的不断发展和完善
这里举一个简单的例子,"mysql组件之memcache",完了之后,你会发现mysql虽然弱,但也算短小精干,O(∩_∩)O
memcache是一个内存数据库,数据以键值(key--value)对的方式,对外提供访问,特点当然是快快快,这里不赘述,有兴趣的百度之
mysql早已兼容memcached组件,本文介绍mysql5.7.12中如何将两个组合起来

1、memcached的安装
需要提前安装libevent
这里提供两种安装方式
a、yum 安装
    yum我配置的是光盘,也就是说redhat7.1中有libevent的安装文件,只要install即可,如下命令(简单吧)
yum install -y libevent
yum install -y memcached
b、基于源码的安装
  有时候你只能拿到源码(也许你需要安装最新版的软件,光盘没有),那就需要编译后再安装,编译需要GCC
I.先安装gcc
#yum install gcc*(gcc很多依赖,我懒得选,直接yum install)
II.安装libevent(进入libevent源码目录执行如下命令)
#./configure -prefix=/opt/libevent
#make
#make install
III.安装memcached(编译memcached的时候需要先安装autoconf automake libtool进入memcached源码目录执行如下命令)
#yum install -y autoconf automake libtool
#autoreconf -ivf  --如果不加这个命令,编译的时候会报错make: *** [aclocal.m4] Error 127
#cd /home/zxt/memcached-1.4.24
#./configure --prefix=/opt/memcached --with-libevent=/opt/libevent/
#make
#make install

ok,安装完成!

2、启动memcached
/opt/memcached/bin/memcached -d -m 1024 -p 11211 -l localhost -c 256  -u root (其中m为内存,p为端口 l为地址 c为连接数)
如果是yum安装的请去掉上边的路径“/opt/memcached/bin
3、监控状态
/home/zxt/memcached-1.4.25/scripts/memcached-tool 127.0.0.1:11211 stats 
如果是yum安装的请去掉上边的路径“/home/zxt/memcached-1.4.25/scripts/
4、停止memcached
ps -ef|grep memcached  然后kill -9(你懂的)
5、mysql上配置memcached
其实也很简单,只要执行“innodb_memcached_config.sql”,然后做一些简单配置即可,如果找不到“innodb_memcached_config”在哪儿,试着用find,好吧,至少当时我是这么做的
找到后在mysql命令行下source一下(就好比oracle的sql中,是使用@文件来执行的)
mysql> source /usr/share/mysql/innodb_memcached_config.sql
如果执行出错,没事别慌,删掉如下两个数据库,重新执行
mysql> drop database innodb_memcache;
mysql> drop database test;
mysql> source /usr/share/mysql/innodb_memcached_config.sql
其实,这只是一个DEMO,他的逻辑很简单,创建几张元数据表,然后一张demo_test的test表,demo_test表中的数据会和memcache进行自动同步,注意,这里是自动同步,不需要额外的配置(就是这么牛)
执行完后,可做如下检查
mysql> USE innodb_memcache;
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies |
| config_options |
| containers |
+---------------------------+
mysql> USE test;
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| demo_test |
+----------------+


mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
name: aaa
db_schema: test
db_table: demo_test
key_columns: c1
value_columns: c2
flags: c3
cas_column: c4
expire_time_column: c5
unique_idx_name_on_key: PRIMARY
mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+------------------+------+------+------+
| AA | HELLO, HELLO | 8 | 0 | 0 |
+----+------------------+------+------+------+

6、启用mysql的属性
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;--这是个可选参数,修改了事务的级别,确保数据能够及时同步
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
ERROR 1125 (HY000): Function 'daemon_memcached' already exists--如果已经启用,可通过如下脚本重新安装
mysql> UNINSTALL PLUGIN daemon_memcached;
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

ok,做完所有这些配置,就搭建好环境了,innodb_memcached_config.sql我会附在本文的最后

下来做一下测试,这里需要开两个窗口,一个访问memcached的数据,另外一个则是在mysql中进行check
访问memcached的数据需要telnet memcached的端口,如果没有安装telnet,请执行如下命令来安装:
#yum install -y telnet

a、现看两边的数据
窗口一:
mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
+----+--------------+------+------+------+
1 row in set (0.01 sec)
mysql>

窗口二:

[root@ct-test1 libevent-2.0.22-stable]# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END

b、从memcache中插入数据,由mysql检查是否同步
窗口二:
[root@ct-test1 libevent-2.0.22-stable]# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END
set BB 0 0 7
zxttest
STORED

插入数据用set 格式为 set A B C D (其中A 为key,key--value的key;B是一个标志,memcache中B没多大意思,默认0即可;C代表有效时间;D代表长度),输入外set ABCD 后回车继续输入value,我这里输入的是“zxttest”

再mysql中查询
窗口一:
mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | HELLO, HELLO |    8 |    0 |    0 |
| BB | zxttest      |    0 |    2 |    0 |
+----+--------------+------+------+------+
2 rows in set (0.00 sec)

mysql> 

b、从mysql中插入数据,由memcache检查是否同步
窗口一:
mysql> insert into demo_test values ('CC','test from mysql',0,2,0);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> 

窗口二:
[root@ct-test1 libevent-2.0.22-stable]# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get AA
VALUE AA 8 12
HELLO, HELLO
END
set BB 0 0 7
zxttest
STORED
get CC
VALUE CC 0 15
test from mysql
END

memcache中查询数据用get X (X为key)

ok,大概就是这个样子,但是,问题又来了,mysql是怎么做到数据同步的呢?自己如何定制自己的表呢?
其实,检查一下如下这张表就很比较容易理解了
mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.05 sec)

也就是说,是通过 innodb_memcache.containers来维护两个之间的关系的,让我们自己定义需要memcache缓存的表时
1、定义一张表,要有主键哈
2、然后在innodb_memcache.containers中insert 一条记录即可,可以仿照表中现有数据,改改就行
3、通过如下命令重启daemon_memcache组件
mysql> UNINSTALL PLUGIN daemon_memcached;
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

ok,就是这么简单!这里本来还想做一下java中如何调用memcache的例子,想了想,这毕竟是开发人员的事,对吧,就不掺和了

============================================================================
innodb_memcached_config.sql的源码,大家一起拜读
[root@ct-test1 ~]# cat /usr/share/mysql/innodb_memcached_config.sql
create database innodb_memcache;

use innodb_memcache;

-- ------------------------------------------------------------------------
-- Following are set of "configuration tables" that used to configure
-- the InnoDB Memcached.
-- ------------------------------------------------------------------------

-- ------------------------------------------------------------------------
-- Table `cache_policies`
--
-- Each record in this table represents a named caching policy, specifying:
--  * How the memcache GET command is executed, including whether to get
--    records from local cache only, from InnoDB only, from local cache if
--    present (treating InnoDB as a backing store), or not at all.
--  * Similarly, how memcache SET commands are executed.
--  * How memcache DELETE commands are executed.
--  * Whether flushing the cache should cause a mass delete from NDB.
--
-- ------------------------------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cache_policies` (
`policy_name` VARCHAR(40) PRIMARY KEY,
`get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
NOT NULL ,
`set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
NOT NULL ,
`delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
NOT NULL,
`flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
NOT NULL
) ENGINE = innodb;

-- ------------------------------------------------------------------------
-- Table `containers`
--
-- A container record describes an InnoDB table used for data storage by
-- InnoDB Memcache.
-- There must be a unique index on the `key column`, and unique index name
-- is specified in the `unique_idx_name_on_key` column of the table
-- `value_columns` are comma-separated lists of the columns that make up
-- the memcache key and value. Each column width is defined such that they
-- are in consistent with NDB memcached.
-- ------------------------------------------------------------------------

CREATE  TABLE IF NOT EXISTS `containers` (
`name` varchar(50) not null primary key,
`db_schema` VARCHAR(250) NOT NULL,
`db_table` VARCHAR(250) NOT NULL,
`key_columns` VARCHAR(250) NOT NULL,
`value_columns` VARCHAR(250),
`flags` VARCHAR(250) NOT NULL DEFAULT "0",
`cas_column` VARCHAR(250),
`expire_time_column` VARCHAR(250),
`unique_idx_name_on_key` VARCHAR(250) NOT NULL
) ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `config_options` (
`name` varchar(50) not null primary key,
`value` varchar(50)) ENGINE = InnoDB;

-- ------------------------------------------------------------------------
-- This is an example
-- We create a InnoDB table `demo_test` is the `test` database
-- and insert an entry into contrainers' table to tell InnoDB Memcache
-- that we has such InnoDB table as back store:
-- c1 -> key
-- c2 -> value
-- c3 -> flags
-- c4 -> cas
-- c5 -> exp time
-- PRIMARY -> use primary key to search
-- ------------------------------------------------------------------------

INSERT INTO containers VALUES ("aaa", "test", "demo_test",
      "c1", "c2",  "c3", "c4", "c5", "PRIMARY");

INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
 "innodb_only", "innodb_only", "innodb_only");

INSERT INTO config_options VALUES("separator", "|");
INSERT INTO config_options VALUES("table_map_delimiter", ".");


CREATE DATABASE IF NOT EXISTS test;
USE test

-- ------------------------------------------------------------------------
-- Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
-- Bytes
-- Value (c2) must be VARCHAR or CHAR type
-- Flag (c3) is a 32 bits integer
-- CAS (c4) is a 64 bits integer, per memcached define
-- Exp (c5) is again a 32 bits integer
-- ------------------------------------------------------------------------
CREATE TABLE demo_test (c1 VARCHAR(32),
c2 VARCHAR(1024),
c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
ENGINE = INNODB;

INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);
[root@ct-test1 ~]# 

阅读(248) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册