MySQL数据库- 几种数据迁移的方法详解都在这了！看完必懂_事实生产的数据怎么全量转移到另外一个库

数据迁移的几种方式 - MySQL数据库

本文关键字：MySQL、数据库、数据迁移、导入、导出

文章目录

• 数据迁移的几种方式 - MySQL数据库
  • 一、数据迁移
    • 1. 数据的生命周期
    • 2. 数据迁移的发生
  • 二、数据导出
    • 1. 命令方式
    • 2. 软件方式
  • 三、数据导入
    • 1. 命令方式
    • 2. 软件方式
  • 四、数据传输与数据同步
    • 1. 数据传输
    • 2. 数据同步

开始和数据库玩耍以后，我们将一直与SQL和数据打交道。在日常的操作中，我们只需要对指定的数据库进行操作，执行增删改查，权限管理等。但有些时候由于项目的升级，或者服务器的更换，我们要将数据从一个地方转移到另一个地方，准确的说是从一个数据库服务转移到另一个数据库服务中，因为我们还要继续使用这些数据。

一、数据迁移

数据迁移的操作在应用运行或数据使用过程中十分常见，这里为了让大家对数据有一个更完善的认识，先给大家科普一下数据的生命周期。

1. 数据的生命周期

在数据使用的过程中，同样有一个生命周期的贯穿其中：

 

• 创建：创建是数据的起点，对应业务中的逻辑，如：用户注册、开卡、登记等，此时数据从客观世界流入到数据库。
• 存储：存储是数据生存的过程，按照需要被管理，在复杂系统中会有严格的访问权限控制。
• 使用：使用是数据存在的意义，会在保障数据安全的情况下进行数据的变更与查询。
• 共享：在数据的使用过程中，经常会涉及到数据的共享，用于打通各平台或子系统，合理的共享数据有利于更好的支持和发展业务。
• 归档：一旦数据归档，意味着数据的使用频次将明显下降，归档数据的存储位置一般是适合长久保存的介质，有需要也会进行加密处理。
• 销毁：数据销毁代表数据生命周期的结束，会将数据完全删除，但在大数据时代下，数据即财富，通常只会进行归档操作。

2. 数据迁移的发生

引述维基百科中对数据迁移的解释，可以说比较亲民也很全面了：

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。
数据迁移是任何系统实现、升级或集成的关键考虑因素，通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。数据迁移有多种原因，包括服务器或存储设备更换、维护或升级、应用程序迁移、网站集成、灾难恢复和数据中心迁移。

如果按照数据的流向来分类，可以将数据迁移分为数据导出和数据导入两种操作，这种方式通常会存在一个中间文件，有可能是sql格式的文件，也有可能是各种格式的数据文件。将这些文件保存在磁盘上，需要时再导入到另外的数据库中，这种方式虽然会生成文件，但是可以随时进行数据的恢复。另外一种方式是在原数据库服务与目标数据库服务均开启的情况下，直接进行数据的传输。
由于数据文件的格式多种多样，数据迁移的方式也是多种多样，所以本文只介绍常用的转储SQL、运行SQL、数据传输、数据同步。

二、数据导出

SQL作为通用的数据库语言，可以将数据库中存在的结构和数据转换为完整的SQL语句，包含建表语句以及数据插入语句，并且会根据表间关系自动生成顺序合理的SQL。通常情况下，如果我们以导出数据文件的方式进行数据的迁移，SQL格式将成为首选，甚至可以跨库进行（由于SQL语法通用，经过处理后可以在不同的DBMS之间迁移数据）。

1. 命令方式

• 单表数据导出

如果使用单表数据导出，需要提前已经配置了secure-file-priv选项，来指定一个导出目录，如果未配置则无法导出，导出时会生成一个新的文件。

 

如果是第一次配置，配置完成后需要重启MySQL服务。（如果是Windows系统，请使用Windows路径写法）

SELECT * FROM `表名` INTO OUTFILE '文件路径';

 

单表数据的导出会以数据文件的形式存在，每条数据单独成行，每列数据以分隔符隔开，可以用记事本直接打开，在进行数据导入的时候也可以直接使用。

 

同时，我们也可以使用mysqldump工具直接导出为SQL文件，这其实是个备份工具，支持数据表及数据库的导出操作。

mysqldump -u 用户名 -p 数据库名 数据表名 > '文件路径'

 

会生成建表语句及数据插入语句。

 

• 数据库转储

mysqldump -u 用户名 -p 数据库名 > '文件路径' 

 

会自动生成数据库中所有表的建表语句以及数据插入语句。

 

2. 软件方式

没有使用过Navicat工具的小伙伴可以查看这篇文章，插看基本操作：MySQL数据库界面化工具 - Navicat的常用功能

• 单表数据导出

使用界面化工具来进行数据导出十分简单，以Navicat为例。选择某一个数据表，右键点击：导出向导。

 

根据需要选择对应的数据文件格式：

 

 

 

 

 

通过这种方式导出的sql文件只包含数据，并不包含表结构，如下：

 

• 数据库转储

打开数据库连接后，选择要导出的数据库，右键：转储SQL -> 结构+数据 -> 选择路径。

 

三、数据导入

准备好数据文件或SQL文件后，就可以开始进行数据导入的操作，如果SQL文件中不包含建表语句，则需要先建立表结构，保证字段名称、字段类型（长度）、约束条件等于数据兼容。

1. 命令方式

• 单表数据导入

对于导出得到的数据文件，需要使用LOAD DATA INFILE语句来插入数据：

load data local infile '文件路径' into table 表名;

 

导入完成后会显示执行状态，如果表中存在主键约束或唯一性约束，则相同的数据会被跳过。

• SQL导入

如果在进行数据导出时，直接生成了可执行的SQL文件，则可以使用source命令执行该文件，这种方式同时适用与单表数据导入与整个数据库导入。

source 文件路径;

 

加载sql文件后会显示每条SQL语句的执行结果，其中Query OK代表成功，rows affected代表当前语句对数据库的影响行数（0 rows affected对应建表语句和各种指令，只要显示Query OK就证明执行成功）。

2. 软件方式

• 单表数据导入

选择需要导入数据的数据表，右键：导入向导。

 

 

 

 

 

 

 

 

根据需要选择导入模式，最后点击开始即可。

• SQL导入

如果根据已有的SQL文件执行数据导入，可以直接在打开一个数据库后，新建一个查询窗口，然后将sql文件中的内容粘贴到窗口中执行就好了，这种方式同时适用于单表数据导入与数据库整体导入。
除此之外，如果我们想要进行整个数据库的数据导入，也可以直接右键数据库：运行SQL。

 

 

四、数据传输与数据同步

1. 数据传输

使用数据传输功能的效果与通过SQL语句进行数据导入类似，一般都会先删除已存在的相同的表，这一点一定要注意，使得最终结果与数据源相同，完成完整的数据迁移。
点击工具菜单中的数据传输开始使用：

 

 

 

 

 

2. 数据同步

数据同步的操作步骤与数据传输类似，同样需要先指定数据源与目标数据库，区别在于数据同步会先比较两个数据库的差异，用户可以自行决定是否进行同步，主要适用于有相同表结构的数据库以及主从分布等场景。