如何使用命令行导入导出 SQL 数据库文件

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
最近更新 2022年01月24日

如何使用命令行导入导出 SQL 数据库文件

MySQL 导入导出数据是数据库日常维护中使用最频繁的功能之一,备份、数据导出分析、数据迁移都是导入导出功能都延展,虽然有很多 MySQL GUI 管理工具可以方便的使用图形化操作界面操作导入导出,但面对复杂的工作场景,使用命令行手动操作会更加灵活便捷。本教程详细讲解所有常见的 MySQL 和 MariaDB 导入导出数据的方法(注意:MySQL 和 MariaDB 操作命令一样,可以互换。)

本教程目录

1.MySQL 导入数据 SQL 文件

(1)将 SQL 文件导入至 MySQL / MariaDB 数据库中

(2)使用 source 导入数据库 SQL 文件

2.MySQL 导出数据 SQL 文件

(1)使用 mysqldump 直接导出数据至 SQL 文件

(2)阿里云 / 腾讯云远程服务器中的数据库直接导出到本地计算机

3.使用「卡拉云」快速搭建数据库管理工具,一键导出数据

如何使用卡拉云,5分钟搭建一套适应自己工作流的一键导入导出数据库系统。卡拉云无需部署,即插即用,可根据需求灵活调配,适用于后端工程师快速搭建企业内部系统、数据产品经理查看分析数据,数据分析师根据需求快速搭建数据共享平台分享给组内同学协同查看等应用场景。点这里看详情。

MySQL 导入数据篇

1. 导入方法一:直接使用 mysql 导入 SQL 文件

在我们讲解如何将 *.sql 导入到数据库前。先建一个新数据库用作演示,然后退出 MySQL。

直接导入数据

接着我们直接在「命令行」中使用 mysql 命令导入 SQL 数据文件 /tmp/kalacloud-data-export.sql 。(特别注意:以下命令在命令行中执行,不是在 mysql> 状态下执行)

mysql -u root -p kalacloud_new_database < /tmp/kalacloud-data-export.sql
  • root  :你可以登录数据库的用户名。
  • kalacloud_new_database :刚刚新建的空数据库,这条命令会把数据导入到这其中。
  • /tmp/kalacloud-data-export.sql :这里是你存放需要导入 SQL 文件的文件路径。

如果运行成功,命令行不会有任何提示。如果运行失败,命令行会提示失败原因。要检测是否导入成功,我们可以登录到 MySQL 查看并检查数据库中的数据。

mysql 导入成功状态

登录 MySQL 服务器,选择我们刚刚导入的表查看数据,可以看到已经导入成功了。

2.导入方法二:使用 source 导入 MySQL / MariaDB 数据库 SQL 文件

首先进入 MySQL shell 状态,选择需要导入的数据库kalacloud_new_database,返回结果。

source 导入 方式

然后使用 source 命令,后面直接跟需要导入文件的路径。

mysql> source /tmp/kalacloud_new_database.sql;

导入成功状态

执行 source 命令后,MySQL 开始执行导入,接着我们使用 SHOW TABLESselect 来查看 SQL 文件是否导入正常。

上图可以看到,数据已经导入成功。

3.导入数据命令 sourcemysql < 的区别

(1)命令执行环境的区别:source 在 MySQL sell 里执行,mysql < 在终端命令行中执行

(2)返回结果的区别:source 会连续返回每一行导入结果,如果量大可能会影响速度,mysql < 全部完成后返回结果。

(3)报错是否停止执行的区别:source 遇到报错不会终止执行,mysql < 遇到报错会终止执行。

扩展阅读:我们也可以使用 Workbench 这种免费的 MySQL 图形管理工具来操作,了解更多可看我写的这篇《MySQL Workbench 中文指南》教程。

MySQL 导出数据篇

1.如何使用 mysqldump 导出数据

mysqldump 命令是数据库导出中使用最频繁的一个工具,它可将数据库中的数据备份成已 *.sql 结尾的文本文件,表结构和数据都会存储在其中。

mysqldump 命令的原理也很简单,它先把需要备份的表结构查询出来,然后生成一个 CREATE TABLE 'table' 语句,最后将表中所有记录转化成一条INSERT语句。

可以把它理解为一个批量导出导入脚本。数据导入时,按照规范语句导入数据,大幅减少奇怪的未知错误出现。

mysqldump 的基本命令:

$ mysqldump -u username -p database_name > data-dump.sql
  • username 是数据库的登录名
  • database_name 是需要导出的数据库名称
  • data-dump.sql 是文件输出目录的文件

导出实战 - 从阿里云服务器中的 MySQL 数据库导出数据

$ mysqldump -u kalacloud -p kalacloud_database > /tmp/kalacloud-data-export.sql

01-mysqldump.png

  • kalacloud :数据库账号
  • kalacloud_database :数据库名
  • /tmp/kalacloud-data-export.sql :数据库导出的文件及存放目录

输入数据库 kalacloud 账号的密码执行命令,如果执行过程中,没有任何错误,那么命令行不会有任何输出。

我们可以 cd 到 tmp 目录查看结果。上图可以看到,tmp 目录下已经生成 kalacloud-data-export.sql 的导出文件。

