Yksityiskohtainen kuvaus MySQL Binlogista: ydinteknologia tietokannan luotettavuuden parantamiseksi

2024-07-12

1. Esittely

1.1 Mikä on MySQL Bin Hirsi?

MySQL Bin Log(Binääri Hirsi, binääriloki) on MySQL-tietokannan lokitiedosto, joka tallentaa kaikki tietokantaan tehdyt muutoksetDMLtoiminnot (kuten INSERT, UPDATE, DELETE jne.), muttaEi sisällä SELECTodotaDQL Vain luku -toiminto. Bin Log on tärkeä työkalu MySQL:lle replikoinnin, palautuksen ja auditoinnin saavuttamiseksi. Katso lisätietoja:DDL, DML, DQL ja DCL MySQL:ssä

1.2 Bin Login toiminta- ja sovellusskenaariot

Bin Login päätoimintoihin kuuluvat: master-slave -replikointi, tietojen palautus, tietojen varmuuskopiointi ja tietojen tilaus

Sovellusskenaariot	konsepti	Asia
master-slave replikointi	Pääkirjasto lähettää binlogin päivitystoimintotietueet orjakirjastoon, ja orjakirjasto lukee binlogin ja suorittaa SQL-käskyt.	Verkkokauppaalusta: Päätietokanta tallentaa tilaustoiminnot binlogiin ja synkronoi tilaustiedot tietokannasta kaikkien solmujen johdonmukaisuuden ylläpitämiseksi.
Tietojen palautus	Käytä binlog-tietueita SQL-käskyjen käänteiseen suorittamiseen tietojen palauttamiseksi	Talousjärjestelmä: Järjestelmänvalvojat palauttavat vahingossa poistetut tapahtumatietueet jäsentämällä binlogia varmistaakseen tietojen eheyden ja välttääkseen taloudellisia menetyksiä.
datan varmuuskopio	Binlogia käytetään inkrementaaliseen varmuuskopiointiin, mikä säästää varmuuskopiointiaikaa ja -tilakustannuksia	Sosiaalisen median alusta: Varmuuskopioi binlog-tiedostot säännöllisesti joka päivä palauttaaksesi nopeasti uusimman tilan ilman, että sinun tarvitsee varmuuskopioida koko tietokantaa.
Datan tilaus	Käytä binlogia tietokannan päivitystoimintojen seuraamiseen reaaliajassa	Reaaliaikainen vähittäiskaupan yritysten data-analyysi: Jäsentämällä binlogia myyntitietueen lisäystoiminto tallennetaan ja siirretään reaaliajassa data-analyysialustalle myyntitrendin analysointia varten.

Näiden erityistapausten kautta voimme selvemmin nähdä MySQL-binlogin merkityksen ja monimuotoisuuden käytännön sovelluksissa. Binlog-toiminnon oikea käyttö voi parantaa huomattavasti järjestelmän luotettavuutta, palautuskykyä ja liiketoiminnan vastausnopeutta.

2. Bin Login peruskäsitteet

2.1 Kuinka Bin Log toimii

Kun MySQL-palvelin ottaa Bin Log -toiminnon käyttöön, kaikki tietokannan muokkaustoimenpiteet ovat voimassatapahtuman ennätys Bin Log -tiedostoon. Nämä tapahtumat tallennetaan suoritusjärjestyksessä muodostaen jatkuvan toimintalokisekvenssin. Nämä tapahtumat voidaan toistaa uudelleen tietokannan tilan luomiseksi, kun tiedot on palautettava tai kopioitava.

2.2 Kolme Bin Log -muotoa

MySQL Bin Logissa on kolme tietuemuotoa:
Seuraavassa on kolme MySQL Bin Login tietuemuotoa järjestettynä taulukkomuotoon:

äänitysmuoto	kuvata	etu	puute
Lausuntoon perustuva kirjaus (SBL)	Tallenna itse suoritettu SQL-käsky	Lokin määrä on pieni ja sopii yksinkertaisiin SQL-toimintoihin.	Tietojen yhdenmukaisuutta ei välttämättä taata joissain tapauksissa, kuten ei-determinististen funktioiden (kuten NOW()) tapauksessa.
Rivipohjainen kirjaus (RBL)	Tallenna kunkin tietorivin erityiset muutokset	Tarkempi, sopii monimutkaisiin toimintoihin ja kopiointiin	Suuri lokimäärä, suuri levy ja verkon yläpuolella
sekakirjaus (ML)	Vaihda lausunto- ja rivitilojen välillä erityisten olosuhteiden mukaan.	Kun otetaan huomioon molempien edut	Suhteellisen monimutkainen toteuttaa ja hallita

3. Määritä ja hallitse Bin Log

3.1 Ota Bin Log käyttöön

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

Jos haluat ottaa Bin Login käyttöön, sinun on määritettävä MySQL-määritystiedosto (yleensämy.cnftaimy.ini) ja suorita seuraavat asetukset:

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

