01 Предисловие

Дело в том, что я отвечаю за систему отчетности нашей компании, а Сяопан — мой младший брат. Однажды он случайно удалил часть производственных данных. На меня безумно жаловались и расспрашивали пользователи в группе, и кинулись спрашивать, что мне делать. Я покрылся холодным потом и сделал ему выговор: Глупый, настолько глупый, чтобы войти в музей, неужели производственные данные могут быть перемещены случайно?

Сяопан видит мою обычную улыбку, но я не ожидал такого большого пожара. В спешке он на самом деле встал передо мной на колени: Брат Юань, у меня есть старые люди, молодые люди и подруги, не увольняйте меня. Как только я это услышал, огонь стал еще больше: соавтор, у тебя есть девушка? ? ? В это время наш администратор базы данных Лао Линь пришел сгладить игру:Не паникуйте, молодые люди не могут себя контролировать, и неизбежно, что они поступят неправильно. Я могу восстановить данные в состояние, в котором они были в любое время месяца. Услышав это, Сяо Пан поспешно обнял Лао Линя за бедро, плача и благодаря свою мать.

Вы удивлены, услышав это? Можно ли восстановить данные на полмесяца назад? Как DBA Лао Линь сделал это? Я хорошо поболтал с ним. Лао Линь поджег 82-летнюю Хуа Цзы в руке, глубоко вздохнул и сказал:Все должно начинаться с того, как выполняется оператор обновления..

1.1 Начните с оператора обновления

Предположим, я построил оператор таблицы следующим образом:

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

Данные таблицы следующие:

Сегодня день рождения Чжан Саня, и я добавлю к его возрасту один год. Затем выполните следующую инструкцию sql:

update student set age = age + 1 where id = 2;

Я говорил о том, как выполняется оператор запроса? Пропущенные студенты видят это«Три года работы: Сяопан даже не знает, как выполняется оператор select, это действительно хорошо! 》, процесс оператора запроса и оператор обновления также будут проходить через него снова, как показано на следующей блок-схеме:

Инициируется оператор обновления: сначала коннектор подключается к базе данных. Затем анализатор узнает, что это оператор обновления, посредством лексического и синтаксического анализа. Таким образом, кеш запросов недействителен.

В предыдущей статье упоминалось:Если таблица имеет обновление. то его кеш запросов выйдет из строя. Вот почему я не рекомендую вам использовать кэш запросов.

Оптимизатор решает использовать индекс ID для обновления, и, наконец, исполнитель отвечает за поиск этой строки данных и выполнение обновления.

Вот в чем суть: в отличие от процесса запроса, обновление также включает в себя два важных модуля журнала. Один из них — журнал повторов: журнал повторов, а другой — журнал архивов: binlog. Чтобы ответить на вопрос в начале статьи, вы должны понимать принципы этих двух журналов, чтобы полностью понять, как это делает администратор баз данных.

02 Журнал транзакций: журнал повторов

Что такое журнал повторов? Для простоты понимания возьмем пример из Geek Time:

Я до сих пор помню статью «Конг Иджи», владелец магазина в отелеЕсть классная доска, которая специально используется для записи кредитных историй гостей.. Если в кредит не много людей, то он может написать имя клиента и счет на доске. Но если в кредите слишком много людей, всегда будут времена, когда пороховая доска не сможет его запомнить.В это время у владельца магазина должен быть еще один.Бухгалтерская книга для записи кредита.

Если кто-то хочет пополнить счет или погасить его, у владельца магазина обычно есть два способа:

• Один из способов - напрямую просмотреть бухгалтерскую книгу и добавить или вычесть кредитный счет;

• Другой метод состоит в том, чтобы на этот раз сначала записать счет на пороховой доске, а затем передать бухгалтерскую книгу для учета после ее закрытия.

Когда бизнес процветает, а прилавок очень занят, владелец магазина обязательно выберет последнее, потому что первая операция слишком хлопотна. Во-первых, вы должны найти запись для общего кредита человека. Подумайте об этом, здесь десятки плотно упакованных страниц. Если владелец магазина хочет найти имя, ему, возможно, придется надеть очки для чтения, чтобы найти его медленно. Найдя его, он достанет счеты, чтобы посчитать, и, наконец, напишет результат обратно в бухгалтерскую книгу.

