mysql大表迁移_mysql 大表mysqldump迁移方案

场景

一张历史表product_history 500万数据，凌晨的才会将正式表的数据迁移到历史表，此次需求将历史表迁移到一个更便宜的数据库实例进行存储。

条件

1.此表不是实时写，凌晨才会更新

2.夸数据库实例进行迁移

3.此表对数据准确性有要求，数据必须准确

选型

1.navicat 导出数据(转存储仅结构和数据)

2.重命名表，创建一张新表(适合同一个实例)

3.mysqldump 导数据

操作对比

navicat 支持两个数据库之间直接导数据，不需要先导出到本地再从本地导入到另外的实例

mysqldump 支持两个数据库之间直接导数据，不需要先导出到本地再从本地导入到另外的实例,支持导出压缩。例子：导出的5M的数据压缩只有700k

性能对比

Navicat导出的数据是一条条的insert语句，每一行一条插入语句。

mysqldump 导出的数据,多行数据合并成一行插入。批插入减少sql 语法词法解析，减少插事务(最大的开销)，较少数据的传输

思考准备

大表数据迁移需思考的问题：

1.大量数据读取与插入是否会造成表的死锁。

2.对自己的服务器或者云上的实例的IO,带宽，内存占用有多大，会不会造成内存溢出，cpu 100%

3.迁移的数据特殊类型例如(blob)会不会在导入的时候丢失。

4.不同的引擎之间是否会对导入数据有影响

开始：

导出表数据和结构 mysqldump -h实例ip/域名 -P端口号(大写P表示端口号区别于小写p密码) -u用户名 -p密码 库名 表名|gizp(表示压缩) > 输出地址 xxx.sql

eg：

mysqldump -uroot -p'123456'-h127.0.0.1 -P3306mydb order_product|gizp > /Users/llj/order_product.sql

导出的格式

删除表

创建相同的表结构

给表加写锁

批量插入数据(insert into)

释放表锁

这种带来的隐患

1.删除表：若导出是增量模式，不是全量覆盖则会将原来的表数据删除，只会有增量的数据

将数据导回实例

mysql -h实例ip/域名 -p端口号 -u用户名 -p'密码' 库名