One-step migration from MySQL to Redis

When you migrate a large table from MySQL to Redis, you may find that every time you extract, convert, and import a piece of data, it is unbearable! Here is a tip: You can use pipelines to directly input MySQL output to the redis-cli input, which enables both databases to run at their top speed.

Using this technology, I reduced the time for importing 8 million pieces of MySQL Data to Redis from 90 minutes to two minutes.

Data Protocol from Mysql to Redis

The redis-cli command line tool has a batch insert mode, which is specially designed for batch command execution. The first step is to format the Mysql query content into the available data format of redis-cli. Here we go!

My statistical tables:

 
 

  
  
CREATE TABLE events_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)  
  
  
); 
 
 

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

 
 

  
  
HSET events_all_time [action] [count] 
 
 

Follow the above redis command rules to create an events_to_redis. SQL file, which is used to generate an SQL statement in 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, run the following command:

 
 

  
  
mysql stats_db --skip-column-names --raw < events_to_redis.sql | redis-cli --pipe 
 
 

Important mysql parameter descriptions:

-- Raw: prevents mysql from converting line breaks in field values.

-- Skip-column-names: so that each line output by mysql does not contain column names.

Source: http://www.oschina.net/translate/mysql-to-redis-in-one-step

MySQL to Redis in One Step