A simple database migration method from MySQL to Redis _redis

Moving a large table from MySQL to Redis, you'll find it hard to tolerate the slow pace of fetching, converting, or loading a row of data. Here I'm going to tell you a little trick that frees you up. Use pipe output to pass the contents of the MySQL command line directly to Redis-cli to bypass the "middleware" approach so that the two can achieve the best possible speed in data manipulation.

A MySQL table of about 8 million rows of data, originally imported into the Redis takes 90 minutes, after using this method, only need two minutes. Whether you believe it or not, I believe it anyway.

MySQL to Redis data protocol

The Redis-cli command-line tool has a bulk insert pattern designed specifically for batch execution commands. This first step is to format the contents of the MySQL query into REDIS-CLI available data formats. Here we go!

My statistics:

CREATE TABLE events_all_time (
 ID int (one) unsigned not NULL auto_increment,
 action varchar (255) NOT NULL,
 cou NT Int (one) not NULL DEFAULT 0,
 PRIMARY key (ID),
 UNIQUE key uniq_action (action)
;

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

Hset Events_all_time [Action] [count]
Follow the Redis command rules above to create a Events_to_redis.sql file that contains the SQL used to generate the Redis Data protocol format:

--Events_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,
 ' Events_all_time ' as Redis_key,
 action As HKEY,
 count as Hval from
 events_all_time
) as T

OK, execute with the following command:

MySQL stats_db--skip-column-names--raw < Events_to_redis.sql | REDIS-CLI--pipe

Very important MySQL parameter description:

• --raw: Make MySQL do not convert newline characters in field values.
• --skip-column-names: Do not include column names in each row of the MySQL output.