Весь этот процесс хлопотно думать. Напротив, удобно сначала записать на доске. Вдумайтесь, если бы лавочник не пользовался помощью пороховой доски, ему приходилось бы переворачивать бухгалтерскую книгу каждый раз, когда он ведет учет, не была бы эффективность невыносимо низкой?

2.1 Зачем нужен журнал повторов?

Точно так же в MySQL, если каждое обновление записывается на диск, это может вызвать серьезные проблемы с производительностью:

• Поскольку Innodb взаимодействует с дисками в единицах страниц, а транзакция, скорее всего, изменит только несколько байтов на странице данных, сбрасывать на диск всю страницу данных в это время — пустая трата ресурсов!
• Транзакция может включать изменение нескольких страниц данных, и эти страницы данных физически не являются смежными, используйтеПроизвольная производительность записи ввода-вывода слишком низкая!

Чтобы решить эту проблему, разработчики MySQL использовали идею, похожую на розовую доску продавца, чтобы повысить эффективность обновления. Эта идея называется WAL (Write-Ahead Logging) в MySQL, что означает:Сначала запишите журнал повторов, а затем запишите на диск. Журналы и диски соответствуют розовой доске и книге выше.

Для MySQL характерно следующее: есть записи, которые необходимо обновить, InnDB записывает записи в журнал повторов,и обновить страницу данных в памяти, на данный момент обновление завершено. В то же время фоновый поток будет асинхронно обновлять запись операции на странице данных на диске.

PS:Когда обновляемая страница данных находится в памяти, страница данных в памяти будет обновляться напрямую; когда ее нет в памяти, если можно использовать буфер изменений (место ограничено, об этом мы поговорим позже в статье) обновление будет Операции записываются в буфер изменений, и эти операции записываются в журнал повторов; если в это время есть операция запроса, запускается операция слияния и возвращается измененное значение записи.

Некоторые люди говорят, что движок InnoDB записывает записи журнала в журнал повторов, где журнал повторов, разве он тоже не на диске?

Да, это тоже процесс записи на диск, но в отличие от процесса обновления, процесс обновления представляет собой случайный ввод-вывод на диск, что занимает много времени. А запись журнала повторов — это последовательный ввод-вывод на диск, что более эффективно..

PPS:Существование журнала повторов должно преобразовывать глобальную случайную запись в локальную последовательную запись, тем самым повышая эффективность..

2.2 Процесс записи журнала повторов

Журнал повторов записывает, какие изменения были внесены в страницы данных транзакцией.. Он состоит из двух частей: буфер журнала в памяти (буфер журнала повторов) и файл журнала на диске (файл журнала повторов).

Каждый раз, когда mysql выполняет оператор DML, он сначала записывает записи в буфер журнала повторов, а затем записывает несколько записей операций в файл журнала повторов одновременно в более поздний момент времени. То есть упомянутая выше технология WAL.

Операционная система компьютера сообщает нам, что буферные данные в пользовательском пространстве не могут быть записаны напрямую на диск. Потому что средний должен пройти через буфер пространства ядра операционной системы (OS Buffer).

так,Запись буфера журнала повторов в файл журнала повторов фактически сначала записывает буфер ОС, а затем ОС вызывает функцию fsync() для сброса журнала на диск.. Процесс выглядит следующим образом:

MySQL поддерживает три временных интервала для записи буфера журнала повторов в файл журнала повторов, которые можно настроить с помощью параметра innodb_flush_log_at_trx_commit Значение каждого значения параметра следующее:Рекомендуется установить его равным 1, чтобы гарантировать, что данные не будут потеряны после аварийного перезапуска MySQL..

значение параметра	значение
0 (отложенная запись)	Когда транзакция зафиксирована, журнал в буфере журнала повторов не будет записываться в буфер ОС, но будет записываться в буфер ОС каждую секунду и записываться в файл журнала повторов с помощью вызова fsync(). То есть, когда он установлен на 0, он сбрасывается на диск каждую секунду (приблизительно), а при сбое системы 1 секунда данных будет потеряна.
1 (запись в реальном времени, обновление в реальном времени)	Каждый раз, когда транзакция фиксируется, журнал в буфере журнала повторов записывается в буфер ОС, и вызывается функция fsync(), чтобы сбросить его в файл журнала повторов. Этот метод не приведет к потере данных даже в случае сбоя системы, но поскольку каждая фиксация записывается на диск, производительность ввода-вывода низкая.
2 (запись в реальном времени, отложенное обновление)	Каждая фиксация записывается только в буфер операционной системы, а затем каждую секунду вызывается функция fsync() для записи журнала из буфера операционной системы в файл журнала повторов.

