Ing Mysql Data to memcached [reprint]

This article discusses the combined application of MySQL and memcached. The future trend has been gradually clarified. The combination of the two can make MySQL stronger.

Almost a year ago, I wrote an article about ing Mysql Data to memcached. At that time, MySQL and memcached functions for MySQL were not mature enough, memcached functions for MySQL was upgraded to version 0.8, while MySQL also released the GA version. In addition, many of my friends reflected that the implementation in the previous article was not successful for various reasons, so I got this article, this is an upgraded version of the previous article.

The test environment is run in Linux. The version system is centos5.
The following are the related software, including its version and:

Download mysql-5.1.30
Download memcached-1.2.6
Download libevent-1.4.7-stable
Download memcached_functions_mysql-0.8
Download libmemcached-0.26

Compile and install mysql. The installation depends on your individual requirements. Many compilation details and parameters irrelevant to the test are omitted.

[root@localhost ~]#tar xzf mysql-5.1.30.tar_001.gz[root@localhost ~]#cd mysql-5.1.30[root@localhost ~]#./configure --prefix=/usr/local/mysql51[root@localhost ~]#make[root@localhost ~]#make install[root@localhost ~]#./scripts/mysql_install_db --user=mysql --skip-name-resolve[root@localhost ~]#/usr/local/mysql51/bin/mysqld_safe

The commands for installing memcached and libevent are omitted. Install libevent by default and install memcached in the/usr/local/memcached directory.
Start memcached.

/usr/local/memcached/bin/memcached -d -m 50 -u root -p 11211

Compile and install libmemcache.

[root@localhost ~]#tar xzf libmemcached-0.26.tar.gz[root@localhost ~]#cd libmemcached-0.26[root@localhost ~]#./configure --with-memcached=/usr/local/memcached/bin/memcached[root@localhost ~]# make && make install

Compile and install memcache udfs for MySQL.

[root@localhost ~]# tar xzf memcached_functions_mysql-0.8.tar.gz[root@localhost ~]# cd memcached_functions_mysql-0.8[root@localhost ~]# ./configure --with-mysql-config=/usr/local/mysql51/bin/mysql_config[root@localhost ~]# make && make install

After compilation, copy the compiled library file to the MySQL plug-in directory to facilitate loading and usage.

cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql51/lib/mysql/plugin/

Go to the source code directory of memcached_functions_mysql. There is an SQL file for UDF initialization.

[root@localhost ~]# mysql <sql/install_functions.sql

Note: If you are not familiar with or understand these udfs, you can refer to readme in the source code directory.

So far, the compilation and installation of related software are complete for testing. The purpose is to insert a new MySQL record to memcached at the same time, when the record is updated, records in memcached are synchronously updated. When the record is deleted, memcached related records are also deleted. Therefore, Three triggers are created for this purpose, if you are not familiar with the MySQL trigger program, refer to Chapter 21st of the MySQL manual. In the following SQL statement, memcached is the name of the table to be operated. The SQL statement is as follows:

# Insert memcachedcreate trigger mysqlmmci after insert on memcached for each row set @ TMP = memc_set (New. key, new. value); # update memcachedcreate trigger mysqlmmcu after update on memcached for each row set @ TMP = memc_set (New. key, new. value); # Delete the memcached record when deleting the record create trigger mysqlmmcd before delete on memcached for each row set @ TMP = memc_delete (old. key );

The following is a test record. You can operate memcached to view the situation while operating mysql. Of course, you can also include the-VV parameter to view the relevant information when starting memcached.

MySQL operation records:

[root@localhost ~]#mysql -S /tmp/mysql51.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test;Database changed mysql> create table `memcached` (`key` varchar(10), `value` varchar(100));                                     Query OK, 0 rows affected (0.00 sec) mysql> create trigger mysqlmmci after insert on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);Query OK, 0 rows affected (0.00 sec) mysql> create trigger mysqlmmcu after update on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);Query OK, 0 rows affected (0.00 sec) mysql> create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(OLD.key);Query OK, 0 rows affected (0.00 sec) mysql> insert into memcached values("keyi", "valuei"),("keyu","valueu"),("keyd", "valued");                                         Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0 mysql> update memcached set `value`="update" where `key`="keyu";Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0 mysql> delete from memcached where `key`="keyd";Query OK, 1 row affected (0.00 sec) mysql> quitBye

Memcache viewing records:

[root@localhost ~]#telnet 127.0.0.1 11211Trying 127.0.0.1...Connected to 127.0.0.1.Escape character is '^]'.get keyiVALUE keyi 0 6valueiENDget keyuVALUE keyu 0 6valueuENDget keydVALUE keyd 0 6valuedENDget keyuVALUE keyu 0 6updateENDget keydENDquitConnection closed by foreign host.

So far, we have basically implemented synchronizing Mysql Data to memcached, and the performance has not yet been tested. Of course, the above is just a simple data ing function, if the implementation of the production environment, you need to consider the namespace, high reliability issues, these are all through the database name-table name-keywords to achieve the unique purpose of key, high reliability is a big problem.

Address: http://www.libing.name/2009/02/06/mysql-map-data-to-memcached.html