暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 数据库导入导出基础扫盲

数据库导入导出基础扫盲

DBA天团 2021-02-05
1121

在使用MySQL/Oracle数据库过程中，难免会遇到数据迁移的需求，此时就需要使用工具对数据执行提取和导入操作。由于业务暂时只涉及同构数据库的数据迁移（即MySQL->MySQL和Oracle->Oracle），并且不考虑导数过程中的增量数据，因此不会使用ETL工具，而是使用基本的逻辑导出和逻辑导入的方式来执行。最近做了一些实验，在此总结一下。

在导入导出工具的选择上，使用 mydumper/myloader
进行MySQL的数据导入导出，使用 expdp/impdp
进行Oracle的数据导入导出。接下来分别对这两种数据库的迁移过程作介绍。

mydumper/myloader

常用的MySQL逻辑备份工具主要有Oracle官方的 mysqldump
和 mysqlpump
，以及本篇文章介绍的 mydumper
这三种。之所以不选用官方工具是由于 mysqldump
是单线程的备份工具，性能比较差；而 mysqlpump
只支持备份5.7之后版本的MySQL且单表依然是单线程导出。 mydumper
既支持多线程，也支持5.6版本，是比较合适的选择。

安装过程不再赘述，直接介绍 mydumper
命令常用的几个参数，其余参数可以通过 mydumper--help
命令查询：

  1. -B, --database 要导出的db名
  2. -T, --tables-list 要导出的表名,多个表使用逗号分隔，t1,t2,t3  
  3. -o, --outputdir 导出数据文件存放的目录，不存在会自动创建
  4. -x, --regex 使用正则表达式匹配导出目标 db1.table1|db2.table2|db3 
  5. -m, --no-schemas 只导出数据，不导出建库建表语句
  6. -d, --no-data 只导出建库建表的语句
  7. --less-locking 减少innodb表的锁表时间

另外，连接数据库的参数（-u， -p， -h 等）与mysql命令的代表的意思一致。举几个例子说明 mydumper
使用方法：

  1. #1. 导出整个testdb库（不包括触发器、函数等）
  2. mydumper -u root -p xxx -h localhost -P3306 -B testdb -o /tmp/bak

  4. #2. 导出testdb库下的 a, b 两张表的表结构
  5. mydumper -u root -p xxx -h localhost -P3306 -B testdb -T a,b -d -o /tmp/bak 

  7. #3. 导出testdb整个库和tmp库下的表 c
  8. mydumper -u root -p xxx -h localhost -P3306 -x "testdb|tmp.c" -o /tmp/bak

mydumper
导出的文件分为4种类型，分别为：

  1. metadata
     包含导出时刻的binlog 位点信息 ，如果启用gtid ，则记录gtid信息。

  2. db.table.sql
     数据文件，包含insert语句，如果是空表默认不生成此文件。

  3. db.table-schema.sql
     包含建表语句

  4. db-schema.sql
     包含建库语句

以第三条命令为例，执行后生成的备份文件如下：

  1. [mysql@test ~]$ ls bak
  2. metadata  testdb.a-schema.sql  testdb.a.sql  testdb.b-schema.sql  testdb-schema-create.sql  tmp.c-schema.sql  tmp.c.sql

可以看出由于testdb.b里没有数据，因此没有生成相应的数据文件。

相应地，对于导入工具 myloader
，经常使用的参数有：

  1. -B, --database 还原到哪个数据库
  2. -d, --directory 备份文件的文件夹
  3. -o, --overwrite-tables 如果要恢复的表存在则先drop表，需要目录中存在表结构文件

  5. #1. 导入备份文件到newdb库
  6. mydumper -u root -p xxx -h localhost -P3306 -B newdb -d /tmp/bak 

  8. #2. 不加-B参数，会依据备份文件的命名规则导入到相应目标库
  9. mydumper -u root -p xxx -h localhost -P3306 -d /tmp/bak

这里解释下第二条命令：“依据备份文件的命名规则导入到相应目标库”。上面提到 mydumper
生成的文件有特定的格式，除了 metadata
之外的备份文件都是以db名开头的，这样就可以保证在执行 myloader
命令时不使用 -B
参数指定目标db也能顺利执行，由程序按照命名规则判断导入到哪个db。经过测试发现，通过修改特定备份文件的db前缀，就可以达到控制此文件的导入db的目的。这样也变相使得 myloader
工具拥有了同时对多个db名进行remap的能力。

expdp/impdp

oracle 10g之前的版本提供了导出和导入实用程序（ exp/imp
），大家应该比较熟悉。它们在oracle 11g中仍然可以使用，但是Oracle官方强烈建议使用数据泵（ expdp/impdp
）来代替，因为数据泵不仅性能更好，还提供了更多的高级特性，例如通过 include
、 exclude
两个参数提供了非常细粒度的目标对象控制，通过 remap_schema
、 remap_table
、 remap_tablespace
几个参数实现导入过程中自动修改对象属主、 表名或数据所在表空间。对于部分担心还要重新学习新命令使用方式而依然坚守 exp/imp
的dba们，数据泵还提供了legacy模式，用于支持之前的 exp/imp
的参数写法。不过有两点需要注意：一是数据泵是服务器端工具，而不是客户端工具；二是 expdp
在备份之前需要建立辅助的master table，因此只能在可写的库上执行导出程序。

