MySQL データを迅速に移行する方法について説明する記事

MySQL でデータを迅速に移行するにはどうすればよいですか?次の記事では、MySQL データを迅速に移行する 2 つの方法について説明します。

#私たちは通常、データベースからより強力なパフォーマンスを備えたデータベース サーバーにデータを移行する必要があるシナリオに遭遇します。現時点で行う必要があるのは、データベースのデータを迅速に移行することです。

それでは、データベース内のデータを迅速に移行するにはどうすればよいでしょうか?今日はこのテーマについてお話します。

データベース データを移行するには 2 つの方法があります。1 つは 物理移行、もう 1 つは 論理移行です。

まず、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,&#39;shanhe&#39;,&#39;male&#39;,concat(&#39;shanhe&#39;,i,&#39;@helloworld&#39;));
        set i=i+1;
        select concat(&#39;shanhe&#39;,i,&#39;_ok&#39;);
    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 関連情報を出力しないことを示します。

これら 3 つのパラメータを追加することは、主に、次のようにすべての操作によって発生する不要な 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

上記のケースを通じて、最適化された最終結果を確認します。その効果は次のとおりです。最小限。したがって、データ量が比較的大きい (100 万レコードを超える) 場合には、この論理最適化方法はお勧めできません。

ファイル移行

ファイル移行は、名前が示すとおり、データベースのストレージ ファイルを直接移行することです。論理移行方法と比較して、この移行方法はパフォーマンスがはるかに高く、メモリのバーストがほとんどありません。 大量のデータを含むシナリオでデータを移行する場合は、ファイル移行を使用することをお勧めします。方法 は次のとおりです。 :

mysql> select * from s1 into outfile '/var/lib/mysql-files/1.txt';
Query OK, 55202 rows affected (0.04 sec)

50,000 個を超えるデータをファイルにエクスポートするのに、わずか約 0.04 秒しかかからなかったことがわかります。 mysqldump と比較すると、2 倍以上高速です。

注: この方法でエクスポートされたデータは、MySQL データベースのディレクトリにのみエクスポートできます。このディレクトリを設定するためのパラメータは secure_file_priv です。これを行わないと、データベースは ERROR 1290 (HY000): The MySQL Server is running with the --secure-file-priv を報告します。オプションを使用すると、このステートメントの実行中にエラーが発生することはありません。

データをエクスポートした後、次のようにファイル内のデータをデータベースにインポートし、その効果を確認します。

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

注: outfile にインポートすると、テーブル構造が生成されるため、データをインポートする前にテーブル構造を手動で作成する必要があります。

インポートに費やされた合計時間は 0.27 秒であり、mysqldump の 2 倍以上であることがわかります。

この方法では主に、各データを \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文件。

1、我们在迁移到的数据库中创建与需要迁移的数据表完全相同的数据表。

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)

2、删除新创建的数据表的表空间，这是因为新创建的数据库的表空间没有数据且会跟迁移过来的数据表空间冲突，我们提前删除，具体删除步骤如下：

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

3、创建一个原有数据表的配置文件，这样做的目的是将原有数据表的一些配置复制过来（注意：这一步会自动将数据表上锁）。

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 中国語 Web サイトの他の関連記事を参照してください。