One step to complete MySQL migration to Redis

When migrating a large table from MySQL to Redis, you may find that every time you extract, convert, and import a single piece of data, it's unbearable to be slow! Here's a tip that you can use the pipeline to input MySQL output directly to the REDIS-CLI input, which allows two databases to run at their top speed.

Using this technique, I've reduced the time it takes to import 8 million MySQL data into Redis from 90 minutes to two minutes.

MySQL-to-redis data protocol

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

My Table of statistics:

2. CREATE TABLE events_all_time (
4. ID int (one) unsigned not NULL auto_increment,
6. Action varchar (255) is not NULL,
8. Count Int (one) not NULL DEFAULT 0,
10. PRIMARY KEY (id),
12. UNIQUE KEY uniq_action (action)
14. );

The Redis commands that are prepared to execute in each row of data are as follows:

1. hset Events_all_time [action] [count]

Follow the Redis command rules above to create a events_to_redis.sql file that is used to generate the SQL for the Redis Data protocol format:

2. --Events_to_redis.sql  
6. SELECT CONCAT (
8. "*4\r\n",
10. ' $ ', LENGTH (redis_cmd) , ' \ r \ n ',
12. Redis_cmd, ' \ r \ n ' ,  
14. ' $ ', LENGTH (Redis_key) , ' \ r \ n ',
16. Redis_key, ' \ r \ n ' ,  
18. ' $ ', LENGTH (hkey) , ' \ r \ n ',
20. hkey, ' \ r \ n ' ,  
22. ' $ ', LENGTH (hval) , ' \ r \ n ',
24. Hval, ' \ r '  
26. )
28. from  (  
30. SELECT
32. ' hset ' as Redis_cmd,
34. ' events_all_time ' as Redis_key,
36. action as hkey,
38. count as hval
40. from events_all_time
42. ) as t

OK, execute with the following command:

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

Important MySQL Parameter description:

--raw: Make MySQL do not convert the line break in field values.

--skip-column-names: Does not include the column name in each line of MySQL output.

Redis Data Bulk Import Export

one way to do this is to export and import Redis data from one server to a new server for work, but he needs to install the Ruby environment, and there may be unexpected errors during the installation of the REDIS-DUMP environment. So we have to choose other methods. A moment is a few ideas for reference.

1, data export, do not write their own, and do not use third-party scripts,
echo "Hgetall xxx" | redis-cli-h localhost-p 6379
echo "Hgetall xxx" | redis-cli-h localhost-p 6379 >> wordlist.raw
2. So get the result that you can go up to the server to view the Wordlist.raw file
3. Organize the file format into the input required
$f = ' xx.oo ';
$lines = 0;
$inkey = 0;
$rawfile = ' xx.oo ';
$hashkey = ' xx ';
$buff = ";
$fp = fopen ($rawfile, ' W ');
$fps = fopen ($f, ' R ');
While ($line = fgets ($fps)) {
$inkey =! $inkey;
if ($inkey) {
$f = ' Bayes_wordlist.raw ';
$lines = 0;
$inkey = 0;
$rawfile = ' Bayes_wordlist.3.raw ';
$hashkey = ' bayes_wordlist ';
$buff = ";
$fp = fopen ($rawfile, ' W ');
$fps = fopen ($f, ' R ');
While ($line = fgets ($fps)) {
$inkey =! $inkey;
if ($inkey) {
$line = sprintf (' "%s" ', Trim ($line));
$buff = "Hset $hashkey". Trim ($line);
  }
Else
 {
$buff. = ' "'. Trim ($line). '";
fwrite ($fp, $buff. " \ r \ n ");
 }
}
$buff = "Hset $hashkey". Trim ($line);
 }
Else
 {
$buff. = ' "'. Trim ($line). '";
fwrite ($fp, $buff. " \ r \ n ");
 }
}
If you choose which library to write to select X in the first row
4. Using REDIS-CLI for import
echo ' Date ' > Pipe.log && cat xx.oo | redis-cli >> pipe.log && echo ' Date ' >> pipe.log< /c5>
5. Add a time record, and record the import results, the actual execution of the imported statement is
Cat Wordlist.raw | redis-cli, of course, strict redis-cli also add-h localhost-p 6379 parameters.