memcached mysql 队列_通过UDFs实现Memcached与Mysql的自动更新

通过UDFs实现Memcached与Mysql的自动更新

一：UDFs使用简介：

UDFs是User Defined Functions的缩写，表示Mysql的用户定义函数，应用程序可以利用这些函数从Mysql5.0以上的版本的数据库中访问Memcached写入或者获取数据。此外，Mysql从5.1版本开始支持触发器，从而可以在触发器中使用UDFs直接更新Memcached的内容，这种方式降低了应用程序设计与编写的复杂性。

二：UDFs的安装：

安装UDFs需要在数据库服务器上安装两个包，分别是libmemcached和memcached-functions-mysql,这两个包可以从http://download.tangent.org/下载。

1：源码包编译安装Mysql

安装cmake

MySQL从5.5版本开始，通过./configure进行编译配置方式已经被取消，取而代之的是cmake工具。

因此，我们首先要在系统中源码编译安装cmake工具。

[root@memcached ~]# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz

[root@memcached ~]# tar zxvf cmake-2.8.4.tar.gz

[root@memcached ~]# cd cmake-2.8.4

[root@memcached cmake-2.8.4]# ./configure

[root@memcached cmake-2.8.4]# make && make install

确保一下软件已安装：

通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool*

安装前的系统设置

建立mysql安装目录及数据存放目录

[root@memcached ~]# mkdir /usr/local/mysql

[root@memcached ~]# mkdir /usr/local/mysql/data

创建用户及组：

[root@memcached ~]# groupadd mysql

[root@memcached ~]# useradd -g mysql mysql

赋予数据存放目录的权限：

[root@memcached ~]# chown -R mysql:mysql /usr/local/mysql/data/

从configure变更为cmake：

[root@memcached ~]# tar zxvf mysql-5.5.20.tar.gz

[root@memcached ~]# cd mysql-5.5.20

[root@memcached mysql-5.5.20]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DSYSCONFDIR=/usr/local/mysql/etc \

-DMYSQL_DATADIR=/usr/local/mysql/data \

-DMYSQL_TCP_PORT=3306 \

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \

-DMYSQL_USER=mysql \

-DEXTRA_CHARSETS=all \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_EMBEDDED_SERVER=1 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1

[root@memcached mysql-5.5.20]# make && make install

配置并初始化数据库

[root@memcached ~]# mkdir /usr/local/mysql/log

[root@memcached ~]# mkdir /usr/local/mysql/etc

[root@memcached ~]# cd mysql-5.5.20

[root@memcached mysql-5.5.20]# cp support-files/my-medium.cnf /usr/local/mysql/etc/my.cnf

初始化数据库

执行前需赋给scripts/mysql_install_db文件执行权限：

[root@memcached mysql-5.5.20]# chmod 755 scripts/mysql_install_db

[root@memcached mysql-5.5.20]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

创建管理MySQL数据库的shell脚本：

[root@memcached mysql-5.5.20]# mkdir /usr/local/mysql/init.d

[root@memcached mysql-5.5.20]# cp support-files/mysql.server /usr/local/mysql/init.d/mysql

赋予shell脚本可执行权限：

[root@memcached mysql-5.5.20]# chmod +x /usr/local/mysql/init.d/mysql

启动mysql：

[root@memcached mysql-5.5.20]# /usr/local/mysql/init.d/mysql start

Starting MySQL..                                           [  OK  ]

通过命令行登录管理MySQL服务器(提示输入密码时直接回车)：

[root@memcached ~]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysqld.sock

输入以下SQL语句，创建一个具有root权限的用户(admin)和密码(12345678)：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY '12345678';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '12345678';

Query OK, 0 rows affected (0.00 sec)

为root帐户设置初始密码：

[root@memcached ~]# /usr/local/mysql/bin/mysqladmin -uroot password 'centos'

删除本机匿名连接的空密码帐号：

[root@memcached ~]# /usr/local/mysql/bin/mysql -uroot -pcentos mysql

mysql> select Host,User,Password from user; //看下有哪些是空密码的账户

mysql> delete from user where password='';  //执行删除

mysql> select Host,User,Password from user;   //确认下

OK了，mysql安装完毕。

------------------------------------------------------------------------------------------

安装libevent+memcache：

Memcached的安装：

1：安装Memcached前需要先安装libevent:

[root@memcached ~]# wget -b http://www.monkey.org/~provos/libevent-1.4.4-stable.tar.gz

[root@memcached ~]# tail -5 wget-log |sed '/^$/d'   ##查看是否下载正确

