One step to complete MySQL migration to Redis

Source: Internet
Author: User
Tags bulk insert mysql query mysql command line

Moving a big table from MySQL to Redis, you'll find that the slow speed you get when extracting, converting, or loading a row of data is unbearable. I'm going to tell you a little trick to get you out of here. Use the "Pipeline output" method to pass the contents of the MySQL command line directly to the REDIS-CLI, in order to bypass the "middleware" way to make the two in the data operation to achieve the best speed.

A MySQL table of about 8 million rows of data, originally imported into Redis takes 90 minutes, and it takes only two minutes to use this method. 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 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:

?
1234567 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 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:

?
123456789101112131415161718192021 -- 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_timeAS 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.

One step to complete MySQL migration to Redis

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.