Explicación detallada de MySQL Binlog: tecnología central para mejorar la confiabilidad de la base de datos

2024-07-12

1. Introducción

1.1 ¿Qué es MySQL? Papelera ¿Registro?

MySQL Bin Log(Binario Registro, registro binario) es un archivo de registro de la base de datos MySQL que registra todas las modificaciones realizadas en la base de datos.DMLoperaciones (como INSERTAR, ACTUALIZAR, ELIMINAR, etc.), peroExcluye SELECCIONAResperarDQL Operación de solo lectura. Bin Log es una herramienta importante para que MySQL logre replicación, recuperación y auditoría. Para más detalles, consulte:DDL, DML, DQL y DCL en MySQL

1.2 La función y los escenarios de aplicación de Bin Log

Las funciones principales de Bin Log incluyen: replicación maestro-esclavo, recuperación de datos, copia de seguridad de datos y suscripción de datos.

Escenarios de aplicación	concepto	Caso
replicación maestro-esclavo	La biblioteca maestra envía los registros de la operación de actualización en el binlog a la biblioteca esclava, y la biblioteca esclava lee el binlog y ejecuta declaraciones SQL.	Plataforma de comercio electrónico: la base de datos principal registra las operaciones de pedidos en binlog y sincroniza los datos de los pedidos de la base de datos para mantener la coherencia de todos los nodos.
Recuperación de datos	Utilice registros binlog para ejecutar sentencias SQL de forma inversa para recuperar datos	Sistema financiero: los administradores recuperan registros de transacciones eliminados accidentalmente analizando binlog para garantizar la integridad de los datos y evitar pérdidas financieras.
copias de seguridad	Binlog se utiliza para realizar copias de seguridad incrementales, lo que ahorra tiempo y costos de espacio en las copias de seguridad.	Plataforma de redes sociales: haga una copia de seguridad de los archivos binlog con regularidad todos los días para restaurarlos rápidamente al estado más reciente sin tener que realizar una copia de seguridad completa de toda la base de datos.
Suscripción de datos	Utilice binlog para monitorear las operaciones de actualización de la base de datos en tiempo real	Análisis de datos en tiempo real de empresas minoristas: al analizar binlog, la operación de inserción de registros de ventas se captura y se transfiere a la plataforma de análisis de datos en tiempo real para el análisis de tendencias de ventas.

A través de estos casos específicos, podemos ver más claramente la importancia y diversidad del binlog de MySQL en aplicaciones prácticas. El uso adecuado de la función binlog puede mejorar en gran medida la confiabilidad, la capacidad de recuperación y la velocidad de respuesta comercial del sistema.

2. Conceptos básicos de Bin Log

2.1 Cómo funciona Bin Log

Cuando el servidor MySQL habilita la función Bin Log, todas las operaciones de modificación de la base de datos seránregistro de evento al archivo Bin Log. Estos eventos se almacenan en orden de ejecución, formando una secuencia de registro de operación continua. Estos eventos se pueden reproducir para recrear el estado de la base de datos cuando es necesario restaurar o copiar los datos.

2.2 Tres formatos de Bin Log

MySQL Bin Log tiene tres formatos de registro:
Los siguientes son los tres formatos de registro de MySQL Bin Log organizados en forma de tabla:

formato de registro	describir	ventaja	defecto
Registro basado en declaraciones (SBL)	Registre la declaración SQL ejecutada	El volumen de registro es pequeño y adecuado para operaciones SQL simples.	Es posible que no se garantice la coherencia de los datos en algunos casos, como funciones no deterministas (como NOW())
Registro basado en filas (RBL)	Registre los cambios específicos de cada fila de datos.	Más preciso, adecuado para operaciones y copias complejas	Gran volumen de registros, gran disco y sobrecarga de red
Registro mixto (ML)	Cambie entre los modos Declaración y Fila según circunstancias específicas.	Teniendo en cuenta las ventajas de ambos	Relativamente complejo de implementar y gestionar.

3. Configurar y administrar Bin Log

3.1 Habilitar registro de contenedor

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 Bin Log, necesita configurar el archivo de configuración de MySQL (generalmentemy.cnfomy.ini) y realice la siguiente configuración:

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

