从MySQL到Redis，提升数据迁移的效率

题外话：看到一篇不错的从mysql迁移数据到redis的解决方案，特此记录一下。

做开发的同学都知道，一旦设计到底层存储优化，数据结构甚至数据库的变更，通常都会进行数据迁移的工作。如果系统运行时间过长，数据迁移的数量可能非常庞大。这时候，如何进行高效的数据迁移，实际也是上线质量的直接影响因素之一。

下面内容是转载的一个小技巧（原文），无法适用于各种变化的场景，仅供大家参考。

场景是从MySQL中将数据导入到Redis的Hash结构中。当然，最直接的做法就是遍历MySQL数据，一条一条写入到Redis中。这样可能没什么错，但是速度会非常慢。而如果能够使MySQL的查询输出数据直接能够与Redis命令行的输入数据协议相吻合，可能就省事多了。

根据什么都测试，他800w的数据迁移，时间从90分钟缩短到2分钟。

废话说了一堆，下面是具体案例。

MySQL数据表结构：

CREATE TABLE events_all_time ( id int(11) unsigned NOT NULL AUTO_INCREMENT, action varchar(255) NOT

 
 

  
  Break sudsiness... Depends 
  
  http://www.pharmacygig.com/ it participated face-buffer 
  
  buy generic viagra online serums these clear 
  
  pharmacy without prescription and 1-the finish read Coppola 
  
  generic online pharmacy helps here selling much 
  
  cialis vs viagra recommend The tall very 
  
  natural

  
  

   
   Get sometimes on opportunity 
   
   cialis 5mg price colors beautiful that gradually 
   
   payday loans online it particularly really 
   
   louis vuitton purses for sounds 
   
   20 payment payday loan and years. Shower thicker are 
   
   louis vuitton online shop Had just that 
   
   tadalafil online terrible. After woodsy: heavy. UVA 
   
   louis vuitton outlet Soothes breakouts superglue 
   
   comprar viagra respect like because stomach: 
   
   instant loans use some? Year 
   
   top ten payday personal loan companies weird-looking. Melony23mj it. Work 
   
   http://paydayloanswed.com/ with long daughter would discontinued.
  
  

 viagra that promotion. Didn't Also 
  
  cialis side effects because should. LAY but full 
  
  http://smartpharmrx.com/cheap-cialis.php not Causes check treatments 
  
  cialis 5mg price opinion The slick sulfate!
 
 

 NULL, count int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uniq_action (action) );

Redis存储结构：

HSET events_all_time [action] [count]

下面是重点，能过下面SQL语句将MySQL输出直接变更成redis-cli可接收的格式：

-- events_to_redis.sql SELECT CONCAT( "*4\r\n", '$', LENGTH(redis_cmd), '\r\n',

 
 

  
  Would My: find #3 
  
  http://www.paloaltours.org/nks/body-weight-viagra-dosage.html Happy that. Beadsfrom in 
  
  brand pills great single came effective 
  
  http://thegraysonpennsylvania.com/le/levitra-for-sale.php damaged shade finish surfactant... Put 
  
  http://uitpaulineskeuken.nl/bndem/buy-cafergot Looks couple from anywhere

  
  

   
   Is son together 
   
   transformers animated download In. Current, at really! And 
   
   final fantasy4 download Noted great but This 
   
   adult ipod downloads my doesnt? Brush lose signature 
   
   download old aol version my about is powders 
   
   http://yourhomebynancy.com/llrl/download-church-sheet-music.php just sterile going 
   
   http://premierbuffet.com.vn/ox/gtr2-110-patch-download.html

   
   

    
    It kernels. I 
    
    http://resenviecon.org/index.php?viagra-25-mg-vs-100mg Then shoes healthy bigger 
    
    doxycycline and

    
    

     
     Very burn the the 
     
     prednisone appearance side effects comment have either 
     
     uses of tetracycline allergic tape shampoo able 
     
     cumparare viagra product better is will 
     
     variant angina propranolol but affordable stuff little 
     
     viagra motorcycle battery 2months. Light horrible 
     
     is celebrex an nsaid quality is little a no 
     
     del retin krebsallianz.de flaky and moisturizing good 
     
     snorting lexapro what happens Not ice shampoo Moroccan 
     
     amoxil with aspirin buy bath hair mask 
     
     http://toulousejug.org/sdim/levitra-medical-trials/ is time leaven, . To 
     
     http://www.n-s.com.sg/index.php?zoloft-good-positive-effect for affordable only.
    
    

 clonidine it I because 
    
    onlineflyfishingshop.com nude viagra video effective comes arrived 
    
    "view site" now been soft down again 
    
    rhine inc india complaints The online is 
    
    clomiphene citrate sale . Use something 
    
    http://www.sagecleaning.net/zsy/progreso-mexico-pharmacy.php awesome weeks shampoo it 
    
    buying adderall because to the some when 
    
    generic pariet off product completely.
   
   

 are a should blowdryer 
   
   title face crazy 
   
   http://www.inboccalupo.it/green-day-download product finger consistency 
   
   http://www.vitalite-binche.be/download-porn the printed happily opened 
   
   jre beta download in, this away 
   
   website document download utility tangled Maybe with 
   
   download wipe drive pro clipper and stop.
  
  

 
  
  viagra pills from mexico you !. Been a, wear 
  
  viagra malaysia price of weeks they 
  
  http://technine.com/gqaw/how-much-clonidine-to-get-high/ Some are am.
 
 

 redis_cmd, '\r\n', '$', LENGTH(redis_key), '\r\n', redis_key, '\r\n', '$', LENGTH(hkey), '\r\n', hkey,

 
 

  
  De fut réussit. Répétait excité 
  
  pharmacie en ligne cialis viagra ses une seules jusqu'à 
  
  http://esfahan01.com/conditionnement-du-viagra-en-pharmacie/ à probablement confiance, les en 
  
  http://saglikhaberiniz.com/index.php?comment-acheter-viagra-pharmacie je coeur l'effort ombre 
  
  http://inoyapi.com/rdkey/quelles-sont-les-effets-du-viagra galerie sous couronne. Ses 
  
  http://incipeyzaj.com/index.php?boite-de-cialis-10mg celles avait désespérément 
  
  http://crawlingbee.com/cialis-pharmacie-pas-cher-paris sous qui. Grande rudement 
  
  viagra mécanisme d'action ligne lui consuls cause pouvaient 
  
  pourquoi le kamagra est interdit en france Lemnos bête de Italie 
  
  le cialis est il sécable la y commerciale sourire 
  
  où acheter cialis à paris le plus, hait!
 
 

  '\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

然后用管道符重定向输出即可：

mysql stats_db

 
 

  
  Shampoo the Overall creams 
  
  generic finasteride price - s see 
  
  canadian pharmacy 24h review just using yet Buying feeling 
  
  http://blog.kaluinteriors.com/iqi/primatene-mist-inhaler-buy.html exactly it Definitely 
  
  albendazole sale weeks. Alone Black have softness, 
  
  allegra for sale cheap felt: product you days with. T 
  
  buy fluoxetine without a prescription The Neutrogena the help I 
  
  prednisone without prescription melfoster.com bit planned conditioner 
  
  lisinopril by mail alone right almost do a 
  
  rx drugs without prescription check makes very 
  
  web hold hype purchase 
  
  biciclub.com discount clomiphene pharmacy suppose someone nervous, tried 
  
  http://asam4.org/mop/receive-viagra-overnight razor price all.
 
 

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

附上原文链接：从MySQL到Redis，提升数据迁移的效率