[root@memcached ~]# tar zxf libevent-1.4.4-stable.tar.gz

[root@memcached ~]# cd libevent-1.4.4-stable

[root@memcached libevent-1.4.4-stable]# ./configure

[root@memcached libevent-1.4.4-stable]# make && make install

2:安装Memcached的过程：

[root@memcached ~]# wget -b http://memcached.googlecode.com/files/memcached-1.4.5.tar.gz

[root@memcached ~]# tail -5 wget-log.1 |sed '/^$/d'

[root@memcached ~]# tar zxf memcached-1.4.5.tar.gz

[root@memcached ~]# cd memcached-1.4.5

[root@memcached memcached-1.4.5]# ./configure

[root@memcached memcached-1.4.5]# make && make install

##安装完成后，Memcached的默认目录为/usr/local/bin/memcached

3:启动Memcached：

[root@memcached ~]# /usr/local/bin/memcached -m 100m -p 11211 -d -u root -P /var/run/memcached.pid -c 512

有时会出现找不到libevent文件，此时将/usr/local/lib 加入到/etc/ld.so.conf中即可。

[root@memcached ~]# echo "/usr/local/lib" >> /etc/ld.so.conf

[root@memcached ~]# ldconfig

4：关闭Memcached：

[root@memcached ~]# kill `cat /var/run/memcached.pid`

————————————————————————————————————

安装libmencached:

[root@memcached ~]# wget http://download.tangent.org/libmemcached-0.34.tar.gz

[root@memcached ~]# tar zxvf libmemcached-0.34.tar.gz

[root@memcached ~]# cd libmemcached-0.34

[root@memcached libmemcached-0.34]# ./configure --prefix=/usr/local/libmemcached34 --with-memcached=/usr/local/bin/memcached

[root@memcached libmemcached-0.34]# make && make install

[root@memcached libmemcached-0.34]# echo "/usr/local/libmemcached34/" >> /etc/ld.so.conf

[root@memcached libmemcached-0.34]# ldconfig

-------------------------------------------------------------------------------

安装memcached_functions_mysql：

[root@memcached ~]# wget http://download.tangent.org/memcached_functions_mysql-0.9.tar.gz

[root@memcached ~]# tar zxf memcached_functions_mysql-0.9.tar.gz

[root@memcached ~]# cd memcached_functions_mysql-0.9

[root@memcached memcached_functions_mysql-0.9]# ./configure --prefix=/usr/local/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached34

[root@memcached memcached_functions_mysql-0.9]# make && make install

拷贝lib文件到mysql的plugin下面：

[root@memcached memcached_functions_mysql-0.9]# cp /usr/local/memcache_mysql/lib/libmemcached_functions* /usr/local/mysql/lib/plugin

添加memcache UDF 函数：

[root@memcached memcached_functions_mysql-0.9]# /usr/local/mysql/bin/mysql -uroot -pcentos

mysql> source sql/install_functions.sql;

mysql> select * from mysql.func;

+------------------------------+-----+---------------------------------+----------+

| name                         | ret | dl                              | type     |

+------------------------------+-----+---------------------------------+----------+

| memc_add                     |   2 | libmemcached_functions_mysql.so | function |

| memc_add_by_key              |   2 | libmemcached_functions_mysql.so | function |

| memc_servers_set             |   2 | libmemcached_functions_mysql.so | function |

| memc_server_count            |   2 | libmemcached_functions_mysql.so | function |

| memc_set                     |   2 | libmemcached_functions_mysql.so | function |

| memc_set_by_key              |   2 | libmemcached_functions_mysql.so | function |

| memc_cas                     |   2 | libmemcached_functions_mysql.so | function |

| memc_cas_by_key              |   2 | libmemcached_functions_mysql.so | function |

| memc_get                     |   0 | libmemcached_functions_mysql.so | function |

| memc_get_by_key              |   0 | libmemcached_functions_mysql.so | function |

| memc_delete                  |   2 | libmemcached_functions_mysql.so | function |

| memc_delete_by_key           |   2 | libmemcached_functions_mysql.so | function |

| memc_append                  |   2 | libmemcached_functions_mysql.so | function |

| memc_append_by_key           |   2 | libmemcached_functions_mysql.so | function |

| memc_prepend                 |   2 | libmemcached_functions_mysql.so | function |

| memc_prepend_by_key          |   2 | libmemcached_functions_mysql.so | function |

| memc_increment               |   2 | libmemcached_functions_mysql.so | function |

| memc_decrement               |   2 | libmemcached_functions_mysql.so | function |

| memc_replace                 |   2 | libmemcached_functions_mysql.so | function |

