A brief talk on the coupling between Redis and MySQL and the efficient migration of MySQL to Redis using pipelines

http://blog.csdn.net/dba_waterbin/article/details/8996872

㈠redis and MySQL coupling


Early in the business architecture, we should "eat in the bowl and look at the pot", don't let MySQL have a dream, and Redis unintentionally
After all, some relational structure is not suitable for redis run, "Male and female collocation, work not tired", recommended to let MySQL and Redis knot

Secondly, these 2 people, generally in different scenarios do choose, but not in the performance of the choice,
Only in the case of 2, comprehensive performance, hardware cost, operation and maintenance costs, and other options
For example, Web game enable Redis+mysql:
In-game: Buddy relationships, leaderboards, counters, queues, caches are all well suited for Redis

Another example is the architecture of Sina Weibo, such as user-focused relationships:

In MySQL is a < fan, the person of concern > such a row is stored. And in Redis you can save as a set, or zset, etc.

The general process is copied from MySQL to Redis.
The basic structure should be:
1. Tweet--> enter Message Queue--> Deposit mysql--> Copy to Redis
2. Query--> Query Cache--> Query Redis--> query MySQL



㈡ Fast Migration Mysql→→redis

①mysql the table to be exported David_lin

[Plain]View Plaincopyprint?

2. mysql> desc David_lin;
4. +---------+-------------+------+-----+---------+-------+
6. | Field | Type | Null | Key | Default | Extra |
8. +---------+-------------+------+-----+---------+-------+
10. | ID | Int (11) | NO | PRI |       NULL | |
12. | MyName | varchar (25) | NO | UNI |       NULL | |
14. | Mymoney | Int (11) |     NO | |       0 | |
16. +---------+-------------+------+-----+---------+-------+
20. Mysql> select * from David_lin;
22. +----+--------+---------+
24. | ID | MyName | Mymoney |
26. +----+--------+---------+
28. | 1 |  David | 100000 |
30. | 2 |  Rocky | 200000 |
32. +----+--------+---------+

② writing an export script

The Redis commands executed in each row of data are as follows:
Hset David_lin [myname] [Mymoney]

[Plain]View Plaincopyprint?

2. [email protected] ~]# cat Mysql_to_redis.sql
4. SELECT CONCAT (
6. "*4\r\n",
8. ' $ ', LENGTH (redis_cmd), ' \ r \ n ',
10. Redis_cmd, ' \ r \ n ',
12. ' $ ', LENGTH (redis_key), ' \ r \ n ',
14. Redis_key, ' \ r \ n ',
16. ' $ ', LENGTH (hkey), ' \ r \ n ',
18. HKEY, ' \ r \ n ',
20. ' $ ', LENGTH (hval), ' \ r \ n ',
22. Hval, ' \ R '
24. )
26. From (
28. SELECT
30. ' Hset ' as Redis_cmd,
32. ' David ' as Redis_key,
34. MyName as HKEY,
36. Mymoney as Hval
38. From David_lin
40. ) as T

③ Start Import

[Plain]View Plaincopyprint?

1. [Email protected] ~]# mysql-uroot-poracle test--skip-column-names--raw < Mysql_to_redis.sql | REDIS-CLI--pipe
2. All data transferred. Waiting for the last reply ...
3. Last reply received from server.
4. errors:0, replies:0

④ in Redis query

[Plain]View Plaincopyprint?

1. Redis 127.0.0.1:6379> Hgetall David
2. 1) "David"
3. 2) "100000"
4. 3) "Rocky"
5. 4) "200000"

Here is only a demo, a small amount of data, but, look at the results, some similar rows to row ha, column operations, there are wood:)

A brief talk on the coupling between Redis and MySQL and the efficient migration of MySQL to Redis using pipelines