моя контактная информация
Почтамезофия@protonmail.com
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
MySQL Bin Log
(Двоичный Бревно, двоичный журнал) — файл журнала базы данных MySQL, в котором записываются все изменения, выполненные в базе данных.DML
операции (такие как INSERT, UPDATE, DELETE и т. д.), ноИсключает ВЫБРАТЬждатьDQL
Операция только для чтения. Bin Log — важный инструмент MySQL для реализации репликации, восстановления и аудита. Подробности см.:DDL, DML, DQL и DCL в MySQL
Основные функции Bin Log включают в себя: репликацию «главный-подчиненный», восстановление данных, резервное копирование данных и подписку на данные.
Сценарии применения | концепция | Случай |
---|---|---|
репликация главный-подчиненный | Главная библиотека отправляет записи операций обновления в бинлоге ведомой библиотеке, а ведомая библиотека читает бинлог и выполняет операторы SQL. | Платформа электронной коммерции: основная база данных записывает операции с заказами в binlog и синхронизирует данные заказов из базы данных для обеспечения согласованности всех узлов. |
Восстановление данных | Используйте записи binlog для обратного выполнения операторов SQL для восстановления данных. | Финансовая система: администраторы восстанавливают случайно удаленные записи транзакций путем анализа binlog, чтобы обеспечить целостность данных и избежать финансовых потерь. |
резервное копирование данных | Binlog используется для инкрементального резервного копирования, экономя время резервного копирования и затраты на пространство. | Платформа социальных сетей: регулярное ежедневное резервное копирование файлов binlog для быстрого восстановления до последнего состояния без необходимости полного резервного копирования всей базы данных. |
Подписка на данные | Используйте binlog для мониторинга операций обновления базы данных в режиме реального времени. | Анализ данных розничных компаний в режиме реального времени. Путем анализа binlog операция вставки записей о продажах фиксируется и передается на платформу анализа данных в режиме реального времени для анализа тенденций продаж. |
Благодаря этим конкретным случаям мы можем более ясно увидеть важность и разнообразие MySQL binlog в практических приложениях. Правильное использование функции binlog может значительно повысить надежность системы, возможности восстановления и скорость бизнес-реагирования.
Когда сервер MySQL включает функцию Bin Log, все операции по изменению базы данных будут выполняться.запись события в файл журнала Bin. Эти события сохраняются в порядке выполнения, образуя непрерывную последовательность журнала операций. Эти события можно воспроизвести, чтобы воссоздать состояние базы данных, когда данные необходимо восстановить или скопировать.
MySQL Bin Log имеет три формата записей:
Ниже приведены три формата записей MySQL Bin Log, организованные в виде таблицы:
формат записи | описывать | преимущество | недостаток |
---|---|---|---|
Ведение журнала на основе операторов (SBL) | Запишите сам выполненный оператор SQL. | Объем журнала небольшой и подходит для простых операций SQL. | В некоторых случаях согласованность данных не может быть гарантирована, например, при использовании недетерминированных функций (например, NOW()). |
Ведение журнала по строкам (RBL) | Запишите конкретные изменения каждой строки данных. | Более точный, подходит для сложных операций и копирования. | Большой объем журнала, большие нагрузки на диск и сеть. |
Смешанная лесозаготовка (ML) | Переключайтесь между режимами Statement и Row в зависимости от конкретных обстоятельств. | Учитывая преимущества обоих | Относительно сложно внедрить и управлять |
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)
Чтобы включить Bin Log, вам необходимо настроить файл конфигурации MySQL (обычноmy.cnf
илиmy.ini
) и выполните следующую настройку:
[mysqld]
log-bin=mysql-bin
server-id=1
После изменения конфигурации перезапустите MySQL.осуществлятьSHOW VARIABLES LIKE 'log_bin';
Значение значения включено.
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)
Общие параметры конфигурации журнала Bin включают в себя:
log_bin
: Включить журнал бункеров.server_id
: уникальный идентификатор сервера, используемый для репликации.binlog_format
: Установите формат журнала ячеек (ЗАЯВЛЕНИЕ, СТРОКА, СМЕШАННЫЙ).expire_logs_days
: установите количество дней для автоматического истечения срока действия и удаления файлов журнала Bin.max_binlog_size
: установите максимальный размер одного файла журнала Bin.Общие команды для управления файлами Bin Log:
Просмотр списка файлов журнала Bin:
SHOW BINARY LOGS;
Просмотр размера файла журнала Bin:
SHOW MASTER STATUS;
Удалить старые файлы журнала Bin.:
PURGE BINARY LOGS TO 'mysql-bin.000010';
или:
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');
Используйте инструмент mysqlbinlog для просмотра содержимого журнала Bin:
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)
Если вы откроете его напрямую, он будет искажен.
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 — это инструмент командной строки, используемый для анализа файлов журнала Bin. Общие варианты включают в себя:
--start-datetime
: укажите время начала анализа.--stop-datetime
: укажите время окончания синтаксического анализа.--start-position
: укажите позицию, с которой начинается синтаксический анализ.--stop-position
: Укажите позицию, в которой заканчивается синтаксический анализ.Например, чтобы просмотреть журнал бункеров за определенный период времени:
mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
События Bin Log могут быть воспроизведены на сервере MySQL для восстановления данных:
mysqlbin logbinlog.000001 | mysql -u root -p
Основной принцип репликации master-slave MySQL заключается в том, что главная база данных записывает Bin Log, а подчиненная база данных считывает и воспроизводит эти журналы для достижения синхронизации данных. Конкретные шаги заключаются в следующем:
Настройте основную библиотеку:
[mysqld]
log-bin=mysql-bin
server-id=1
Настроить подчиненную библиотеку:
[mysqld]
server-id=2
relay-log=relay-bin
Создайте пользователя репликации в основной базе данных.:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Настройка репликации на подчиненной базе данных:
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=0;
START SLAVE;
Проверьте статус репликации:
SHOW SLAVE STATUSG
Статус репликации можно отслеживать с помощью следующей команды:
SHOW SLAVE STATUSG
Пояснения к общим полям статуса:
Slave_IO_Running
: состояние потока ввода-вывода.Slave_SQL_Running
:Состояние потока SQL.Seconds_Behind_Master
: время отставания подчиненной библиотеки от главной.Инкрементное резервное копирование означает резервное копирование всех изменений, произошедших с момента последнего полного или последнего инкрементного резервного копирования. Инкрементное резервное копирование можно выполнить с помощью Bin Log.
Выполните полное резервное копирование:
mysqldump --all-databases --master-data=2 > full_backup.sql
Запишите текущее местоположение журнала Bin Log.:
Найдите следующие строки в файле полной резервной копии:
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
Журнал резервной копии:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
Восстановить полную резервную копию:
mysql < full_backup.sql
Применить инкрементное резервное копирование:
mysql < incremental_backup.sql
MySQL Bin Log
Это мощный инструмент, который широко используется в таких сценариях, как восстановление данных, репликация и аудит.При правильной настройке и использованииBin Log
, что может значительно повысить надежность и доступность базы данных MySQL. В практических приложениях администраторам и разработчикам баз данных крайне важно овладеть навыками использования и методами оптимизации Bin Log.