Что за файлы mysql bin

Обновлено: 02.07.2024

У меня есть MM Replication в mysql, и я хочу сжать немного свободного места в коробке, удалив ненужные файлы, я наткнулся на эти mysql-bin файлы внутри. /var/db/mysql/ Существуют сотни таких файлов, как mysql-bin.000123 , mysql-bin.000223 и т. Д. Я проверил репликацию mysql, выполнив, show master status и show slave status они использование некоторых файлов mysql-bin в определенных позициях, но я предполагаю, что все остальные файлы bin являются остатками, которые больше не будут использоваться. В этом случае безопасно ли удалять все эти файлы mysql-bin, кроме тех, на которые в данный момент указывает репликация?

Если удалить безопасно, могу ли я что-нибудь сделать для автоматического удаления этих файлов, если они не используются?

Пожалуйста, не просто удаляйте их в ОС.

Вы должны позволить MySQL сделать это за вас. Вот как это делает mysqld:

Файл mysql-bin.[index] содержит список всех двоичных журналов, которые mysqld сгенерировал и автоматически повернул. Механизмы для очистки бункеров в сочетании с mysql-bin.[index] :

Они будут очищать все двоичные журналы до указанного вами временного журнала или метки времени.

Например, если вы запустите

это удалит все двоичные журналы раньше mysql-bin.000223 .

это удалит все двоичные журналы до полуночи 3 дня назад.

Если вы хотите, чтобы binlog вращался автоматически и оставалось 3 дня, просто установите это:

затем добавьте это к /etc/my.cnf

и MySQL удалит их журналы для вас

Это очень важно. Когда вы запустите SHOW SLAVE STATUS\G , вы увидите два двоичных журнала от мастера:

Master_Log_File
Relay_Master_Log_File

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

Таким образом, репликация не прерывается.

Обратите внимание на опечатку - подчеркивание, а не тире: [mysqld] expire_logs_days=3 (и вы должны включить [mysqld] раздел Это работает для меня. Вопрос, в чем разница между . mysql> SET GLOBAL expire_logs_days = 3; и expire-logs-days=3 в /etc/my.cnf .. Они одинаковы? Это избыточно или нет? Или важно запустить SET GLOBAL. потом добавить expire-logs-days=.. ? Благодарю. Быстро удалите все журналы, очевидно: PURGE BINARY LOGS BEFORE DATE(NOW()); почему нет нормальных значений по умолчанию для этого? Я нигде, никогда не изменял размер файла журнала до какого-то гигантского размера. У меня было 10,0 ГБ файлов журнала, после выполнения этой команды мой размер папки mysql.bin сократился до 1,6 ГБ.

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

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

Вы можете удалить двоичные журналы, которые вам не нужны, сначала посмотрев, какой самый старый журнал вы хотите сохранить:

1. Бинарный журнал двоичного журнала MySQL можно назвать наиболее важным журналом MySQL. Он записывает все операторы DDL и DML (за исключением оператора запроса данных select), записанные в виде событий, а также содержит время, затраченное оператором. Бинарный журнал безопасен для транзакций.

DDL - язык определения данных, язык определения базы данных

Основные команды: create, alter, drop и т. Д. Ddl в основном используется для определения или изменения структуры, типа данных, связи и ограничений между таблицами и других начальных задач. Большинство из них используется при создании таблиц.

DML ---- язык манипулирования данными

Основные команды: slect, update, insert, delete, как и его название, эти 4 команды являются языком, используемым для управления данными в базе данных.

2. Общие параметры mysqlbinlog следующие:

a, --start-datetime: прочитать из двоичного журнала указанное время, равное метке времени или позже, чем локальный компьютер
b. --stop-datetime: прочитать указанное время меньше метки времени или равно времени локального компьютера из двоичного журнала. Значение такое же, как указано выше.
c. --start-position: считывать указанную позицию события позиции из двоичного журнала как начало.
d, --stop-position: прочитать указанную позицию события позиции из двоичного журнала как конец события

3. Как правило, при включении binlog будет потеря производительности около 1%.

4. Есть два наиболее важных сценария использования binlog.

репликация mysql master-slave: репликация MySQL открывает binlog на стороне master, и master передает свой двоичный журнал подчиненным устройствам для достижения согласованности данных master-slave.
б) Восстановление данных: используйте инструмент mysqlbinlog для восстановления данных.

