mysql数据库迁移到redis_数据库迁移之mysql-redis.txt

一、mysql迁移数据到redis

关于redis+mysql应用：

微博当然是最大的redis集群了：

总结了基本流程：

1. 发微博– > 进入消息队列– > 存入MySQL– > 复制到Redis

2. 查询 – > 查询缓存– > 查询Redis – > 查询MySQL

yum install -y mariadb-server.x86_64(省事，源码安装)

[root@localhost ~]# systemctl start mariadb.service

[root@localhost ~]# systemctl enable mariadb.service

[root@localhost ~]# mysql

MariaDB [(none)]> create database mysql_redis;

MariaDB [(none)]> use mysql_redis;

MariaDB [mysql_redis]> create table event_all_time (

-> id int(11) unsigned not null auto_increment

-> ,

-> action varchar(255) not null,

-> count int(11) not null default 0,

-> primary key (id),

-> unique key uniq_action(action));

Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql_redis]> show tables;

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

| Tables_in_mysql_redis |

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

| event_all_time |

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

MariaDB [mysql_redis]> desc event_all_time;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) unsigned | NO | PRI | NULL | auto_increment |

| action | varchar(255) | NO | UNI | NULL | |

| count | int(11) | NO | | 0 | |

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

3 rows in set (0.00 sec)

MariaDB [mysql_redis]> insert into event_all_time values(1,"asd",123);

.

.(插入数据 )

.

MariaDB [mysql_redis]> select * from event_all_time;

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

| id | action | count |

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

| 1 | asd | 123 |

| 2 | asdf | 1234 |

| 3 | asdfg | 12345 |

| 4 | asdfhg | 123456 |

| 5 | asdfhgh | 1234567 |

| 6 | asdfhghj | 12345678 |

| 7 | asdfhghjk | 123456789 |

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

7 rows in set (0.00 sec)

%%对于每行数据中执行的 Redis命令如下： HSET events_all_time [action] [count]%%

[root@localhost ~]# vim event_to_redis.sql

-- event_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,

'event_all_time' AS redis_key,

action AS hkey,

count AS hval

FROM event_all_time

) AS t

*4 #表示有4个参数

第一个参数：redis命令

第二个参数：redis键

第三，四个命令：redis值

# 一条语句结束

每行默认以 \r\n 结尾

同时在执行完一行后，以 \r\n 代码一条语句结束

开始转移

[root@localhost ~]# mysql mysql_redis --skip-column-names --raw < event_to_redis.sql | redis-cli -h 192.168.122.137 --pipe

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

Last reply received from server.

errors: 0, replies: 7

源码安装

mysql -uroot -p mysql_redis --skip-column-names --raw < event_to_redis.sql | redis-cli -h 192.168.122.137 --pipe [-a ireanlau ]

很重要的mysql参数说明：

--raw: 使mysql不转换字段值中的换行符。

--skip-column-names: 使mysql输出的每行中不包含列名

-a 加登陆密码

验证

192.168.122.137:6379> hgetall event_all_time

1) "asd"

2) "123"

3) "asdf"

4) "1234"

5) "asdfg"

6) "12345"

7) "asdfhg"

8) "123456"

9) "asdfhgh"

10) "1234567"

11) "asdfhghj"

12) "12345678"

13) "asdfhghjk"

14) "123456789"