Подробное объяснение MySQL Binlog: основная технология повышения надежности базы данных

2024-07-12

Каталог статей

1. Введение

1.1 Что такое MySQL Корзина Бревно?

MySQL Bin Log（Двоичный Бревно, двоичный журнал) — файл журнала базы данных MySQL, в котором записываются все изменения, выполненные в базе данных.DMLоперации (такие как INSERT, UPDATE, DELETE и т. д.), ноИсключает ВЫБРАТЬждатьDQL Операция только для чтения. Bin Log — важный инструмент MySQL для реализации репликации, восстановления и аудита. Подробности см.:DDL, DML, DQL и DCL в MySQL

1.2 Функции и сценарии применения Bin Log

Основные функции Bin Log включают в себя: репликацию «главный-подчиненный», восстановление данных, резервное копирование данных и подписку на данные.

Сценарии применения	концепция	Случай
репликация главный-подчиненный	Главная библиотека отправляет записи операций обновления в бинлоге ведомой библиотеке, а ведомая библиотека читает бинлог и выполняет операторы SQL.	Платформа электронной коммерции: основная база данных записывает операции с заказами в binlog и синхронизирует данные заказов из базы данных для обеспечения согласованности всех узлов.
Восстановление данных	Используйте записи binlog для обратного выполнения операторов SQL для восстановления данных.	Финансовая система: администраторы восстанавливают случайно удаленные записи транзакций путем анализа binlog, чтобы обеспечить целостность данных и избежать финансовых потерь.
резервное копирование данных	Binlog используется для инкрементального резервного копирования, экономя время резервного копирования и затраты на пространство.	Платформа социальных сетей: регулярное ежедневное резервное копирование файлов binlog для быстрого восстановления до последнего состояния без необходимости полного резервного копирования всей базы данных.
Подписка на данные	Используйте binlog для мониторинга операций обновления базы данных в режиме реального времени.	Анализ данных розничных компаний в режиме реального времени. Путем анализа binlog операция вставки записей о продажах фиксируется и передается на платформу анализа данных в режиме реального времени для анализа тенденций продаж.

Благодаря этим конкретным случаям мы можем более ясно увидеть важность и разнообразие MySQL binlog в практических приложениях. Правильное использование функции binlog может значительно повысить надежность системы, возможности восстановления и скорость бизнес-реагирования.

2. Основные понятия Bin Log

2.1 Принцип работы Bin Log

Когда сервер MySQL включает функцию Bin Log, все операции по изменению базы данных будут выполняться.запись события в файл журнала Bin. Эти события сохраняются в порядке выполнения, образуя непрерывную последовательность журнала операций. Эти события можно воспроизвести, чтобы воссоздать состояние базы данных, когда данные необходимо восстановить или скопировать.

2.2 Три формата журнала Bin Log

MySQL Bin Log имеет три формата записей:
Ниже приведены три формата записей MySQL Bin Log, организованные в виде таблицы:

формат записи	описывать	преимущество	недостаток
Ведение журнала на основе операторов (SBL)	Запишите сам выполненный оператор SQL.	Объем журнала небольшой и подходит для простых операций SQL.	В некоторых случаях согласованность данных не может быть гарантирована, например, при использовании недетерминированных функций (например, NOW()).
Ведение журнала по строкам (RBL)	Запишите конкретные изменения каждой строки данных.	Более точный, подходит для сложных операций и копирования.	Большой объем журнала, большие нагрузки на диск и сеть.
Смешанная лесозаготовка (ML)	Переключайтесь между режимами Statement и Row в зависимости от конкретных обстоятельств.	Учитывая преимущества обоих	Относительно сложно внедрить и управлять

3. Настройка журнала Bin и управление им.

3.1 Включить журнал бункеров

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

Чтобы включить Bin Log, вам необходимо настроить файл конфигурации MySQL (обычноmy.cnfилиmy.ini) и выполните следующую настройку:

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

После изменения конфигурации перезапустите 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 Настройка параметров журнала бункеров

Общие параметры конфигурации журнала Bin включают в себя:

log_bin: Включить журнал бункеров.
server_id: уникальный идентификатор сервера, используемый для репликации.
binlog_format: Установите формат журнала ячеек (ЗАЯВЛЕНИЕ, СТРОКА, СМЕШАННЫЙ).
expire_logs_days: установите количество дней для автоматического истечения срока действия и удаления файлов журнала Bin.
max_binlog_size: установите максимальный размер одного файла журнала Bin.

3.3 Управление файлами журнала Bin Log

Общие команды для управления файлами Bin Log:

Просмотр списка файлов журнала Bin：
```
SHOW BINARY LOGS;
1
```
Просмотр размера файла журнала Bin：
```
SHOW MASTER STATUS;
1
```

Удалить старые файлы журнала Bin.：

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

или:

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

3.4 Просмотр содержимого журнала бункеров


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

Используйте инструмент 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)
1
2
3
4
5
6
7
8
9
10
11
12

Если вы откроете его напрямую, он будет искажен.

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 Использование инструмента mysqlbinlog

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
1

3.6 Анализ и воспроизведение журнала Bin Log

События Bin Log могут быть воспроизведены на сервере MySQL для восстановления данных:

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

4. Применение Bin Log при репликации

4.1 Принцип репликации «главный-подчиненный»

Основной принцип репликации master-slave MySQL заключается в том, что главная база данных записывает Bin Log, а подчиненная база данных считывает и воспроизводит эти журналы для достижения синхронизации данных. Конкретные шаги заключаются в следующем:

Основная библиотека выполняет операции записи и записывает эти операции в журнал Bin Log.
Подчиненная библиотека подключается к основной библиотеке, считывает журнал Bin и применяет его к своим собственным данным.

4.2 Настройка репликации master-slave

Настройте основную библиотеку：
```
[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3
```
Настроить подчиненную библиотеку：
```
[mysqld]
server-id=2
relay-log=relay-bin
1
2
3
```

Создайте пользователя репликации в основной базе данных.：

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

Настройка репликации на подчиненной базе данных：

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

Проверьте статус репликации：
```
SHOW SLAVE STATUSG
1
```

4.3 Мониторинг и управление репликацией

Статус репликации можно отслеживать с помощью следующей команды:

SHOW SLAVE STATUSG
1

Пояснения к общим полям статуса:

Slave_IO_Running: состояние потока ввода-вывода.
Slave_SQL_Running:Состояние потока SQL.
Seconds_Behind_Master: время отставания подчиненной библиотеки от главной.

4.4 Инкрементное резервное копирование

Инкрементное резервное копирование означает резервное копирование всех изменений, произошедших с момента последнего полного или последнего инкрементного резервного копирования. Инкрементное резервное копирование можно выполнить с помощью Bin Log.

Выполните полное резервное копирование：
```
mysqldump --all-databases --master-data=2 > full_backup.sql
1
```
Запишите текущее местоположение журнала Bin Log.：

Найдите следующие строки в файле полной резервной копии:
```
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
1
```

Журнал резервной копии：

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

4.5 Восстановление на основе журнала Bin

Восстановить полную резервную копию：
```
mysql < full_backup.sql
1
```
Применить инкрементное резервное копирование：
```
mysql < incremental_backup.sql
1
```

5. Напишите в конце

MySQL Bin Log Это мощный инструмент, который широко используется в таких сценариях, как восстановление данных, репликация и аудит.При правильной настройке и использованииBin Log , что может значительно повысить надежность и доступность базы данных MySQL. В практических приложениях администраторам и разработчикам баз данных крайне важно овладеть навыками использования и методами оптимизации Bin Log.

Обмен технологиями