Coupling between Redis and MySQL and efficient MySQL migration from MySQL to Redis using pipelines

Coupling between Redis and MySQL and efficient migration from MySQL to Redis using pipelines

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 <粉丝，关注的人> In this way, one row is stored. 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

[Plain]

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 | rock | 200000 |

+ ---- + -------- + --------- +

② Compile the export script

The Redis command executed in each row of data is as follows:

HSET David _lin [myname] [mymoney]

[Plain]

[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

[Plain]

[Root @ odd ~] # Mysql-uroot-poracle test -- skip-column-names -- raw <mysql_to_redis. SQL | redis-cli -- pipe

All data transferred. Waiting for the last reply...

Last reply replicated Ed from server.

Errors: 0, replies: 0

④ Query in Redis

[Plain]

Redis 127.0.0.1: 6379> hgetall david

1) "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

BitsCN.com