Mi informacion de contacto
Correo[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
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.DML
operaciones (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
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.
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.
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. |
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)
Para habilitar Bin Log, necesita configurar el archivo de configuración de MySQL (generalmentemy.cnf
omy.ini
) y realice la siguiente configuración:
[mysqld]
log-bin=mysql-bin
server-id=1
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)
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.Comandos comunes para administrar archivos Bin Log:
Ver la lista de archivos de registro de Bin:
SHOW BINARY LOGS;
Ver el tamaño del archivo Bin Log:
SHOW MASTER STATUS;
Eliminar archivos antiguos de Bin Log:
PURGE BINARY LOGS TO 'mysql-bin.000010';
o:
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 创建一个名为 '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');
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)
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
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
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
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:
Configurar la biblioteca principal:
[mysqld]
log-bin=mysql-bin
server-id=1
Configurar la biblioteca esclava:
[mysqld]
server-id=2
relay-log=relay-bin
Crear un usuario de replicación en la base de datos primaria:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
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;
Comprobar el estado de la replicación:
SHOW SLAVE STATUSG
El estado de la replicación se puede monitorear con el siguiente comando:
SHOW SLAVE STATUSG
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.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
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;
Registro de la papelera de respaldo:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
Restaurar copia de seguridad completa:
mysql < full_backup.sql
Aplicar copia de seguridad incremental:
mysql < incremental_backup.sql
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.