Memcached实现MySQL数据同步

戎桐

2023-12-01

一、介绍

1、Memcached介绍

Memcached 是一个高性能的分布式内存对象缓存系统，用于动态Web应用以减轻数据库负载。它通过在内存中缓存数据和对象来减少读取数据库的次数，从而提高动态、数据库驱动网站的速度。Memcached基于一个存储键/值对的hashmap。其守护进程（daemon ）是用C写的，但是客户端可以用任何语言来编写，并通过memcached协议与守护进程通信。

2、Memcache和Redis的比较

由于Redis只使用单核，而Memcached可以使用多核，所以在比较上，平均每一个核上Redis在存储数据时比Memcached性能更高。而在100k以上的数据中，Memcached性能要高于Redis，虽然Redis最近也在存储大数据的性能上进行优化，但是比起Memcached，还是稍有逊色。

二、Memcached安装

MySQL官网Memcached安装参考链接：https://dev.mysql.com/doc/refman/5.6/en/ha-memcached-install.html
百度网盘链接: https://pan.baidu.com/s/1IYlh2Yqf1AUPLru_43upwQ 密码: 8n5c

1、通过yum进行安装

[root@server1 ~]# yum install -y memcached
[root@server1 ~]# /etc/init.d/memcached restart

2、查看端口

[root@server1 ~]# netstat -lntup|grep memcached
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      1069/memcached      
tcp        0      0 :::11211                    :::*                        LISTEN      1069/memcached      
udp        0      0 0.0.0.0:11211               0.0.0.0:*                               1069/memcached      
udp        0      0 :::11211                    :::*                                    1069/memcached

三、MySQL安装

编译安装MySQL博客链接：https://blog.csdn.net/Dream_ya/article/details/81585830！！！
官网下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

1、下载MySQL

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.23-1.el6.x86_64.rpm

2、解压安装

[root@server1 ~]# tar xf mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar
[root@server1 ~]# ls
mysql-5.7.23-1.el6.x86_64.rpm-bundle.tar          mysql-community-embedded-devel-5.7.23-1.el6.x86_64.rpm
mysql-community-client-5.7.23-1.el6.x86_64.rpm    mysql-community-libs-5.7.23-1.el6.x86_64.rpm
mysql-community-common-5.7.23-1.el6.x86_64.rpm    mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm
mysql-community-devel-5.7.23-1.el6.x86_64.rpm     mysql-community-server-5.7.23-1.el6.x86_64.rpm
mysql-community-embedded-5.7.23-1.el6.x86_64.rpm  mysql-community-test-5.7.23-1.el6.x86_64.rpm

[root@server1 ~]# yum install -y mysql-community-client-5.7.23-1.el6.x86_64.rpm  mysql-community-common-5.7.23-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.23-1.el6.x86_64.rpm mysql-community-libs-5.7.23-1.el6.x86_64.rpm mysql-community-server-5.7.23-1.el6.x86_64.rpm

3、进行初始化设置

[root@server1 ~]# /etc/init.d/mysqld restart
[root@server1 ~]# grep "temporary password" /var/log/mysqld.log           ###查看临时密码
2018-08-29T14:43:19.871859Z 1 [Note] A temporary password is generated for root@localhost: 5,Xau;f#mkWw

[root@server1 ~]# mysql_secure_installation           ###密码要有大小写加字符

四、libmemcached安装

注意：使用高版本的会和memcached_functions_mysql不兼容，否则memcached_functions_mysql会失败！！！
libmemcached下载链接：https://launchpad.net/libmemcached/+download

[root@server1 ~]# wget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz
[root@server1 ~]# yum install -y gcc gcc-c++
[root@server1 ~]# tar xf libmemcached-0.34.tar.gz 
[root@server1 ~]# cd libmemcached-0.34
[root@server1 libmemcached-0.34]# ./configure --prefix=/usr/local/libmemcached --with-memcached
[root@server1 libmemcached-0.34]# make && make install

