Penjelasan rinci tentang MySQL Binlog: teknologi inti untuk meningkatkan keandalan database

2024-07-12

1. Perkenalan

1.1 Apa itu MySQL Tempat sampah Catatan?

Bahasa Indonesia: Bahasa Indonesia: MySQL Bin Log(Biner Catatan, log biner) adalah file log database MySQL yang mencatat semua modifikasi yang dilakukan pada databaseDMLoperasi (seperti INSERT, UPDATE, DELETE, dll.), tetapiTidak termasuk PILIHTungguDQL Operasi hanya-baca. Bin Log adalah alat penting bagi MySQL untuk mengimplementasikan replikasi, pemulihan, dan audit. Untuk detailnya, lihat:DDL, DML, DQL dan DCL di MySQL

1.2 Fungsi dan skenario penerapan Bin Log

Fungsi utama Bin Log meliputi: replikasi master-slave, pemulihan data, pencadangan data, dan langganan data

Skenario aplikasi	konsep	Kasus
replikasi master-slave	Perpustakaan master mengirimkan catatan operasi pembaruan di binlog ke perpustakaan budak, dan perpustakaan budak membaca binlog dan mengeksekusi pernyataan SQL.	Platform e-niaga: Basis data utama mencatat operasi pemesanan ke binlog, dan menyinkronkan data pesanan dari basis data untuk menjaga konsistensi semua node.
Pemulihan data	Gunakan catatan binlog untuk mengeksekusi pernyataan SQL secara terbalik guna memulihkan data	Sistem keuangan: Administrator memulihkan catatan transaksi yang terhapus secara tidak sengaja dengan menguraikan binlog untuk memastikan integritas data dan menghindari kerugian finansial.
cadangan data	Binlog digunakan untuk pencadangan tambahan, menghemat waktu pencadangan dan biaya ruang	Platform media sosial: Cadangkan file binlog secara rutin setiap hari untuk memulihkan status terbaru dengan cepat tanpa harus mencadangkan seluruh database.
Langganan data	Gunakan binlog untuk memantau operasi pembaruan basis data secara real time	Analisis data real-time perusahaan ritel: Dengan mengurai binlog, operasi penyisipan catatan penjualan ditangkap dan ditransfer ke platform analisis data secara real-time untuk analisis tren penjualan.

Melalui kasus-kasus khusus ini, kita dapat melihat lebih jelas pentingnya dan keragaman binlog MySQL dalam aplikasi praktis. Penggunaan fungsi binlog yang tepat dapat meningkatkan keandalan sistem, kemampuan pemulihan, dan kecepatan respons bisnis.

2. Konsep dasar Bin Log

2.1 Prinsip kerja Bin Log

Ketika server MySQL mengaktifkan fungsi Bin Log, semua operasi modifikasi pada database akan dilakukancatatan acara ke file Bin Log. Peristiwa ini disimpan dalam urutan eksekusi, membentuk urutan log operasi berkelanjutan. Peristiwa ini dapat diputar ulang untuk membuat ulang keadaan database saat data perlu dipulihkan atau disalin.

2.2 Tiga format Bin Log

MySQL Bin Log memiliki tiga format catatan:
Berikut tiga format record MySQL Bin Log yang disusun dalam bentuk tabel:

format rekaman	menggambarkan	keuntungan	kekurangan
Pencatatan Berbasis Pernyataan (SBL)	Rekam pernyataan SQL yang dieksekusi itu sendiri	Volume lognya kecil dan cocok untuk operasi SQL sederhana.	Konsistensi data mungkin tidak dijamin dalam beberapa kasus, seperti fungsi non-deterministik (seperti SEKARANG())
Pencatatan Berbasis Baris (RBL)	Catat perubahan spesifik dari setiap baris data	Lebih akurat, cocok untuk operasi dan penyalinan yang rumit	Volume log besar, disk besar, dan overhead jaringan
Penebangan Campuran (ML)	Beralih antara mode Pernyataan dan Baris sesuai dengan keadaan tertentu.	Mempertimbangkan kelebihan keduanya	Relatif rumit untuk diterapkan dan dikelola

3. Konfigurasikan dan kelola Bin Log

3.1 Aktifkan Log Bin

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

Untuk mengaktifkan Bin Log, Anda perlu mengkonfigurasi file konfigurasi MySQL (biasanyamy.cnfataumy.ini) dan lakukan konfigurasi berikut:

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

Setelah mengubah konfigurasi, restart mysql.melaksanakanSHOW VARIABLES LIKE 'log_bin'; Nilai Nilai AKTIF.

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 Konfigurasikan parameter Bin Log

