MySQL Trigger trigger Operation Memcache Instance

The operation of the memcache is usually put inside the program to operate, add, UPDATE, delete what. If you can use MySQL to operate on memcache, that's better, the code side will be simpler. However, the use of MySQL to operate memcache, more suitable for the implementation of a simple way. The following is the installation process and the problems encountered, before looking at the installation process, I think should first look at the installation time I encountered problems, so you can avoid, less go a little detour.
One, install the software you want
mysql5.1 Previous version: http://downloads.mysql.com/archives/
libevent Download: wget http:// www.monkey.org/~provos/libevent-1.2.tar.gz
libmemcached Download: http://download.tangent.org/
memcached Download: http ://pecl.php.net/package/memcached
Memcached_functions_mysql Download: http://download.tangent.org/
When downloading something, Also has the skill, the downloading thing, do not be too new, too new, otherwise this not to be able, that also not good, too old also not good, too old, this cannot install, that also can not install, therefore must find a stable version. In general, the software will identify the basic version, stable version and so on.
Two, MySQL installation
mysql installation is a lot, you can use the System Software Management Pack to install, different Linux version, software management tools are not the same.
Redhat,centos has yum,ubuntu have apt get, arch has Pacman etc. It is simpler to install the management tools with the system. If you download how to install it, MySQL's official website provides three kinds of MySQL installation source code, one is. RPM, one is binary, one is to compile their own.
1, use RPM to install

The code is as follows	Copy Code
Rpm-i mysql-server-version.i386.rpm Rpm-i mysql-client-version.i386.rpm

2, binary package for installation
Binary package installation has a disadvantage, is to install to where, are dead. There is a problem in the installation process, check if the glibc is installed, and the version is not too low.

The code is as follows	Copy Code
Groupadd MySQL useradd-g MySQL MySQL Tar zxvf/path/to/mysql-version-os.tar.gz-c/usr/local Cd/usr/local MV Mysql-version-os./mysql Cd/usr/local/mysql scripts/mysql_install_db--user=mysql Chown-r Mysql:mysql/usr/local/mysql Bin/mysqld_safe--user=mysql &

3, the source code itself compiled

code is as follows	copy code
groupadd MySQL   useradd-g mysql mysql  tar zxvf/path/to/mysql-version-os.tar.gz  CD/MYSQL-VERSION-OS&NB Sp ./configure--prefix=/usr/local/mysql//path customizable   make && make install  CP support-files/m y-medium.cnf/etc/my.cnf  cd/usr/local/mysql  bin/mysql_install_db        --user=mysql  chown-r mysql:mysql/usr/local/mysql  Bin/mysqld_safe--user= mysql        &

Installation of a distress problem: install MySQL at least 5.1 version of the above, server-side, or client, after loading memcached_functions_mysql, call the Libmemcached module will be an error.

code is as follows

copy code

[root@blackghost sql]#/usr/local/mysql/bin/mysql <install_functions.sql ERROR 1126 (HY000) at line 1:can ' t open shared library ' L Ibmemcached_functions_mysql.so ' (errno:22/usr/local/mysql/lib/mysql/plugin/libmemcached_functions_mysql.so: Undefined symbol:memcached_string_append) The above is to start MySQL with Mysqld_safe, or you can use Mysql.server to start it in/usr/local/mysql/ Under Share/mysql, you can also take mysql.server to the boot-initiated directory, and rename to httpd [Root@blackghost mysql]#./mysql.server Stop Shutting down MySQL. success! [Root@blackghost mysql]#./mysql.server start starting MySQL. success!

Ii. installation of Libevent and memcached
Please refer to: Linux memcached installation
Third, the installation of libmemcached

The code is as follows	Copy Code
Tar zxvf libmemcached-0.37.tar.gz CD libmemcached-0.37 ./configure--prefix=/usr/local/libmemcached37--with-memcached Make && make install

Installation problems: At that time I downloaded the libmemcached-0.42.tar.gz, the installation of Memcached_functions_mysql process encountered such a problem
Servers.c:263:28:error: ' Memcached_st ' has no member named ' Hosts '
Servers.c:264:28:error: ' Memcached_st ' has no member named ' Hosts '
Later I check on the Internet, libmemcached-0.37 no this problem, make me very silent, is not backward-compatible?
Four, install Memcached_functions_mysql

The code is as follows

Copy Code