五、安装 memcached_functions_mysql

1、下载地址

memcached_functions_mysql下载地址：https://launchpad.net/memcached-udfs

[root@server1 ~]# wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gz

2、编译安装

[root@server1 ~]# tar xf memcached_functions_mysql-1.1.tar.gz 
[root@server1 ~]# cd memcached_functions_mysql-1.1
[root@server1 memcached_functions_mysql-1.1]# ./configure --with-mysql=/usr/bin/mysql_config --with-libmemcached=/usr/local/libmemcached
[root@server1 memcached_functions_mysql-1.1]# make && make install

六、环境配置

1、查看MySQL的plugin位置

[root@server1 ~]# mysql -u root -pQ1w2e3r4t5y6.
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)

2、拷贝文件到MySQL的 plugin下面

[root@server1 ~]# cp /usr/local/lib/libmemcached_functions_mysql.* /usr/lib64/mysql/plugin/

3、往MySQL添加memcache UDF函数

mysql> source /root/memcached_functions_mysql-1.1/sql/install_functions.sql
mysql> select * from mysql.func;       ###我们可以通过此命令查询添加函数

七、测试

1、测试连接

mysql> select memc_servers_set('10.10.10.1:11211');
+--------------------------------------+
| memc_servers_set('10.10.10.1:11211') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select memc_server_count();
+---------------------+
| memc_server_count() |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select memc_set('m','llppppp');
+-------------------------+
| memc_set('m','llppppp') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select memc_get('m');
+---------------+
| memc_get('m') |
+---------------+
| llppppp       |
+---------------+
1 row in set (0.00 sec)

[root@server1 ~]# yum install -y telnet
[root@server1 ~]# telnet 10.10.10.1 11211             ###可以发现数据库生成的可以访问到
Trying 10.10.10.1...
Connected to 10.10.10.1.
Escape character is '^]'.
get m
VALUE m 0 7
llppppp
END
quit
Connection closed by foreign host.

2、触发器环境准备

（1）新建数据库和表

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table linux(id int not null,name varchar(15) not null,primary key(id));
Query OK, 0 rows affected (0.60 sec)

（2）建立触发器

mysql> delimiter $
mysql> create trigger linux_insert_memc before insert on linux for each row begin set @m=memc_set(NEW.id,NEW.name);
    -> end$
Query OK, 0 rows affected (0.38 sec)

（3）更新数据

mysql> create trigger linux_update_memc before update on linux for each row begin set @m=memc_replace(OLD.id,NEW.name);
    -> end$
Query OK, 0 rows affected (0.47 sec)

（4）删除数据

mysql> create trigger linux_delete_memc before delete on linux for each row begin set @m=memc_delete(OLD.id);
    -> end$
Query OK, 0 rows affected (0.07 sec)

3、连接memcached服务器进行数据测试

（1）插入数据测试

mysql> insert into linux value('1','aaa');
mysql> insert into linux value('2','bbb');

[root@server1 ~]# telnet 10.10.10.1 11211
Trying 10.10.10.1...
Connected to 10.10.10.1.
Escape character is '^]'.
get 1
VALUE 1 0 3
aaa
END
get 2
VALUE 2 0 3
bbb
END
quit
Connection closed by foreign host.

（2）更改数据测试

可以发现自动改变为dream！！！

mysql> update linux set name='dream' where id=1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@server1 ~]# telnet 10.10.10.1 11211
Trying 10.10.10.1...
Connected to 10.10.10.1.
Escape character is '^]'.
get 1
VALUE 1 0 5
dream
END
quit
Connection closed by foreign host.

4、删除数据

mysql> delete from linux where id=1;
Query OK, 1 row affected (0.36 sec)

[root@server1 ~]# telnet 10.10.10.1 11211       ###可以发现数据已被删除
Trying 10.10.10.1...
Connected to 10.10.10.1.
Escape character is '^]'.
get 1
END
quit
Connection closed by foreign host.