Процесс написания выглядит следующим образом:

2.3 Структура файла журнала повторов

Журнал повторов InnoDB имеет фиксированный размер. Например, его можно настроить как набор из 4 файлов, размер каждого файла 1 ГБ, тогда файл журнала повторов может записывать операции объемом 4 ГБ. Пишите с нуля. Пишите до конца, а затем возвращайтесь к началу, чтобы писать в цикле. Как показано ниже:

На приведенном выше рисунке запись pos представляет позицию LSN (логический порядковый номер) текущей записи журнала повторов.После записи один раз она возвращается к началу файла 0 после записи в конец файла 3; контрольная точка указывает, что страница данных изменяет запись Позиция LSN (логический порядковый номер), соответствующая журналу повторов после выхода на рынок, также перемещается назад и циркулирует.

PS: Контрольная точка — это текущая позиция, которую нужно стереть, которая должна соответствовать LSN на странице данных..

Часть между записью pos и контрольной точкой — это незаписанная область журнала повторов, которую можно использовать для записи новых записей; между контрольной точкой и записью pos — записываемая область журнала повторов, представляющая собой запись об изменении страницы данных. быть смытым.

Когда запись pos догоняет контрольную точку, это означает, что файл журнала повторов заполнен, и новые обновления не могут быть выполнены в это время. Вы должны сначала остановить и стереть некоторые записи (записи должны быть сброшены перед стиранием), а затем нажать контрольную точку, чтобы двигаться вперед, чтобы освободить место для нового журнала.

2.4 Что такое аварийное сохранение?

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

Например: При перезапуске innodb сначала будет проверяться LSN страницы данных на диске.Если LSN страницы данных меньше, чем LSN контрольной точки в логе, восстановление начнется с контрольной точки.

2.5 Журнал отката журнала отмены

**Журнал отмены, в основном обеспечивает функцию отката, но также имеет и другую функцию, то есть контроль версий нескольких строк (MVCC) для обеспечения атомарности транзакций. **В процессе модификации данных в журнале отмены будет записана логическая запись, противоположная текущей операции (можно считать, что при удалении записи в журнале отмены будет записана соответствующая запись вставки, и наоборот). наоборот, когда запись обновляется. При записи она записывает соответствующую противоположную запись обновления), если по какой-то причине транзакция не удается выполнить ненормально, вы можете использовать журнал отмены для отката, чтобы гарантировать целостность транзакции, поэтому журнал отмены также существенный.

03 Архивный журнал: binlog

Говоря о процессе выполнения оператора запроса в предыдущей статье, мы говорили об архитектуре MySQL, включая уровень сервера и уровень механизма. Журнал повторов — это журнал, относящийся к движку InnoDB, а уровень сервера также имеет свой собственный журнал, который называется binlog.

Вначале в MySQL не было механизма InnoDB. Движок, поставляемый с MySQL, называется MyISAM, но MyISAM не имеет возможности защиты от сбоев.журналы binlog можно использовать только для архивирования. InnoDB была введена в MySQL другой компанией в виде подключаемого модуля, и полагаться только на binlog не имеет возможности защиты от сбоев, поэтому InnoDB использует другой набор систем ведения журналов, а именно журнал повторов, для достижения возможности защиты от сбоев.

3.1 формат журнала binlog?

binlog имеет три формата: STATMENT, ROW и MIXED.

До MySQL 5.7.7 форматом по умолчанию был STATEMENT, а после MySQL 5.7.7 значением по умолчанию был ROW. Формат журнала указывается через binlog-format.

• STATMENT: каждый оператор sql, который изменяет данные, будет записан в binlog.
• ROW: не записывает контекстную информацию SQL, нужно только записать, какие данные были изменены. Запомните два, как до, так и после обновления.
• СМЕШАННЫЙ: смесь первых двух режимов, общая репликация использует режим STATEMENT для сохранения binlog, для операций, которые не могут быть реплицированы в режиме STATEMENT, используйте режим ROW для сохранения binlog

3.2 Может ли binlog выполнять аварийное сохранение?