Kun olet muokannut asetuksia, käynnistä mysql uudelleen.toteuttaaSHOW VARIABLES LIKE 'log_bin'; Arvo-arvo on PÄÄLLÄ.

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 Määritä Bin Log -parametrit

Yleisiä roskalokin määritysparametreja ovat:

log_bin: Ota Bin Log käyttöön.
server_id: Palvelimen yksilöllinen tunniste, jota käytetään replikointiin.
binlog_format: Aseta lokerolokin muoto (STATEMENT, ROW, MIXED).
expire_logs_days: Aseta päivien lukumäärä Bin Log -tiedostojen automaattiselle vanhenemiselle ja poistamiselle.
max_binlog_size: Aseta yhden Bin Log -tiedoston enimmäiskoko.

3.3 Hallinnoi lokitiedostoja

Yleiset komennot Bin Log -tiedostojen hallintaan:

Näytä Bin Log -tiedostoluettelo：
```
SHOW BINARY LOGS;
1
```
Näytä Bin Log -tiedoston koko：
```
SHOW MASTER STATUS;
1
```

Poista vanhat Bin Log -tiedostot：

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

tai:

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

3.4 Näytä Bin Lokin sisältö


-- 创建一个名为 '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

Käytä mysqlbinlog-työkalua tarkastellaksesi Bin Login sisältöä:

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

Jos avaat sen suoraan, se sotkeutuu.

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 Mysqlbinlog-työkalun käyttäminen

mysqlbinlog on komentorivityökalu, jota käytetään Bin Log -tiedostojen jäsentämiseen. Yleisiä vaihtoehtoja ovat:

--start-datetime: Määritä aika, jolloin jäsentäminen alkaa.
--stop-datetime: Määritä aika, jolloin jäsentäminen päättyy.
--start-position: Määritä paikka, josta jäsentäminen alkaa.
--stop-position: Määritä paikka, johon jäsentäminen päättyy.

Jos haluat esimerkiksi tarkastella roskakorilokia tietyltä ajanjaksolta:

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

3.6 Säiliön lokin jäsentäminen ja toistaminen

Bin Log -tapahtumat voidaan toistaa MySQL-palvelimelle tietojen palauttamista varten:

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

4. Bin Log in -replikoinnin soveltaminen

4.1 Master-slave replikointiperiaate

MySQL-isäntä-slave-replikoinnin perusperiaate on, että päätietokanta tallentaa Bin Log -tietokanta ja orjatietokanta lukee ja toistaa nämä lokit tietojen synkronoinnin saavuttamiseksi. Tarkat vaiheet ovat seuraavat:

Pääkirjasto suorittaa kirjoitusoperaatioita ja tallentaa nämä toiminnot roskalokiin.
Orjakirjasto muodostaa yhteyden pääkirjastoon, lukee Bin-lokin ja soveltaa sitä omiin tietoihinsa.

4.2 Konfiguroi master-slave-replikointi

Määritä pääkirjasto：

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

Määritä orjakirjasto：

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

Luo replikointikäyttäjä ensisijaiseen tietokantaan：

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

Määritä replikointi orjatietokannassa：

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

Tarkista replikaation tila：
```
SHOW SLAVE STATUSG
1
```

4.3 Replikoinnin valvonta ja hallinta

Replikoinnin tilaa voidaan valvoa seuraavalla komennolla:

SHOW SLAVE STATUSG
1

Yleiset tilakenttien selitykset:

Slave_IO_Running: IO-säikeen tila.
Slave_SQL_Running:SQL-säikeen tila.
Seconds_Behind_Master: Aika, jonka orjakirjasto on jäljessä pääkirjastosta.

4.4 Inkrementaalinen varmuuskopiointi

Inkrementaalinen varmuuskopiointi tarkoittaa kaikkien muutosten varmuuskopiointia viimeisen täyden varmuuskopioinnin tai viimeisimmän inkrementaalisen varmuuskopioinnin jälkeen. Inkrementaalinen varmuuskopiointi voidaan saavuttaa Bin Login avulla.

Suorita täydellinen varmuuskopio：

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

Tallenna nykyinen Bin Log -sijainti：

Etsi seuraavat rivit koko varmuuskopiotiedostosta:
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Varmuuskopiointiloki：

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

4.5 Bin Log -pohjainen palautus

Palauta täysi varmuuskopio：
```
mysql < full_backup.sql
1
```
Ota asteittainen varmuuskopio：
```
mysql < incremental_backup.sql
1
```

5. Kirjoita loppuun

MySQL Bin Log Se on tehokas työkalu, jota käytetään laajasti skenaarioissa, kuten tietojen palauttamisessa, replikaatiossa ja auditoinnissa.Oikein konfiguroimalla ja käyttämälläBin Log , mikä voi parantaa huomattavasti MySQL-tietokannan luotettavuutta ja saatavuutta. Käytännön sovelluksissa on tärkeää, että tietokannan ylläpitäjät ja kehittäjät hallitsevat Bin Login käyttötaidot ja optimointimenetelmät.

Teknologian jakaminen