咱们通常会遇到这样的一个场景，便是需求将一个数据库的数据搬迁到一个功能愈加强悍的数据库服务器上。这个时分需求咱们做的便是快速搬迁数据库的数据。

那么，怎么才干快速地搬迁数据库中的数据呢？今天咱们就来聊一聊这个话题。

数据库的数据搬迁无外乎有两种方法，一种是物理搬迁，另一种则是逻辑搬迁。

首要，咱们生成 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 TABLESs1.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=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时，在主库上履行上面的 SQLload data infile '/var/lib/mysql-files/1.txt' into table s3.s1;时，就会导致从库无法重复上方 SQL 的成果，这是由于从库中并没有/var/lib/mysql-files/1.txt这个文件。详细步骤如下：

  1. 主库履行load data infile '/var/lib/mysql-files/1.txt' into table s3.s1;；
  2. binlog 日志的作业模式假如是STATEMENT时，将在 binlog 中记载上方的 SQL；
  3. 然后在从库中从头履行 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文件。

  1. 咱们在搬迁到的数据库中创立与需求搬迁的数据表完全相同的数据表。

    mysql> create database t1;
    Query OK, 1 row affected (0.01 sec)

    mysql> use t1;
    Database changed

    mysql> CREATE TABLEs1(

    ->   `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)

  1. 删除新创立的数据表的表空间，这是由于新创立的数据库的表空间没有数据且会跟搬迁过来的数据表空间冲突，咱们提早删除，详细删除步骤如下：

    mysql> alter table t1.s1 discard tablespace;
    Query OK, 0 rows affected (0.01 sec)

  1. 创立一个原有数据表的装备文件，这样做的意图是将原有数据表的一些装备仿制过来（留意：这一步会主动将数据表上锁）。

    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

  1. 将装备文件和表空间文件搬迁至新的数据库。

    仿制文件的方法能够灵敏多变

    [root@dxd mysql]# cp s1/s1.cfg t1/
    [root@dxd mysql]# cp s1/s1.ibd t1/

    设置权限，很重要，假如权限不共同会导致数据读取表空间数据失利

    [root@dxd mysql]# chown -R mysql.mysql t1/

  1. 将原有数据表解锁。

    mysql> use s1;
    Database changed

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

  1. 载入新的表空间。

    mysql> use t1;

    mysql> alter table s1 import tablespace;
    Query OK, 0 rows affected (0.09 sec)

  1. 测验。

    mysql> select count( ) from s1;
    +—————+
    | count(     ) |
    +—————+
    | 55202 |
    +—————+
    1 row in set (0.03 sec)

咱们看到此刻就完成了数据搬迁。

这种数据搬迁尽管功能很好，可是进程十分费事，很简单呈现操作失误的状况。

总结

今天，咱们介绍了三种数据库搬迁的方法，分别是：逻辑搬迁、文件搬迁和物理搬迁。

逻辑搬迁的方法首要是运用mysqldump指令进行搬迁，其原理首要是将数据库中的数据和结构生成 SQL 文件，再导入即可。这种搬迁方法首要适用于数据量比较小且服务器功能较好的场景下，例如数据连少于 500 万条以下的场景。

文件搬迁的方法其实也算是逻辑搬迁的范畴，它首要经过指令将数据保存在文件中，然后再导入数据库即可，这种搬迁方法是不会搬迁表结构的，所以在导入数据之前需求手动创立表结构，其原理跟逻辑搬迁的方法相同。

物理搬迁的方法适用于数据量比较大的场景，这种场景不易导致服务器因资源占用过多而宕机，可是操作进程费事且会锁定原数据表。

在实践应用进程中，咱们通常挑选运用 mysqldump 的方法进行数据搬迁；假如数据量大，咱们首选方法应该是提高服务器的功能，以至于它能够承载处理相应数据量的功能；假如有必要搬迁，能够考虑运用第三方专业的数据搬迁东西。