我们在用head -n 5 kalacloud-data-export.sql命令检查一下。你会看到类似下图的内容。

02-kalacloud-data-export.png

至此,我们已经将指定数据库导出到 *.sql 文件中了,后文我们讲解如何将这些数据导入到数据库。

进阶提示:我们可以使用 scp 命令,将导出文件下载至本地计算机。

在本地计算机的命令行终端里,输入:

scp root@192.168.180.134:/tmp/kalacloud-data-export.sql /Users/kalacloud/Downloads

root 远程计算机的登录账号

192.168.180.134 为远程计算机的 IP 地址

/tmp/kalacloud-data-export.sql 为需要下载到本地的数据库文件在远程计算机上的存储位置

/Users/kalacloud/Downloads 为本地计算机的存储位置,远程文件将下载到这个目录中

scp命令

使用 scp 将导出的 SQL 文件下载到本地再进行后续处理。当然我们也可以一步导出至本地计算机,下面我们继续讲解进阶导出方法。

扩展阅读:有关数据库在两台服务器之间迁移的问题可看我写的《如何迁移 MySQL 数据库,阿里云、腾讯云迁移案例

2.进阶导出:将阿里云 / 腾讯云远程服务器中的数据库导出到本地计算机

前文我们讲了如何在远程服务器上操作导出数据库,导出后保存在远程服务器中。有时我们需要把数据导出给产品或运营进行数据分析,又或者我们使用的云服务是独立 MySQL 数据库,这时,你需要直接把数据导出到本地计算机中。

$ mysqldump -h remote_IP_address  -u username -p -P3306 --default-character-set=utf8 --set-gtid-purged=OFF database_name >/Users/kalacloud/Desktop/data-dump.sql
  • remote_IP_address :远程服务器的 IP
  • username :拥有远程登录权限的 MySQL 账号
  • 3306:远程登录的数据库端口,默认是 3306 ,如果不是可根据情况替换
  • default-character-set=utf8 :导出时指定字符集
  • set-gtid-purged=OFF :全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。开启这个功能导入导出时,可能会出错,故关闭。
  • database_name :需要导出的数据库名称
  • /Users/kalacloud/Desktop/data-dump.sql :本地计算机保存路径及保存文件名

提示:mysqldump常见报错:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')

可在命令中添加 column-statistics=0 参数。因 MySQL 数据库早期版本 information_schema 数据库中没有名为 COLUMN_STATISTICS 的数据表,新版 mysqldump 默认启用,我们可以通过此命令禁用它。

导出实战 - 将阿里云服务器中的数据库直接导出到本地计算机

$ mysqldump -h123.57.56.228  -ukalacloud-remote -p -P3306 --default-character-set=utf8 --set-gtid-purged=OFF --column-statistics=0 kalacloud_database >/Users/kalacloud/Desktop/kalacloud-data-export.sql
  • 123.57.56.228: 远程数据库 ip 地址
  • kalacloud-remote:拥有远程访问权限的数据库账号。
  • -P 3306:数据库访问端口,可根据自己情况修改。
  • /Users/kalacloud/Desktop/kalacloud-data-export.sql :本地计算机保存路径及保存文件名

阿里云数据导出

执行命令后,命令行并没有任何信息输出,但我们已经可以在桌面上看到导出后生成的文件了。

阿里云数据导出到本地

已经导出到本地桌面的远程端数据库

当然,mysqldump 也可以分表备份,比较常见的场景有

# 备份单个库
mysqldump -uroot -p -R -E --single-transactio --databases [database_one] > database_one.sql

# 备份部分表
mysqldump -uroot -p --single-transaction [database_one] [table_one] [table_two] > database_table12.sql

# 排除某些表
mysqldump -uroot -p [database_one] --ignore-table=[database_one.table_one] --ignore-table=[database_one.table_two] > database_one.sql

# 只备份结构
mysqldump -uroot -p [database_one] --no-data > [database_one.defs].sql

# 只备份数据
mysqldump -uroot -p [database_one] --no-create-info > [database_one.data].sql

扩展阅读:《如何远程连接 MySQL 数据库,阿里云腾讯云外网连接教程

扩展阅读:有关把 MySQL 查询出来的结果保存到文件可看我写的这篇《如何在 MySQL 导出数据到文件》教程。

三. 使用「卡拉云」一键导入导出数据

在本教程中,我们讲解了如何导入导出数据库至 SQL 文件。mysqldump 还有很多使用变化,你可以参考 mysqldump 官方文档了解更多。

除了 MySQL / MariaDB 数据迁移这类适合使用终端命令操作外,大多数对 MySQL / MariaDB 数据导入导出操作还是为了数据展示、分析、协同共享等产品和运营层面的应用场景。比如后端工程师接到产品需求,协助导出某类数据等场景,如果这类需求频繁出现,推荐使用卡拉云,卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。

卡拉云快速拖拽生成组件,迅速搭建企业内部工具

卡拉云不仅可以像命令行一样灵活,还可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天。欢迎免费试用 卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

kalacloud 优惠券发放核销 上图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。

如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。更多数据库相关教程可访问 卡拉云 查看。

卡拉云联合创始人蒋川

有关 MySQL 教程,可继续拓展学习: