Improving data migration efficiency from MySQL to Redis

Developers know that once the design is optimized to the underlying storage, data structures, and even database changes, data migration is usually performed. If the system runs too long, the number of data migration may be very large. At this time, how to perform efficient data migration is actually one of the direct influencing factors of the online quality. The following content is a small reprinted

Developers know that once the design is optimized to the underlying storage, data structures, and even database changes, data migration is usually performed. If the system runs too long, the number of data migration may be very large. At this time, how to perform efficient data migration is actually one of the direct influencing factors of the online quality. The following content is a small reprinted

Developers know that once the design is optimized to the underlying storage, data structures, and even database changes, data migration is usually performed. If the system runs too long, the number of data migration may be very large. At this time, how to perform efficient data migration is actually one of the direct influencing factors of the online quality.

The following is a reprinted tip (the original article), which cannot be applied to various scenarios and is for your reference only.

The scenario is to import data from MySQL to the Hash structure of Redis. Of course, the most direct approach is to traverse MySQL Data and write data to Redis one by one. There may be no errors, but the speed will be very slow. However, if MySQL's query and output data can be directly consistent with the input data protocol of the Redis command line, it may be much easier.

Based on all tests, his data migration time was shortened from 90 minutes to 2 minutes.

The following is a detailed case.

MySQL DATA table structure:

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));

Redis storage structure:

HSET events_all_time [action] [count]

The following is the focus. You can use the following SQL statement to directly change the MySQL output to a format that can be received by redis-cli:

-- events_to_redis.sqlSELECT 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

Then, use the pipeline to redirect the output:

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

Zone 42 VPS

The 42qu.com cloud host is sold to entrepreneurs. Click here to view details

Warning: Call_user_func_array () expects parameter 1 to be a valid callback, function 'embed _ rssfooter 'not found or invalid function name in /Home/b55/htdocs/blog.nosqlfan.com/wp-appsdes/plugin.phpOn line 166