Parameter konfigurasi Bin Log yang umum meliputi:

log_bin: Aktifkan Log Bin.
server_id: Pengidentifikasi unik server, digunakan untuk replikasi.
binlog_format: Mengatur format Bin Log (PERNYATAAN, BARIS, CAMPURAN).
expire_logs_days: Mengatur jumlah hari untuk masa berlaku otomatis dan penghapusan file Bin Log.
max_binlog_size: Mengatur ukuran maksimum satu file Bin Log.

3.3 Kelola file Bin Log

Perintah umum untuk mengelola file Bin Log:

Lihat daftar file Bin Log：
```
SHOW BINARY LOGS;
1
```
Lihat ukuran file Bin Log：
```
SHOW MASTER STATUS;
1
```

Hapus file Bin Log lama：

PURGE BINARY LOGS TO 'mysql-bin.000010';
1

atau:

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
1

3.4 Lihat konten Bin Log


-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Gunakan alat mysqlbinlog untuk melihat konten Bin Log:

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

Kalau langsung dibuka pasti kacau.

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.5 Menggunakan alat mysqlbinlog

mysqlbinlog adalah alat baris perintah yang digunakan untuk mengurai file Bin Log. Opsi umum meliputi:

--start-datetime: Menentukan waktu dimulainya penguraian.
--stop-datetime: Menentukan waktu berakhirnya penguraian.
--start-position: Tentukan posisi dimana parsing dimulai.
--stop-position: Tentukan posisi akhir penguraian.

Misalnya, untuk melihat Bin Log untuk jangka waktu tertentu:

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3.6 Mengurai dan memutar ulang Bin Log

Peristiwa Bin Log dapat diputar ulang ke server MySQL untuk pemulihan data:

mysqlbin logbinlog.000001 | mysql -u root -p
1

4. Penerapan replikasi Bin Log in

4.1 Prinsip replikasi master-slave

Prinsip dasar replikasi master-slave MySQL adalah database master mencatat Bin Log, dan database slave membaca dan memutar ulang log ini untuk mencapai sinkronisasi data. Langkah-langkah spesifiknya adalah sebagai berikut:

Perpustakaan utama melakukan operasi penulisan dan mencatat operasi ini ke Bin Log.
Pustaka budak terhubung ke pustaka utama, membaca Log Bin, dan menerapkannya ke datanya sendiri.

4.2 Konfigurasikan replikasi master-slave

Konfigurasikan perpustakaan utama：

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

Konfigurasikan perpustakaan budak：

[mysqld]
server-id=2
relay-log=relay-bin
1
2
3

Buat pengguna replikasi di database utama：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
1
2

Konfigurasikan replikasi pada database budak：

CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_USER='replica',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=0;
START SLAVE;
1
2
3
4
5
6
7

Periksa status replikasi：
```
SHOW SLAVE STATUSG
1
```

4.3 Memantau dan mengelola replikasi

Status replikasi dapat dipantau dengan perintah berikut:

SHOW SLAVE STATUSG
1

Penjelasan bidang status umum:

Slave_IO_Running: Status utas IO.
Slave_SQL_Running:Status utas SQL.
Seconds_Behind_Master: Waktu perpustakaan budak tertinggal dari perpustakaan utama.

4.4 Pencadangan tambahan

Pencadangan tambahan berarti mencadangkan semua perubahan sejak pencadangan penuh terakhir atau pencadangan tambahan terakhir. Pencadangan tambahan dapat dilakukan dengan menggunakan Bin Log.

Lakukan pencadangan penuh：

mysqldump --all-databases --master-data=2 > full_backup.sql
1

Catat lokasi Bin Log saat ini：

Cari baris berikut di file cadangan lengkap:

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1

Log Tempat Cadangan：

mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
1

4.5 Pemulihan berbasis Bin Log

Pulihkan cadangan penuh：
```
mysql < full_backup.sql
1
```
Terapkan cadangan tambahan：
```
mysql < incremental_backup.sql
1
```

5. Tulis di akhir

Bahasa Indonesia: Bahasa Indonesia: MySQL Bin Log Ini adalah alat canggih yang banyak digunakan dalam skenario seperti pemulihan data, replikasi, dan audit.Dengan mengkonfigurasi dan menggunakan dengan benarBin Log , yang dapat sangat meningkatkan keandalan dan ketersediaan database MySQL. Dalam aplikasi praktis, sangat penting bagi administrator database dan pengembang untuk menguasai keterampilan penggunaan dan metode optimasi Bin Log.

Berbagi teknologi