Map Mysql Data to memcached

ArticleDirectory

• February 6 th, 2009
• You may also like

Map Mysql Data to memcached February 6 th, 2009

Author: Zhang Libing
Source: http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html

I wrote an article about it almost a year ago.Map Mysql Data to memcachedAt that time, MySQL and memcached functions for MySQL were not mature enough. One year later,Memcached functions for MySQLAfter upgrading to version 0.8, MySQL also released the GA version. In addition, many friends reflected that the implementation in the previous article was not successful for various reasons, so this article was published, 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:

Mysql-5.1.30Download
Memcached-1.2.6Download
Libevent-1.4.7-stableDownload
Memcached_functions_mysql-0.8Download
Libmemcached-0.26Download

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-M50 -U Root-P11211
 

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 the source code directory.Readme.

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 to implement this function. If MySQL is triggeredProgramIf you are not familiar with it, refer to MySQL Manual Chapter 21st. memcached in the following SQL statement is the name of the table to be operated. The SQL statement is as follows:

 # Insert memcached when inserting data Create trigger mysqlmmci after insert on memcached For Each row set @ TMP = Memc_set ( New. Key  , New. Value  )  ;  # Update memcached when updating records Create trigger mysqlmmcu after update on memcached For Each row set @ TMP = Memc_set ( New. Key  , New. Value  )  ;  # Deleting a memcached 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 ; Or \ G. Your MySQL connection ID is 6 Server 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 11211  trying 127.0.0.1...  connected  to 127.0.0.1.  escape  character is  '^]' .  Get  keyivalue keyi  0   6  valueiendget keyuvalue keyu  0   6  valueuendget keydvalue keyd  0   6  valuedendget keyuvalue keyu  0   6  updateendget 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.

You may also like