Explicação detalhada do MySQL Binlog: tecnologia central para melhorar a confiabilidade do banco de dados

2024-07-12

1. Introdução

1.1 O que é MySQL Lixeira Registro?

MySQL Bin Log(Binário Registro, log binário) é um arquivo de log do banco de dados MySQL que registra todas as modificações realizadas no banco de dadosDMLoperações (como INSERT, UPDATE, DELETE, etc.), masExclui SELECIONARespereDQL Operação somente leitura. Bin Log é uma ferramenta importante para o MySQL implementar replicação, recuperação e auditoria. Para obter detalhes, consulte:DDL, DML, DQL e DCL no MySQL

1.2 Os cenários de função e aplicação do Bin Log

As principais funções do Bin Log incluem: replicação mestre-escravo, recuperação de dados, backup de dados e assinatura de dados

Cenários de aplicação	conceito	Caso
replicação mestre-escravo	A biblioteca mestre envia os registros da operação de atualização no log binário para a biblioteca escrava, e a biblioteca escrava lê o log binário e executa instruções SQL.	Plataforma de comércio eletrônico: o banco de dados principal registra as operações de pedidos no log binário e sincroniza os dados dos pedidos do banco de dados para manter a consistência de todos os nós.
Recuperação de dados	Use registros binlog para executar instruções SQL reversamente para recuperar dados	Sistema financeiro: os administradores recuperam registros de transações excluídos acidentalmente analisando o log binário para garantir a integridade dos dados e evitar perdas financeiras.
backup de dados	Binlog é usado para backup incremental, economizando tempo de backup e custos de espaço	Plataforma de mídia social: faça backup dos arquivos binlog regularmente todos os dias para restaurar rapidamente o status mais recente sem precisar fazer backup completo de todo o banco de dados.
Assinatura de dados	Use binlog para monitorar operações de atualização de banco de dados em tempo real	Análise de dados em tempo real de empresas de varejo: Ao analisar o binlog, a operação de inserção do registro de vendas é capturada e transferida para a plataforma de análise de dados em tempo real para análise de tendências de vendas.

Através destes casos específicos, podemos ver mais claramente a importância e diversidade do binlog do MySQL em aplicações práticas. O uso adequado da função binlog pode melhorar muito a confiabilidade do sistema, a capacidade de recuperação e a velocidade de resposta do negócio.

2. Conceitos básicos de Bin Log

2.1 Princípio de funcionamento do Bin Log

Quando o servidor MySQL habilita a função Bin Log, todas as operações de modificação no banco de dados serãoregistro de evento para o arquivo de log bin. Esses eventos são armazenados em ordem de execução, formando uma sequência de log de operação contínua. Esses eventos podem ser reproduzidos para recriar o estado do banco de dados quando os dados precisarem ser restaurados ou copiados.

2.2 Três formatos de Bin Log

O MySQL Bin Log possui três formatos de registro:
A seguir estão os três formatos de registro do MySQL Bin Log organizados em forma de tabela:

formato de registro	descrever	vantagem	deficiência
Registro baseado em declaração (SBL)	Registre a própria instrução SQL executada	O volume de log é pequeno e adequado para operações SQL simples.	A consistência dos dados pode não ser garantida em alguns casos, como funções não determinísticas (como NOW())
Registro baseado em linha (RBL)	Registre as alterações específicas de cada linha de dados	Mais preciso, adequado para operações complexas e cópias	Grande volume de log, disco grande e sobrecarga de rede
Exploração madeireira mista (ML)	Alterne entre os modos Declaração e Linha de acordo com circunstâncias específicas.	Levando em conta as vantagens de ambos	Relativamente complexo de implementar e gerenciar

3. Configure e gerencie o Bin Log

3.1 Habilitar registro de 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

Para habilitar o Bin Log, você precisa configurar o arquivo de configuração do MySQL (geralmentemy.cnfoumy.ini) e execute a seguinte configuração:

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

