Redis as a cache server for MySQL (read/write separation, data synchronization via MySQL triggers)

I. Introduction of Redis
Redis is a key-value storage system. Similar to memcached, data is cached in memory in order to ensure efficiency. The difference is that Redis periodically writes the updated data to disk or writes the modified operation to the appended record file, and Master-slave (Master-Slave) synchronization is implemented on this basis. In some cases can be a good complement to the relational database. It provides java,c/c++ (Hiredis), C#,php,javascript,perl,object-c,python,ruby and other clients, which is convenient to use.

Second, the structure diagram
<ignore_js_op>
The approximate structure is read-write separation, synchronizing data from MySQL to Redis via triggers


Third, install LNMP environment (here for the sake of convenience, is to use Yum to install)

1. Modify the Yum source

1 2 3 4 5 6 7 8 9 10 11 12 13

[Email protected] ~]# vim/Etc/yum.repos.d/epel.repo #添加这个文件 [Epel] Name=extra Packages for Enterprise Linux 6-$basearch baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch Failovermethod=priority Enabled=1 Gpgcheck=0 [Nginx] Name=nginx Repo baseurl=http://nginx.org/packages/centos/6/$basearch/ Gpgcheck=0 Enabled=1

2. Yum Installation

1	[[email protected] ~]# yum-y install nginx php php-fpm php-cli php-common php-gd php-mbstring php-mysql php-pdo p Hp-devel php-xmlrpc php-xml php-bcmath php-dba php-enchant MySQL mysql-server

3, simple configuration of Nginx

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22

[Email protected] ~]# vim/etc/nginx/nginx.conf server { Listen 80; #定义使用www. xx.com Access server_name www.xx.com; #设定本虚拟主机的访问日志 Access_log/logs/www.xx.com.access.log main; #默认请求 Location/{ root/www/; Default Web site root location for #定义 server Index index.php index.html index.htm; #定义首页索引文件的名称 } Location ~ \. php$ { root/www/; Fastcgi_pass 127.0.0.1:9000; Fastcgi_index index.php; Fastcgi_param script_filename/www/$fastcgi _script_name; Include Fastcgi_params; } }

4. Start the service

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23

[Email protected] ~]# sed-i ' s/apache/nginx/g '/etc/php-fpm.d/www.conf [[email protected] ~]#/etc/init.d/php-fpm start is startingPHP-FPM: [OK] [[email protected] ~]#/etc/init.d/mysqld start starting  MYSQLD: [OK] [[email protected] ~]# mkdir/{logs,www} [[email protected] ~]# chown-r Nginx:nginx/{logs,www} [[email protected] ~]#/etc/init.d/nginx start is starting nginx: [OK] [[email protected] www]# service iptables stop iptables:flushing firewall rules: [OK] Iptables:set Ting chains to policy accept:filter [OK] iptables:unloading modules: [OK] [[email protected] redis]# NETSTAT-TNLP #查看监听 Active Internet connections (only servers) Proto recv-q send-q Local Address Foreign address State pid/program name  TCP 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 2101/nginx  TCP 0 0 12 7.0.0.1:9000 0.0.0.0:* LISTEN 7544/php-fpm  TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1871/mysqld span>

5. Give MySQL authorization

1 2 3 4

[[email protected] ~]# MySQL Mysql> Grant all privileges on * * to [e-mail protected] identified by ' 123456 '; mysql> flush Privileges;

6. Testing

1 2 3 4

[Email protected] ~]# vim/www/index.php <?php Phpinfo (); ?>

Then visit the page to see the information about PHP, the basic environment even if the building is complete.

Iv. installation of Redis

1. Installing Redis

1 2 3 4 5 6 7 8 9 11

[[email protected] ~]# wget-c-t 0 http://download.redis.io/releases/redis-2.8.19.tar.gz< br> [[email protected] ~]# mkdir/usr/local/redis [[email protected] ~]# tar xvf redis-2.8.19.tar.gz  #安装很简单, direct make is available [[email protected] ~]# cd redis-2.8.19 [[email  Protected] redis-2.8.19]# make  #编译完成后, copy the executable file in src to the directory you just created [[email protected] src]# CP Redis-benchmark redis-check-aof redis-check-dump redis-cli redis-sentinel redis-server /usr/ local/redis/ [[email protected] redis-2.8.19]# cp redis.conf sentinel.conf/usr/local/redis/

