怎么實(shí)現(xiàn)MyISAM分區(qū)表遷移及合并

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)怎么實(shí)現(xiàn)MyISAM分區(qū)表遷移及合并，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

dyn_change_yyyymm 是一張按照日期(字段time_d)分區(qū)的表(myisam)，只有insert，select操作。
2016年3月份起結(jié)構(gòu)如下：
 CREATE TABLE `dyn_change_201603` (
  `time_d` int(8) NOT NULL COMMENT '帳期',
  `id` char(32) NOT NULL COMMENT '業(yè)務(wù)標(biāo)識(shí)',
  `content` text COMMENT '內(nèi)容',
  `create_time` int(11) NOT NULL COMMENT '時(shí)間',
  KEY `idx_id` (`id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='動(dòng)態(tài)數(shù)據(jù)記錄表'
/*!50100 PARTITION BY RANGE (time_d)
(PARTITION dypt_20160301 VALUES LESS THAN (20160302) ENGINE = MyISAM,
 PARTITION dypt_20160302 VALUES LESS THAN (20160303) ENGINE = MyISAM,
 PARTITION dypt_20160303 VALUES LESS THAN (20160304) ENGINE = MyISAM,
 PARTITION dypt_20160304 VALUES LESS THAN (20160305) ENGINE = MyISAM,
 PARTITION dypt_20160305 VALUES LESS THAN (20160306) ENGINE = MyISAM,
 PARTITION dypt_20160306 VALUES LESS THAN (20160307) ENGINE = MyISAM,
 PARTITION dypt_20160307 VALUES LESS THAN (20160308) ENGINE = MyISAM,
 PARTITION dypt_20160308 VALUES LESS THAN (20160309) ENGINE = MyISAM,
 PARTITION dypt_20160309 VALUES LESS THAN (20160310) ENGINE = MyISAM,
 PARTITION dypt_20160310 VALUES LESS THAN (20160311) ENGINE = MyISAM,
 PARTITION dypt_20160311 VALUES LESS THAN (20160312) ENGINE = MyISAM,
 PARTITION dypt_20160312 VALUES LESS THAN (20160313) ENGINE = MyISAM,
 PARTITION dypt_20160313 VALUES LESS THAN (20160314) ENGINE = MyISAM,
 PARTITION dypt_20160314 VALUES LESS THAN (20160315) ENGINE = MyISAM,
 PARTITION dypt_20160315 VALUES LESS THAN (20160316) ENGINE = MyISAM,
 PARTITION dypt_20160316 VALUES LESS THAN (20160317) ENGINE = MyISAM,
 PARTITION dypt_20160317 VALUES LESS THAN (20160318) ENGINE = MyISAM,
 PARTITION dypt_20160318 VALUES LESS THAN (20160319) ENGINE = MyISAM,
 PARTITION dypt_20160319 VALUES LESS THAN (20160320) ENGINE = MyISAM,
 PARTITION dypt_20160320 VALUES LESS THAN (20160321) ENGINE = MyISAM,
 PARTITION dypt_20160321 VALUES LESS THAN (20160322) ENGINE = MyISAM,
 PARTITION dypt_20160322 VALUES LESS THAN (20160323) ENGINE = MyISAM,
 PARTITION dypt_20160323 VALUES LESS THAN (20160324) ENGINE = MyISAM,
 PARTITION dypt_20160324 VALUES LESS THAN (20160325) ENGINE = MyISAM,
 PARTITION dypt_20160325 VALUES LESS THAN (20160326) ENGINE = MyISAM,
 PARTITION dypt_20160326 VALUES LESS THAN (20160327) ENGINE = MyISAM,
 PARTITION dypt_20160327 VALUES LESS THAN (20160328) ENGINE = MyISAM,
 PARTITION dypt_20160328 VALUES LESS THAN (20160329) ENGINE = MyISAM,
 PARTITION dypt_20160329 VALUES LESS THAN (20160330) ENGINE = MyISAM,
 PARTITION dypt_20160330 VALUES LESS THAN (20160331) ENGINE = MyISAM,
 PARTITION dypt_20160331 VALUES LESS THAN (20160401) ENGINE = MyISAM) */

在不影響insert的情況下，需要將dyn_change_201603遷移到新環(huán)境下。
步驟如下：
1）假設(shè)當(dāng)前日期為：2016年3月30日，對(duì)應(yīng)分區(qū)dypt_20160330，新環(huán)境創(chuàng)建dyn_change_201603表（非分區(qū)表）。insert 操作可以移到新環(huán)境下；
2）將老環(huán)境下的表dyn_change_201603 重命名為： dyn_change_201603_old；
3）到mysql的數(shù)據(jù)目錄下，將dyn_change_201603_old的數(shù)據(jù)文件&&索引文件scp到新環(huán)境對(duì)應(yīng)的路徑下面。[注：dyn_change_201603_old此時(shí)的對(duì)應(yīng)數(shù)據(jù)&&索引文件不同與dyn_change_201603的數(shù)據(jù)&&索引文件]
4）然后交換dyn_change_201603，dyn_change_201603_old表名。交換后，90%以上的數(shù)據(jù)都在dyn_change_201603表，而少部分?jǐn)?shù)據(jù)在dyn_change_201603_old（只在分區(qū)dypt_20160330 中）；
    dyn_change_201603接受當(dāng)前insert操作；
5）然后導(dǎo)出dyn_change_201603_old 的dypt_20160330分區(qū)的數(shù)據(jù)（僅有少量數(shù)據(jù)），可以導(dǎo)出dyn_change_201603_30.csv文件；
6）然后將dyn_change_201603_30.csv的數(shù)據(jù)導(dǎo)入dyn_change_201603；
7）alter table dyn_change_201603 analyze partition dypt_20160330。 

[注：此時(shí)可在insert操作較少的時(shí)間 or 暫停insert 操作]。
遷移合并完成。

上述就是小編為大家分享的怎么實(shí)現(xiàn)MyISAM分區(qū)表遷移及合并了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道。