数据库迁移之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"