Журнал binlog включает два типа файлов:

1) Файл индекса двоичного журнала (суффикс имени файла .index) используется для записи всех двоичных файлов.

2) Двоичный файл журнала (суффикс имени файла .00000 *) записывает все события операторов DDL и DML (кроме оператора запроса данных select) в базе данных.

Два, формат двоичного журнала MySQL

Также существует три формата binlog: STATEMENT, ROW, MIXED.

1. Режим STATMENT: репликация на основе операторов SQL (репликация на основе операторов, SBR), каждый оператор SQL, который изменяет данные, будет записан в двоичный журнал.

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

Недостатки: в некоторых случаях данные в ведущем-ведомом будут несовместимы (например, функция sleep (), last_insert_id (), пользовательские функции (udf) и т. Д.)

2. Репликация на основе строк (RBR): вместо записи контекстной информации каждого оператора SQL необходимо только записать, какие данные были изменены и во что превратилось изменение. (Это режим версии 5.7)

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

Недостатки: будет создано большое количество журналов, особенно когда таблица изменений приводит к резкому увеличению журнала.

3. Смешанная репликация (MBR): два вышеуказанных режима являются смешанными. Общая репликация использует режим STATEMENT для сохранения binlog. Для операций, которые не могут быть реплицированы в режиме STATEMENT, используйте режим ROW для сохранения binlog. MySQL будет следовать за выполненным SQL. оператор выбирает метод сохранения журнала.

4, представлена версия 5.6: binlog_row_image

Параметр binlog_row_image является новым параметром MySQL 5.6. Значение по умолчанию - FULL. В версии 5.7 значение по умолчанию также FULL. Но сегодня я увидел, что шаблон параметра версии MySQL 5.7 клиента использует MINIMAL вместо FULL. модификация выразила сомнения. Вообще говоря, чтобы изменить значение параметра по умолчанию, мы должны четко учитывать масштаб влияния, поэтому я собираюсь провести тест и прийти к выводу, какое значение параметра является наилучшей настройкой. Формат binlog должен быть строковым или смешанным, а не форматом инструкции.
Расшифровка названия:
перед изображением: перед изображением, то есть перед изменением содержимого в таблице базы данных.
после изображения: после изображения, то есть измененного содержимого в таблице базы данных.

Три настройки, сходства и различия binlog_row_image

Параметр binlog_row_image может иметь три допустимых значения: FULL, MINIMAL, NOBLOB.
Функции трех разных значений следующие:

FULL: Log all columns in both the before image and the after image.
binlog регистрирует все переднее и заднее зеркала.

MINIMAL: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.
Переднее зеркало журнала binlog записывает только столбец уникальной идентификации (столбец уникального индекса, столбец первичного ключа), а заднее зеркало записывает только измененный столбец.

noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.
binlog записывает все столбцы, как и полный формат. Но для столбцов формата BLOB или TEXT, если это не единственный столбец идентификации (столбец уникального индекса, столбец первичного ключа) или он не был изменен, он не будет записан.

Три, конфигурация binlog

Добавьте следующий файл конфигурации в раздел mysqld файла конфигурации MySQL my.cnf:

Четыре часто используемых команды работы с журналом binlog

1. Проверьте, открыт ли журнал binlog (ВЫКЛ: закрыт, ВКЛ: открыт)

2. Просмотрите все расположения журналов binlog

3. Просмотреть весь список журналов binlog

4. Проверьте статус главного устройства, то есть номер имени последнего (последнего) журнала binlog и значение конечной точки pos (Position) последнего рабочего события.

