Condivisione della tecnologia

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1. Introduzione

1.1 Cos'è MySQL Bidone Tronco d'albero?

Il mio SQL Bin Log(Binario Tronco d'albero, log binario) è un file di log del database MySQL che registra tutte le modifiche apportate al databaseDMLoperazioni (come INSERT, UPDATE, DELETE, ecc.), maEsclude SELECTAspettareDQL Operazione di sola lettura. Bin Log è uno strumento importante per MySQL per ottenere replica, ripristino e controllo. Per i dettagli, vedere:DDL, DML, DQL e DCL in MySQL

1.2 Le funzioni e gli scenari applicativi di Bin Log

Le funzioni principali di Bin Log includono: replica master-slave, ripristino dei dati, backup dei dati e sottoscrizione dei dati

Attraverso questi casi specifici, possiamo vedere più chiaramente l'importanza e la diversità del binlog MySQL nelle applicazioni pratiche. L'uso corretto della funzione binlog può migliorare notevolmente l'affidabilità del sistema, la capacità di ripristino e la velocità di risposta aziendale.

2. Concetti base di Bin Log

2.1 Come funziona il registro dei contenitori

Quando il server MySQL abilita la funzione Bin Log, tutte le operazioni di modifica al database verrannoregistrazione dell'evento nel file di registro del contenitore. Questi eventi vengono memorizzati in ordine di esecuzione, formando una sequenza di log delle operazioni continua. Questi eventi possono essere riprodotti per ricreare lo stato del database quando i dati devono essere ripristinati o copiati.

2.2 Tre formati di Bin Log

MySQL Bin Log ha tre formati di record:
Di seguito sono riportati i tre formati di record di MySQL Bin Log organizzati in forma di tabella:

3. Configurare e gestire il registro del contenitore

3.1 Abilita registro contenitore

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

Per abilitare Bin Log, è necessario configurare il file di configurazione MySQL (solitamentemy.cnfOmy.ini) ed eseguire la seguente configurazione:

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

Dopo aver modificato la configurazione, riavvia mysql.strumentoSHOW VARIABLES LIKE 'log_bin'; Il valore Valore è ON.

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 Configurare i parametri del registro contenitore

I parametri comuni di configurazione del registro collocazione includono:

• log_bin: Abilita registro raccoglitore.
• server_id: identificatore univoco del server, utilizzato per la replica.
• binlog_format: imposta il formato del registro contenitore (STATEMENT, ROW, MIXED).
• expire_logs_days: imposta il numero di giorni per la scadenza automatica e l'eliminazione dei file di registro del contenitore.
• max_binlog_size: imposta la dimensione massima di un singolo file di registro del contenitore.

3.3 Gestire i file di registro del contenitore

Comandi comuni per la gestione dei file di registro bin:

• Visualizza l'elenco dei file di registro del contenitore：

  SHOW BINARY LOGS;
  1

• Visualizza la dimensione del file di registro del contenitore：

  SHOW MASTER STATUS;
  1

• Elimina i vecchi file di registro del contenitore：

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

  O:

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

---

3.4 Visualizzare il contenuto del registro del contenitore

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

Utilizza lo strumento mysqlbinlog per visualizzare il contenuto di 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

Se lo apri direttamente, sarà confuso.

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 Utilizzo dello strumento mysqlbinlog

mysqlbinlog è uno strumento da riga di comando utilizzato per analizzare i file Bin Log. Le opzioni comuni includono:

• --start-datetime: Specificare l'ora di inizio dell'analisi.
• --stop-datetime: Specificare l'ora in cui termina l'analisi.
• --start-position: Specificare la posizione in cui inizia l'analisi.
• --stop-position: Specificare la posizione in cui termina l'analisi.

Ad esempio, per visualizzare il registro collocazione per un periodo di tempo specifico:

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

3.6 Analisi e riproduzione del registro contenitore

Gli eventi Bin Log possono essere riprodotti nel server MySQL per il ripristino dei dati:

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

---

4. Applicazione di Bin Log in replica

4.1 Principio di replica master-slave

Il principio di base della replica master-slave di MySQL è che il database master registra il Bin Log e il database slave legge e riproduce questi log per ottenere la sincronizzazione dei dati. I passaggi specifici sono i seguenti:

1. La libreria principale esegue operazioni di scrittura e registra queste operazioni nel registro contenitore.
2. La libreria slave si connette alla libreria principale, legge il Bin Log e lo applica ai propri dati.

4.2 Configurare la replica master-slave

1. Configura la libreria principale：

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

2. Configura la libreria slave：

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

3. Creare un utente di replica nel database primario：

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

4. Configurare la replica sul database slave：

   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

5. Controlla lo stato della replica：

   SHOW SLAVE STATUSG
   1

4.3 Monitoraggio e gestione della replica

Lo stato della replica può essere monitorato con il seguente comando:

SHOW SLAVE STATUSG
1

Spiegazioni comuni dei campi di stato:

• Slave_IO_Running: stato del thread IO.
• Slave_SQL_Running:stato del thread SQL.
• Seconds_Behind_Master: il tempo in cui la libreria slave resta indietro rispetto alla libreria master.

---

4.4 Backup incrementale

Backup incrementale significa eseguire il backup di tutte le modifiche dall'ultimo backup completo o dall'ultimo backup incrementale. Il backup incrementale può essere ottenuto utilizzando Bin Log.

1. Esegui un backup completo：

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

2. Registra la posizione corrente del registro del contenitore：

   Cerca le seguenti righe nel file di backup completo:

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

3. Registro del contenitore di backup：

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

Ripristino basato su registro 4.5 Bin

1. Ripristina il backup completo：

   mysql < full_backup.sql
   1

2. Applicare il backup incrementale：

   mysql < incremental_backup.sql
   1

---

5. Scrivi alla fine

Il mio SQL Bin Log È uno strumento potente ampiamente utilizzato in scenari come il ripristino, la replica e il controllo dei dati.Configurando e utilizzando correttamenteBin Log , che può migliorare notevolmente l'affidabilità e la disponibilità del database MySQL. Nelle applicazioni pratiche, è fondamentale per gli amministratori e gli sviluppatori di database padroneggiare le capacità di utilizzo e i metodi di ottimizzazione di Bin Log.