场景
一张历史表 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 文档
导出表数据和结构 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'密码' 库名 < Documents/sql/order_product.sql
来源: https://www.cnblogs.com/blogxiao/p/13074211.html