Слияние кода завершено, страница обновится автоматически
Начало было простым — мне требовался инструмент для анализа mysql binlog с целью восстановления данных при необходимости. Первое, что пришло в голову, это использование официального инструмента от MySQL — mysqlbinlog. Однако после использования этого инструмента выяснилось, что он позволяет указывать только уровень базы данных, а не таблицы. Кроме того, иногда создание/удаление таблиц не представляет интереса, а вот DML операции очень важны. Также возникает вопрос, можно ли фильтровать только определённые типы SQL запросов для конкретной таблицы? Именно поэтому я начал поиск подходящих инструментов онлайн, и основные направления были следующими:
Преобразование binlog путём имитации slave: представителем такого подхода является binlog2sql.
Прямое чтение и анализ binlog файла: этот метод использует mysqlbinlog и аналогичные программы.Инструмент binlog2sql, который я использовал, работал неплохо, но был слишком медленным. Я решил найти способ создания инструмента, который бы мог быстро анализировать binlog и удовлетворять моим требованиям. После долгих поисков я решил самостоятельно написать такой инструмент. Начальный выбор я сделал не в пользу Perl, поскольку я мало знаком с этим языком. Однако, попробовав Go и Python, я заметил недостатки этих языков при работе с текстами, и выбрал Perl за его мощные возможности работы с регулярными выражениями и выполнением shell команд.Этот небольшой инструмент использует mysqlbinlog как основной источник данных, получает читаемый binlog через Perl, а затем выполняет потоковое обработывание данных, чтобы достичь цели. По моим наблюдениям, такая комбинация работает достаточно эффективно. Учитывая мой ограниченный опыт программирования на Perl, программа может содержать ошибки. Если вы обнаружите ошибки или знаете лучшие способы реализации, пожалуйста, сообщите об этом через issue или pull request, или напишите мне на почту 1459307744@qq.com.
Это инструмент, написанный на Perl, предназначенный для преобразования MySQL binlog в SQL. Он предназначен для решения проблемы отсутствия возможности указания конкретных таблиц в mysqlbinlog. Основные функции включают:
INSERT, UPDATE, DELETE.Общая идея скрипта очень проста и состоит из трёх шагов:1. Получение информации о полях таблиц из information_schema через работу с указанным источником данных.
2. Получение указанного файла binlog с помощью mysqlbinlog.
3. Парсинг binlog, извлечение SQL, замена заполнителей на названия полей, восстановление SQL, что позволяет достичь цели возврата в прошлое.По моим практическим тестам, использование Perl и mysqlbinlog обеспечивает хорошую производительность. Процесс полностью основан на потоковой обработке, что теоретически позволяет работать даже с большими файлами binlog. Однако в нашей компании случаи восстановления данных встречаются не так часто (если ошибок будет слишком много, возможно, потеряю работу), поэтому некоторые моменты могут быть рассмотрены недостаточно всесторонне. Для более подробной проверки и модификаций можно использовать ISSUE.
Этот раздел крайне важен, пожалуйста, обратите внимание.
Вопрос: Если я удалю таблицу, смогу ли я восстановить данные?
Ответ: Если вы удалили таблицу, но есть её резервная копия и структура таблицы не изменилась, то восстановление возможно.
Вопрос: Я удалил таблицу, но не восстановил её структуру, могу ли я восстановить все SQL-операции, которые были выполнены над этой таблицей ранее?
Ответ: Нет. Мы используем информацию из information_schema для получения названий полей и их порядка, чтобы заменять заполнители в binlog. Если таблица была удалена и её структура не восстановлена, DML-операции будут игнорированы.
Вопрос: Если структура таблицы изменилась, могу ли я восстановить данные?Ответ: Нет. Если вы изменили таблицу, какое значение имеет восстановление старых данных? Даже если вы попробуете принудительно распарсить данные, они могут оказаться некорректными или просто невалидными.## Установка и конфигурация
GitHub: https://github.com/Succy/bin2sql
Gitee: https://gitee.com/succy/bin2sql
Просто скачайте исполняемые файлы из раздела release, добавьте им права на выполнение, и они будут готовы к использованию на Linux.
Сначала следует отметить, что тестирование проводилось в окружении Linux, а также разработка была выполнена с учётом этой среды. Это связано с тем, что язык Perl часто входит в состав многих дистрибутивов Linux. Я использую CentOS 7, где версия Perl составляет 5.16.3. Для использования в окружении Windows требуется установка среды Perl, а также замена внутреннего mysqlbinlog на mysqlbinlog.exe (при условии, что mysqlbinlog.exe находится в переменной среды).
Внимание: MySQL Server обязательно должен быть запущен с включённым бинлогом, а также установлен режим binlog_format=row, так как только в этом режиме записываются данные до и после обновлений. Если вы используете режим mixed, пожалуйста, переходите на ветку mixed, так как этот режим не поддерживает восстановление данных.
Мои тесты проводились на версии MySQL 5.7.25, теоретически поддерживаются все версии >= 5.6. Однако, я не проверял это.
Ниже приведены шаги установки:
@1. Поэтому, чтобы восстановить реальные названия полей, используется база данных information_schema MySQL. Таким образом, в скрипте используется Perl DBI для работы с базой данных.На CentOS 7 существует несколько способов установки DBI/DBD::mysql, в том числе использование yum или скачивания исходного кода. Ниже приведены мои шаги установки.1\. Установка CPAN через yum (CPAN — это менеджер пакетов для Perl, аналогичен npm для Node.js или pip для Python)
yum -y install perl-CPAN
2\. Установка DBI через CPAN (при первом использовании CPAN вам могут потребоваться некоторые конфигурационные настройки, которые можно оставить по умолчанию)
perl -MCPAN -e shell
install DBI
3\. Установка DBD::mysql через CPAN
perl -MCPAN -e shell
install DBD::mysql
1\. Клонирование данного проекта на локальную машину сервера
2\. Переход в корневую директорию проекта и предоставление прав на выполнение
cd bin2sql && chmod +x bin2sql
3\. Запуск bin2sql
```Запуск bin2sql выводит следующее справочное сообщение```shell
MySQL Binlog в SQL
Параметры:
-h, --host=name Получить binlog с сервера, по умолчанию localhost.
-u, --user=name Подключиться к удалённому серверу как имя пользователя, по умолчанию root.
-P, --port=# Номер порта для соединения или 3306 для значения по умолчанию.
-p, --password[=name] Пароль для подключения к удалённому серверу.
-t, --tables=name Экспортировать таблицы с указанными именами таблиц, разделителями запятой.
-d, --database=name Вывести записи только для этой базы данных (локальный лог только).
-B, --flashback Отображение SQL для восстановления, только DML может быть использовано для восстановления.
--start-datetime=name Начало чтения binlog с первого события, имеющего дату и время равные или позднее указанной;
аргумент должен быть датой и временем в часовом поясе местного времени, в любом допустимом
формате для типов DATETIME и TIMESTAMP в MySQL, например: 2004-12-25 11:25:56 (вы должны
вероятно использовать кавычки для вашей оболочки, чтобы правильно установить его).
``````shell
--start-datetime=name Начало чтения binlog с первого события, имеющего дату и время равные или позднее указанной;
аргумент должен быть датой и временем в часовом поясе местного времени, в любом допустимом
формате для типов DATETIME и TIMESTAMP в MySQL, например: 2004-12-25 11:25:56 (вы должны
вероятно использовать кавычки для вашей оболочки, чтобы правильно установить его).
--start-position=# Начните чтение бинлога с позиции N. Применимо к первому
переданному в командной строке бинлогу.
--stop-datetime=name Остановите чтение бинлога при первой записи, имеющей дату,
равную или последующую по сравнению с аргументом; аргумент
должен представлять дату и время в локальном часовом поясе,
в любом допустимом формате для типов DATETIME и TIMESTAMP
сервера MySQL, например: 2004-12-25 11:25:56 (вы должны
использовать кавычки для вашей оболочки, чтобы правильно
установить значение).
--stop-position=# Остановите чтение бинлога с позиции N. Применимо к последнему
переданному в командной строке бинлогу.
--sql-type Тип SQL, который вы хотите обработать, поддерживает INSERT, UPDATE, DELETE.
-f, --binlog=name Чтение из файла бинлога.
--help Выведите справочное сообщение.### 3. Авторское предложение
Если у вас несколько серверов баз данных, рекомендуется запускать этот скрипт на пустом сервере, используя параметр `-h` для удалённого получения и анализа бинлого. Не следует случайно запускать его на серверах баз данных, поскольку это может привести к двум причинам:
+ Не требуется устанавливать зависимости DBI и DBD для Perl;
+ Параллельное выполнение `mysqlbinlog` и `bin2sql`, которое потребляет некоторое количество процессора, может влиять на производительность I/O сервера баз данных.
## Использование и примеры
### 1. Опции использования
#### Настройка соединения с MySQL
> Для получения информации о схеме и получения информации из бинлого с помощью `mysqlbinlog` используются одни и те же параметры соединения с MySQL.
-h хост; -P порт; -u имя пользователя; -p пароль
Эти параметры предназначены для mysqlbinlog. Здесь важно обратить внимание на параметр -u: он должен иметь права REPLICATION SLAVE.
Рекомендовано предоставить следующие права:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO User
#### Фильтрация объектов
-d, --databases SQL целевых баз данных, разделённых запятыми, например -d db1,db2. Обязательный параметр.
-t, --tables SQL целевых таблиц, разделённых пробелами, например -t tbl1 tbl2. Необязательный параметр. По умолчанию пустой.
```--sql-type Разбор указанного SQL-типа, поддерживаются INSERT, UPDATE, DELETE. Несколько типов разделены запятой, например --sql-type INSERT,DELETE. Необязательный параметр. По умолчанию разбор всех типов.```#### Диапазон разбора
> Эти параметры используются в сочетании с mysqlbinlog
-f,--binlog Бинлог-файл для разбора, полный путь не требуется. Обязательный параметр.
--start-position Начальная позиция разбора. Необязательный параметр.
--stop-position Конечная позиция разбора. Необязательный параметр.
--start-datetime Начальное время разбора, формат '%Y-%m-%d %H:%M:%S'. Необязательный параметр. По умолчанию фильтрация отключена.
--stop-datetime Конечное время разбора, формат '%Y-%m-%d %H:%M:%S'. Необязательный параметр. По умолчанию фильтрация отключена.
#### Возврат
-B, --flashback Генерация SQL для возврата, необязательный параметр. По умолчанию значение false. Только DML поддерживают возврат, DDL не поддерживают возврат.
### 2. Пример
#### Создание примерной таблицы
```sql
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`username` varchar(255) DEFAULT NULL COMMENT 'Имя пользователя',
`address` varchar(255) DEFAULT NULL COMMENT 'Адрес',
`create_time` datetime DEFAULT NULL COMMENT 'Дата создания',
`phone_no` varchar(255) DEFAULT NULL COMMENT 'Номер телефона',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> select * from user; +----+---------------+-----------------+---------------------+--------------+ | id | username | address | create_time | phone_no | +----+---------------+-----------------+---------------------+--------------+ | 1 | Succy | Guangxi Nanning | 2021-05-03 21:05:14 | 1300000001 | | 2 | Wang Xiaohua | Shanxi Taiyuan | 2021-05-03 21:05:46 | 15099999999 | | 3 | Jiang Xiaobai | Chongqing | 2021-05-03 21:06:21 | 19788888888 | | 4 | Guo Jing | Hubei Xiangyang | 2021-05-03 21:06:43 | 188888898999 | | 5 | Yang Guo | Jiangsu Suzhou | 2021-05-03 21:07:10 | 16666666878 | | 6 | Lu Wushuang | Shandong Jinan | 2021-05-03 21:07:35 | 155236995454 | +----+---------------+-----------------+---------------------+--------------+ 6 строк (0.00 сек)
#### Получение всех операций над таблицей user```shell
shell> . /bin2sql -d demo -f mysql-bin.000002 -h 127.0.0.1 -t user
#210503 21:03:54 end_log_pos: 7852
CREATE TABLE demo.user (
id int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
username varchar(255) NULL COMMENT 'Имя пользователя',
address varchar(255) NULL COMMENT 'Адрес',
create_time datetime(0) NULL COMMENT 'Дата создания',
phone_no varchar(255) NULL COMMENT 'Номер телефона',
PRIMARY KEY (id)
);
#210503 21:05:24 end_log_pos: 8128
INSERT INTO demo.user VALUES(1, 'Succy', 'Гuangxi Nanning', '2021-05-03 21:05:14', '1300000001');
#210503 21:05:53 end_log_pos: 8440
INSERT INTO demo.user VALUES(2, 'Wang Xiaohua', 'Shanxi Taiyuan', '2021-05-03 21:05:46', '15099999999');
#210503 21:06:27 end_log_pos: 8746
INSERT INTO demo.user VALUES(3, 'Jiang Xiaobai', 'Chongqing', '2021-05-03 21:06:21', '19788888888');
#210503 21:06:50 end_log_pos: 9053
INSERT INTO demo.user VALUES(4, 'Guo Jing', 'Hubei Xiangyang', '2021-05-03 21:06:43', '188888898999');
#210503 21:07:14 end_log_pos: 9359
INSERT INTO demo.user VALUES(5, 'Yang Guo', 'Jiangsu Suzhou', '2021-05-03 21:07:10', '16666666878');
#210503 21:07:44 end_log_pos: 9672
INSERT INTO demo.user VALUES(6, 'Lu Wushuang', 'Shandong Jinan', '2021-05-03 21:07:35', '155236995454');
#210503 21:10:45 end_log_pos: 10030
UPDATE demo.user SET id=5, username='Yang Guoguo', address='Jiangsu Nanjing', create_time='2021-05-03 21:07:10', phone_no='16666666878' WHERE id=5 AND username='Yang Guo' AND address='Jiangsu Suzhou' AND create_time='2021-05-03 21:07:10' AND phone_no='16666666878';
#210503 21:11:07 end_log_pos: 10340
INSERT INTO demo.user VALUES(7, 'Gongsun Luee', 'Jueqing Valley', '2021-05-03 21:11:01', '188777738934');
#210503 21:11:30 end_log_pos: 10645
INSERT INTO demo.user VALUES(8, 'Cheng Ying', 'Taohuashan Island', '2021-05-03 21:11:24', '1778346836483');
#210503 21:11:33 end_log_pos: 10951
DELETE FROM demo.user WHERE id=3 AND username='Jiang Xiaobai' AND address='Chongqing' AND create_time='2021-05-03 21:06:21' AND phone_no='19788888888';
#210503 21:11:51 end_log_pos: 11315
UPDATE demo.user SET id=2, username='Wang Xiaohua', address='Guangdong Shenzhen', create_time='2021-05-03 21:05:46', phone_no='15099999999' WHERE id=2 AND username='Wang Xiaohua' AND address='Shanxi Taiyuan' AND create_time='2021-05-03 21:05:46' AND phone_no='15099999999';> Обнаружено, что некоторые данные были удалены неправильно, а также внесённые изменения оказались неверными. Нужно восстановить все удалённые и изменённые данные.### Восстановление всех некорректно изменённых данных
shell> ./bin2sql -d demo -f mysql-bin.000002 -h 127.0.0.1 -t user -B --sql-type DELETE,UPDATE
#210503 21:10:45 end_log_pos: 10030
UPDATE `demo`.`user` SET `id`=5, `username`='Yang Guo', `address`='Jiangsu Suzhou', `create_time`='2021-05-03 21:07:10', `phone_no`='16666666878' WHERE `id`=5 AND `username`='Yang Guo Guo' AND `address`='Jiangsu Nanjing' AND `create_time`='2021-05-03 21:07:10' AND `phone_no`='16666666878';
#210503 21:11:33 end_log_pos: bk
INSERT INTO `demo`.`user` VALUES(3, 'Jiang Xiaobai', 'Chongqing', '2021-05-03 21:06:21', '19788888888');
#210503 21:11:51 end_log_pos: 11315
UPDATE `demo`.`user` SET `id`=2, `username`='Wang Xiao Hua', `address`='Shanxi Taiyuan', `create_time`='2021-05-03 21:05:46', `phone_no`='15099999999' WHERE `id`=2 AND `username`='Wang Xiao Hua' AND `address`='Guangdong Shenzhen' AND `create_time`='2021-05-03 21:05:46' AND `phone_no`='15099999999';
Еще больше возможностей ждут вас
binlog2sql Этот проект предоставил мне пример для моей Python-версии, хотя последний был отменён.
MySQL_Binlog_Table_Filter Этот инструмент послужил основой для данного проекта. Поскольку я новичок в Perl, этот проект помог мне понять многие аспекты написания кода на этом языке.
Вы можете оставить комментарий после Вход в систему
Неприемлемый контент может быть отображен здесь и не будет показан на странице. Вы можете проверить и изменить его с помощью соответствующей функции редактирования.
Если вы подтверждаете, что содержание не содержит непристойной лексики/перенаправления на рекламу/насилия/вульгарной порнографии/нарушений/пиратства/ложного/незначительного или незаконного контента, связанного с национальными законами и предписаниями, вы можете нажать «Отправить» для подачи апелляции, и мы обработаем ее как можно скорее.
Комментарии ( 0 )