Después de modificar la configuración, reinicie mysql.implementarSHOW VARIABLES LIKE 'log_bin'; El valor del Valor está activado.

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 los parámetros del registro de contenedores

Los parámetros de configuración comunes del Bin Log incluyen:

log_bin: Habilite el registro de contenedor.
server_id: Identificador único del servidor, utilizado para la replicación.
binlog_format: Establezca el formato del registro de contenedor (DECLARACIÓN, FILA, MIXTO).
expire_logs_days: establezca el número de días para la caducidad y eliminación automática de los archivos Bin Log.
max_binlog_size: establece el tamaño máximo de un único archivo Bin Log.

3.3 Administrar archivos de registro del contenedor

Comandos comunes para administrar archivos Bin Log:

Ver la lista de archivos de registro de Bin：
```
SHOW BINARY LOGS;
1
```
Ver el tamaño del archivo Bin Log：
```
SHOW MASTER STATUS;
1
```

Eliminar archivos antiguos de Bin Log：

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

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

3.4 Ver el contenido del registro del contenedor


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

Utilice la herramienta mysqlbinlog para ver el contenido de 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

Si lo abre directamente, quedará 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 Usando la herramienta mysqlbinlog

mysqlbinlog es una herramienta de línea de comandos que se utiliza para analizar archivos Bin Log. Las opciones comunes incluyen:

--start-datetime: Especifique la hora a la que comienza el análisis.
--stop-datetime: Especifique la hora a la que finaliza el análisis.
--start-position: Especifique la posición donde comienza el análisis.
--stop-position: Especifique la posición donde finaliza el análisis.

Por ejemplo, para ver el registro de contenedores durante un período de tiempo específico:

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

3.6 Analizar y reproducir Bin Log

Los eventos de Bin Log se pueden reproducir en el servidor MySQL para la recuperación de datos:

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

4. Aplicación de la replicación de Bin Log in

4.1 Principio de replicación maestro-esclavo

El principio básico de la replicación maestro-esclavo de MySQL es que la base de datos maestra registra Bin Log y la base de datos esclava lee y reproduce estos registros para lograr la sincronización de datos. Los pasos específicos son los siguientes:

La biblioteca principal realiza operaciones de escritura y registra estas operaciones en el Bin Log.
La biblioteca esclava se conecta a la biblioteca principal, lee el registro Bin y lo aplica a sus propios datos.

4.2 Configurar la replicación maestro-esclavo

Configurar la biblioteca principal：

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

Configurar la biblioteca esclava：

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

Crear un usuario de replicación en la base de datos primaria：

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

Configurar la replicación en la base de datos esclava：

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

Comprobar el estado de la replicación：
```
SHOW SLAVE STATUSG
1
```

4.3 Monitoreo y gestión de replicación

El estado de la replicación se puede monitorear con el siguiente comando:

SHOW SLAVE STATUSG
1

Explicaciones comunes del campo de estado:

Slave_IO_Running: Estado del hilo IO.
Slave_SQL_Running:Estado del hilo SQL.
Seconds_Behind_Master: El tiempo que la biblioteca esclava va por detrás de la biblioteca maestra.

4.4 Copia de seguridad incremental

Copia de seguridad incremental significa realizar una copia de seguridad de todos los cambios desde la última copia de seguridad completa o la última copia de seguridad incremental. Se puede lograr una copia de seguridad incremental utilizando Bin Log.

Realizar una copia de seguridad completa：

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

Registre la posición actual del registro del contenedor：

Busque las siguientes líneas en el archivo de copia de seguridad completo:
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Registro de la papelera de respaldo：

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

4.5 Recuperación basada en registros de bin

Restaurar copia de seguridad completa：
```
mysql < full_backup.sql
1
```
Aplicar copia de seguridad incremental：
```
mysql < incremental_backup.sql
1
```

5. Escribe al final

MySQL Bin Log Es una herramienta poderosa que se usa ampliamente en escenarios como recuperación, replicación y auditoría de datos.Al configurar y utilizar correctamenteBin Log , que puede mejorar en gran medida la confiabilidad y disponibilidad de la base de datos MySQL. En aplicaciones prácticas, es fundamental que los administradores y desarrolladores de bases de datos dominen las habilidades de uso y los métodos de optimización de Bin Log.

Compartir tecnología