Redis-benchmark pressure test tool
Redis-check-aof Check the integrity of the Redis Persistence command file
Redis-check-dump Check the integrity of the Redis persisted data file The
redis-cli Redis client on Linux
Redis-sentinel Redis-sentinel is a cluster management tool that is primarily responsible for master-slave switching.
Daemon launcher for Redis-server redis server

2, Redis extension for PHP installation

1 2 3 4 5 6 7 8 9 11 +

[[email protected] ~]# wget-c-t 0 https://github.com/owlient/phpredis/ Archive/master.zip [[email protected] ~]# unzip master.zip [[email protected] ~]# CD phpredis-master/ [[Email protected] phpredis-master]# phpize  [[Email  protected] phpredis-master]#./configure--with-php-config=/usr/bin/php-config [[email protected] phpredis-master]# make && make install  #修改php的配置文件, if there is no "extension=redis.so", add this line [[Email  protected] ~]# vim/etc/php.ini  extension=redis.so [[email protected] ~]#/ etc/init.d/php-fpm restart Stop php-fpm: [OK] starting  PHP-FPM: [OK]

3, whether the installation was successful

or the interface to access Phpinfo

<ignore_js_op> 
See this is the installation is complete.

v. Read/write separation
Here is simply a read, no write operation of the relevant code, after a test, directly into the database to perform the update to simulate the write operation.
1. Insert some test data in MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[Email protected] ~]# mysql-u root-p123456 mysql> CREATE DATABASE mytest; mysql> CREATE TABLE ' test ' (' id ' int (7) NOT NULL auto_increment, ' name ' char (8) DEFAULT NULL, PRIMARY KEY (' id ')) engin E=innodb auto_increment=10 DEFAULT Charset=utf8; mysql> INSERT into ' Test ' VALUES (1, ' Sven '), (2, ' Jim '), (3, ' ' Zhu '), (4, ' Wang '), (5, ' FTD '), (6, ' Test '), (+ +, ' test01 '), (7, ' test02 '), (9, ' test03 '); Mysql> select * from Mytest.test; +----+--------+ | ID | name | +----+--------+ | 1 | Sven | | 2 | Jim | | 3 | Zhu | | 4 | Wang | | 5 | FTD | | 6 | Test | | 7 | test01 | | 8 | test02 | | 9 | test03 | +----+--------+

2, write the test code of PHP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

<?Php $redis = new Redis (); $redis->connect (' 127.0.0.1 ', 6379) or die ("could net connect Redisserver "); $query = "SELECT * FROM Test limit 8"; //For the sake of simplicity, there are 8 data for ($key = 1; $key < 9; $key + +) { if (! $redis->get ($key)) { $connect = mysql_connect (' 127.0.0.1 ', ' root ', ' 123456 '); mysql_select_db (mytest); $result = mysql_query ($query); //If $key is not found, the query sql results to Redis while ($row = Mysql_fetch_assoc ($ Result) { $redis->set ($row [' id '], $row [' name ']); } $myserver = ' mysql '; Break } Else { $myserver = "Redis"; $data [$key] = $redis->get ($key); } } Echo $myserver; echo "<br>"; for ($key = 1; $key < 9; $key + +) { echo "number is <b><font color= #FF0000 > $key </font></ B> "; Echo "<br>"; echo "name is <b><font color= #FF0000 > $data [$key]</font></b>"; Echo "<br>"; } ?

First access, no corresponding key in Redis
<ignore_js_op> 
re-access, at which time data is available in Redis
<ignore_js_op>  
Here, we have implemented Redis as the MySQL cache server, but if the update Mysql,redis still have the corresponding key, the data will not be updated, there will be MySQL and redis data inconsistencies. So the next step is to sync the changed data to Redis via MySQL triggers.

Vi. implementing synchronization via Gearman

1, Introduction

