mysql大量数据迁移（内含mysql表空间的介绍）

要看量有多大、业务是否可以暂停迁移 等
这个过程可以用于数据备份、数据迁移、数据整合等多种场景

来自 https://www.zhihu.com/people/huhu520-10/posts
和其他

逻辑迁移

逻辑迁移的原理是根据 MySQL 数据库中的数据和表结构转换成 SQL 文件。采用这一原理常用的迁移工具有 mysqldump

mysqldump

使用下面两行命令，将数据导出为一个sql，再导入到目标表里；

# 导出
mysqldump -u root -p --no-create-info --skip-extended-insert -t partition_demo tb_user > tb_user_data.sql

# 导入
mysql -u root -p partition_demo < tb_user_data.sql

其中，
partition_demo：数据库名；
tb_user：表名；
tb_user_data.sql：导出的文件名，可以在前面加上文件路径，文件内容实际上就是insert语句；
root：数据库用户名；

随着数据库递增，迁移的时长也会相应地增加。此时，如果需要迁移的数据表中的数据足够大（假设上千万条），mysqldump 很有可能会将内存撑爆进而导致迁移失败。所以，在迁移这样的数据表的时候，我们可以简单优化一下 mysqldump ，具体如下。
–add-locks=0：这个参数表示在迁移数据的时候不加 LOCK TABLES s1.s1 WRITE;，也就是说在导入数据时不锁定数据表。
–single-transaction：表示的是在导出数据时，不锁定数据表。
–set-gtid-purged=OFF：表示在导入数据时，不输出 GTID 相关的信息。

加上这三个参数主要是为了减少所有的操作导致不必要的 IO ，
但是最终结果，优化的效果微乎其微。所以，这种逻辑优化的方式，在数据量比较大的情况下（百万条以上）不可取。

直接insert into

如果表数据量不大，可以直接用下面这行SQL，将数据直接插入到另外一张表里。当然，下面SQL是全字段插入，如果需要做映射，可以选择某几个字段插入；

insert into tb_user select * from tb_user_source;

文件迁移

txt迁移法

文件迁移文件迁移顾名思义就是直接迁移数据库的存储文件。这种迁移方式相对于逻辑迁移的方式来说，性能上要高出很多，同时也很少会把内存撑爆；在面对数据量较大的场景下迁移数据，建议使用文件迁移的方式，具体如下：

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 关键字即可。

直接迁移数据文件

作者：Hu先生的Linux
链接：https://zhuanlan.zhihu.com/p/598514702
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

物理迁移物理迁移也是迁移文件，所不同是物理迁移一般是直接迁移 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 changedmysql> 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 changedmysql> 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/1.

将原有数据表解锁。
mysql> use s1;Database changedmysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
2.载入新的表空间。

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

3.测试。

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

我们看到此时就实现了数据迁移。这种数据迁移虽然性能很好，但是过程非常麻烦，很容易出现操作失误的情况。

直接迁移数据文件的另一篇 表空间直接迁移文件法

表空间（Tablespace）是用于管理和存储数据的一种逻辑概念。表空间主要用于 InnoDB 存储引擎，它允许多个表共享同一个物理文件，从而提高磁盘空间的利用率和管理效率。

表空间的概念
表空间：表空间是一个逻辑容器，用于存储一个或多个表的数据。每个表空间可以包含一个或多个数据文件（通常是 .ibd 文件）。
数据文件：每个数据文件是一个物理文件，存储在文件系统中。InnoDB 表的数据和索引信息都存储在这些数据文件中。

类型
MySQL 中主要有两种类型的表空间：

系统表空间（System Tablespace）：

也称为 ibdata 文件。
默认情况下，所有表的数据和索引都存储在系统表空间中。
系统表空间通常包含多个文件，文件名默认为 ibdata1、ibdata2 等。
系统表空间还包含数据字典、事务日志、双写缓冲区（Doublewrite Buffer）等元数据信息。
独立表空间（File-Per-Table Tablespace）：

每个表都有一个独立的数据文件，文件名与表名相同，扩展名为 .ibd。
独立表空间的好处是每个表的数据和索引都存储在一个单独的文件中，便于管理和备份。
启用独立表空间需要在 MySQL 配置文件中设置 innodb_file_per_table 参数为 ON。
配置参数
innodb_file_per_table：

控制是否启用独立表空间。
默认值为 ON（MySQL 5.6.6 及以后版本）。
如果设置为 OFF，所有表的数据和索引都将存储在系统表空间中。
innodb_data_file_path：

指定系统表空间的数据文件路径和大小。
例如：innodb_data_file_path=ibdata1:10M:autoextend
innodb_data_home_dir：

指定系统表空间数据文件的根目录。
例如：innodb_data_home_dir=/var/lib/mysql/
创建和管理表空间
创建表空间
CREATE TABLESPACE ts1 ADD DATAFILE ‘ts1.ibd’ ENGINE=InnoDB;
1
删除表空间
DROP TABLESPACE ts1;
1
查看表空间
SHOW TABLESPACE;
1
示例
假设我们有一个数据库 mydb，并且希望创建一个独立表空间的表 mytable。

启用独立表空间：
在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中添加或修改以下配置：

[mysqld]
innodb_file_per_table = ON
1
2
创建表：

CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
) ENGINE=InnoDB;
1
2
3
4
5
查看表空间：

SHOW TABLESPACE;
1
注意事项
迁移表到独立表空间：
如果已经创建了表并且存储在系统表空间中，可以使用 ALTER TABLE 命令将其迁移到独立表空间：

ALTER TABLE mytable ENGINE=InnoDB;
1
回收表空间：
当删除表时，独立表空间的数据文件并不会自动删除。需要手动删除：

DROP TABLE mytable;
– 手动删除 .ibd 文件
rm /path/to/mytable.ibd
1
2
3
性能影响：

独立表空间可以提高磁盘空间利用率和管理效率。
但过多的独立表空间文件可能会导致文件系统开销增加，特别是在有大量表的情况下。
————————————————

                        版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/a772304419/article/details/143696462

步骤

1.源实例上获取表的定义

show create table

2.目标实例上创建表，根据以上获取到的定义

create table t11 int)ENGINE=INNODB;

3.在目标实例丢弃刚刚创建的表的表空间

ALTER TABLE t1 DISCARD TABLESPACE;

4.源实例上运行 FLUSH TABLES…FOR EXPORT语句，使要导入的表进入静止状态，此时该表只允许只读事务

FLUSH TABLES tI FOR EXPORT;

5.将.ibd 文件和.cfg 元数据文件从源实例复制到目标实例。

scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test6.

在源实例上，使用 UNLOCK TABLES 释放由 FLUSH TABLES…FOR EXPORT 语句获取的锁

UNLOCK TABLES

7.在目标实例上，导入表空间

ALTER TABLE t1 IMPORT TABLESPACE:

使用SELECT INTO OUTFILE和LOAD DATA INFILE

这种方法适用于在服务器之间迁移数据，特别是当使用mysqldump不方便或者需要更高效迁移大量数据时。

导出数据：

SELECT * INTO OUTFILE '/path/to/local/table_data.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM database_name.table_name;

导入数据：

LOAD DATA INFILE '/path/to/local/table_data.csv'
INTO TABLE new_database_name.table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

其他方法

Canal

可以使用Canal，用代码的方式转移，这种方式的好处是灵活可控，数据量大的表也可以，另外可以实现在线转移，不影响线上业务。参考下面这两篇文章：

主从同步 然后从变主