| memc_replace_by_key          |   2 | libmemcached_functions_mysql.so | function |

| memc_servers_behavior_set    |   2 | libmemcached_functions_mysql.so | function |

| memc_servers_behavior_get    |   0 | libmemcached_functions_mysql.so | function |

| memc_behavior_set            |   2 | libmemcached_functions_mysql.so | function |

| memc_behavior_get            |   0 | libmemcached_functions_mysql.so | function |

| memc_list_behaviors          |   0 | libmemcached_functions_mysql.so | function |

| memc_list_hash_types         |   0 | libmemcached_functions_mysql.so | function |

| memc_list_distribution_types |   0 | libmemcached_functions_mysql.so | function |

| memc_udf_version             |   0 | libmemcached_functions_mysql.so | function |

| memc_libmemcached_version    |   0 | libmemcached_functions_mysql.so | function |

| memc_stats                   |   0 | libmemcached_functions_mysql.so | function |

| memc_stat_get_keys           |   0 | libmemcached_functions_mysql.so | function |

| memc_stat_get_value          |   0 | libmemcached_functions_mysql.so | function |

+------------------------------+-----+---------------------------------+----------+

32 rows in set (0.00 sec)

-------------------------------------------------------------------------------------------------

memcached_functions_mysql应用实例：

下面通过一个具体的实例来演示memcached_functions_mysql的使用方法：

1：创建两张表：

新建两张表:urls和results，更新urls表的内容，使系统自动更新Memcached的内容。results用来记录更新Memcached失败的记录。

mysql> use tests;

mysql> drop table if exists urls;

mysql> create table urls(

-> id int(10) NOT NULL,

-> url varchar(255) NOT NULL DEFAULT '',

-> PRIMARY KEY (id)

-> );

mysql> drop table if exists results;

mysql> create table results(

-> id int(10) NOT NULL,

-> result varchar(255) NOT NULL DEFAULT 'error',

-> time timestamp NULL DEFAULT CURRENT_TIMESTAMP,

-> PRIMARY KEY (id)

-> );

------------------------------------------------------------------------------------

2：建立3个trigger

当向urls表中插入数据时，对Memcached执行set操作。trigger的代码如下：

mysql> DELIMITER //

mysql> DROP TRIGGER IF EXISTS url_mem_insert;

-> CREATE TRIGGER url_mem_insert

-> BEFORE INSERT ON urls

-> FOR EACH ROW BEGIN

-> set @mm = memc_set(NEW.id, NEW.url);

-> if @mm <> 0 then

-> insert into results(id) values(NEW.id);

-> end if;

-> END //

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

当对urls表中的数据进行更新时，对Memcached执行replace操作。trigger代码如下：

mysql> DELIMITER //

mysql> DROP TRIGGER IF EXISTS url_mem_update;

-> CREATE TRIGGER url_mem_update

-> BEFORE UPDATE ON urls

-> FOR EACH ROW BEGIN

-> set @mm = memc_replace(OLD.id,NEW.url);

-> if @mm <> 0 then

-> insert into results(id) values(OLD.id);

-> end if;

-> END //

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>DELIMITER ;

当对urls表中的数据进行删除操作时，对Memcached执行delete操作。trigger的代码如下：

mysql> DELIMITER //

mysql> DROP TRIGGER IF EXISTS url_mem_delete;

-> CREATE TRIGGER url_mem_delete

-> BEFORE DELETE ON urls

-> FOR EACH ROW BEGIN

-> set @mm = memc_delete(OLD.ID);

-> if @mm <> 0 then

-> insert into results(id) values(OLD.id);

-> END if;

-> END //

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

---------------------------------------------------------------------------------------

3：设置Memcached的相关参数：

设置UDFs操作Memcached服务器的IP地址和端口。

mysql> select memc_servers_set('192.168.0.166:11900');

+-----------------------------------------+

| memc_servers_set('192.168.0.166:11900') |

+-----------------------------------------+

|                                       0 |

mysql> select memc_server_count();

+---------------------+

| memc_server_count() |

+---------------------+

|                   1             1

+---------------------+

在Mysql命令行中列出可以修改Memcached参数行为，执行的命令和输出结果如下：

mysql> select memc_list_behaviors()\G;

*************************** 1. row ***************************

memc_list_behaviors():

MEMCACHED SERVER BEHAVIORS

MEMCACHED_BEHAVIOR_SUPPORT_CAS

MEMCACHED_BEHAVIOR_NO_BLOCK

MEMCACHED_BEHAVIOR_TCP_NODELAY