Após modificar a configuração, reinicie o mysql.implementoSHOW VARIABLES LIKE 'log_bin'; O valor Valor está ATIVADO.

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 Configurar parâmetros do Log Bin

Os parâmetros de configuração comuns do Bin Log incluem:

log_bin: Habilite o registro do compartimento.
server_id: identificador exclusivo do servidor, usado para replicação.
binlog_format: Defina o formato do Bin Log (STATEMENT, ROW, MIXED).
expire_logs_days: Defina o número de dias para expiração e exclusão automática dos arquivos Bin Log.
max_binlog_size: Defina o tamanho máximo de um único arquivo Bin Log.

3.3 Gerenciar arquivos de log de bin

Comandos comuns para gerenciar arquivos Bin Log:

Ver lista de arquivos de log bin：
```
SHOW BINARY LOGS;
1
```
Ver o tamanho do arquivo Bin Log：
```
SHOW MASTER STATUS;
1
```

Excluir arquivos antigos do Bin Log：

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

ou:

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

3.4 Visualizar conteúdo do Log do Bin


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

Use a ferramenta mysqlbinlog para visualizar o conteúdo do 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 você abri-lo diretamente, ele ficará distorcido.

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 Usando a ferramenta mysqlbinlog

mysqlbinlog é uma ferramenta de linha de comando usada para analisar arquivos Bin Log. As opções comuns incluem:

--start-datetime: Especifique a hora em que a análise começa.
--stop-datetime: Especifique a hora em que a análise termina.
--start-position: Especifique a posição onde a análise começa.
--stop-position: Especifique a posição onde a análise termina.

Por exemplo, para visualizar o Bin Log de um período específico:

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

3.6 Analisando e reproduzindo Bin Log

Os eventos do Bin Log podem ser reproduzidos no servidor MySQL para recuperação de dados:

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

4. Aplicação de Bin Log na replicação

4.1 Princípio de replicação mestre-escravo

O princípio básico da replicação mestre-escravo MySQL é que o banco de dados mestre registra o Bin Log e o banco de dados escravo lê e reproduz esses logs para obter a sincronização de dados. As etapas específicas são as seguintes:

A biblioteca principal executa operações de gravação e registra essas operações no Bin Log.
A biblioteca escrava se conecta à biblioteca principal, lê o Bin Log e o aplica aos seus próprios dados.

4.2 Configurar replicação mestre-escravo

Configurar a biblioteca principal：

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

Configurar biblioteca escrava：

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

Crie um usuário de replicação no banco de dados primário：

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

Configure a replicação no banco de dados escravo：

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

Verifique o status da replicação：
```
SHOW SLAVE STATUSG
1
```

4.3 Monitorando e gerenciando replicação

O status da replicação pode ser monitorado com o seguinte comando:

SHOW SLAVE STATUSG
1

Explicações comuns dos campos de status:

Slave_IO_Running: status do encadeamento IO.
Slave_SQL_Running:Status do encadeamento SQL.
Seconds_Behind_Master: O tempo que a biblioteca escrava fica atrás da biblioteca mestre.

4.4 Backup incremental

Backup incremental significa fazer backup de todas as alterações desde o último backup completo ou o último backup incremental. O backup incremental pode ser obtido usando Bin Log.

Execute um backup completo：

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

Registre a localização atual do Bin Log：

Procure as seguintes linhas no arquivo de backup completo:
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Log do compartimento de backup：

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

4.5 Recuperação baseada em log de bin

Restaurar backup completo：
```
mysql < full_backup.sql
1
```
Aplicar backup incremental：
```
mysql < incremental_backup.sql
1
```

5. Escreva no final

MySQL Bin Log É uma ferramenta poderosa amplamente utilizada em cenários como recuperação de dados, replicação e auditoria.Ao configurar e usar adequadamenteBin Log , o que pode melhorar muito a confiabilidade e disponibilidade do banco de dados MySQL. Em aplicações práticas, é crucial que administradores e desenvolvedores de banco de dados dominem as habilidades de uso e métodos de otimização do Bin Log.

Compartilhamento de tecnologia