Coupling Between redis and MySQL and efficient migration from MySQL to redis using Pipelines

(I) coupling between redis and MySQL


In the early stages of the Business architecture, we should "look at the pot in a bowl", don't let MySQL dream, redis does not care
After all, some relational structures are not suitable for running in redis, "working in combination with men and women". We recommend that you link MySQL with redis.

Second, these two people generally make choices in different scenarios, instead of selecting performance,
Only when both are available, comprehensive performance, hardware costs, O & M costs, and other options
For example, enable redis + MySQL for web games:
In the game, friend relationships, rankings, counters, queues, and cache are suitable for implementation through redis.

Another example is the Sina Weibo architecture, such as the user relationship:

In MySQL, it is stored in a row like <fans, followers>. In redis, you can save it as a set or zset.

The general process is replicated from MySQL to redis.
The basic structure should be:
1. Send a microblog --> enter the Message Queue --> Save to MySQL --> copy to redis
2. query --> query cache --> query redis --> query MySQL



(Ii) Fast MySQL migration → redis

① The table David _lin to be exported from MySQL

mysql> desc david_lin;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id      | int(11)     | NO   | PRI | NULL    |       || myname  | varchar(25) | NO   | UNI | NULL    |       || mymoney | int(11)     | NO   |     | 0       |       |+---------+-------------+------+-----+---------+-------+mysql> select * from david_lin;+----+--------+---------+| id | myname | mymoney |+----+--------+---------+|  1 | david  |  100000 ||  2 | rocky  |  200000 |+----+--------+---------+

② Compile the export script

The redis Command executed in each row of data is as follows:
Hset David _lin [myname] [mymoney]

[root@odd ~]# cat mysql_to_redis.sql SELECT CONCAT(  "*4\r\n",  '$', LENGTH(redis_cmd), '\r\n',  redis_cmd, '\r\n',  '$', LENGTH(redis_key), '\r\n',  redis_key, '\r\n',  '$', LENGTH(hkey), '\r\n',  hkey, '\r\n',  '$', LENGTH(hval), '\r\n',  hval, '\r')FROM (  SELECT  'HSET' AS redis_cmd,  'david' AS redis_key,  myname AS hkey,  mymoney AS hval  FROM david_lin) AS t

③ Start Import

[root@odd ~]# mysql -uroot -poracle test --skip-column-names --raw < mysql_to_redis.sql | redis-cli --pipeAll data transferred. Waiting for the last reply...Last reply received from server.errors: 0, replies: 0

④ Query in redis

redis 127.0.0.1:6379> hgetall david1) "david"2) "100000"3) "rocky"4) "200000"

Here is only a demo, a small amount of data, but, look at this result, some similar row-to-column conversion, column operations, and wood :)

By David Lin
2013-05-30
Good lucky