MySQL에서 데이터를 빠르게 마이그레이션하는 방법을 자세히 설명하는 기사
- 藏色散人앞으로
- 2023-03-25 16:03:301839검색
이 기사에서는 MySQL에서 데이터를 빠르게 마이그레이션하는 방법에 대해 주로 설명합니다. 관심 있는 친구는 아래를 살펴보고 모든 사람에게 도움이 되기를 바랍니다.
우리는 일반적으로 데이터베이스에서 더 강력한 성능을 갖춘 데이터베이스 서버로 데이터를 마이그레이션해야 하는 시나리오에 직면합니다. 이때 우리가 해야 할 일은 데이터베이스 데이터를 빠르게 마이그레이션하는 것입니다.
그렇다면 데이터베이스의 데이터를 어떻게 빠르게 마이그레이션할 수 있을까요? 오늘 우리는 이 주제에 대해 이야기하겠습니다.
기본적으로 데이터베이스 데이터를 마이그레이션하는 방법에는 두 가지가 있습니다. 하나는 물리적 마이그레이션이고 다른 하나는 논리적 마이그레이션입니다.
먼저 50,000개의 테스트 데이터를 생성합니다.
-- 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 파일로 변환하는 것입니다. 이 원칙을 사용하는 일반적으로 사용되는 마이그레이션 도구에는 mysqldumpmysqldump。
下面我们就来测试一下：
[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가 있습니다.
- 지금 테스트해 보겠습니다.
-- 我们将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
우리가 볼 수 있는 것은 해당 SQL이 생성된다는 것입니다. 이제 생성된 SQL을 사용하여 다른 데이터베이스로 마이그레이션합니다.
mysql> create database t1; Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> CREATE TABLE s1 (
간단한 시간 누적 계산으로는 약 1초 정도 소요되지만 데이터베이스가 증가할수록 마이그레이션 시간도 그에 따라 증가합니다. 이때, 마이그레이션해야 할 데이터 테이블의 데이터가 충분히 큰 경우(수천만 개의 항목 가정) mysqldump에 의해 메모리가 버스트되어 마이그레이션이 실패할 가능성이 높습니다. 따라서 이러한 데이터 테이블을 마이그레이션할 때 다음과 같이 간단하게 mysqldump를 최적화할 수 있습니다. -
🎜
--set-gtid-purged=OFF: 데이터를 가져올 때 GTID 관련 정보가 출력되지 않음을 나타냅니다. 🎜🎜
--add-locks=0: 이 매개변수는 LOCK TABLES s1.s1 WRITE;는 데이터를 가져올 때 데이터 테이블이 잠겨 있지 않음을 의미합니다. <li><p></p></li>
<code>--single-transaction: 데이터를 내보낼 때 데이터 테이블이 잠겨 있지 않음을 나타냅니다. -> `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;🎜위의 경우를 통해 최종 결과를 보면 최적화 효과는 최소화됩니다. 따라서 이 논리적 최적화 방법은 데이터의 양이 상대적으로 많을 때(레코드 100만 개 이상) 권장되지 않습니다. 🎜🎜🎜🎜🎜파일 마이그레이션🎜🎜🎜파일 마이그레이션은 이름에서 알 수 있듯이 🎜데이터베이스의 저장소 파일을 직접 마이그레이션🎜하는 것입니다. 이 마이그레이션 방법은 논리적 마이그레이션 방법에 비해 성능이 훨씬 뛰어나며 메모리 버스트가 거의 없습니다.
데이터가 많은 시나리오에서 데이터를 마이그레이션하는 경우 파일 마이그레이션 방법을 사용하는 것이 좋습니다. 🎜<pre class="brush:php;toolbar:false">Query OK, 0 rows affected (0.04 sec)</pre>🎜우리가 볼 수 있는 것은 50,000개 이상의 데이터를 파일로 내보낼 때 약 0.04초밖에 걸리지 않았다는 것입니다. mysqldump와 비교하면 2배 이상 빠릅니다. 🎜<blockquote>🎜참고: 이 방법으로 내보낸 데이터는 MySQL 데이터베이스의 디렉터리로만 내보낼 수 있습니다. 이 디렉터리를 구성하는 매개변수는 <code>secure_file_priv입니다. 이를 수행하지 않으면 데이터베이스에서 ERROR 1290(HY000)을 보고합니다. MySQL 서버가 --secure-file-로 실행 중입니다. priv 옵션을 사용하면 이 명령문을 실행할 수 없습니다 오류. 🎜🎜데이터를 내보낸 후 파일의 데이터를 데이터베이스로 가져와 다음과 같이 효과를 확인합니다. 🎜mysql> alter table t1.s1 discard tablespace; Query OK, 0 rows affected (0.01 sec)
🎜참고: outfile에서는 테이블 구조를 생성하지 않으므로 Before에서는 데이터를 가져오려면 테이블 구조를 수동으로 생성해야 합니다. 🎜🎜가져오기에 소요된 총 시간은
0.27초로 mysqldump보다 2배 이상 빠른 것을 확인할 수 있습니다. 🎜🎜이 방법은 주로 각 데이터 조각을 n 줄 바꿈 형식으로 파일에 직접 저장합니다. 🎜🎜가져올 때 가져온 데이터 테이블의 필드가 각 행의 데이터 열 수와 일치하는지 먼저 확인합니다. 일치하지 않으면 행별로 가져옵니다. 오류가 직접 보고됩니다. 🎜🎜주의가 필요한 문제가 있습니다. 우리 데이터베이스가 마스터-슬레이브 아키텍처 데이터베이스인 경우 여기서 문제가 발생할 가능성이 높습니다. 이 문제에 대해 이야기하기 전에 먼저 마스터-슬레이브 복제의 원리에 대해 조금 설명해야 합니다. 🎜🎜마스터-슬레이브 복제의 원리는 주로 binlog 로그에 의존합니다. binlog 로그의 구체적인 단계는 다음과 같습니다. 🎜🎜🎜🎜메인에서 SQL을 실행합니다. 데이터베이스에 수정된 데이터를 저장합니다. 🎜🎜🎜🎜은 메인 라이브러리의 덤프 스레드에 의해 슬레이브 라이브러리로 전달됩니다. 🎜🎜🎜🎜 슬레이브 라이브러리의 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를 선택합니다. 데이터 양이 많은 경우 해당 데이터 양의 성능을 처리할 수 있도록 서버 성능을 향상시키는 것이 첫 번째 선택입니다. ; 마이그레이션이 필요한 경우 타사 전문 데이터 마이그레이션 도구를 사용하는 것이 좋습니다.
추천 학습: "MySQL 비디오 튜토리얼"
위 내용은 MySQL에서 데이터를 빠르게 마이그레이션하는 방법을 자세히 설명하는 기사의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!