我们通常会遇到这样的场景，就是我们需要将一个数据库的数据迁移到一个性能更强大的数据库服务器上。这个时候我们要做的就是快速迁移数据库中的数据。那么，如何才能快速迁移数据库中的数据呢？今天我们就来聊聊这个话题。数据库数据迁移有两种方式，一种是物理迁移，一种是逻辑迁移。首先，我们生成50,000个测试数据。具体如下：--1.准备表createtables1(idint,namevarchar(20),genderchar(6),emailvarchar(50));--2.创建存储过程实现batch插入记录分隔符$$createprocedureauto_insert1()BEGINdeclareiintdefault1;while(i<50000)doinsertintos1values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));设我=我+1；selectconcat('shanhe',i,'_ok');endwhile;END$$delimiter;--3.查看存储过程showcreateprocedureauto_insert1\G--4.调用存储过程callauto_insert1()复制代码逻辑迁移逻辑迁移的原理是将MySQL数据库中的数据和表结构转换成SQL文件。利用这个原理的常用迁移工具是mysqldump。下面我们来测试一下：[root@dxd~]#mysqldump-h172.17.16.2-uroot-pTest123！s1s1--result-file=/opt/s1.sql[root@dxd~]#ll/opt/-rw-r--r--1rootroot2684599May1000:24s1.sql复制代码我们可以看到生成了对应的SQL。现在我们通过生成的SQL迁移到另一个数据库。mysql>uses2;Databasechangedmysql>source/opt/s1.sql复制代码通过简单的时间累加计算大约需要1秒，但是随着数据库的增加，迁移时间也会相应增加。这时候如果需要迁移的数据表中的数据足够大（假设上千万），mysqldump很可能爆内存导致迁移失败。因此，在迁移这样的数据表时，我们可以简单的优化mysqldump，如下。--add-locks=0：该参数表示不添加LOCKTABLESs1.s1WRITE；迁移数据时，即导入数据时不锁定数据表。--single-transaction：表示导出数据时不锁定数据表。--set-gtid-purged=OFF：表示导入数据时不输出GTID相关信息。添加这三个参数主要是为了减少所有操作带来的不必要的IO，如下：[root@dxd~]#mysqldump-h172.17.16.2-uroot-pTest123!--add-locks=0--single-transaction--set-gtid-purged=OFFs1s1--result-file=/opt/s1.sql复制代码通过上面的案例，我们可以看到最终的结果，并且优化的效果很小。所以这种逻辑优化方式在数据量比较大（百万条以上）时不可取。文件迁移顾名思义，文件迁移就是直接迁移数据库的存储文件。与逻辑迁移方式相比，这种迁移方式性能要高很多，同时很少爆内存；在数据量较大的场景下迁移数据时，推荐使用文件迁移方式。具体如下：mysql>select*froms1intooutfile'/var/lib/mysql-files/1.txt';查询OK，55202行受影响（0.04秒）复制代码我们可以看到，50000多行数据导出到文件时，只用了0.04秒左右。与mysqldump相比，速度快了一倍以上。注意：这种方式导出的数据只能导出到MySQL数据库的目录下。配置这个目录的参数是secure_file_priv。如果不这样做，数据库会报错ERROR1290(HY000)：MySQL服务器正在以--secure-file-priv选项运行，因此无法执行这条语句。导出数据后，我们将文件中的数据导入数据库中查看效果，如下：mysql>loaddatainfile'/var/lib/mysql-files/1.txt'intotables3.s1;QueryOK,55202rowsaffected(0.27sec)Records:55202Deleted:0Skipped:0Warnings:0Copycode注意：intooutfile不会生成表结构，所以需要在导入数据之前手动创建表结构。我们可以看到导入总共耗时0.27秒，比mysqldump快了一倍多。这种方法主要是将每条数据以\n换行的形式直接保存在文件中。导入时会先判断导入的数据表的字段是否与每行数据的列数一致。如果一致，则逐行导入。如果没有，会直接报错。这里有一个问题需要引起我们的注意。如果我们的数据库是主从架构的数据库，那么这里很有可能会出现问题。在说这个问题之前，我们不得不在这里解释一下主从复制的原理。主从复制的原理主要依赖于binlog日志，binlog日志的具体操作步骤如下：在主库上执行SQL，将修改后的数据保存到binlog日志中；将主库上的dump线程转发给从库；库中IO线程接收主库发送的binlog日志；将binlog日志数据写入relaylog；从库上通过SQL线程从relaylog中重放binlog日志，进而实现主从数据一致性。在这个过程中，相信仔细阅读这本小册子第15条的朋友们一定会有一个疑问。当binlog日志的工作模式为STATEMENT时，执行上面的SQLloaddatainfile'/var/libonthemainlibrary/mysql-files/1.txt'intotables3.s1；，上面SQL的结果不能从库中重复出来，因为库文件中没有/var/lib/mysql-files/1.txt。具体步骤如下：主库执行loaddatainfile'/var/lib/mysql-files/1.txt'intotables3.s1;;如果binlog日志的工作模式为STATEMENT，则上面的SQL会被记录到binlog中；然后重新执行从库binlog中记录的上述SQL。很明显，当从库执行SQL时，会立即报错。这个时候怎么办？这时候就需要引入上面SQL的load关键字：如果加上local关键字，SQL会在本地搜索/var/lib/mysql-files/1.txt；如果不加local关键字，SQL会在主库端寻找/var/lib/mysql-files/1.txt。因此，在主从架构中，需要使用文件迁移的方式来迁移数据，而无需添加local关键字。物理迁移物理迁移也是迁移文件，不同的是物理迁移一般是直接迁移MySQL数据文件。这种迁移方式性能良好，但操作过程繁琐且容易出错。具体的，让我们详细解释一下。首先是一种非常简单的迁移方式，就是直接将MySQL数据库的数据文件打包迁移。下面做个例子：--我们把s1数据库的数据全部迁移到s4数据库[root@dxdmysql]#pwd/var/lib/mysql[root@dxdmysql]#cp-rs1s4[root@dxdmysql]#chown-Rmysql.mysqls4--重启数据库[root@dxdmysql]#systemctlrestartmysqld--查看表数据mysql>selectcount(*)froms1;ERROR1146(42S02):Table's4.s1'doesn'texist复制代码我们可以看到在查询数据的时候报了一个1146的错误，这是因为INnoDB存储引擎中的数据表需要注册到MySQL数据库的数据字典中。当我们直接复制数据文件时，它并没有注册到数据字典中。换句话说，我们正在复制数据。之后需要将数据库系统注册到数据字典中才能正常识别。接下来介绍一下如何在数据字典中进行注册。具体步骤如下。注意：物理迁移数据表数据最重要的是迁移表空间，因为对于InnoDB存储引擎来说，数据是存放在数据表空间中的，即.idb文件。我们在要迁移的数据库中创建与需要迁移的数据表完全相同的数据表。mysql>createdatabaset1;QueryOK,1rowaffected(0.01sec)mysql>uset1;Databasechangedmysql>CREATETABLEs1(->idint(11)DEFAULTNULL，->namevarchar(20)DEFAULTNULL，->genderchar(6)DEFAULTNULL,->emailvarchar(50)DEFAULTNULL->)ENGINE=InnoDBDEFAULTCHARSET=utf8;复制代码QueryOK,0rowsaffected(0.04sec)删除新建数据表的表空间，这是因为新建数据库的表空间没有数据，会和迁移的数据表空间冲突。我们提前删除它。具体删除步骤如下：mysql>altertablet1.s1discardtablespace;QueryOK,0rowsaffected(0.01sec)创建原始数据表的配置文件。这样做的目的是复制原数据表的一些配置（注意：这一步会自动锁定数据表）。mysql>uses1;Databasechangedmysql>flushtables1forexport;QueryOK,0rowsaffected(0.01sec)检查.cfg文件是否已经创建[root@dxdmysql]#pwd/var/lib/mysql[root@dxdmysql]#lls1/总使用量12312-rw-r---1mysqlmysql65May1000:26db.opt-rw-r---1mysqlmysql520May1015:15s1.cfg-rw-r————1mysqlmysql8652May1000:27s1.frm-rw-r————1mysqlmysql12582912May1000:27s1.ibd将配置文件和表空间文件迁移到新数据库。复制文件的方式可以灵活多变[root@dxdmysql]#cps1/s1.cfgt1/[root@dxdmysql]#cps1/s1.ibdt1/设置权限很重要，如果权限不一致，dataFailedtoreadtablespacedata[root@dxdmysql]#chown-Rmysql.mysqlt1/解锁原数据表。mysql>使用s1；数据库更改mysql>unlocktables;查询正常，0行受影响（0.00秒）加载新表空间。mysql>使用t1；mysql>改变表s1导入表空间；查询正常，0行受影响（0.09秒）测试。mysql>selectcount()froms1;+——————+|计数（）|+——————+|55202|+——————+1rowinset(0.03sec)weseethis当实现数据迁移时。这种数据迁移虽然性能不错，但是过程非常繁琐，容易出现操作错误。小结今天，我们介绍了三种数据库迁移方式，分别是：逻辑迁移、文件迁移和物理迁移。逻辑迁移的主要方法是使用mysqldump命令进行迁移。原理是将数据库中的数据和结构生成SQL文件，然后导入。这种迁移方式主要适用于数据量比较小，服务器性能较好的场景，比如数据连接小于500万的场景。文件迁移方式其实属于逻辑迁移范畴。它主要是通过命令将数据保存在文件中，然后导入到数据库中。该迁移方式不迁移表结构，需要在导入数据前手动创建。表结构，其原理与逻辑迁移相同。物理迁移方式适用于数据量比较大的场景。在这种场景下，不容易因为资源占用过多导致服务器宕机，但是操作过程比较繁琐，而且会锁住原来的数据表。在实际应用过程中，我们通常会选择使用mysqldump进行数据迁移；如果数据量很大，我们首选的方法应该是提高服务器的性能，使其能够处理相应数据量的性能；如果需要迁移，可以考虑使用第三方专业的数据迁移工具。