Tar xzf memcached_functions_mysql-0.9.tar.gz CD memcached_functions_mysql-0.9 ./configure--prefix=/usr/local/memcache_mysql--with-mysql-config=/usr/local/mysql/bin/mysql_config-- With-libmemcached=/usr/local/libmemcached37 Make && make install cp/usr/local/memcache_mysql/lib/libmemcached_functions*/usr/local/mysql/lib/mysql/plugin Don't forget to add--with-libmemcached=/usr/local/libmemcached37 or you'll report the following error Checking for mysql_config .../usr/bin/mysql_config Checking for libmemcached >= 0.17 ... Configure:error:libmemcached not found Create a MySQL memcache action function MySQL <memcached_functions_mysql-0.9/sql/install_functions.sql Note that the path is not wrong. Install_functions.sql defines some of the memcache action functions: [Root@blackghost zhangy]# cat Memcached_functions_mysql-0.9/sql/install_functions.sql |awk ' {if ($!~/^$/) {print $}} Memc_add Memc_add_by_key Memc_servers_set Memc_servers_version Memc_server_count Memc_set Memc_set_by_key Memc_cas Memc_cas_by_key Memc_get Memc_get_by_key Memc_delete Memc_delete_by_key Memc_append Memc_append_by_key Memc_prepend Memc_prepend_by_key Memc_increment Memc_decrement Memc_replace Memc_replace_by_key Memc_servers_behavior_set Memc_servers_behavior_get Memc_behavior_set Memc_behavior_get Memc_list_behaviors Memc_list_hash_types Memc_list_distribution_types Memc_udf_version Memc_libmemcached_version Memc_stats Memc_stat_get_keys Memc_stat_get_value

The installation is basically over here, let's test it.
Five, test
Start memcached:

The code is as follows

Copy Code

/usr/local/bin/memcached-d-M 20-u zhangy-p 12000-p./memcached.pid /usr/local/bin/memcached-d-M 20-u zhangy-p 13000-p./mem.pid Create a test that has Drop table if exists URLs; CREATE TABLE URLs ( ID int (3) NOT NULL, URL varchar () NOT NULL default ', Primary KEY (ID) ); Connection memcched, root boot memcahed port to be the same Select Memc_servers_set (' 127.0.0.1:12000,127.0.0.1:13000 '); Set a start sequence Select Memc_set (' Urls:sequence ', 0); Create Insert memcached Trigger DELIMITER | DROP TRIGGER IF EXISTS Url_mem_insert | CREATE TRIGGER Url_mem_insert Before INSERT on URLs For each ROW BEGIN SET new.id= memc_increment (' urls:sequence '); SET @mm = Memc_set (concat (' URLs: ', new.id), New.url); End | Create an update memcached trigger DROP TRIGGER IF EXISTS url_mem_update | CREATE TRIGGER Url_mem_update Before UPDATE on URLs For each ROW BEGIN SET @mm = Memc_replace (concat (' URLs: ', old.id), New.url); End | To create a delete memcached trigger DROP TRIGGER IF EXISTS url_mem_delete | CREATE TRIGGER Url_mem_delete Before DELETE on URLs For each ROW BEGIN SET @mm = Memc_delete (concat (' URLs: ', old.id)); End |

DELIMITER; When we write a trigger, we use the MySQL execution separator, so we change it when we write the trigger or the stored procedure, for example: DELIMITER |

The code is as follows

Copy Code

Insert some test data Insert into URLs (URL) values (' http://google.com '); Insert into URLs (URL) values (' http://baidu.com/'); Insert into URLs (URL) values (' http://www. www.111cn.net/'); Insert into URLs (URL) values (' http://www.111cn.net/'); Insert into URLs (URL) values (' http://www.111cn.net '); Insert into URLs (URL) values (' http://mysql.com '); SELECT * from URLs; The 6 data that is inserted is displayed, and the following display and deletion are the same. Don't say much. Select Memc_get (' urls:1 '); Select Memc_get (' Urls:2 '); Select Memc_get (' Urls:3 '); Select Memc_get (' urls:4 '); Select Memc_get (' Urls:5 '); Select Memc_get (' Urls:6 '); Update URLs set url= ' http://mysql.com/sun ' where url = ' http://www.111cn.net '; Select URL from urls where url = ' http://www.111cn.net/manual '; Select Memc_get (' Urls:6 '); Delete from urls where url = ' http://www.111cn.net/'; SELECT * from URL where url= ' http://www.111cn.net/'; Select Memc_get (' urls:4 ');