Можно ли добиться возможности cash_safe только с одним binlog? Ответ да, но в бинлог тоже надо добавить чекпойнт.После сбоя базы данных и перезапуска sql after binlog чекпойнт переигрывается. Но это делает binlog слишком большим количеством связанных функций.

Некоторые люди говорят, что также возможно напрямую сравнивать и сопоставлять полные файлы binlog и дисковых баз данных, но это неэффективно. Поскольку binlog — это запись уровня сервера, а не уровня механизма, это может привести к несогласованности данных:

Если binlog записывает 3 части данных, обычно уровень движка также записывает 3 части данных, но в это время узел отключен и перезапущен, binlog обнаруживает, что есть 3 записи, которые необходимо воспроизвести, поэтому воспроизводятся 3 записи. , но уровень механизма может уже записать на диск 2 фрагмента данных, нужно только воспроизвести 1 фрагмент данных. Будут ли повторяться первые два данных, воспроизводимых binlog, например, будет сообщено о дублирующемся ключе.

Кроме того, binlog является дополнительной записью, и невозможно определить, какое содержимое binlog было записано на диск, а какое не было записано при сбое. Redolog — это циклическая запись, и содержимое от контрольной точки до записи pos не записывается на диск.

Поэтому бинлог не подходит для аварийного сохранения.

3.3 Разница между двумя бревнами

Существует три основных различия между журналом повторов и бинлогом:

• Журнал повторов уникален для движка InnoDB, бинлог реализуется серверным уровнем MySQL и может использоваться всеми движками.
• Журнал повторов — это физический журнал, записанныйкакие изменения были сделаны на странице данных;binlog — это логический журнал, в котором записывается исходная логика оператора, например **"добавить 1"** к полю age строки с ID=2.
• Журнал повторов пишется циклически, и место всегда будет израсходовано, бинлог можно писать дополнительно.добавить написатьЭто означает, что после того, как файл binlog будет записан до определенного размера, он переключится на следующий, а не перезапишет предыдущий журнал.

3.4 Процесс выполнения оператора обновления

Поняв концепцию двух видов журналов, давайте взглянем на процесс исполнителя и механизма InnoDB при выполнении оператора обновления:

• Исполнитель извлекает данные строки с id = 2. Идентификатор является первичным ключом, и механизм использует поиск по дереву, чтобы найти строку. Если страница данных, на которой находится эта строка, уже находится в памяти, она будет возвращена непосредственно исполнителю, в противном случае ее необходимо прочитать в память с диска перед возвратом.
• Исполнитель получает данные строки, предоставленные движком, добавляет к этому значению 1, например, раньше было N, теперь N+1, получает новую строку данных, а затем вызывает интерфейс движка для записи нового ряд данных.
• Механизм обновляет новую строку данных в памяти и записывает операцию обновления в журнал повторов, который в это время находится в состоянии подготовки. Затем сообщите исполнителю, что выполнение завершено и транзакция может быть зафиксирована в любой момент.
• Исполнитель генерирует бинлог этой операции и записывает бинлог на диск.
• Исполнитель вызывает интерфейс транзакции фиксации механизма, механизм изменяет только что записанный журнал повторов в состояние фиксации, а журнал повторов записывает имя файла и информацию о местоположении двоичного журнала, чтобы обеспечить согласованность между двоичным журналом и журналом повторов. и обновление завершено.

Весь процесс показан на рисунке ниже, где оранжевое поле указывает на то, что оно выполняется внутри InnoDB, а зеленое поле указывает на то, что оно выполняется в исполнителе:

3.5 Двухэтапная фиксация

Поскольку журнал повторов и бинлог — это две независимые логики, если двухфазная фиксация не используется, либо сначала запишите журнал повторов, а затем бинлог, либо используйте обратный порядок. Давайте посмотрим, что не так с этими двумя способами.

Все еще используя предыдущий оператор обновления в качестве примера. Предположим, что текущая строка с id=2, значение поля age равно 22, а затем предположим, что при выполнении оператора обновления после записи первого журнала происходит сбой, а второй журнал еще не был записан, что случится?

1. Сначала запишите журнал повторов, а затем binlog. Предположим, что процесс MySQL аварийно перезапускается, когда журнал повторов записывается, а двоичный журнал не был записан. Как мы говорили ранее, после записи журнала повторов, даже в случае сбоя системы, данные все равно можно восстановить, поэтому значение возраста в этой строке после восстановления равно 22.

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

