mysql memcached 数据同步_Mysql自动同步memcached

Mysql自动同步memcached并把表原有数据进行初始化到memcached

Mysql通过触发器把表数据更改自动同步到memcached,而且可以实现对mysql表原有数据进行初始化到memcached下.

具体安装略.....

下面是对自己建的you测试表操作

建立insert触发器

delimiter $$

create trigger you_mem_insert before insert on you for each row begin set @tex=memc_set(concat('text_',NEW.id),NEW.text);

set @us=memc_set(concat('user_',NEW.id),NEW.user);

set @ti=memc_set(concat('time_',NEW.id),NEW.time);

end $$

建立update触发器

delimiter $$

create trigger you_mem_update before update on you for each row begin set @tex=memc_set(concat('text_',OLD.id),NEW.text);

set @us=memc_set(concat('user_',OLD.id),NEW.user);

set @ti=memc_set(concat('time_',OLD.id),NEW.time);

end $$

建立delete触发器

delimiter $$

create trigger you_mem_delete before delete on you for each row begin set @tex=memc_delete(concat('text_',OLD.id));

set @us=memc_delete(concat('user_',OLD.id));

set @ti=memc_delete(concat('time_',OLD.id));

end $$

建立完成后退出再登录,进入到you表所在的库.

通过show triggers可查看添加的触发器

添加memcached服务器地址

select memc_servers_set('127.0.0.1:11211');

mysql可以通过select memc_set()来把数据写入到memcached,所以可以对mysql进行初始化,把表已有数据初始化到memcached下,脚本如下(mem.sh) ;

mysql -e "select * from test.you" > 3.txt

k=`cat 3.txt |wc -l`

for((i=2;i<=k;i++))

do

mysql--database test -e "select memc_set('text_`awk -v var=$i'NR==var{print$1}' 3.txt`','`awk -v var=$i'NR==var{print$2}' 3.txt`')"

mysql--database test -e "select memc_set('user_`awk -v var=$i'NR==var{print$1}' 3.txt`','`awk -v var=$i'NR==var{print$3}' 3.txt`')"

mysql--database test -e "select memc_set('time_`awk -v var=$i'NR==var{print$1}' 3.txt`','`awk -v var=$i'NR==var{print$4}' 3.txt`')"

done

运行mem.sh

再telnet到memcached上,可查看到表数据全部初始化到memcached上来了.

后期的就是由mysql自动同步memcached了,但这样子对于应用到生产环境还是不行的.