Artikel yang menerangkan secara terperinci cara memindahkan data dengan cepat dalam MySQL

Artikel ini membawakan anda pengetahuan yang berkaitan tentang MySQL terutamanya tentang cara memindahkan data dalam MySQL dengan cepat. Saya harap ia dapat membantu semua orang.

Kami biasanya menghadapi senario di mana kami perlu memindahkan data daripada pangkalan data kepada pelayan pangkalan data dengan prestasi yang lebih berkuasa. Apa yang perlu kita lakukan pada masa ini ialah dengan cepat memindahkan data pangkalan data.

Jadi, bagaimanakah kita boleh memindahkan data dalam pangkalan data dengan cepat? Hari ini kita akan bercakap mengenai topik ini.

Terdapat dua cara untuk memindahkan data pangkalan data, satu ialah penghijrahan fizikal dan satu lagi ialah penghijrahan logik.

Pertama, kami menjana 50,000 keping data ujian. Butirannya adalah seperti berikut:

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

Migrasi logik

Prinsip migrasi logik ialah menukar mengikut data dan struktur jadual dalam pangkalan data MySQL ke dalam fail SQL . Alat migrasi yang biasa digunakan yang menggunakan prinsip ini ialah mysqldump.

Mari kita uji di bawah:

[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

Apa yang dapat kita lihat ialah SQL yang sepadan dijana. Sekarang kita berhijrah ke pangkalan data lain dengan SQL yang dihasilkan.

mysql> use s2;
Database changed

mysql> source /opt/s1.sql

Dengan pengiraan pengumpulan masa yang mudah, ia mengambil masa kira-kira 1 saat, tetapi apabila pangkalan data meningkat, masa migrasi juga akan meningkat dengan sewajarnya. Pada masa ini, jika data dalam jadual data yang perlu dipindahkan adalah cukup besar (dengan mengandaikan berpuluh-puluh juta entri), mysqldump berkemungkinan akan memecahkan memori dan menyebabkan pemindahan gagal. Oleh itu, apabila memindahkan jadual data sedemikian, kita hanya boleh mengoptimumkan mysqldump, seperti berikut.

• --add-locks=0: Parameter ini bermaksud tidak menambah LOCK TABLES s1.s1 WRITE; apabila memindahkan data, iaitu jadual data tidak dikunci semasa mengimport data.

• --single-transaction: Menunjukkan bahawa jadual data tidak dikunci semasa mengeksport data.

• --set-gtid-purged=OFF: Menunjukkan bahawa maklumat berkaitan GTID tidak akan dikeluarkan apabila mengimport data.

Menambah ketiga-tiga parameter ini adalah terutamanya untuk mengurangkan IO yang tidak perlu yang disebabkan oleh semua operasi, seperti berikut:

[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

Melalui kes di atas, kita melihat Hasil akhirnya ialah bahawa kesan pengoptimuman adalah minimum. Oleh itu, kaedah pengoptimuman logik ini tidak digalakkan apabila jumlah data agak besar (lebih daripada satu juta rekod).

Penghijrahan fail

Penghijrahan fail, seperti namanya, adalah untuk menghijrah terus fail storan pangkalan data . Berbanding dengan kaedah penghijrahan logik, kaedah penghijrahan ini mempunyai prestasi yang jauh lebih tinggi dan jarang memecahkan ingatan 在面对数据量较大的场景下迁移数据，建议使用文件迁移的方式, butirannya adalah seperti berikut:

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

Kita dapat melihat Apa yang menakjubkan ialah apabila mengeksport lebih daripada 50,000 keping data ke fail, hanya mengambil masa kira-kira 0.04 saat. Berbanding dengan mysqldump, ia lebih daripada dua kali lebih pantas.

Nota: Data yang dieksport dengan cara ini hanya boleh dieksport ke direktori pangkalan data MySQL. Parameter untuk mengkonfigurasi direktori ini ialah secure_file_priv Jika anda tidak melakukan ini, pangkalan data akan melaporkan ralat ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

Selepas mengeksport data, kami akan mengimport data dalam fail ke dalam pangkalan data untuk melihat kesannya, seperti berikut:

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

Nota: into outfile is Struktur jadual tidak akan dijana, jadi anda perlu membuat struktur jadual secara manual sebelum mengimport data.

Kita dapat lihat bahawa masa import mengambil masa keseluruhan 0.27 saat, iaitu lebih daripada dua kali lebih pantas daripada mysqldump.

Kaedah ini terutamanya menyimpan setiap keping data secara langsung dalam fail dalam bentuk n pemisah baris.

Apabila mengimport, ia akan terlebih dahulu menentukan sama ada medan jadual data yang diimport adalah konsisten dengan bilangan lajur data dalam setiap baris Jika ia konsisten, ia akan diimport baris demi baris tidak konsisten, ralat akan dilaporkan secara langsung.

Terdapat masalah di sini yang perlu kami perhatikan Jika pangkalan data kami adalah pangkalan data seni bina tuan-hamba, masalah mungkin akan timbul di sini. Sebelum membicarakan isu ini, kita mesti menerangkan serba sedikit tentang prinsip replikasi tuan-hamba.

Prinsip replikasi tuan-hamba terutamanya bergantung pada log binlog Langkah-langkah khusus log binlog adalah seperti berikut:

• Jalankan SQL pada. pangkalan data utama, dan ubah suai Data disimpan dalam log binlog; 🎜>

  dimajukan oleh IO dalam perpustakaan hamba Benang menerima log binlog yang dihantar daripada pustaka utama; >

• 通过从库上的 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 这个文件。具体步骤如下：

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文件。

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

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 万条以下的场景。

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

Penghijrahan fizikal sesuai untuk senario di mana jumlah data agak besar Senario ini tidak mungkin menyebabkan pelayan ranap disebabkan penggunaan sumber yang berlebihan, tetapi proses pengendaliannya menyusahkan dan asal. jadual data akan dikunci.

Dalam aplikasi praktikal, kami biasanya memilih untuk menggunakan mysqldump untuk pemindahan data jika jumlah data adalah besar, pilihan pertama kami adalah untuk meningkatkan prestasi pelayan supaya ia boleh mengendalikan prestasi yang sepadan; jumlah data; Jika pemindahan diperlukan, pertimbangkan untuk menggunakan alat migrasi data profesional pihak ketiga.

Pembelajaran yang disyorkan: "Tutorial Video MySQL"