Gearman is a distributed framework for distributing tasks:
Gearman Job Server:gearman core Programs, The installation needs to be compiled and run in the background as a daemon.
Gearman Client: Can be understood as a requester of a task.
Gearman worker: The real performer of a task typically needs to write its own logic and run it through the daemon, Gearman the worker receives the contents of the Gearman client-delivered task, which is processed sequentially.

Approximate process: The MySQL trigger to be written under

is equivalent to the Gearman client. To modify a table, inserting a table is equivalent to sending a task directly. The relational data is then mapped to a JSON format through the Lib_mysqludf_json UDF library function, then the task is added to the Gearman task queue through the GEARMAN-MYSQL-UDF plug-in, and finally through the Redis_ worker.php, the worker side of Gearman, completes the Redis database update.

2, installation startup

1 2 3 4 5

[Email protected] ~]# yum-y install Gearmand libgearman-devel [[email protected] ~]#/etc/init.d/gearmand start Starting Gearmand: [OK] [Email protected] ~]#/etc/init.d/gearmand status Gearmand (PID 7702) is running ...

3, install PHP gearman extension

1 2 3 4 5 6 7 8 9 11 +

[[email protected] ~]# wget-c-t 0 https://pecl.php.net/get/ Gearman-1.1.1.tgz [[email protected] ~]# tar xvf gearman-1.1.1.tgz  [[email protected] ~]# CD gearman-1.1.1 [[email protected] gearman-1.1.1]# phpize  [[email protected] gearman-1.1.1] #./configure--with-php-config=/usr/bin/php-config [[email protected] gearman-1.1.1]# MAKE&NBSP [[email protected] gearman-1.1.1]# make install #如果php的配置文件中没有extension = gearman.so, add this line [[email protected] ~]# vim/etc/php.ini  Extension = gearman.so [[email  Protected] ~]#/etc/init.d/php-fpm restart Stop php-fpm: [OK] starting

<ignore_js_op>
So the installation is successful.

4, Installation Lib_mysqludf_json
The Lib_mysqludf_json UDF library function maps relational data to JSON format. In general, the data in a database is mapped in JSON format and is converted by a program.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27

[Email protected] ~]# Wget-c-T 0 https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip [Email protected] ~]# Unzip Master.zip [Email protected] ~]# CD lib_mysqludf_json-master/ [[email protected] lib_mysqludf_json-master]# gcc $ (mysql_config--cflags)-shared-fpic-o lib_Mysqludf_json.so LIB_MYSQLUDF_JSON.C LIB_MYSQLUDF_JSON.C:40:23: Error: my_global.h: no file or directory LIB_MYSQLUDF_JSON.C:41:20: Error: my_sys.h: no file or directory LIB_MYSQLUDF_JSON.C:43:19: Error: mysql.h: no file or directory LIB_MYSQLUDF_JSON.C:44:21: Error: m_ctype.h: no file or directory LIB_MYSQLUDF_JSON.C:45:22: Error: m_string.h: no file or directory #这里编译报错是因为没有安装mysql的开发包, if it is a source-installed MySQL, you need toetc/ld.so.conf.d/ where is the header file for #目录下新建一个文件告诉系统 MySQL [Email protected] lib_mysqludf_json-master]# yum-y install Mysql-devel [[email protected] lib_mysqludf_json-master]# gcc $ (mysql_config--cflags)-shared-fpic-o lib_mysqludf_json.so lib_ Mysqludf_json.c Mysql> show global variables like ' plugin_dir '; +---------------+-------------------------+ | variable_name | Value | +---------------+-------------------------+ | Plugin_dir | /usr/lib64/Mysql/plugin | +---------------+-------------------------+ #将模块拷贝到插件目录下 [email protected] lib_mysqludf_json-master]# CP lib_mysqludf_json.so/usr/lib64/mysql/plugin/ #注册UDF函数 mysql> CREATE FUNCTION json_object RETURNS STRING SONAME ' lib_mysqludf_json.so ';

5, Installation Gearman-mysql-udf
This plugin is used to manage the distributed queues that call Gearman.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24

