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.不同的引擎之间是否会对导入数据有影响。

　

 

开始：

　　数据库mysql 5.7，官方mysqldump文档https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#mysqldump-performance-options

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

　　eg：

　　　　mysqldump -uroot -p'123456'  -h127.0.0.1 -P3306  --single-transaction  --quick mydb order_product|gzip > /Users/llj/order_product.sql.gz

　　　　

导出的格式

　　删除表

　　创建相同的表结构

　　给表加写锁

　　　　批量插入数据（insert into）

　　释放表锁

这种带来的隐患

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

2.mysqldump导出默认锁库或者锁表 用一下参数控制不锁库。

　　实际操作直接导并没有锁表，mysqldump 导出执行的sql 

　　SELECT /*!40001 SQL_NO_CACHE */ * FROM `product_history`;

3.性能推荐使用gzip 减少传输数据，百万数据导出只要十分钟

4. 需要注意的参数：

　　--single-transaction: 作用：

　　　　mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

　　没有这个参数则导出的时候时候需要导出的用户需要有查询，视图，锁表等权限。某些参数可能需要其他的权限。

　　若没有这个参数导入的时候会报：Access denied; you need (at least one of) the SUPER privilege(s) for this operation 解决，即使用户名有最高权限依然不可以。加上这个参数只要有写权限就可以了。

　　

　　

 

　　原因：若没有这个参数：数据库为了保证

　　将数据导回实例

　　　压缩过的先解压

　　gzip -d xxxx.sql.gz

　　再导入

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