MEMCACHED_BEHAVIOR_HASH

MEMCACHED_BEHAVIOR_CACHE_LOOKUPS

MEMCACHED_BEHAVIOR_SOCKET_SEND_SIZE

MEMCACHED_BEHAVIOR_SOCKET_RECV_SIZE

MEMCACHED_BEHAVIOR_BUFFER_REQUESTS

MEMCACHED_BEHAVIOR_KETAMA

MEMCACHED_BEHAVIOR_POLL_TIMEOUT

MEMCACHED_BEHAVIOR_RETRY_TIMEOUT

MEMCACHED_BEHAVIOR_DISTRIBUTION

MEMCACHED_BEHAVIOR_BUFFER_REQUESTS

MEMCACHED_BEHAVIOR_USER_DATA

MEMCACHED_BEHAVIOR_SORT_HOSTS

MEMCACHED_BEHAVIOR_VERIFY_KEY

MEMCACHED_BEHAVIOR_CONNECT_TIMEOUT

MEMCACHED_BEHAVIOR_KETAMA_WEIGHTED

MEMCACHED_BEHAVIOR_KETAMA_HASH

MEMCACHED_BEHAVIOR_BINARY_PROTOCOL

MEMCACHED_BEHAVIOR_SND_TIMEOUT

MEMCACHED_BEHAVIOR_RCV_TIMEOUT

MEMCACHED_BEHAVIOR_SERVER_FAILURE_LIMIT

MEMCACHED_BEHAVIOR_IO_MSG_WATERMARK

MEMCACHED_BEHAVIOR_IO_BYTES_WATERMARK

MEMCACHED_HASH_DEFAULT

MEMCACHED_HASH_MD5

MEMCA

1 row in set (0.00 sec)

-----------------------------------------------------------------------

设置MEMCACHED_BEHAVIOR_NO_BLOCK为打开状态，这样在Memcached出问题时(不能连接)，数据可以继续插入Mysql中，但有报错提示：如果不设置此值，那么Memcached失败时，数据需要等到Memcached失败超时后才可以插入到表中。

通过下面设置，可以避免这种情况的发生：

mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');

+--------------------------------------------------------------+

| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |

+--------------------------------------------------------------+

|                                                            0 |

+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');

+-----------------------------------------------------------------+

| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |

+-----------------------------------------------------------------+

|                                                               0 |

+-----------------------------------------------------------------+

1 row in set (0.00 sec)

---------------------------------------------------------------------------------------------

4：对memcached_functions_mysql的简单功能进行测试

1)：向表urls中插入数据，然后查看Memcached是否对数据执行set操作。

mysql> insert into urls (id,url) values (1,'http://www.51newcar.com');

mysql> select memc_get('1');

+-------------------------+

| memc_get('1')           |

+-------------------------+

| http://www.51newcar.com |

+-------------------------+

1 row in set (0.00 sec)

[root@memcached ~]# telnet 192.168.0.166 11900

Trying 192.168.0.166...

Connected to memcached.51auto.com (192.168.0.166).

Escape character is '^]'.

get 1

VALUE 1 0 23

http://www.51newcar.com

END

----------------------------------------------------------------------------

2)更新表urls里面的数据，然后查询Memcached中是否也进行了更新。

mysql> update test.urls set url='http://blog.kuchuli.com' where id =1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select memc_replace('1','http://blog.kuchuli.com');

+---------------------------------------------+

| memc_replace('1','http://blog.kuchuli.com') |

+---------------------------------------------+

|                                           0 |

+---------------------------------------------+

1 row in set (0.00 sec)

mysql> select memc_get('1');

+-------------------------+

| memc_get('1')           |

+-------------------------+

| http://blog.kuchuli.com |

+-------------------------+

1 row in set (0.00 sec)

[root@memcached ~]# telnet 192.168.0.166 11900

Trying 192.168.0.166...

Connected to memcached.51auto.com (192.168.0.166).

Escape character is '^]'.

get 1

VALUE 1 0 23

http://blog.kuchuli.com

END

------------------------------------------------------------------------------

3)删除urls中的数据，然后查看Memcached是否也将该数据删除了。

mysql> delete from test.urls where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select memc_get('2');

+---------------+

| memc_get('2') |

+---------------+

| NULL          |

+---------------+

1 row in set (0.00 sec)

[root@memcached ~]# telnet 192.168.0.166 11900

Trying 192.168.0.166...

Connected to memcached.51auto.com (192.168.0.166).

Escape character is '^]'.

get 2

END

-----------------------------------------------------------------------------------------

OK Done！！！总之源码包有很多有bag。