5. Очистите журнал журнала, и с этого момента будет создан новый пронумерованный файл журнала binlog;

6. Сбросить (очистить) все журналы binlog

В-пятых, просмотрите содержимое журнала binlog

Есть два распространенных способа

1. Используйте mysqlbinlog для просмотра метода команды.

server id 1: служебный номер хоста базы данных

end_log_pos 796: узел pos в конце sql

thread_id = 11: номер потока

д. Его также можно просмотреть по моменту времени.

2. Вышеупомянутый метод считывает полный текст журнала binlog. Нелегко различить и просмотреть информацию о точках pos. Вот более удобная команда запроса:

a. IN'log_name ': укажите имя файла binlog для запроса (если не указано, это первый файл binlog)

б. FROM pos: укажите, с какой точки pos начинать (если не указано, она начинается с первой точки pos всего файла)

c. LIMIT [смещение]: смещение (0, если не указано)

d, row_count: запросить общее количество элементов (все строки не указаны)

2. Вышеупомянутый оператор может разделить указанный файл журнала binlog на допустимые строки событий и вернуть его, а также может использовать limit для указания начального смещения точки pos и запроса количества записей!

а. Запросите первый и самый ранний журнал бинарных журналов:

б. Укажите файл запроса mysql-bin.000002

c. Укажите запрос к файлу mysql-bin.000002, начиная с позиции pos: 624:

г. Задайте запрос к файлу mysql-bin.000002, начиная с точки pos: 624, и запросите 10 (т.е. 10 операторов)

д. Укажите файл запроса mysql-bin.000002, начиная с позиции pos: 624, смещение 2 строки (т.е. пропустите 2 в середине), запрос 10 (т.е. 10 предложений).

Шесть, используйте журнал binlog для восстановления данных mysql

1. Следующие операции выполняются с таблицей элементов библиотеки ops, и создается другая библиотека ops1.

Заранее вставьте два фрагмента данных:

2. Запустите моделирование сцены ниже:

A. Библиотека ops будет выполнять запланированное задание полного резервного копирования в четыре часа утра каждый день следующим образом:

б. Описание параметра:

-B: назначенная база данных

-F: обновить журнал

-R: резервное копирование хранимых процедур и т. Д.

-X: заблокировать таблицу

--Master-data: добавить оператор CHANGE MASTER, файл двоичного журнала и информацию о точке расположения в оператор резервного копирования. Эта опция записывает на выходе расположение и имя файла двоичного журнала. Для этой опции требуется разрешение RELOAD, и должно быть включено двоичное ведение журнала. Если значение параметра равно 1, расположение и имя файла записываются в вывод дампа в форме оператора CHANGE MASTER. Если вы используете этот главный сервер дампа SQL для установки подчиненного сервера, подчиненный сервер запускается с правильного расположение двоичного журнала главного сервера. Если значение параметра равно 2, оператор CHANGE MASTER записывается как комментарий SQL. Если значение не указано, это действие по умолчанию. --master-data = 2 - закомментировать строку журнала изменений, = 1 - без комментария.

Когда резервное копирование базы данных завершено, нет необходимости беспокоиться о потере данных, потому что есть данные полной резервной копии, потому что в приведенном выше примере используется параметр -F во время полного резервного копирования, а затем, когда операция резервного копирования данных только начинается. Система автоматически обновит журнал, так что новый журнал binlog будет автоматически создан.Этот новый журнал binlog будет использоваться для записи в базе данных «операций добавления, удаления и изменения» после резервного копирования.

Другими словами, mysql-bin.000003 используется для записи всех «добавлений, удалений и изменений» в базу данных после 4:00.

3. Я выхожу на работу в девять часов утра. В связи с потребностями бизнеса с базой данных будут выполняться различные операции "добавления, удаления и изменения".

Например: вставьте или измените данные в таблице элементов в библиотеке ops и библиотеке ops1:

Сначала вставьте данные утром:

4. В полдень измените работу базы данных:

5. В 18:00 дня по необъяснимым причинам произошла трагедия!

Shouqian выполнил оператор drop и напрямую удалил библиотеку ops1! Бояться писать!

Затем я создал базу данных ops2 и вставил данные

6. Не паникуйте сейчас .

Сначала внимательно проверьте последний журнал binlog и запишите ключевую точку позиции, операция которой вызвала повреждение базы данных (обычно на последних нескольких шагах);

а. Сначала создайте резервную копию последнего файла журнала binlog

б) Затем выполните операцию обновления индекса журнала, чтобы перезапустить новый файл журнала binlog. Само собой разумеется, что файл mysql-bin.000004 больше не будет записываться позже, потому что нам удобно проанализировать причину и найти узел ops.Все будущие операции с базой данных будут записаны в следующий файл журнала.

7. Метод чтения журнала binlog был упомянут выше.

А. Способ 1. Используйте mysqlbinlog для чтения журнала binlog:

б. Войдите на сервер и просмотрите (рекомендуется этот метод)

show binlog events in 'mysql-bin.000004'\G;

Согласно анализу, интервал pos, который вызвал разрушение данных ops библиотеки, находится между 3064-3153 (это вычисляется в соответствии с узлом pos интервала журнала), а интервал pos, который вызвал разрушение библиотеки ops1 библиотеки, находится между 3218 -3310. Просто восстановите его до соответствующей точки pos.

8. Сначала восстановите полностью подготовленные данные в 4 часа утра (рекомендуется создать новую библиотеку, а затем заменить текущую библиотеку после успешного восстановления)

Это восстановит данные резервной копии по состоянию на 4:00 утра.

Но это восстанавливает данные только до 4 утра того дня, а данные между 4: 00-18: 00 не восстанавливаются! ! Как это сделать? Не паникуйте! Это может быть восстановлено на основе нового журнала binlog mysql-bin.000004, упомянутого ранее.

9. Восстановить данные из журнала binlog

а. Формат синтаксиса команды восстановления:

б. Объяснение опций общих параметров:

--start-position = 875 начальная позиция
--stop-position = 954 конечная точка позиции
--start-datetime = "2016-9-25 22:01:08" время начала
--stop-datetime = "2019-9-25 22:09:46" Время окончания
--database = ops указывает, что восстанавливается только база данных ops (часто на хосте несколько баз данных, только локальные журналы журналов)

c. Нечасто используемые варианты:

-u --user = имя пользователя для подключения к удаленному хосту

-p --password [= имя] пароль для подключения к удаленному хосту

-h --host = name получить журнал binlog с удаленного хоста

--read-from-remote-server читать журнал binlog с сервера Mysql

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

д. Полное восстановление (вам нужно вручную отредактировать mysql-bin.000003 с помощью vim, чтобы удалить оператор drop) (этот метод не прошел проверку)

е. Укажите восстановление конечной точки pos (частичное восстановление):

(Из-за добавления --database = ops соответствующие операции с библиотекой ops1 в двоичном журнале не будут восстановлены. Если необходимо восстановить соответствующие операции библиотеки ops1, добавьте --database = ops1)

g. Восстановление заданного интервала положительных точек (частичное восстановление)

В ссылке f мы восстановились до момента перед удалением библиотеки. После удаления библиотеки мы также создали библиотеку ops2, создали таблицу элементов и добавили данные. На данный момент нам нужно пропустить удаление библиотеки и вернуться к созданию библиотека ops2. А момент создания таблицы элементов можно восстановить с помощью точки interval pos:

h. Элемент таблицы, созданный позже, восстанавливается, но библиотека ops1 удаляется, потому что есть операция по удалению библиотеки ops1 в середине. Если вы хотите продолжить восстановление данных, вставленных в следующую таблицу, вам нужно только использовать точка pos после создания таблицы.Точка начала для восстановления всех данных, кроме удаленной библиотеки. Взаимодействие с другими людьми

