freebsd mysql memcached进行内存缓存

freebsd mysql memcached进行内存缓存

先来实践下吧
ports安装memcached，libmemcache，pecl-memcache一切顺利，真是太方便了！
启动memcached不多说了，一搜一大片，看man也可以
memcached -d -m 128 -u nobody
下载 Jan's UDF  udf_memcache.c
编译
gcc -DDBUG_OFF -I/usr/local/include/ -shared -o udf_memcache.so udf_memcache.c -g -Wall -O2 -L/usr/local/lib/ -lmemcache `mysql_config --cflags`
呵呵，编译gcc的参数还记得一些
有个警告，不管了
执行Jan's create-function-memcache script
抄个测试脚本
测试
php info.php
 -- Test 1. values in memcache before SQL delete: --
row1: will be deleted
row2: will not be
 -- Test 2. values in memcache after SQL delete: --
row1:
row2: will not be
 
哈哈，大功告成，明天再仔细研究
 
测试脚本
Header("Content-type: text/plain");
# ------ configure this:  -----
$mc_host = "localhost" ;
$mc_port = "11211";
$mysql_host = "localhost";  $mysql_user = "root";
$mysql_db = "test";
$mc = new Memcache;$mc->connect($mc_host,$mc_port);
mysql_connect($mysql_host,$mysql_user, "abc123");
mysql_select_db($mysql_db);
# Define the tables
$q[1] = "DROP TABLE table1" ;
$q[2] = "CREATE TABLE table1 (name varchar(30), value varchar(30))";
$q[3] = "CREATE TRIGGER tr1 AFTER INSERT on table1 "."FOR EACH ROW DO memcache_set(\"$mc_host:$mc_port\",NEW.name,NEW.value)";
$q[4] = "CREATE TRIGGER tr2 AFTER DELETE on table1 "."FOR EACH ROW DO memcache_delete(\"$mc_host:$mc_port\",OLD.name)";
# Insert rows into the tables; the trigger will send the data to memcache
$q[5] = "insert into table1 values('row1','will be deleted')";
$q[6] = "insert into table1 values('row2','will not be')";
# run the queries
for($i = 1; $q[$i] ; $i++)
        if(! mysql_query($q[$i])) echo mysql_error() . "\n";
echo "\n -- Test 1. values in memcache before SQL delete: -- \n";
echo "row1: " . $mc->get("row1") . "\n";
echo "row2: " . $mc->get("row2") . "\n";
# This should cause the trigger to delete row1 from memcache
mysql_query("delete from table1 where name='row1'");
echo "\n -- Test 2. values in memcache after SQL delete: -- \n";
echo "row1: " . $mc->get("row1") . "\n";
echo "row2: " . $mc->get("row2") . "\n";
exit();
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
$version = $memcache->getVersion();
echo "Server's version: ".$version."\n";
//phpinfo();?>
 
参考文章:
http://mysqlblog.lenoxway.net/index.php?/archives/3-MySQL-triggers-and-memcache.html
I mentioned a few weeks ago that Jan Kneschke has posted some MySQL user-defined functions -- available here -- for communicating with memcache. I want to outline an example of how to automate cache maintenance by calling these functions from triggers. In a big web application, you have critical high-performance code (which, if you are running memcache, is no doubt already cache-aware), but you probably also have a bunch of little scripts for performing sundry administrative tasks. With triggers in the database, you can keep these scripts the way they are, and even run ad hoc SQL statements, without worrying about leaving inconsistent records in memcache.
This all requires running MySQL 5 with dynamically loaded UDF code -- but of course you don't have to upgrade the whole database farm to MySQL 5, just a single replication slave to run the triggers. On this one slave, create AFTER DELETE and AFTER UPDATE triggers that will delete a record from memcache whenever it gets changed or deleted in the database, like this:
CREATE trigger cache_expire_delete AFTER DELETE on table1 FOR EACH ROW DO memcache_delete("memcache_host:11211",OLD.pk) ;
Here are the details.
First, build Sean Chittenden's libmemcache. (On a GNUish operating system, you need to use BSD make rather than GNU make for this). Then build Jan's UDF (there are notes on doing this in my previous post), and place the udf_memcache.so loadable shared object file into /usr/local/lib/.
Next, log into a dynamically-linked MySQL server (e.g. the mysql-max distribution) as root, and execute Jan's create-function-memcache script. The script contains four CREATE FUNCTION statements, like this:
CREATE FUNCTION memcache_delete RETURNS INTEGER SONAME 'udf_memcache.so';
CREATE FUNCTION memcache_set RETURNS INTEGER SONAME 'udf_memcache.so';
CREATE FUNCTION loads in the shared object file, links the SQL function to the UDF code, and creates a record in the mysql.func system table. If you get error 1126 here, it means that the server could not find the .so file.
The rest of it can be illustrated by this PHP script, showing both INSERT and DELETE triggers in action.