Memcached and MySQL data synchronization

1. Introduction

In a production environment, we often use MySQL as the database for the application. However, as the number of users increases, we will naturally choose Memcached as the cache database, thus reducing the pressure on MySQL. However, memcached maintains data synchronization among users, applications and MySQL.

For example, the user exchanges some data from the memcached cache and executes the delete command. It needs to be deleted in MySQL, and then you need to design a program to delete the corresponding data of Memcached.

If we can do the automatic deletion of the corresponding data in memcached when adding, deleting, and changing in MySQL, then it is not beautiful. This of course can be done, we will adopt the MySQL Memcached UDF (User Defined Functions) method.

Mysql memcached UDF is actually a series of functions that use memcache through libmemcached. With these functions, you can get, get, set, cas, append, prepend, delete, increment, decrement objects on memcache. If we use mysql trigger to use these Function, then you can manage memcache better and more automatically through mysql!

2. Software Installation

Introduction:

The installation of these two softwares is more painful. After all, it is open source software, which version depends on which version, and which version is not compatible with which version. At the time I installed the two software for one morning. Because I used to install the PHP memcached extension module to rely on this software library, so I installed a new version of libmemcached-1.0.18, so I can use all the functions in PHP’s memcached extension module. ,

But when you install memcached_functions_mysql, it is very painful.

At the beginning I installed memcached_functions_mysql-1.1 directly, and the following error occurred when I went to make.

Servers.c:122: error: ‘memcached_st’ has no member named ‘hosts’
servers.c:123: error: ‘memcached_st’ has no member named ‘hosts’
servers.c:124: error: ‘memcached_st’ has no member Named ‘hosts’
servers.c:126: error: ‘memcached_st’ has no member named ‘hosts’

What is the reason for this?

This is the software compatibility issue mentioned above.

Later on the Internet, I saw that this version of memcached_functions_mysql-1.1 is compatible with libmemcached-0.37 and above.

Then I will test again and again:

Libmemcached (version 1.0.2, 1.0.3, 1.0.18, 0.34, 0.36) + memcached_functions_mysql-1.1 for installation. The result was only 0.34, 0.36.

Because I want to guarantee a relatively high version of libmemcached , because the lower version does not support some of the new features of PHP ‘s memcached extension.

Since the memcached_functions_mysql-1.1 version does not correspond to the high version of libmemcached, then I will retreat to the next, using memcached_functions_mysql-0.9.

Baidu has not been looking for a long time, and finally found in the foreign website github.

Then install the memcached_functions_mysql-0.9 test, the results are still not ideal.

Suddenly a flash of light, then I installed two libmemcached, a version 1.0.18, a version of 0.34. The final successful installation. ( libmemcached-0.34 is paired with memcached_functions_mysql-1.1 for installation)

1): Install libmemcached-0.34

2): Install memcached_functions_mysql-1.1

# tar zxvf memcached_functions_mysql-1.1.tar.gz 
# cd memcached_functions_mysql-1.1 
# ./configure --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached 
# make 
# make install

3): Copy lib files to MySQL plugin the following

# cp /usr/local/lib/libmemcached_functions_mysql.* /usr/local/mysql/lib/plugin/

4): Add memcache UDF function to MySQL

There are two ways:

1. Execute source install_functions.sql in MySQL

mysql>source /home/username/(memcached_functions_mysql-1.1 source path) /sql/ install_functions.sql

2. execute mysql <sql/install_functions.sql on the Linux host

# /usr/local/mysql/bin/mysql < /home/username/(memcached_functions_mysql-1.1 source path) /sql/ install_functions.sql

5): Check if the add function is successful

mysql> select * from mysql .func;

The result shows that there are many functions that indicate that the function was added successfully.

Note:

1. must have — with-libmemcached, otherwise it will show that libmemcached can not be found

2. there is a small detail, is often installed and uninstall libmemcached (that is, — prefix has the same name), even if there is — — — memecached the correct path, it may show that libmemcached can not be found.

Solution: You can use a different name ( — prefix); or restart linux

3. Function Introduction

From the results of select * from mysql.func; we can see a lot of functions about memcached. Students who are familiar with the memcached command can see the approximate function of this function at a glance. Memc_set, memc_get This is not quite a set and get command.

Specific functions can refer to the documentation:

Http://dev.mysql.com/doc/refman/5.1/en/ha-memcached-interfaces-mysqludf.html

Simply test these functions:

# Of course, before you test, you must first establish a connection between MySQL and memcached! 
 mysql> select memc_servers_set('192.168.95.11:11211' );
 mysql> select memc_server_count();
 mysql> select memc_set('m','llppppp' );
 mysql> select memc_get('m');

Telnet 192.168.95.11 11211

4. Example test

1. create a new database test233 and table tab1

2. establish three triggers

Insert data:
 mysql> delimiter $
 mysql> create trigger tab1_insert_memc
     -> before insert on tab1
     -> for  each row begin
     -> set @m=memc_set( NEW .id, NEW . name);   
     -> end $
Update data:
mysql> create trigger tab1_update_memc
    -> before update on tab1
    -> for  each row begin
    -> set @m=memc_replace(OLD.id, NEW . name);   
    -> end $
Delete data:
mysql> create trigger tab1_delete_memc
    -> before delete on tab1 
    -> for  each row begin
    -> set @m=memc_delete(OLD. id);
    -> end $
mysql> delimiter;

3. connect memcached server for data testing

mysql> select memc_servers_set('192.168.95.11:11211');

1) Insert several pieces of data into tab1 and view the results

mysql> insert into tab1 value('1','1111aaaaff');

Telnet 192.168.95.11 11211

2) change the data

mysql> update tab1 set name='ooooooooooooooo' where id=1;

Telnet 192.168.95.11 11211

3) delete data

mysql> delete from tab1 where id=1;

Telnet 192.168.95.11 11211

5. Summary recommendations

1. if the mysql service restart, you need to reconnect the memcached server select memc_servers_set (‘192.168.95.11:11211)

2. use the command to view the parameters that can be set, according to the specific project settings the most appropriate parameters select memc_list_behaviors () / G

3. Set MEMCACHED_BEHAVIOR_NO_BLOCK to the open state, indicating that data can be inserted into MySQL when there is a problem with memcached. Otherwise it will wait until timeout performs the insert operation.

4. when using this feature in MySQL, do not blindly arrange all the table data in this form, to avoid the use of too many functions and triggers in MySQL and affect performance.