[Email protected] ~]# Wget-c-T 0 https://launchpad.net/gearman-my ... ysql-udf-0.6.tar.gz [Email protected] ~]# tar xvf gearman-mysql-udf-0.6.tar.gz [Email protected] ~]# CD gearman-mysql-udf-0.6 [Email protected] gearman-mysql-udf-0.6]#/configure--with-mysql=/usr/bin/mysql_config--libdir=/usr/lib64/mysql/plugin/ [[email protected] gearman-mysql-udf-0.6]# make [[email protected] gearman-mysql-udf-0.6]# make install #注册UDF函数 mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME ' libgearman_mysql_udf.so '; mysql> CREATE FUNCTION Gman_servers_set RETURNS STRING SONAME ' libgearman_mysql_udf.so '; #查看函数 Mysql> select * from Mysql.func; +--------------------+-----+-------------------------+----------+ | name | RET | DL | Type | +--------------------+-----+-------------------------+----------+ | Json_object | 0 | lib_mysqludf_json.so | function | | Gman_do_background | 0 | libgearman_mysql_udf.so | function | | Gman_servers_set | 0 | libgearman_mysql_udf.so | function | +--------------------+-----+-------------------------+----------+ #指定gearman的服务信息 mysql> SELECT gman_servers_set (' 127.0.0.1:4730 ');

6, write the MySQL trigger (according to the actual situation)

1 2 3 4 5

DELIMITER $$ CREATE TRIGGER Datatoredis after UPDATE on test for each ROW BEGIN SET @RECV =gman_do_background (' Synctoredis ', Json_object (new.id as ' id ', new.name as ' name ')); end$$ DELIMITER;

7, write the worker side of the Gearman

-

1 2 3 4 5 6 7 8 9 11 + - + - - + - - - - -

-[[[Email protected] ~]# vim/www/redis_worker.php <? PHP $worker = new Gearmanworker (); $worker->addserver (); $worker->addfunction (' Synctoredis ', ' Synctoredis '); $redis = new Redis (); $redis->connect (' 127.0.0.1 ', 6379); while ($worker->work ()); function Synctoredis ($job) { Global $redis; $workString = $job->workload (); $work = Json_decode ($workString); if (!isset ($work->id)) { return false; } $redis->set ($work->id, $work->name); } ? #后台运行 Background process  [[email protected] www] # nohup php redis_ worker.php & "$ redis-> set ($ work-> id, $ work-> name);" This statement is to store the ID as key and name as value separately, and it needs to be consistent with the PHP test code written earlier. 8. Update the data in MySQL mysql> set @RECV = 1; Mysql> Select @RECV; + ------ + | @RECV | + ------ + | 1 | + ------ + mysql> Update Test Set name = 'SSSS' where id = 1; Mysql> Select @RECV; + ------ + | @RECV | + ------ + | NULL | + ------ + As you can see from the return value, the trigger is successful (here @recv is the return value of MySQL Tigger above). We look at the data in Redis: [Email protected] redis] #. / Redis-cli 127.0.0.1:6379> Get 1 "Sven" There's no change in the data here, and that's where we're going wrong. [Email protected] ~] # Vim / var / log / audit / audit.log TYPE = AVC Msg = audit (1427807674.425: 107): avc: denied {name_connect} for pid = 12453 comm = "mysqld" dest = 4730 scontext = u Nconfined_u: system_r: mysqld_t: s0 Tcontext = system_u: o Bject_r: port_t: s0 Tclass = tcp_socket #Seeing such a log, we know that SELinux is preventing synchronization. #Now change the mode of selinux to Permissive [Email protected] ~] # Getenforce Enforcing [Email protected] ~] # Setenforce 0 [Email protected] ~] # Getenforce Permissive Once setup is complete, perform update again and go to Redis for viewing 127.0.0.1:6379> Get 1 "SSSs" Refresh the PHP interface just a bit <ignore_js_op> It's almost done here, as long as application writes the data to MySQL, and the MySQL trigger detects the update and synchronizes the data to Redis via Gearman. Then read it directly from the Redis. Of course it's just an experimental environment, and there's actually a lot of detail to adjust. Redis as a cache server for MySQL (read / write separation, data synchronization via MySQL triggers)