10. Дополнительно: Вы также можете указать интервал восстановления временного узла (частичное восстановление): Для восстановления по времени требуется, чтобы команда mysqlbinlog считывала содержимое журнала binlog и находила временной узел.

a,/application/mysql3306/bin/mysqlbinlog /application/mysql3306/mysql_data/mysql-bin.000002

Вы можете видеть, что две точки времени под каждым красным прямоугольником на рисунке - это начальное событие и время окончания события соответственно.

в. Основной принцип аналогичен принципу восстановления через точку поз.

Так называемое восстановление состоит в том, чтобы позволить mysql повторно выполнять операторы SQL, хранящиеся в указанном интервале абзаца в журнале binlog, один за другим.

Неожиданно на одном из серверов закончилось место. Разбираясь в чём дело я выяснил что MySQL забил всё место файликами mysql-bin.xxxxxx. Всё из-за того что я изначально не правильно настроил логирование и забил на это забыл про это.

За долгое время работы бинарные логи в Mysql становятся просто огромные, и могут значительно превышать объем самой базы данных. Файлы вида mysql-bin.xxxxx представляют из себя бинарные логи со всеми запросами к БД. Они нужны для репликации данных или восстановления информации в случае сбоя.

Данная инструкция по правильной настройке и удалению логов MySQL — она подойдет как для Windows, так и Linux, например CentOS. Так как MariaDB — продолжает традиции MySQL, инструкция подойдёт и для нее.

Безопасно удаляем логи mysql-bin.xxx в MariaDB

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

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

Прописываем expire_logs_days=14 в конфиге MySQL. Скорей всего это файлик /etc/my.cnf.
и перезапускаем сервер mysql.

Всё! Этого будет вполне достаточно. После перезапуска MySQL или MariaDB должны сами почистить лишние логи. Но если этого не произошло, то можно вручную удалить их через консоль MySQL. Для этого можно воспользоваться вот такой командой:

В итоге мы будем иметь постоянно только логи за последние 14 дней. Эту цифру вы можете подобрать для себя в зависимости от критичности данных в БД и размера диска на сервере.

Отключить бинарные логи MySQL

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

Если данные на вашем сервере представляют некую ценность, то будет разумно обеспечить условия для возможности восстановления данных в случае их повреждения.

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

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

Конфигурация

Активируем бинарные логи в секции [mysqld] файла /etc/my.cnf (/etc/mysql/my.cnf)

После перезагрузки mysql вы увидите в /var/lib/mysql файл mysqld-bin.00001

Восстановление базы

или непосредственно в MySQL

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

Можно указать файл журнала для вывода в параметрах утилиты. Например:

В данном случае будет обработан файл mysql-bin.000012 (из текущей директории), вывод оправится в out.sql, будут выведены только команды, относящиеся к изменению базы с именем db_name. Параметром -s мы запретили вывод дополнительной служебной информации.

-s запрещаем вывод дополнительной служебной информации.

Здесь мы ограничиваем вывод запросов, которые выполнялись пользователем USER_BD начиная с указанной даты. Параметр -t сообщает утилите, что нужно обрабатывать и логи, которые идут после файла mysql-bin.000001.

Обратите внимание, что если вы будете перенаправлять вывод сразу в MySQL, то в журнал обновлений добавятся свежие записи и произойдет зацикливание.

Для предотвращения добавим еще и параметр -D, который запрещает ведение лога. Этот запрет будет доступен только если выполнять команду из под рута.

Теперь можно загрузить в базу восстановленные из журнала данные.

Удаление файлов журнала

Если у вас проблемы с дисковым пространством и файлы логов надо будет срочно удалить…

Различные варианты удаления:

Удалить логи до mysql-bin.010

Удалить логи старше 3-х дней

Удалить все логи

Если нужно изменить параметр срока хранения логов без перезагрузки сервера:

Читайте также: