怎么实现MyISAM分区表迁移及合并

这期内容当中小编将会给大家带来有关怎么实现MyISAM分区表迁移及合并,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
dyn_change_yyyymm 是一张按照日期(字段time_d)分区的表(myisam),只有insert,select操作。
2016年3月份起结构如下:
CREATE TABLE `dyn_change_201603` (
`time_d` int(8) NOT NULL COMMENT ‘帐期’,
`id` char(32) NOT NULL COMMENT ‘业务标识’,
`content` text COMMENT ‘内容’,
`create_time` int(11) NOT NULL开发云主机域名 COMMENT ‘时间’,
KEY `idx_id` (`id`),
KEY `idx_create_time` (`create_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’动态数据记录表’
/*!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迁移到新环境下。
步骤如下:
1)假设当前日期为:2016年3月30日,对应分区dypt_20160330,新环境创建dyn_change_201603表(非分区表)。insert 操作可以移到新环境下;
2)将老环境下的表dyn_change_201603 重命名为:dyn_change_201603_old;
3)到mysql的数据目录下,将dyn_change_201603_old的数据文件&&索引文件scp到新环境对应的路径下面。[注:dyn_change_201603_old此时的对应数据&&索引文件不同与dyn_change_201603的数据&&索引文件]
4)然后交换dyn_change_201603,dyn_change_201603_old表名。交换后,90%以上的数据都在dyn_change_201603表,而少部分数据在dyn_change_201603_old(只在分区dypt_20160330 中);
dyn_change_201603接受当前insert操作;
5)然后导出dyn_change_201603_old的dypt_20160330分区的数据(仅有少量数据),可以导出dyn_change_201603_30.csv文件;
6)然后将dyn_change_201603_30.csv的数据导入dyn_change_201603;
7)alter tabledyn_change_201603 analyze partitiondypt_20160330。[注:此时可在insert操作较少的时间 or 暂停insert 操作]
迁移合并完成。
上述就是小编为大家分享的怎么实现MyISAM分区表迁移及合并了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注开发云行业资讯频道。

相关推荐: MySQ有哪些好用的修改root密码的方法

本文主要给大家介绍MySQ有哪些好用的修改root密码的方法,文章内容都是笔者用心摘选和编辑的,具有一定的针对性开发云主机域名,对大家的参考意义还是比较大的,下面跟笔者一起了解下MySQ有哪些好用的修改root密码的方法吧。 方法1: 用SET PASSWOR…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/27 15:32
下一篇 06/27 15:33

相关推荐

发表评论

您的电子邮箱地址不会被公开。