Если вам нужно использовать этот бинлог для восстановления временной библиотеки, потому что бинлог этого оператора утерян, временная библиотека будет обновлена ​​без этого времени, а значение возраста в восстанавливаемой строке равно 22, что отличается от значения оригинальной библиотеки.

2. Сначала запишите бинлог, а затем повторите лог. Если происходит сбой после записи binlog, поскольку журнал повторов еще не был записан, транзакция недействительна после восстановления после сбоя, поэтому значение age равно 22. Но бинлог уже записал в лог "изменение с 22 на 23". Поэтому при использовании binlog для последующего восстановления будет еще одна транзакция, а значение age в восстанавливаемой строке равно 23, что отличается от значения исходной базы данных.

Поэтому, если "двухэтапная фиксация" не используется, состояние базы данных может не совпадать с состоянием, восстановленным с помощью binlog.

Кроме того:Параметр sync_binlog рекомендуется установить равным 1, что означает, что бинлог каждой транзакции сохраняется на диске, что может гарантировать, что бинлог не будет потерян после аварийного перезапуска MySQL..

3.6 Сценарии применения binlog

• Репликация ведущий-ведомый: откройте бинарный журнал на главной стороне, затем отправьте бинарный журнал на каждую подчиненную сторону, а подчиненная сторона воспроизведет бинарный журнал для достижения согласованности данных ведущий-ведомый.

• Восстановление данных: Восстановите данные с помощью инструмента mysqlbinlog.

04 Процесс восстановления данных

Как упоминалось ранее, бинлог записывает все логические операции в виде «добавления записи». Если ваш администратор базы данных обещает, что его можно будет восстановить в течение месяца, все бинлоги за последний месяц будут сохранены в системе резервного копирования, и система будет регулярно выполнять резервное копирование всей базы данных. «Регулярность» здесь зависит от важности системы, это может быть раз в день или раз в неделю.

Когда вам нужно выполнить восстановление до указанной секунды, например, в один из дней в 2 часа дня, вы обнаружили, что таблица была удалена по ошибке в 12 часов дня, и вам нужно получить данные, затем вы можете сделать следующее:

• Во-первых, найти самую последнюю полную резервную копию, если повезет, это может быть резервная копия прошлой ночи, и восстановить из этой резервной копии во временную базу данных;
• Затем, начиная с точки времени резервного копирования, по очереди извлекаются резервные бинлоги и воспроизводятся до момента, когда таблица была случайно удалена в полдень.

Таким образом, ваша временная база данных будет такой же, как онлайн-база данных до случайного удаления, а затем вы сможете извлечь табличные данные из временной базы данных и восстановить их в онлайн-базе данных по мере необходимости.

Увидев это, Фатти улыбнулся и посмотрел на отца.

плечи гигантов

• «Высокопроизводительный MySQL»
• zhihu.com/question/411272546/answer/1375199755
• zhihu.com/question/425750274/answer/1525436152
• time.geekbang.org/column/article/68633
• my.oschina.net/vivotech/blog/4289724
• hiddenpps.blog.csdn.net/article/details/108505371

05 Резюме

В этой статье объясняется несколько аспектов журнала транзакций (повторного выполнения): Зачем нужен журнал повторного выполнения? Его процесс записи, структура, что сохраняется, что такое аварийное сохранение и т. д.; кроме того, мы также говорили об определении binlog, формате журнала, отличии от журнала повторов, процессе выполнения оператора обновления, двухэтапном сценарии применения фиксации и binlog.

Ну, это сводка журнала MySQL Гоу Гэ. Спасибо за ваши усилия в техническом сообществе.Если я вижу дальше, то это потому, что я стою на ваших плечах. Я надеюсь, что эта статья будет полезна для вас, увидимся в следующей статье~

06 Отправьте несколько книг

Если вы видите здесь, как эта статья, пожалуйста, помогите найти красивый. WeChat поискJavaFish, подписывайтесь и отвечайтеэлектронная книгаОтправьте вам более 1000 электронных книг по программированию, включая C, C++, Java, Python, GO, Linux, Git, базы данных, шаблоны проектирования, внешний интерфейс, искусственный интеллект, интервью, структуры данных и алгоритмы, а также основы работы с компьютером. изображение ниже для деталей. Ответить1024Отправьте вам полный набор видеоуроков по Java.