Map Mysql Data to memcached

ArticleDirectory

• February 26 th, 2008

Record & Share

Map Mysql Data to memcached February 26 th, 2008

Author: Zhang Libing
Source: http://www.libing.name/2008/02/26/mysql-map-memcached.html

This article describes how to trigger a MySQL udfs for memcachedProgramA Method of ing Mysql Data to memcached can be used to update memcache data in real time when MySQL data is updated, thereby reducing the development of corresponding programs to maintain memcached data.

Official introduction to MySQL udfs for memcached:

This is a set of MySQL udfs (User Defined Functions) to work with memcached using libmemcached. with these functions you get, set, append, prepend, delete, increment, decrement objects in memcached, as well as set which servers to use and which behavior the server connections will use. combine these functions with MySQL triggers and you can manage your memcached cache. the library makes use of libmemcached. you can also use memcached as a global sequence generator for MySQL by making use of the increment function. these functions are compatible with all versions of MySQL.

Install the following software and Development kits during testing:

MySQL 5.0 +
Memcached (libevent ...)
Libmemcached

Download udfs: http://download.tangent.org/memcached_functions_mysql-0.2.tar.gz

Compile and install:

Tar xzvf memcached_functions_mysql-0.2.tar.gz
CD memcached_functions_mysql-0.2
./Configure-with-mysql = mysql_dir/bin/mysql_config-libdir = mysql_dir/lib/MySQL/
Make
Make install

After the installation is complete, load the udfs into MYSQL:

Memc_servers_set ()
Mysql> Create Function memc_servers_set returns int soname "libmemcached_functions_mysql.so ";

Memc_set ()
Mysql> Create Function memc_set returns int soname "libmemcached_functions_mysql.so ";

Memc_get ()
Mysql> Create Function memc_get returns string soname "libmemcached_functions_mysql.so ";

Memc_delete ()
Mysql> Create Function memc_delete returns string soname "libmemcached_functions_mysql.so ";

Multiple rows are omitted here ......
Test whether the UDF is successfully installed:
Add memcached first. You can add multiple memcached instances.

Mysql> select memc_servers_set ('2017. 0.0.1 ′);
Mysql> select memc_set ('libing ', 'roast ');
+ ---------- +
| Memc_set ('libing', 'roast ') |
+ ---------- +
| 0 |
+ ---------- +
1 row in SET (0.00 Sec)
Mysql> select memc_get ('libing ');
+ ----------- +
| Memc_get ('libing ') |
+ ----------- +
| Roast |
+ ----------- +
1 row in SET (0.00 Sec)

The test maps Mysql Data to memcached. If you are not familiar with the MySQL trigger program, refer to Chapter 21st of the MySQL manual.

Mysql> Create Table memcached ('key' int, 'value' varchar (100 ));
Query OK, 0 rows affected (0.02 Sec)

Mysql> Create trigger mysqlmmc before insert on memcached for each row
> Set @ TMP = memc_set (New. Key, new. value );
Query OK, 0 rows affected (0.00 Sec)

Mysql> insert into memcached values (9, 'roast ');
Query OK, 1 row affected (0.01 Sec)

Mysql> select memc_servers_set ('2017. 0.0.1 ′);
+ ----------- +
| Memc_servers_set ('2017. 0.0.1 ') |
+ ----------- +
| 1, 58360307675824128 |
+ ----------- +
1 row in SET (0.00 Sec)

Mysql> select memc_get ('9 ′);
+ ----- +
| Memc_get ('9') |
+ ----- +
| Roast |
+ ----- +
1 row in SET (0.00 Sec)

[Root @ Nd-ZF-mx ~] # Telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to ND (127.0.0.1 ).
Escape Character is '^]'.
Get 9
Value 9 0 5
Roast
End

the memcached trigger program is synchronously updated when a new record of the database is generated.
memcached needs to be updated synchronously when the database is updated or deleted to achieve the ing relationship.