共计 5265 个字符，预计需要花费 14 分钟才能阅读完成。
咱们通常会遇到这样的一个场景，就是须要将一个数据库的数据迁徙到一个性能更加强悍的数据库服务器上。这个时候须要咱们做的就是疾速迁徙数据库的数据。
那么，如何能力疾速地迁徙数据库中的数据呢？明天咱们就来聊一聊这个话题。
数据库的数据迁徙无外乎有两种形式，一种是物理迁徙，另一种则是逻辑迁徙。
首先，咱们生成 5 万条测试数据。具体如下：
— 1. 筹备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
— 2. 创立存储过程，实现批量插入记录
delimiter $$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<50000)do
insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));
set i=i+1;
select concat('shanhe',i,'_ok');
end while;END$$
delimiter ;
— 3. 查看存储过程
show create procedure auto_insert1\G
— 4. 调用存储过程
call auto_insert1()
复制代码
逻辑迁徙
逻辑迁徙的原理是依据 MySQL 数据库中的数据和表构造转换成 SQL 文件。采纳这一原理罕用的迁徙工具有 mysqldump。
上面咱们就来测试一下：
[root@dxd ~]# mysqldump -h172.17.16.2 -uroot -pTest123! s1 s1 –result-file=/opt/s1.sql
[root@dxd ~]# ll /opt/
-rw-r–r– 1 root root 2684599 5 月 10 00:24 s1.sql
复制代码
咱们能够看到的是，生成了相应的 SQL。当初咱们通过生成的 SQL 迁徙到另一个数据库中。
mysql> use s2;
Database changed
mysql> source /opt/s1.sql
复制代码
通过简略的工夫累加计算，大概耗费了 1 秒钟的工夫，然而随着数据库递增，迁徙的时长也会相应地减少。此时，如果须要迁徙的数据表中的数据足够大（假如上千万条），mysqldump 很有可能会将内存撑爆进而导致迁徙失败。所以，在迁徙这样的数据表的时候，咱们能够简略优化一下 mysqldump，具体如下。
–add-locks=0：这个参数示意在迁徙数据的时候不加 LOCK TABLES s1.s1 WRITE;，也就是说在导入数据时不锁定数据表。
–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=OFF s1 s1 –result-file=/opt/s1.sql
复制代码
通过下面的案例，咱们看最终后果，优化的成果微不足道。所以，这种逻辑优化的形式，在数据量比拟大的状况下（百万条以上）不可取。
文件迁徙
文件迁徙顾名思义就是间接迁徙数据库的存储文件。这种迁徙形式绝对于逻辑迁徙的形式来说，性能上要高出很多，同时也很少会把内存撑爆；在面对数据量较大的场景下迁徙数据，倡议应用文件迁徙的形式，具体如下：
mysql> select * from s1 into outfile ‘/var/lib/mysql-files/1.txt’;
Query OK, 55202 rows affected (0.04 sec)
复制代码
咱们能够看到的是，将 5 万多条数据导出到文件中时，只花了 0.04 秒左右的工夫。相比拟 mysqldump 来说快了一倍多。
留神：这种形式导出的数据只能导出到 MySQL 数据库的目录中。配置这个目录的参数是 secure_file_priv，如果不这样做，数据库会报一个 ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement 的谬误。
导出数据之后，咱们再将该文件中的数据导入到数据库中，看一下成果，具体如下：
mysql> load data infile ‘/var/lib/mysql-files/1.txt’ into table s3.s1;
Query OK, 55202 rows affected (0.27 sec)
Records: 55202 Deleted: 0 Skipped: 0 Warnings: 0
复制代码
留神：into outfile 是不会生成表构造的，因而在导入数据之前，须要手动创立表构造。
咱们能够看出，导入破费的工夫总共是 0.27 秒，相比拟 mysqldump 而言，也要快两倍多。
这种形式次要是将每一条数据都以 \n 换行的形式间接保留在文件之中。
导入的时候，首先会判断导入的数据表的字段是否与每一行的数据的列数统一，如果统一则一行一行地导入，如果不统一则间接报错。
这外面有一个问题须要咱们留神，如果咱们的数据库是主从架构的数据库，这里很可能就会产生一个问题。讲这个问题之前，咱们得首先在这里略微阐明一下主从复制的原理。
主从复制的原理次要是依赖于 binlog 日志，binlog 日志具体步骤如下：
主库上执行 SQL，并且把批改的数据保留在 binlog 日志之中；
由主库上的 dump 线程转发给从库；
由从库中的 IO 线程接管主库发送过去的 binlog 日志；
将 binlog 日志数据写入中继日志之中；
通过从库上的 SQL 线程从中继日志中重放 binlog 日志，进而达到主从数据统一。
在这个过程之中，我置信仔细阅读本小册第 15 篇文章的敌人肯定有一个疑难，当 binlog 日志的工作模式为 STATEMENT 时，在主库上执行下面的 SQL load data infile ‘/var/lib/mysql-files/1.txt’ into table s3.s1; 时，就会导致从库无奈反复上方 SQL 的后果，这是因为从库中并没有 /var/lib/mysql-files/1.txt 这个文件。具体步骤如下：
主库执行 load data infile ‘/var/lib/mysql-files/1.txt’ into table s3.s1;；
binlog 日志的工作模式如果是 STATEMENT 时，将在 binlog 中记录上方的 SQL；
而后在从库中从新执行 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@dxd mysql]# pwd
/var/lib/mysql
[root@dxd mysql]# cp -r s1 s4
[root@dxd mysql]# chown -R mysql.mysql s4
— 重启数据库
[root@dxd mysql]# systemctl restart mysqld
— 查看该表数据
mysql> select count(*) from s1;
ERROR 1146 (42S02): Table ‘s4.s1’ doesn’t exist
复制代码
咱们能够看到的是查问数据的时候报了一个 1146 的谬误，这是因为 INnoDB 存储引擎中的数据表是须要在 MySQL 数据库的数据字典中注册的，咱们间接将数据文件复制过来的时候并没有在数据字典中注册，换句话说就是在把数据复制过来之后，还须要在数据字典中注册数据库系统能力失常辨认。
上面咱们就来介绍一下在数据字典中该如何注册，具体步骤如下。
注：物理迁徙数据表数据实际上最次要的就是迁徙表空间，因为对于 InnoDB 存储引擎来说，数据是存储在数据表空间中的，也就是.idb 文件。
咱们在迁徙到的数据库中创立与须要迁徙的数据表完全相同的数据表。
mysql> create database t1;
Query OK, 1 row affected (0.01 sec)
mysql> use t1;
Database changed
mysql> CREATE TABLE s1 (
-> id int(11) DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> gender char(6) DEFAULT NULL,
-> email varchar(50) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码
Query OK, 0 rows affected (0.04 sec)
删除新创建的数据表的表空间，这是因为新创建的数据库的表空间没有数据且会跟迁徙过去的数据表空间抵触，咱们提前删除，具体删除步骤如下：
mysql> alter table t1.s1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)
创立一个原有数据表的配置文件，这样做的目标是将原有数据表的一些配置复制过去（留神：这一步会主动将数据表上锁）。
mysql> use s1;
Database changed
mysql> flush table s1 for export;
Query OK, 0 rows affected (0.01 sec)
查看是否曾经创立 .cfg 文件
[root@dxd mysql]# pwd
/var/lib/mysql
[root@dxd mysql]# ll s1/
总用量 12312
-rw-r——- 1 mysql mysql 65 5 月 10 00:26 db.opt
-rw-r——- 1 mysql mysql 520 5 月 10 15:15 s1.cfg
-rw-r——- 1 mysql mysql 8652 5 月 10 00:27 s1.frm
-rw-r——- 1 mysql mysql 12582912 5 月 10 00:27 s1.ibd
将配置文件和表空间文件迁徙至新的数据库。
复制文件的形式能够灵便多变
[root@dxd mysql]# cp s1/s1.cfg t1/
[root@dxd mysql]# cp s1/s1.ibd t1/
设置权限，很重要，如果权限不统一会导致数据读取表空间数据失败
[root@dxd mysql]# chown -R mysql.mysql t1/
将原有数据表解锁。
mysql> use s1;
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
载入新的表空间。
mysql> use t1;
mysql> alter table s1 import tablespace;
Query OK, 0 rows affected (0.09 sec)
测试。
mysql> select count() from s1;
+—————+
| count() |
+—————+
| 55202 |
+—————+
1 row in set (0.03 sec)
咱们看到此时就实现了数据迁徙。
这种数据迁徙尽管性能很好，然而过程十分麻烦，很容易呈现操作失误的状况。
总结
明天，咱们介绍了三种数据库迁徙的形式，别离是：逻辑迁徙、文件迁徙和物理迁徙。
逻辑迁徙的形式次要是应用 mysqldump 命令进行迁徙，其原理次要是将数据库中的数据和构造生成 SQL 文件，再导入即可。这种迁徙形式次要实用于数据量比拟小且服务器性能较好的场景下，例如数据连少于 500 万条以下的场景。
文件迁徙的形式其实也算是逻辑迁徙的领域，它次要通过命令将数据保留在文件中，而后再导入数据库即可，这种迁徙形式是不会迁徙表构造的，所以在导入数据之前须要手动创立表构造，其原理跟逻辑迁徙的形式雷同。
物理迁徙的形式实用于数据量比拟大的场景，这种场景不易导致服务器因资源占用过多而宕机，然而操作过程麻烦且会锁定原数据表。
在理论利用过程中，咱们通常抉择应用 mysqldump 的形式进行数据迁徙；如果数据量大，咱们首选形式应该是晋升服务器的性能，以至于它可能承载解决相应数据量的性能；如果必须迁徙，能够思考应用第三方业余的数据迁徙工具。
