[转]如何在 MySQL / MariaDB 中导入导出数据,导入导出数据库文件、Excel、CSV |
您所在的位置:网站首页 › mariadb保存数据库目录 › [转]如何在 MySQL / MariaDB 中导入导出数据,导入导出数据库文件、Excel、CSV |
在日常的数据库维护工作中,经常需要对数据库进行导入导出操作,备份、分析、迁移数据都需要用到导入导出功能,在本教程中将详细讲解所有常见的 MySQL 和 MariaDB 中导入导出数据的方法(注意:MySQL 和 MariaDB 两个数据库操作命令一样,可以互换。) 本教程将详细讲解 1. MySQL / MariaDB 数据库数据「导出」(1)使用 mysqldump直接导出数据至 SQL 文件 (2)阿里云 / 腾讯云远程服务器中的数据库直接导出到本地计算机 (3)使用 into outfile命令导出数据至 CSV / Excel 提示:如果你正在寻找数据迁移方案,请查看我写的的另一篇专门针对 MySQL 数据迁移的教程,教程中包含腾讯云、阿里云迁移实战。 2. MySQL / MariaDB 数据库数据「导入」(1)将 SQL 文件导入至 MySQL / MariaDB 数据库中 (2)使用 source 导入数据库 SQL 文件 (3)将 CSV / Excel 文件 导入至 MySQL / MariaDB 数据库中 3. 使用「卡拉云」一键导入导出 MySQL / MariaDB 数据如何使用卡拉云,5分钟搭建一套适应自己工作流的一键导入导出数据库系统。卡拉云无需部署,即插即用,可根据需求灵活调配,适用于后端工程师快速搭建企业内部系统、数据产品经理查看分析数据,数据分析师根据需求快速搭建数据共享平台分享给组内同学协同查看等应用场景。点这里看详情。 4. 先决条件跟随本教程学习如何导入导出 MySQL 或 MariaDB 数据库,首先要有 一台 Linux 服务器,本文以 Ubuntu 为例 已安装 MySQL 或 MariaDB server (还未安装,安装教程请看这篇《MySQL 安装教程》) MySQL 或 MariaDB Server 中有数据库(用于导出) 教程使用 MacOS 演示本地计算机操作,此操作同时适用于 Windows 及 Linux 一. 导出 MySQL 或 MariaDB 数据库 1.如何使用 mysqldump 导出数据mysqldump 命令是数据库导出中使用最频繁对一个工具,它可将数据库中的数据备份成已 *.sql 结尾的文本文件,表结构和数据都会存储在其中。 mysqldump 命令的原理也很简单,它先把需要备份的表结构查询出来,然后生成一个 CREATE TABLE 'table' 语句,最后将表中所有记录转化成一条INSERT语句。 可以把它理解为一个批量导出导入脚本。数据导入时,按照规范语句导入数据,大幅减少奇怪的未知错误出现。 mysqldump 的基本命令: css 复制代码 $ mysqldump -u username -p database_name > data-dump.sql username 是数据库的登录名 database_name 是需要导出的数据库名称 data-dump.sql 是文件输出目录的文件导出实战 - 从阿里云服务器中的 MySQL 数据库导出数据 javascript 复制代码 $ mysqldump -u kalacloud -p kalacloud_database > /tmp/kalacloud-data-export.sql输入数据库 kalacloud 账号的密码执行命令,如果执行过程中,没有任何错误,那么命令行不会有任何输出。 我们可以 cd 到 tmp 目录查看结果。上图可以看到,tmp 目录下已经生成 kalacloud-data-export.sql 的导出文件。 我们在用head -n 5 kalacloud-data-export.sql命令检查一下。你会看到类似下图的内容。 至此,我们已经将指定数据库导出到 *.sql 文件中了,后文我们讲解如何将这些数据导入到数据库。 进阶提示:我们可以使用 scp 命令,将导出文件下载至本地计算机。 在本地计算机的命令行终端里,输入: ruby 复制代码 scp [email protected]:/tmp/kalacloud-data-export.sql /Users/kalacloud/Downloadsroot 远程计算机的登录账号 192.168.180.134 为远程计算机的 IP 地址 /tmp/kalacloud-data-export.sql 为需要下载到本地的数据库文件在远程计算机上的存储位置 /Users/kalacloud/Downloads 为本地计算机的存储位置,远程文件将下载到这个目录中 使用 scp 将导出的 SQL 文件下载到本地再进行后续处理。当然我们也可以一步导出至本地计算机,下面我们继续讲解进阶导出方法。 扩展阅读:《如何远程连接 MySQL 数据库,阿里云腾讯云外网连接教程》 2.进阶:将阿里云 / 腾讯云远程服务器中的数据库导出到本地计算机前文我们讲了如何在远程服务器上操作导出数据库,导出后保存在远程服务器中。有时我们需要把数据导出给产品或运营进行数据分析,又或者我们使用的云服务是独立 MySQL 数据库,这时,你需要直接把数据导出到本地计算机中。 vbnet 复制代码 $ 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 默认启用,我们可以通过此命令禁用它。 导出实战 - 将阿里云服务器中的数据库直接导出到本地计算机 arduino 复制代码 $ 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 也可以分表备份,比较常见的场景有 css 复制代码 # 备份单个库 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 数据库,阿里云、腾讯云迁移案例》 3.使用 into outfile 命令导出 MySQL / MariaDB 数据至 CSV / Excel有时我们需要将数据导出给运营或产品进行数据分析,这时导出 CSV 文件会更加方便使用。 csharp 复制代码 mysql> select * from users into outfile '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',';FIELDS TERMINATED BY ',' 数据以 , 进行分隔。 首先我们登录 MySQL shell,选择需要导出的数据库use kalacloud_database; 然后执行导出命令。 导出后会显示成功提示,CD 到导出目录可看到 CSV 文件已导出。 提示:into outfile 常见报错 vbscript 复制代码 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement这是因为你的 MySQL 配置了--secure-file-priv 限制了导出文件的存放位置。 你可以使用以下命令来查看具体配置信息 sql 复制代码 show global variables like '%secure_file_priv%';secure_file_priv 为 NULL 时,表示不允许导入或导出。 secure_file_priv 为路径时(/var/lib/mysql-files/ )时,表示只允许在路径目录中执行。 secure_file_priv 没有值时,表示可在任意目录的导入导出。 你可以打开 my.cnf 或 my.ini,添加以下语句,重启 MySQL server 即可 ini 复制代码 secure_file_priv=''扩展阅读:有关把 MySQL 查询出来的结果保存到文件可看我写的这篇《如何在 MySQL 中保存查询结果到文件》教程。 二. MySQL 或 MariaDB 数据库导入数据接着我们讲解如何将 *.sql 导入到数据库中。我们先建一个新数据库用作演示。 我们以 root 或有足够权限的账号登录 MySQL: css 复制代码 $ mysql -u root -p输入登录密码后,进入 MySQL shell 状态。接着我们创建一个新数据库,在这个例子中,我们用 kalacloud_new_database 作为新数据库名称。 shell 复制代码 mysql> CREATE DATABASE kalacloud_new_database;执行命令后返回内容 java 复制代码 Query OK, 1 row affected (0.00 sec)用于演示的新数据库创建完成,我们使用 CTRL+D 退出 MySQL shell 1.直接使用 mysql 导入 SQL 文件在命令行中我们导入上文导出的 /tmp/kalacloud-data-export.sql 文件(注意:以下命令在命令行中执行,不是在 mysql> 状态下执行) javascript 复制代码 mysql -u root -p kalacloud_new_database < /tmp/kalacloud-data-export.sql root :你可以登录数据库的用户名。 kalacloud_new_database :刚刚新建的空数据库,这条命令会把数据导入到这其中。 /tmp/kalacloud-data-export.sql :是上文我们从数据库导出的 sql 文件,这里我们把它再导入到新数据库中。如果运行成功,命令行不会有任何提示。如果运行失败,命令行会提示失败原因。要检测是否导入成功,我们可以登录到 MySQL 查看并检查数据库中的数据。 登录 MySQL server ,使用 USE kalacloud_new_database; 选择刚刚我们导入数据的新建数据库,然后使用SHOW TABLES; 查看数据库中包含的表,最后用SELECT * FROM users;打开表查看内容。 扩展阅读:《MySQL 配置文件逐行解析》教程 2.使用 source 导入 MySQL / MariaDB 数据库 SQL 文件进入 MySQL shell 状态,我们还是导入本教程前文导出的 /tmp/kalacloud-data-export.sql 文件,到新数据库中。 ini 复制代码 mysql> USE kalacloud_new_database;首先选择需要导入的数据库kalacloud_new_database,返回结果。 复制代码 Database changed然后使用 source shell 复制代码 mysql> source /tmp/kalacloud_new_database.sql;执行 source 命令后,MySQL 开始执行导入,接着我们使用 SHOW TABLES 和 select 来查看 SQL 文件是否导入正常。 上图可以看到,数据已经导入成功。 特别提示:source 和 mysql |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |