mysql-utilities工具之mysqldbexport和mysqlimport

0    131    1

Tags:

👉 本文共约3867个字,系统预计阅读时间或需15分钟。

mysqldbexport

mysqldbexport 从一个或多个数据库导出对象定义的元数据和数据。默认情况下,仅仅导出对象的定义。与mysqldump类似,但区别也大。mysqldbexport 可以有多个格式,使数据更容易提取和转移。

要排出特定对象名称,可以使用--exclude选项,格式:db.obj 。也可以提供一个搜索模式。如 --exclude=db1.trig1 ,排除单个触发器。--exclude=trig_ 排除以trig_开头的。也可以使用正则表达式。

跳过对象类型,可以使用--skip选项指定对象列表。这在为了提取某个特定对象情况下,非常有用。同样,跳过BLOB数据更新可以指定 --skip-blobs选项。

输出格式有:

  • sql (默认)以SQL语句方式输出。对于对象定义,以适当的 CREATEGRANT 语句。 对于数据,以 INSERT 语句。(或者批量插入 --bulk-insert指定该选项。.
  • grid网格化输出,类似于mysql命令行客户端输出
  • csv逗号分隔格式输出
  • tab制表符格式输出
  • vertical类似于mysql命令行客户端\G执行的输出

指定要显示多少数据,可以使用 --display 选项:

  • brief只显示重建对象的最小列
  • full完整显示重建对象的列列表
  • names只显示对象名称

注意:对于SQL格式输出,--display选项被忽略。

指定 --no-headers 选项,关闭CSV或tab显示对象的格式头部。

指定--quiet选项,关闭所有反馈信息。

指定 --file-per-table选项,每个表数据单独保存。每个文件的名称以数据库和表名称组成。格式:db1.table_name.csv。

默认情况下,复制操作是使用一致性快照来读取源数据库。要改变锁定模式,可以使用–locking选项来指定锁定类型值。值no-locks关闭锁,lock-all只使用表锁。默认是snapshot。此外,使用WRITE锁,在复制过程中将锁定目标表。

从主或者从服务器导数据还可以包含复制语句。–rpl选项指定

  • master包含 CHANGE MASTER 语句,使目标服务器作为指定--server选项的从。在导数据之前,执行 STOP SLAVE 语句。在完成后执行 CHANGE MASTER 和 START SLAVE语句。slave包含 CHANGE MASTER 语句,使目标服务器成为与–server选项指定的服务器的同一个主服务器的从。只对当前服务器是从有效。
  • both同时包含 'master' 和 'slave' 信息的 CHANGE MASTER 语句 ,可以成为当前服务器的新从,也可以成为主。

–repl-user选项指定复制的用户名和密码。如果指定了--rpl-file选项,复制语句信息写入到文件中,而不是输出流。--comment-rpl选项,注释掉CHANGE MASTER 语句。

如果要导出的数据库的服务器上启用了GTIDs(GTID_MODE = ON),如果只导出其中一部分数据库,将会有警告信息产生。这是因为GTID报表生成包括所有数据库的gtids,不仅仅是某个的。

如果有启用GTID,但是使用了–skip-gtid也会收到警告。

如果启用了GTID,最好是导出或导入所有的数据库。

从另一台服务器上导入这些导出的数据,需要确保这些数据以及日志中所有的GTIDs正确记录。

选项

必需提供连接参数和赋予要访问对象的适当权限。

从源服务器上导出所有对象,必须要有:SELECT、SHOW VIEW权限,同时还需要有mysql数据库的SELECT权限。实际所需的权限可能会有所不同,以实际情况为准。

对于 --format, --export 和 --display 选项,参数值不区分大小写,但是不能参数值钱不能包含空格。

实例

只导出定义语句

只导出数据,且是批量插入语句:

如果数据库中并不是所有的表是innodb引擎的,为了确保数据的一致性,需要在导出前锁定表。可以加上--locking=lock-all选项:

为当前的数据库创建一个从服务器:

既然有 mysqldbexport 那就有mysqldbimport 。实物都是对立存在的。 有男就有女,有矛就有盾.

mysqldbimport

mysqldbimport 顾名思义,导入。mysqldbexport 的反面。将mysqldbimport导出的数据导入到另一个数据库服务器上。

如果一个对象已经存在于目标服务器上,那么将先删除再导入的。

跳过对象类型,可以使用–skip选项指定对象列表。这在为了提取某个特定对象情况下,非常有用。同样,跳过BLOB数据更新可以指定 –skip-blobs选项。

指定输入的格式,与mysqldbexport导出的格式对应:

  • sql (默认)以SQL语句方式输出。对于对象定义,以适当的 CREATE 和 GRANT 语句。 对于数据,以 INSERT 语句。(或者批量插入 --bulk-insert指定该选项。.
  • grid网格化输出,类似于mysql命令行客户端输出
  • csv逗号分隔格式输出
  • raw_csv 输入一个简单的csv文件,包含用逗号分隔的行值。该文件可以包含行头信息。--table选项需要此格式。
  • tab制表符格式输出
  • vertical类似于mysql命令行客户端\G执行的输出

指定 –no-headers 选项,关闭CSV或tab显示对象的格式头部。

指定–quiet选项,关闭所有反馈信息。

默认情况下,创建的表的存储引起与原始表一样。可以使用--new-storage-engine选项来指定要使用的引擎。如果目标服务器支持该引擎,所有的表将使用该引擎的。

如果目标服务器不支持原表所使用的存储引起,可以使用--default-storage-engine选项来指定默认使用的引擎。

--new-storage-engine选项优先级高于 --default-storage-engine。

如果要导入的数据库的服务器上启用了GTIDs(GTID_MODE = ON),如果mysqlexport导出的数据没有包含GTID报表,将会有警告信息产生。

选项

如果你的系统有多个处理器,可以并发执行。并发导入应用于文件级别,这意味着只有不同的文件可以同时执行的。

实例

导入元数据

批量插入语句导入

批量插入语句导入,导入定义数据和数据

使用mysqldbexport 和 mysqldbimport 工具来创建新的从库

通过这两个工具来为现有的主从复制新加一台新的从库服务器。

环境说明

instance_3306: master

instance_3308:slave

导出数据

导入数据

至此,主从已经建立好了。是不是非常方便非常容易?

主从状态

instance_3306

instance_3308

说明

data.sql文件中会包含如下语句的:

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 262554;

START SLAVE;

在导入之前,先执行下reset master,是为了清除之前的主从信息。否则会报下面的错误。

ERROR: The import operation contains GTID statements that require the global gtid_executed
system variable on the target to be empty (no value). The gtid_executed value must be reset
by issuing a RESET MASTER command on the target prior to attempting the import operation.
Once the global gtid_executed value is cleared, you may retry the import.

我这里就没创建复制用户,就直接以root用户来的。

我的示例

# 只导出定义语句

mysqldbexport --server=mysql_77 --format=sql ecard --export=definitions > ecard.sql

# 只导出数据 批量插入

mysqldbexport --server=mysql_77 --format=sql ecard --export=data --bulk-insert > ecard.sql

mysqldbexport --server=mysql_77 --format=sql ecard --export=data --bulk-insert --file-per-table #一个表一个文件

若数据库中并不是所有表是innodb引擎,为确保数据一致性,需要在导入 前锁定表,加上 --locking=lock-all

# 导出结构和数据 并为当前数据库创建一个从服务器

mysqldbexport --server=mysql_77 --format=sql ecard --export=both --rpl-user=root --rpl=master > ecard.sql

mysqldbexport --server=root:lhr@192.168.66.35:3317 db1 --format=sql --export=definitions > db1.sql

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

mysqldbexport --server=root:lhr@192.168.66.35:3317 db1 --format=sql --export=data --bulk-insert > db1_data.sql

mysqldbimport --server=user:pass@host:port:socket db1.csv db2.sql db3.grid

导入数据,若一个对象已经存在于目标服务器上,将先删除再导入

选项:

-- format 格式

-- import data definitions(默认) both

-- drop - first

-- no - headers 排除表头,适用于tab csv格式

-- dryrun 预导入不执行,测试用。测试文件是否有效

-- table

--skip - blobs --skip -rpl --skip -gtid

--skip =SKIP_OBJECTS 忽略的对象 tables , views , triggers , procedures , functions , events , grants , data , create_db

mysqldbimport --server=mysql_77 --import=definitions --format=csv data.csv

mysqldbimport --server=mysql_77 --import=data --bulk-insert --format=csv data.csv

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部