mysql что такое binlog

Логирование в MySQL

Журналы событий — первый и самый простой инструмент для определения статуса системы и выявления ошибок. Основных логов в MySQL четыре:

Лог ошибок

Этот журнал содержит все ошибки, которые произошли во время работы сервера, включая критические ошибки, а также остановки, включения сервера и предупреждения (warnings). С него нужно начать в случае сбоя системы. По умолчанию все ошибки выводятся в консоль (stderr), также можно записывать ошибки в syslog (по умолчанию в Debian) или отдельный лог-файл:

log_error=/var/log/mysql/mysql_error.log
Ошибки будут писаться в mysql_error.log

Рекомендуем держать этот журнал включенным для быстрого определения ошибок. А для понимания, что значит та или иная ошибка, в MySQL присутствует утилита [http://dev.mysql.com/doc/refman/5.7/en/perror.html perror]:

Объясняет значения кодов ошибок

Бинарный (он же двоичный) лог

В бинарный лог записываются все команды изменения базы данных, пригодится для репликации и восстановления.

Указывает расположение, срок жизни и максимальный размер файла

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

Лог запросов

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

Включает лог и указывает расположение файла

Также его можно включить/отключить во время работы сервера MySQL:

Для применения не нужно перезагружать сервер

Лог медленных запросов

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

Просмотр логов

Для просмотра логов на Debian (Ubuntu) нужно выполнить:

Если логи не указаны отдельно, то находятся в /var/lib/mysql

Ротация логов

Не забывайте сжимать (архивировать, ротировать) файлы логов, чтобы они занимали меньше места на сервере. Для этого используйте утилиту logrotate, отредактировав файл конфигурации /etc/logrotate.d/mysql-server :

Сжимает и архивирует нужные логи, очищает файлы

DDL Log

MySQL также ведет лог языка описания данных. В него собираются данные операций типа DROP_TABLE and ALTER_TABLE. Лог используется для восстановления после сбоев, которые произошли во время выполнения таких операций. [http://dev.mysql.com/doc/refman/5.7/en/ddl-log.html DDL Log] — бинарный файл, не предназначенный для чтения пользователем, поэтому не модифицируйте и не удаляйте его.

Самое главное

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

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

Что такое индексы в Mysql и как их использовать для оптимизации запросов

Как исправить ошибку доступа к базе 1045 Access denied for user

Примеры ad-hoc запросов и технологии для их исполнения

Основные понятия о шардинге и репликации

Настройка Master-Master репликации на MySQL за 6 шагов

Как создать и использовать составной индекс в Mysql

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

Check-unused-keys для определения неиспользуемых индексов в базе данных

Синтаксис и оптимизация Mysql LIMIT

Настройка Master-Slave репликации на MySQL за 6 простых шагов

Типы и способы применения репликации на примере MySQL

Запрос для определения версии Mysql: SELECT version()

Правильная настройка Mysql под нагрузки и не только. Обновлено.

3 примера установки индексов в JOIN запросах

Быстрый подсчет уникальных значений за разные периоды времени

И как правильно работать с длительными соединениями в MySQL

Анализ медленных запросов с помощью EXPLAIN

Описание, рекомендации и значение параметра query_cache_size

Что значит и как это починить

Правила выбора типов данных для максимальной производительности в Mysql

Использование партиций для ускорения сложных удалений

Источник

Журналы (logs) в MySQL

В MySQL на данный момент существуют 4 вида журнала (лога) и при достаточно серьёзной работе с базами на MySQL необходимо за ними следить. Например, бинарный лог у нас за сутки набирает около гигабайта, а размер жёсткого диска на сервере ограничен и за ними надо следить. Однако следить следует не только за бинарным логом, так как логи (журналы) в MySQL могут принести немалую пользу.

Итак, какие логи ведёт MySQL? Это:
1. бинарный лог (binary log)
2. лог ошибок (error log)
3. лог медленный запросов (slow query log)
4. лог запросов (general query log)
5. лог репликаций (relay log)

Каждый из них по-своему полезен.

Бинарный лог

Содержание бинарного лога можно посмотреть с помощью утилиты mysqlbinlog.

Основные настройки в my.cnf

Местоположение лога:
log_bin = /var/log/mysql/mysql-bin.log

Максимальный размер, минимум 4096 байт, по умолчанию 1073741824 байт (1 гигабайт):
max_binlog_size= 500M

Сколько дней хранится:
expire_logs_days = 3

Наиболее часто использующиеся команды

Удаление логов до определённого файла:
PURGE BINARY LOGS TO ‘mysql-bin.000’;

Удаление логов до определённой даты:
PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD hh:mm:ss’;

Лог ошибок

Особенно полезен в случаях сбоев. Лог содержит информацию об остановках, запусках сервера, а также сообщения о критических ошибках. Может содержать сообщения с предупреждениями (warnings).

Основные настройки в my.cnf

Местоположение лога:
log_error = /var/log/mysql/mysql.err

Флаг, указывающий стоит ли записывать в лог в том числе предупреждения (записываются, если значение больше нуля):
log_warnings = 1

Наиболее часто использующиеся команды

Переход к новому файл лога:
shell> mysqladmin flush-logs

Копирование старой части лога (необходимо, так как в случае повторного выполнения fluch он будет удалён):
shell> mv host_name.err-old backup-directory

Лог медленных запросов

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

Основные настройки в my.cnf

Местоположение лога:
log_slow_queries = /var/log/mysql/mysql_slow.log

Со скольки секунд выполнения запрос считается медленным, минимальное значений — 1 секунда, по умолчанию 10 секунд:
long_query_time = 10

Если надо логировать запросы, которые не используют индексы, надо добавить строку:
log-queries-not-using-indexes

Лог запросов

Лог содержит информацию о подключениях и отключениях клиентов, а также все SQL запросы, которые были получены. Фактически, это временный лог. Обычно лог удаляется автоматически сразу после выполнения всех команд (т.е. как только он стал ненужным). Лог ведётся в соответствии с очередность поступления запросов. Этот лог содержит все запросы к базе данных (независимо от приложений и пользователей). Так что если есть желание (или необходимость) проанализировать, какие необходимы индексы, какие запросы могли бы оптимизированы, то этот лог как раз может помочь в таких целях. Лог полезен не только для случаев, когда необходимо знать, какие запросы выполняются с базой данных, но и в случаях, когда ясно, что возникла ошибка с базой данных, но неизвестно, какой запрос был отправлен к базе данных (например, в случае генерации динамического SQL-а). Рекомендуется защищать лог запросов паролем, так как он может данные также о паролях пользователей.

Основные настройки в my.cnf

Местоположение лога:
log = /var/log/mysql/mysql.log

Наиболее часто использующиеся команды

В отличии от других логов, перезагрузка сервера и команда fluch не инициирует создание нового лога. Но это можно сделать вручную:
shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

Лог репликаций

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

Основные настройки в my.cnf

Местоположение лога:
relay-log = /var/log/mysql/mysql-relay-bin.log

Максимальный размер:
max_relay_log_size = 500М

Наиболее часто использующиеся команды

Начать новый файл лога можно только при остановленном дополнительном (slave) сервере:
shell> cat new_relay_log_name.index >> old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index

Команда fluch logs инициирует ротацию лога.

Источник

15 примеров команды mysqlbinlog для двоичных файлов в MySQL

Главное меню » Базы данных » База данных MySQL » 15 примеров команды mysqlbinlog для двоичных файлов в MySQL

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

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

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

Команда mysqlbinlog используется для просмотра содержимого двоичного журнала в удобном для чтения формате. Вы также будете использовать команду mysqlbinlog для чтения содержимого и подключения его к другим утилитам mysql.

В этом уроке мы обсудим следующие примеры, используя команду mysqlbinlog:

1. Получить список текущих двоичных журналов

Из mysql выполните следующую команду show binary logs, которая отобразит все бинарные журналы в вашей системе.

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

По умолчанию файлы двоичного журнала находятся в каталоге /var/lib/mysql, как показано ниже.

2. Поведение по умолчанию Mysqlbinlog

Далее будет отображаться содержимое указанного бинарного файла журнала mysql (например: mysqld-bin.000001) в удобном для пользователя формате.

Ниже приводится частичный вывод указанной выше команды:

Вышеприведенная команда отображает события, которые произошли во всей базе данных в этой системе.

3. Получить записи для конкретной базы данных

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

Следующая команда сбрасывает все события, принадлежащие базе данных «crm», в файл crm-events.txt

4. Отключите двоичный журнал для восстановления.

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

Этот параметр также будет полезен, если вы используете опцию «–to-last-log». Кроме того, имейте в виду, что вам нужна привилегия root для выполнения этой команды.

Для резервного копирования и восстановления вы обычно используете команду mysqldump, но иногда в ситуации, когда вам приходится восстанавливаться после сбоя, полезно использовать mysqlbinlog.

5. Управление base-64 в выводе BINLOG

Используя опцию base64-output, вы можете управлять поведением, когда оператором вывода должны быть операторы BINLOG, закодированные в base64.

Ниже приведены возможные значения для base64-output:

never: Когда вы укажете «never», как показано ниже, будут выводиться операторы BINLOG с кодировкой base64 в выходном файле.

т.е. когда вы используете «never», в выводе команды mysqlbinlog вы не будете использовать строки, похожие на следующие, которые имеют BINLOG с кодировкой base64.

Обратите внимание, что вышеупомянутая опция «never» для вывода base64 будет работать в двоичных файлах журнала, если они не содержат события на основе строк.

always: когда вы укажете опцию «always», отображать только записи BINLOG, когда это возможно. Таким образом, используйте это только тогда, когда вы специально отлаживаете некоторые проблемы.

Ниже приведен вывод выше с помощью «always», который показывает только записи BINLOG.

auto: Это опция по умолчанию. Если вы не укажете опцию base64-decode, она будет использовать auto. В этом случае mysqlbinlog будет печатать записи BINLOG только для определенных типов событий, таких как события на основе строк и события описания формата.

Оба следующих утверждения точно совпадают.

6. Отладочная информация в выводе mysqlbinlog.

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

В следующем параметре debug-info будет отображаться дополнительная опция отладки после завершения обработки данного файла двоичного журнала, как показано ниже.

7. Пропустить первые N количество записей

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

Ниже перечислены первые 10 записей в указанном журнале bin mysql.

Чтобы убедиться, что это работает должным образом, дайте номер события для смещения, и вы не увидите никаких записей. Следующий пример пропустит первые 10 000 записей (событий) из журнала.

В этом примере, поскольку этот конкретный файл журнала, нет 10 000 записей, он не указывает на какие-либо события базы данных на выходе.

8. Сохранить вывод в файл

Вы можете использовать простую команду перенаправления Linux > и сохранить вывод в файл, как показано ниже.

9. Извлечь записи, начиная с определенного положения

Обычно в бинарном файле mysql вы увидите номера позиций, как показано ниже. Ниже представлен частичный вывод mysqlbinlog, где вы видите «15028» – номер позиции.

Следующая команда начнет считывать записи двоичного журнала с номером позиции 15028.

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

10. Извлечь записи до определенного положения

Как и в предыдущем примере, вы также можете читать записи из двоичного журнала mysql до определенной позиции, как показано ниже.

Вышеприведенный пример остановится из binlog точно в позиции 15028. Когда вы укажете несколько файлов двоичного журнала в командной строке, позиция остановки будет применяться только к последнему двоичному журналу в данном списке.

11. Сбросить журналы для чистого вывода Binlog

Когда двоичный журнал не был правильно закрыт, на выходе вы увидите предупреждающее сообщение, как показано ниже.

Как вы видите здесь, он говорит, что файл binlog не был закрыт должным образом.

Когда вы это увидите, подключитесь к mysql и очистите журналы, как показано ниже.

После того, как вы очистите журналы и снова выполните команду mysqlbinlog, вы не увидите, что binlog не закрыл должным образом предупреждающее сообщение в вашем выводе mysqlbinlog.

12. Отображать только SQL-запросы в выводе

По умолчанию, как вы видите в выводе предыдущих примеров, помимо SQL-запросов, вы также увидите некоторую дополнительную информацию в выводе mysqlbinlog.

Ниже представлен частичный вывод вышеуказанной команды. Как вы видите здесь, он отображает только SQL-запросы из данного файла двоичного журнала.

В короткой форме вы не увидите следующее:

13. Просмотр записей, начиная с определенного времени

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

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

Формат метки времени может быть любым, что понимается типами DATETIME и TIMESTAMP сервера MYSQL. Итак, у вас здесь много гибкости.

14. Просмотр записей до определенного времени

Как и в предыдущем примере времени начала, вы также можете указать время остановки, как показано ниже.

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

15. Получить двоичный журнал с удаленного сервера

С вашего локального компьютера вы также можете прочитать бинарные журналы mysql, расположенные на удаленном сервере.

Для этого вам необходимо указать IP-адрес, имя пользователя и пароль для удаленного сервера, как описано ниже.

В приведенном выше:

Следующая команда точно такая же, как приведенная выше команда:

Ниже приводится частичный вывод указанной выше команды:

Если у вас недостаточно прав для удаленной базы данных, вы получите сообщение об ошибке “is not allowed to connect”. В этом случае убедитесь, что вы предоставили правильные привилегии в удаленной базе данных для своего локального клиента (т. е. когда запущена команда mysqlbinlog)

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Источник

Mysql что такое binlog

Events are preceded by header comments that provide additional information. For example:

In the first line, the number following at indicates the file offset, or starting position, of the event in the binary log file.

The second line starts with a date and time indicating when the statement started on the server where the event originated. For replication, this timestamp is propagated to replica servers. server id is the server_id value of the server where the event originated. end_log_pos indicates where the next event starts (that is, it is the end position of the current event + 1). thread_id indicates which thread executed the event. exec_time is the time spent executing the event, on a replication source server. On a replica, it is the difference of the end execution time on the replica minus the beginning execution time on the source. The difference serves as an indicator of how much replication lags behind the source. error_code indicates the result from executing the event. Zero means that no error occurred.

When using event groups, the file offsets of events may be grouped together and the comments of events may be grouped together. Do not mistake these grouped events for blank file offsets.

mysqlbinlog ‘s own connection compression does less if transaction payloads are already compressed, but still operates on uncompressed transactions and headers.

For more information on binary log transaction compression, see Section 5.4.4.5, “Binary Log Transaction Compression”.

Table 4.22 mysqlbinlog Options

Display a help message and exit.

This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case-sensitive):

On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.

Specify the maximum size of a row-based binary log event, in bytes. Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256. The default is 4GB.

The directory where character sets are installed. See Section 10.15, “Character Set Configuration”.

Compress all information sent between the client and the server if possible. See Section 4.2.8, “Connection Compression Control”.

This option was added in MySQL 8.0.17. As of MySQL 8.0.18 it is deprecated. Expect it to be removed in a future version of MySQL. See Configuring Legacy Connection Compression.

This option was added in MySQL 8.0.18.

While db_name is the default database, statements are output whether they modify tables in db_name or a different database.

Suppose that the binary log was created by executing these statements using statement-based-logging:

Print some debugging information when the program exits.

Print debugging information and memory and CPU usage statistics when the program exits.

A hint about which client-side authentication plugin to use. See Section 6.2.17, “Pluggable Authentication”.

Read this option file after the global option file but (on Unix) before the user option file. If the file does not exist or is otherwise inaccessible, an error occurs. If file_name is not an absolute path name, it is interpreted relative to the current directory.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

Use only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. If file_name is not an absolute path name, it is interpreted relative to the current directory.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

Read binary log files even if they are open or were not closed properly.

Request from the server the public key required for RSA key pair-based password exchange. This option applies to clients that authenticate with the caching_sha2_password authentication plugin. For that plugin, the server does not send the public key unless requested. This option is ignored for accounts that do not authenticate with that plugin. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

For information about the caching_sha2_password plugin, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.

Display a hex dump of the log in comments, as described in Section 4.6.9.1, “mysqlbinlog Hex Dump Format”. The hex output can be helpful for replication debugging.

Get the binary log from the MySQL server on the given host.

Tell the MySQL Server to use idempotent mode while processing updates; this causes suppression of any duplicate-key or key-not-found errors that the server encounters in the current session while processing updates. This option may prove useful whenever it is desirable or necessary to replay one or more binary logs to a MySQL Server which may not contain all of the data to which the logs refer.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

Skip the first N entries in the log.

Specify the number of open file descriptors to reserve.

Specifying a password on the command line should be considered insecure. To avoid giving the password on the command line, use an option file. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

The TCP/IP port number to use for connecting to a remote server.

Print the program name and all options that it gets from option files.

For additional information about this and other option-file options, see Section 4.2.2.3, “Command-Line Options that Affect Option-File Handling”.

The transport protocol to use for connecting to the server. It is useful when the other connection parameters normally result in use of a protocol other than the one you want. For details on the permissible values, see Section 4.2.7, “Connection Transport Protocols”.

The REPLICATION SLAVE privilege is required to use these options.

Read the binary log from a MySQL server rather than reading a local log file. This option requires that the remote server be running. It works only for binary log files on the remote server, not relay log files.

The REPLICATION SLAVE privilege is required to use this option.

This option was added in MySQL 8.0.19.

Statements in which table names are qualified with database names are not rewritten to use the new name when using this option.

To employ multiple rewrite rules, specify the option multiple times, as shown here:

Display only those events created by the server having the given server ID.

The path name to a file in PEM format containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. This option applies to clients that authenticate with the sha256_password or caching_sha2_password authentication plugin. This option is ignored for accounts that do not authenticate with one of those plugins. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

This option applies only if the server was started with the shared_memory system variable enabled to support shared-memory connections.

Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems. It is deprecated, and you should expect it to be removed in a future release.

Do not display any GTIDs in the output. This is needed when writing to a dump file from one or more binary logs containing GTIDs, as shown in this example:

The use of this option is otherwise not normally recommended in production.

On Windows, this option applies only if the server was started with the named_pipe system variable enabled to support named-pipe connections. In addition, the user making the connection must be a member of the Windows group specified by the named_pipe_full_access_group system variable.

OFF : Disable FIPS mode.

ON : Enable FIPS mode.

STRICT : Enable “ strict ” FIPS mode.

The permissible ciphersuites for encrypted connections that use TLSv1.3. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.3.2, “Encrypted Connection TLS Protocols and Ciphers”.

This option was added in MySQL 8.0.16.

The permissible TLS protocols for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.3.2, “Encrypted Connection TLS Protocols and Ciphers”.

The user name of the MySQL account to use when connecting to a remote server.

Verify checksums in binary log files.

Display version information and exit.

The compression level to use for connections to the server that use the zstd compression algorithm. The permitted levels are from 1 to 22, with larger values indicating increasing levels of compression. The default zstd compression level is 3. The compression level setting has no effect on connections that do not use zstd compression.

This option was added in MySQL 8.0.18.

Processing multiple files. If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe :

Another approach is to write all the logs to a single file and then process the file:

You can specify more than one archive file, for example:

Источник