Введение в Бинлог
Обычно в MySQL существуют следующие типы журналов:
| тип журнала | Информация, записанная в журнал |
|---|---|
| журнал ошибок | Регистрирует проблемы, возникающие при запуске, работе или остановке mysqld. |
| Общий журнал запросов | Регистрирует установленные клиентские соединения и выполненные операторы |
| двоичный журнал | Заявления, которые изменяют данные |
| журнал ретрансляции | Изменения данных, полученные от мастера репликации |
| журнал медленных запросов | Зарегистрируйте все времена выполненияlong_query_timeсекунд для всех запросов или запросов, которые не используют индексы |
| Журнал DDL (журнал метаданных) | Операции с метаданными выполняются операторами DDL. |
В этой статье в основном представлен двоичный журнал binlog.
Бинарный журнал MySQL можно назвать самым важным журналом MySQL, который записывает всеDDLиDMLОператоры (кроме операторов запроса данных select, show и т. д.),записать как событие, также содержит время, затраченное на выполнение оператора, двоичный журнал MySQL безопасен для транзакций. Основная цель binlog -Скопируйте и восстановите.
Два наиболее важных сценария использования журналов Binlog
- Репликация master-slave MySQL: Репликация MySQL открывает бинарный журнал на стороне мастера, и мастер передает свой двоичный журнал подчиненным устройствам для достижения цели согласованности данных ведущий-ведомый.
- Восстановление данных: восстановить данные с помощью инструмента mysqlbinlog
включить бинлог
Примечание. MySQL версия эксперимента автора: 5.7.22.
Вообще говоря, при включении журналов binlog потеря производительности составляет около 1%.
Включить binlog, настроив/etc/my.cnfили/etc/mysql/mysql.conf.d/mysqld.cnfфайл конфигурацииlog-binОпции:
добавить в конфигурационный файлlog-binКонфигурация, указывающая, что binlog включен, если значение не указано, напишите его какlog-bin=, именем по умолчанию является имя хоста. (Примечание: если в имени есть десятичная точка, в качестве имени берется только часть до первой десятичной точки)
[mysqld]
log-bin=my-binlog-name
также черезSET SQL_LOG_BIN=1команда для включения binlog черезSET SQL_LOG_BIN=0команда для отключения binlog. После включения binlog MySQL необходимо перезапустить, чтобы изменения вступили в силу.
Общие команды работы Binlog
# 是否启用binlog日志
show variables like 'log_bin';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';
# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';
Когда писать Бинлог
Для механизма, поддерживающего транзакции, такого как InnoDB, транзакция должна быть зафиксирована до того, как будет записан binlog. бинлог когдасбросить на дискза которыми следуют параметрыsync_binlogСвязанный.
- Если он установлен в 0, это означает, что MySQL не контролирует обновление binlog, а файловая система контролирует обновление своего кеша;
- Если установлено значение, отличное от 0, это означает, что каждый
sync_binlogВо второй транзакции MySQL вызывает операцию сброса файловой системы, чтобы сбросить двоичный журнал на диск. - Установка его на 1 является самой безопасной, так как в случае сбоя системы будет потеряно не более одного обновления транзакции, но это повлияет на производительность.
еслиsync_binlog=0илиsync_binlog大于1, при сбое питания или сбое операционной системы некоторые транзакции, которые были зафиксированы, но бинлоги которых не были синхронизированы с диском, могут быть потеряны, и программа восстановления не сможет восстановить эти транзакции.
До MySQL 5.7.7 значение sync_binlog по умолчанию было равно 0, MySQL 5.7.7 и более поздние версии используют значение по умолчанию 1, что является самым безопасным выбором. В нормальных условиях для него будет установлено значение 100 или 0, что приведет к потере определенной согласованности для повышения производительности.
Binlog-файлы и расширения
Журнал binlog состоит из двух типов файлов:
- Индексный файл двоичного журнала (суффикс имени файла .index) используется для записи всех допустимых двоичных файлов.
- Двоичный файл журнала (суффикс имени файла .00000*) записывает все события операторов DDL и DML в базе данных.
binlog представляет собой набор двоичных файлов, каждый файл binlog начинается с 4-байтового магического числа, за которым следует набор событий:
- Магическое число: 0xfe62696e соответствует 0xfebin;
- Событие: Каждое событие состоит из двух частей: заголовка и данных: заголовок содержит такую информацию, как время создания события, сервер и т. д., а часть данных содержит конкретную информацию о событии, например, изменение конкретных данных;
- Первое событие используется для описания версии формата файла binlog, т. е. формата, в котором событие записывается в файл binlog;
- Остальные события записываются в том же формате, что и первое событие;
- Последнее событие используется для описания следующего файла binlog;
- Индексный файл binlog представляет собой текстовый файл, содержимое которого представляет собой текущий список файлов binlog.
При возникновении следующих трех ситуаций MySQL повторно сгенерирует новый файл журнала, а порядковый номер файла будет увеличен:
- Когда сервер MySQL остановлен или перезапущен
- использовать
flush logsЗаказ; - Когда размер файла binlog превышает
max_binlog_sizeКогда значение переменной;
max_binlog_sizeМинимальное значение — 4096 байт, максимальное значение и значение по умолчанию — 1 ГБ (1073741824 байта). Транзакция записывается в блок бинарного журнала, поэтому она не разбивается на несколько бинарных журналов. Поэтому, если у вас крупная транзакция, для обеспечения целостности транзакции невозможно переключить лог, можно только записывать лог транзакции в текущий лог-файл до окончания транзакции. см. binlog Когда файл больше, чем max_binlog_size.
Формат журнала Binlog
Формат событий, регистрируемых в двоичном журнале, зависит от формата двоичного журнала. Поддерживаются три типа формата:
- STATEMENT: репликация на основе операторов SQL (репликация на основе операторов, SBR)
- ROW: репликация на основе строк (RBR)
- СМЕШАННАЯ: смешанная репликация (MBR)
существуетMySQL 5.7.7Ранее формат по умолчанию былSTATEMENT,существуетMySQL 5.7.7и позже значение по умолчаниюROW. проход формата журналаbinlog-formatуказанный, такой какbinlog-format=STATEMENT,binlog-format=ROW,binlog-format=MIXED.
Statement
Каждый sql, который изменяет данные, будет записан в binlog.
Преимущества: нет необходимости записывать изменения каждой строки, уменьшать количество журналов binlog, экономить ввод-вывод и повышать производительность.
Недостаток: поскольку записываются только операторы выполнения, для того, чтобы эти операторы правильно выполнялись на ведомом устройстве, необходимо записывать некоторую соответствующую информацию о каждом операторе, когда он выполняется, чтобы гарантировать, что все операторы могут быть получены ведомым устройством и выполнены. на главной стороне Тот же результат. Кроме того, репликация mysql, как и функции некоторых конкретных функций, будет иметь много связанных проблем, чтобы поддерживать согласованность ведомого и ведущего.
Row
Версия MySQL 5.1.5 только начала поддерживатьrow levelДля репликации он не записывает информацию, относящуюся к контексту инструкции sql, а только сохраняет, какая запись была изменена.
Преимущества: в бинарный журнал не нужно записывать связанную с контекстом информацию о выполняемом операторе SQL, а нужно только записывать то, во что была изменена запись. Таким образом, в содержимом строки журнала будут четко записаны сведения о каждой строке модификации данных. И не будет проблем с тем, что хранимые процедуры, или функции, и вызовы триггеров и триггеры не могут быть корректно скопированы при определенных обстоятельствах.
Недостаток: когда все выполненные операторы записываются в журнал, они будут записаны с модификацией каждой строки, что может привести к созданию большого объема содержимого журнала.
Примечание. Если для двоичного формата журнала задано значение ROW, некоторые изменения по-прежнему используют формат на основе операторов, включая все операторы DDL, такие как CREATE TABLE, ALTER TABLE или DROP TABLE.
Mixed
Начиная с версии 5.1.8, MySQL предоставляет смешанный формат, который на самом деле является комбинацией оператора и строки. В смешанном режиме общая модификация оператора использует формат statment для сохранения binlog.Например, некоторые функции, оператор не может завершить операцию репликации master-slave, binlog сохраняется в формате строки, и MySQL будет обрабатывать записи по-разному в зависимости от каждого конкретного выполняемого оператора SQL Форма журнала, то есть выберите один из операторов и строк.
Использование команды mysqlbinlog
Сервер записывает лог бинлога в файл бинлога в бинарном формате, как отобразить его содержимое в текстовом формате, можно использовать команду mysqlbinlog.
# mysqlbinlog 的执行格式
mysqlbinlog [options] log_file ...
# 查看bin-log二进制文件（shell方式）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
# 查看bin-log二进制文件（带查询条件）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
--start-datetime="2019-03-01 00:00:00" \
--stop-datetime="2019-03-10 00:00:00" \
--start-position="5000" \
--stop-position="20000"
При установке формата журнала ROW на мою машину выводится следующая информация
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190308 10:05:03 server id 1 end_log_pos 123 CRC32 0xff02e23d Start: binlog v 4, server v 5.7.22-log created 190308 10:05:03
# Warning: this binlog is either in use or was not closed properly.
# at 123
#190308 10:05:03 server id 1 end_log_pos 154 CRC32 0xb81da4c5 Previous-GTIDs
# [empty]
# at 154
#190308 10:05:09 server id 1 end_log_pos 219 CRC32 0xfb30d42c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
...
...
# at 21019
#190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0
SET TIMESTAMP=1552011009/*!*/;
BEGIN
/*!*/;
# at 21094
#190308 10:10:09 server id 1 end_log_pos 21161 CRC32 0xdb7a2b35 Table_map: `maxwell`.`positions` mapped to number 110
# at 21161
#190308 10:10:09 server id 1 end_log_pos 21275 CRC32 0xec3be372 Update_rows: table id 110 flags: STMT_END_F
### UPDATE `maxwell`.`positions`
### WHERE
### @1=1
### @2='master.000003'
### @3=20262
### @4=NULL
### @5='maxwell'
### @6=NULL
### @7=1552011005707
### SET
### @1=1
### @2='master.000003'
### @3=20923
### @4=NULL
### @5='maxwell'
### @6=NULL
### @7=1552011009790
# at 21275
#190308 10:10:09 server id 1 end_log_pos 21306 CRC32 0xe6c4346d Xid = 13088
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Возьмите его часть для анализа:
# at 21019
#190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0
SET TIMESTAMP=1552011009/*!*/;
BEGIN
/*!*/;
Приведенный выше вывод включает информацию:
- position: позиция в файле, то есть первая строка (# в 21019), указывающая, что запись о событии начинается с 21019-го байта файла
- timestamp: временная метка события, которая является второй строкой (#190308 10:10:09)
- идентификатор сервера: идентификатор сервера (1)
- end_log_pos указывает позицию начала следующего события (т.е. конечную позицию текущего события + 1)
- thread_id: идентификатор потока, который выполняет событие (thread_id=113)
- exec_time: время, затраченное на выполнение события
- error_code: код ошибки, 0 означает отсутствие ошибки
- тип: тип события запрос
Типы событий бинлога
Существует три основных варианта структуры событий binlog:
- v1: используется в MySQL 3.23
- v3: используется в MySQL с 4.0.2 по 4.1.
- v4: используется в MySQL 5.0 и выше
Версии ниже MySQL 5.0 обычно сейчас не используются, поэтому ниже представлены только типы событий binlog версии v4. В binlog есть много типов событий, и эта статья делает здесь несколько простых выводов.
| тип события | инструкция |
|---|---|
| UNKNOWN_EVENT | Это событие никогда не запускается и никогда не записывается в binlog; это происходит, когда binlog читается и никакое другое событие не может быть распознано, что рассматривается как UNKNOWN_EVENT |
| START_EVENT_V3 | Событие, записанное в начале каждого файла binlog, это событие используется в MySQL3.23 - 4.1 и было заменено на FORMAT_DESCRIPTION_EVENT после MYSQL5.0. |
| QUERY_EVENT | Это событие генерируется при выполнении оператора обновления, включая: создание, вставку, обновление, удаление; |
| STOP_EVENT | Это событие генерируется, когда mysqld останавливается |
| ROTATE_EVENT | Когда mysqld переключается на новый файл binlog для генерации этого события, переключение на новый файл binlog можно выполнить, выполнив команду flush logs, или файл binlog больше, чемmax_binlog_sizeРазмер конфигурации параметра; |
| INTVAR_EVENT | Когда в операторе sql используется поле AUTO_INCREMENT или функция 1436753; это событие не используется, когда binlog_format находится в режиме ROW |
| LOAD_EVENT | Это событие генерируется при выполнении оператора LOAD DATA INFILE, используемого в версии MySQL 3.23. |
| SLAVE_EVENT | Неиспользованный |
| CREATE_FILE_EVENT | Это событие генерируется при выполнении инструкции LOAD DATA INFILE, используемой в версиях MySQL 4.0 и 4.1. |
| APPEND_BLOCK_EVENT | Это событие генерируется при выполнении оператора LOAD DATA INFILE, используемого в MySQL 4.0. |
| EXEC_LOAD_EVENT | Это событие генерируется при выполнении инструкции LOAD DATA INFILE, используемой в версиях MySQL 4.0 и 4.1. |
| DELETE_FILE_EVENT | Это событие генерируется при выполнении оператора LOAD DATA INFILE, используемого в MySQL 4.0. |
| NEW_LOAD_EVENT | Это событие генерируется при выполнении инструкции LOAD DATA INFILE, используемой в версиях MySQL 4.0 и 4.1. |
| RAND_EVENT | Это событие генерируется выполнением инструкции, содержащей функцию RAND().Это событие не используется, когда binlog_format находится в режиме ROW. |
| USER_VAR_EVENT | Это событие генерируется выполнением инструкции, содержащей пользовательские переменные.Это событие не используется, когда binlog_format находится в режиме ROW. |
| FORMAT_DESCRIPTION_EVENT | Событие описания, записанное в начале каждого файла binlog, используется в MySQL 5.0 и более поздних версиях вместо START_EVENT_V3. |
| XID_EVENT | Только механизм хранения, который поддерживает XA, механизм хранения локальной тестовой базы данных - innodb, и XID_EVENT появляется на всех из них; отправка транзакции innodb генерирует оператор BEGIN оператора QUERY_EVENT, QUERY_EVENT и COMMIT, если это механизм хранения myIsam, он также будет иметь Оператор BEGIN и COMMIT, просто тип COMMIT не XID_EVENT |
| BEGIN_LOAD_QUERY_EVENT | Это событие генерируется при выполнении инструкции LOAD DATA INFILE, используемой в MySQL 5.0. |
| EXECUTE_LOAD_QUERY_EVENT | Это событие генерируется при выполнении инструкции LOAD DATA INFILE, используемой в MySQL 5.0. |
| TABLE_MAP_EVENT | Используется, когда binlog_format находится в режиме ROW, сопоставляет определение таблицы с числом и записывает перед событиями операций со строками (включая: WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT) |
| PRE_GA_WRITE_ROWS_EVENT | Просрочено, заменено на WRITE_ROWS_EVENT |
| PRE_GA_UPDATE_ROWS_EVENT | Просрочено, заменено на UPDATE_ROWS_EVENT |
| PRE_GA_DELETE_ROWS_EVENT | Просрочено, заменено на DELETE_ROWS_EVENT |
| WRITE_ROWS_EVENT | Используется, когда binlog_format находится в режиме ROW, что соответствует операции вставки. |
| UPDATE_ROWS_EVENT | Используется, когда binlog_format находится в режиме ROW, что соответствует операции обновления. |
| DELETE_ROWS_EVENT | Используется, когда binlog_format находится в режиме ROW, что соответствует операции удаления. |
| INCIDENT_EVENT | Главный сервер имеет ненормальное событие, уведомляет подчиненный сервер и сообщает, что это может привести к тому, что данные будут в несогласованном состоянии. |
| HEARTBEAT_LOG_EVENT | Главный сервер сообщает подчиненному серверу, что главный сервер все еще жив и не записывает в файл журнала. |
Структура событий Binlog
Объект события делится на заголовок события и тело события.Структура события следующая:
+=====================================+
| event | timestamp 0 : 4 |
| header +----------------------------+
| | type_code 4 : 1 |
| +----------------------------+
| | server_id 5 : 4 |
| +----------------------------+
| | event_length 9 : 4 |
| +----------------------------+
| | next_position 13 : 4 |
| +----------------------------+
| | flags 17 : 2 |
| +----------------------------+
| | extra_headers 19 : x-19 |
+=====================================+
| event | fixed part x : y |
| data +----------------------------+
| | variable part |
+=====================================+
Если длина заголовка событияxбайт, то длина тела события равна(event_length - x)байт; устанавливается в теле событияfixed partдлинаyбайт, тоvariable partдлина(event_length - (x + y))байт
Краткий анализ Binlog Event
Проанализируйте событие с самого простого экземпляра, включая создание таблицы, вставку данных, обновление данных и удаление данных;
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test values(1,22,"小旋锋");
update test set name='whirly' where id=1;
delete from test where id=1;
Формат журналаSTATEMENT, просмотреть все события
Формат журналаROWКогда это выглядит следующим образом, вы можете обнаружить, что есть некоторые различия
Что касается анализа событий, при необходимости вы можете обратиться к справочным документам для расчета.
Справочная документация
- Справочное руководство по MySQL 5.7. Ведение двоичного журнала
- MySQL Internals Manual.The Binary Log
- Чжу Сяоси Анализ двоичного журнала MySQL
- Семь ножей Анализ формата бинарного журнала MySQL
- Описание журнала binlog в Mysql и использование журнала binlog для восстановления записей операций с данными
- MySql Binlog первое знакомство
- Убийственные новые возможности MySQL 5.7: принцип GTID и реальный бой
- Практика репликации master-slave на основе MySQL 5.7 GTID