MySQL 几种导数据的方法与遇到的问题

darkdragonking

已于 2023-09-22 08:52:46 修改

阅读量1.1k

点赞数

文章标签： mysql 数据库

于 2023-09-18 14:10:22 首次发布

本文链接：https://blog.csdn.net/darkdragonking/article/details/132879739

版权

零、说在前面

MySQL导数据通常使用第三方工具和MySQL自身的工具，本文分别就这两类方法分别介绍。

一、第三方工具之 Navicat

1.1、Navicat的“数据传输”工具

打开Navicat，点击“工具”标签，找到“数据传输”，即可看到操作界面。这里不对这个工具本身做过多介绍，侧重点在于工具中的一些配置选项的含义的介绍上。如下图所示

上图的传输选项选择好之后，点击下一步，看到如下界面。然后根据需要选择传递哪些表或哪些对象。选好后点击“下一步”

以传输单表举例。选中左侧的某个表，然后可以在右侧看到“高级”选项，这里重点说一下高级选项中的一些参数

点击“下一步”开始数据传输阶段，如果一切顺利，那么过一段时间传输就完成了。

1.2、感想

上图中的“对每个记录集使用事务”勾选项，个人认为不会对数据源库有太大影响，仅仅当目标库开启了binlog后，才会对目标库有影响。众所周知有许多条件会个触发binlog写入磁盘，而完成一次事务也是触发条件之一。因此当这个选项被勾选后，那么我理解每一条插入语句都会被包装成一个事物，那么就会造成频繁的写binlog进而造成过多的IO开销，但好处就是不会造成关键数据的丢失。所以根据数据的重要程度来决定是否勾选这个选项吧。

二、MySQL自身工具之 mysqldump

2.1、使用方法

linux 操作系统下，通常会使用mysqldump 这种基于数据的逻辑备份方式来导出数据，此方法在恢复数据阶段的速度不是很快，但胜在稳定而且备份的文件是基于数据的，所以能不受数据库引擎影响。

导库命令格式为 "mysqldump -u登录名 -p 库名 > 路径文件名"；导表与导库方式的区别在于需要加上表名，命令格式为 "mysqldump -u登录名 -p 库名表名 > 文件期望保存的路径文件名"，如下图所示

上图中的导出数据的命令本身沒有什么可说的，只要命令正确就能导出成功。上图中有一点要说的就是我在导出命令前还加了一个 time 命令，目的是查看操作过程中的耗时情况。当导出操作成功后，会看到如上图下面三行所示的内容，其含义分别是

real: 实际用时，即从命令开始到命令结束的总用时，包括所有进程执行和阻塞等待的时间
user: 用户进程的CPU用时
sys:  CPU内核中执行系统命令调用花费的时间

当需要使用导出的文件进行数据恢复数据时的命令不再区分全库导入还是单表导入，命令都是 "mysql -u登录名 -p 库名 < 文件路径文件名 "，成功之后会在库中看到最新的数据，如下图所示

2.2、加快dump导数据的速度

如果MySQL打开了二进制日志，那么必然会影响写入速度，因此可以临时修改binlog的模式。关于binlog的一些原理，我将在稍后另开帖子补完。这里只说做法。

第一，将 innodb_flush_log_at_trx_commit 参数从默认值1改为0。其含义是把数据库操作写入binlog 和将数据写入磁盘的频率固定为一秒一次，且不受数据库操作是否含有事务的影响。

第二，将 sync_binlog 参数设置为0，此时数据库操作写入binlog文件和写入磁盘的频率受操作系统控制，不受数据库操作次数阈值的控制。

三、遇到的问题

3.1、磁盘空间不足

我在使用mysqldump导入单表的数据时，发现很久很久都没有动静，其耗时远高于导出数据用时，可以在MySQL的命令行中执行 "SHOW PROCESSLIST" 命令，如下图所示

在三次执行show processlist 命令后，观察这几次time字段的值，不难发现的一个insert 语句消耗了太多的时间却毫无进展。可以确认的是这个插入语句没有什么特殊考虑应该是某个环节出现了问题。

通过排查发现原来是存储空间满了。解决方法见我的 VMWare虚拟机扩容并挂载磁盘帖子扩容即可解决，此处就不再赘述了。

此外，除了使用“show processlist”命令查看之外，还可以通过使用 "show status" 命令查看收发的数据量增幅大小来判断任务是否有进展。如下图所示

需要注意的地方在于，做查询条件的参数名一定要用大写，切切！

3.2、字符集编码不一致

目前常用的MySQL版本多为5.7和8.0。其中5.7的编码默认为 latin1，而8.0及以后默认的utf-8编码是 utf8mb4。因此需要在互导数据之前确认一下源头与目标的字符集是否統一。同时建议新建数据库最好都使用 utf8mb4 编码。