数据泵不支持直接指定备份文件的操作系统路径，而是使用oracle的directory对象映射到服务器的实际目录。如果未指定directory对象，oracle将使用默认的目录对象DATAPUMPDIR，但只有SYS和SYSTEM用户才可以使用这个默认目录。为了使用自己指定的路径，一般都新建一个directory并授权给数据泵用户：

  1. #1. 建立目录
  2. create directory EXP_DIR as '/tmp/bak';
  3. #2. 授权
  4. grant read,write on directory EXP_DIR to 'AAA';

输入 expdp-help
命令可以显示出 expdp
命令的帮助信息, impdp
也一样。介绍一些常用参数：

  1. # 输入输出
  2. DIRECTORY  指定备份文件存放的directory对象名
  3. DUMPFILE   导出的文件名
  4. LOGFILE    执行导入导出生成的日志名

  6. # 操作模式，每次执行导出/导入只能选取一种模式
  7. FULL     全库导出
  8. SCHEMAS  导出schema，默认模式，使用逗号连接多个schema
  9. TABLES   导出表及以依赖该表的对象，使用逗号连接多个表
  10. TABLESPACES 导出表空间
  11. TRANSPORTABLE_TABLESPACES 导出表空间里对象的元数据

  13. # 对象过滤，类似白名单黑名单，不可同时使用
  14. INCLUDE    指定导出/导入的对象类型或者对象名称
  15. EXCLUDE    指定不被导出/导入的对象类型或者对象名称

  17. # 高级过滤
  18. CONTENT  指定导出/导入的内容，可以是 ALL | DATA_ONLY | METADATA_ONLY
  19. QUERY    使用where条件过滤特定数据

  21. # 导入目标映射
  22. REMAP_SCHEMA  schema映射，支持多个A:A1,B:B1
  23. REMAP_TABLE   表名映射
  24. REMAP_TABLESPACE 表空间映射

以下示例都使用SYS用户连接，这样就会避免遇到各种权限问题。数据库版本是11.2.0.4。

  1. #1. 导出 FOO BAR 两个schema
  2. expdp \'sys/xxx as sysdba\' directory=exp_dir dumpfile=exp_schemas.dmp logfile=exp_schemas.log schemas=foo,bar

  4. #2. 导出表 FOO.A BAR.B 的元数据
  5. expdp \'sys/xxx as sysdba\' directory=exp_dir dumpfile=exp_table.dmp tables=foo.a,bar.b  content=metadata_only exclude=table_statistics,index_statistics

注意示例2在导出元数据时排除掉了表和索引的统计信息，这是因为如果 impdp
导入带有统计信息的表结构到新库，会出现统计信息锁定的问题。通过查询以下几张视图，可以获取到所有可以在导出时过滤掉的对象的类型： DATABASE_EXPORT_OBJECTS
(FULL模式)， SCHEMA_EXPORT_OBJECTS
(SCHEMA模式)， TABLE_EXPORT_OBJECTS
(TABLE和TABLESPACE模式)。

例如下面的查询就列出了在SCHEMA操作模式下默认会被导出的与权限相关的对象：

  1. sys@test> SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS
  2. 2  WHERE OBJECT_PATH LIKE '%GRANT' AND OBJECT_PATH NOT LIKE '%/%';

  4. OBJECT_PATH                    COMMENTS
  5. ------------------------------ --------------------------------------------------------------------------------
  6. GRANT                          Object grants on the selected tables
  7. OBJECT_GRANT                   Object grants on the selected tables
  8. PROCDEPOBJ_GRANT               Grants on instance procedural objects
  9. PROCOBJ_GRANT                  Schema procedural object grants in the selected schemas
  10. ROLE_GRANT                     Role grants to users associated with the selected schemas
  11. SYSTEM_GRANT                   System privileges granted to users associated with the selected schemas

  13. 6 rows selected.

  15. Elapsed: 00:00:00.01

不同于MySQL，使用 impdp
导入数据到库时一定要注意表空间的问题。在使用 SCHEMAS
和 TABLES
操作模式的时候，导出文件中的建库建表语句会带上表空间的名字，但不会包含创建表空间的语句。因此在导入数据的时候，要保证目标库中已经存在了相关表空间；如果使用了 remap_tablespaces
参数，要保证映射后的表空间存在，如果目标库表空间不存在是会报错的。

  1. #3. 把示例2导出的两张表结构导入到 TEST
  2. impdp \'sys/xxx as sysdba\' directory=exp_dir dumpfile=exp_tables.dmp remap_schemas=foo:test,bar:test


往期精彩文章

__________________________

网易中间件Cetus开源啦

连接cetus中间件Hang住怎么办

MySQL5.7 XA事务丢失

GTID真的来了

MySQL统计信息简介

网易北京研发中心DBA招募令

DBA女神讲解区块链原理


欢迎分享

网易乐得DBA组负责网易乐得电商、网易邮箱、网易技术部数据库日常运维，负责数据库私有云平台的开发和维护，负责数据库及数据库中间件的开发和测试等，分享最前沿实用数据库干货，关注网易乐得DBA，精修数据库功底。

关注「网易乐得DBA」


数据库
文章转载自DBA天团，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论