English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Все, кто работал с крупными системами, знают, что роль логов не следует недооценивать. Часто в后期 проекта оптимизация и обновление происходят на основе решений, основанных на логах. Поэтому изучение MySQL, конечно же, не может пропустить这部分. Все оптимизации, о которых мы говорим на собеседовании, должны быть получены из логов. Систематическое изучение логов MySQL помогает нам точно определять проблемы и повышать уровень своей работы. Кроме того, последующие日志 будут着重从 точки зрения администратора базы данных, систематически понимать конфигурации MySQL с точки зрения 'знай своих врагов и знай себя', чтобы сделать MySQL под рукой своей базы данных.
1. Типы логов MySQL
По умолчанию, все логи MySQL хранятся в виде файлов в корневой директории базы данных:
[root@roverliang data]# pwd /usr/local/webserver/extend_lib/mysql/data [root@roverliang data]# ls auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test
Типы журналов MySQL следующие:
1. Журнал ошибок (error), информация о запуске, работе или остановке сервера MySQL-инстанции.
2. Общий запрос журнала (general), все SQL-запросы или команды, выполняемые сервером MySQL-инстанции.
3. Бинарный журнал (binary), все обновляющие операции в базе данных, не включая select и show.
4. Журнал медленных запросов (slow), SQL-запросы, выполняемые дольше значения long_query_time, или запросы, не использующие индекс.
Два. Кэширование журналов MySQL
В высокоскоростной, стабильной и надежной системе кэш играет至关重要的角色. Обработка журналов MySQL также использует кэширование. Оригинальные журналы MySQL хранятся в памяти сервера MySQL, и если их объем превышает заданный размер, журналы пишутся (или обновляются) на внешнее хранилище, чтобы永远 сохраняться на жестком диске в виде базы данных или файла.
Три. Журнал ошибок MySQL (error log)
Журнал ошибок MySQL主要负责记录MySQL-инстанция每次启动、остановки и подробную информацию о предупреждениях или ошибках, возникающих в процессе работы инстанции. В отличие от других журналов, журнал ошибок MySQL должен быть включен и не может быть отключен.
По умолчанию, имя файла журнала ошибок: имя хоста.err. Но журнал ошибок не записывает все ошибки, только критические ошибки, возникающие в процессе работы сервера MySQL-инстанции.
mysql> show variables like 'log_error'\G *************************** 1. строка *************************** Имя переменной: log_error Значение: /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 строка в наборе (0.02 сек)
Четыре. Общий запрос журнала (general log)
MySQL общие запросы журнала записывают все действия сервера MySQL-инстанции, такие как select, update, insert, delete и т.д., независимо от того, успешно ли выполнен запрос. Также включается информация о подключении и разрыве соединения клиента MySQL с сервером MySQL, независимо от того, было ли подключение успешным или нет. С общим запросом журнала связаны три параметра.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.01 sec)
Обычный лог запросов можно включить, используя команду set @@global.general_log = 1;
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+
Но изменения переменных MySQL этим способом будут действовать только в текущем экземпляре MySQL, после перезагрузки MySQL они будут восстановлены до mặc định. Для постоянного действия измените файл my.cnf mysql, добавив в файл конфигурации:
general_log = 1
general_log_file
Как только обычный лог запросов включен, экземпляр службы MySQL автоматически создает файл обычного лога запросов, параметр general_log_file устанавливает физическое местоположение файла обычного лога запросов. Например:
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
Внимание: поскольку обычный лог запросов почти записывает все операции MySQL, для серверов базы данных с частыми данными доступ, если включить обычный лог запросов MySQL, это значительно снизит производительность базы данных, поэтому рекомендуется отключить обычный лог запросов. Только в особых случаях, например, когда необходимо временно открыть обычный лог запросов для отслеживания некоторых специальных логов запросов.
log_output
Параметр log_output устанавливает, где хранится контент обычных и медленных логов запросов в таблицах базы данных. Используя команду set @@global.log_output='table', можно сохранить обычные и медленные логи запросов в таблицах general и slow_log системы базы данных MySQL. Важно отметить, что这两个表的 хранилище данных осуществляется с помощью CSV, поэтому при просмотре новых данных обычного лога запросов можно использовать SQL-запросы;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
V. Журнал медленных запросов MySQL (slow log)
Вопросы, связанные с журналом медленных запросов MySQL, очень ценятся на собеседованиях. Раньше можно было только теоретически рассуждать о архитектуре master-slave MySQL и оптимизации MySQL с различных сторон, но на самом деле не было真正 понимания, как включать и configuring медленный запрос.
Использование журнала медленных запросов MySQL позволяет эффективно отслеживать запросы с длительным выполнением или запросы, не использующие индексы. Это включает в себя запросы SELECT, UPDATE, DELETE и INSERT, что помогает оптимизировать запросы. Отличие от обычного журнала запросов заключается в том, что журнал медленных запросов содержит только успешно выполненные запросы. С параметрами, связанными с журналом медленных запросов MySQL, есть 5.
1. slow_query_log
Параметр slow_query_log устанавливает, включен ли журнал медленных запросов.
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+
2. slow_query_log_file
Если журнал медленных запросов включен, то экземпляр MySQL автоматически создает файл журнала медленных запросов. Файл, указанный в slowquerylog_file, хранит содержимое журнала медленных запросов. Метод изменения аналогичен приведенному выше.直 接编辑 файл my.cnf.
3. long_query_time
Параметр long_query_time устанавливает порог времени медленных запросов. По умолчанию порог составляет 10 секунд.
4. log_quries_not_using_indexes
Запись запросов, не использующих индексы, в журнал медленных запросов, независимо от скорости выполнения запроса.
mysql> set @@global.log_queries_not_using_indexes=1; mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+
5、log_output
设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;
六、MySQL慢查询日志查看
log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;
mysql> desc mysql.slow_log; +----------------+---------------------+ | Field | Type | +----------------+---------------------+ | start_time | timestamp | | user_host | mediumtext | | query_time | time | | lock_time | time | | rows_sent | int(11) | | rows_examined | int(11) | | db | varchar(512) | | last_insert_id | int(11) | | insert_id | int(11) | | server_id | int(10) unsigned | | sql_text | mediumtext | | thread_id | bigint(21) unsigned | +----------------+---------------------+
其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。
Но использование TABLE для хранения логов медленных запросов не очень распространено, при больших объемах работы это может повлиять на основную службу системы. Мы можем использовать способ сохранения FILE. При установке MySQL в каталоге bin MySQL уже по умолчанию установлена утилита mysqldumpslow.pl для анализа логов медленных запросов. В Windows использование этой утилиты может потребовать настройки, что не входит в диапазон этой статьи, изучайте системы служб, перейдите на Linux. В Linux команды и инструменты можно использовать с помощью команды herself + --help для просмотра документации.
-s указывает способ сортировки
Подопции: c, t, l, r
c: количество выполнений SQL
t: время выполнения
l: время ожидания блокировки
r: количество данных
at, al, ar - это平均值 для t, l, r. -t: означает возврат первых N записей.
-g: сокращение grep. Включает неопределенное соответствие
Обычные методы использования:
//Возврат 20 SQL-запросов с наибольшим количеством обращений ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Возврат 20 SQL-запросов с наибольшим количеством return ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Возврат SQL-запросов, содержащих like ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
Седьмая часть: Бинарные логи (binary)
Бинарные логи отличаются от упомянутых ранее нескольких видов логов, их нельзя напрямую просматривать с помощью cat или less текстового просмотра. Нужны специализированные инструменты. Основная функция бинарных логов - это регистрация изменений в базе данных, поэтому они могут использоваться для синхронизации между основной и резервной базами данных. Содержание включает все операции обновления базы данных, такие как use, insert, delete, update, create, alter, drop. Кратко и понятно можно概括 так: все операции, связанные с изменением данных, должны быть записаны в бинарные логи.
Запуск двоичного журнала Используйте show variables like 'log_bin'\G, чтобы проверить включен ли двоичный журнал.
mysql> show variables like 'log_bin'\G
*************************** 1. строка ***************************
Имя переменной: log_bin
Значение: ОТКЛЮЧЕН
1 строка в наборе (0.00 с)
mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Переменная 'log_bin' является только для чтения
mysql>
Увидеть, что log_bin по умолчанию не включен и является только для чтения переменной, которую нужно настроить в my.cnf, затем перезагрузить MySQL. service mysql restart После перезагрузки MySQL в директории data будет создан файл 1.000001. Фактически, каждый раз при перезагрузке MySQL, в директории будет создан такой файл, и имена файлов будут увеличиваться по порядку. Кроме того, MySQL также создаст индексный файл двоичного журнала в этой директории, который можно проверить командой show variables like 'log_bin_index'\G, а затем использовать команду cat, чтобы увидеть. Внутри будет записано относительное положение двоичных файлов.
Просмотр двоичного журнала можно выполнить с помощью встроенных инструментов MySQL. Конкретное местоположение в директории bin mysql. Часто используемые параметры команды mysqlbinlog:
-s Показать содержимое журнала в сжатом виде
-v Показать содержимое журнала в детализированном виде
-d=имя_базы_данных Показать содержимое журнала только для указанной базы данных
-o=n Пропустить первые n строк MySQL команд в журнале
-r=file Записать указанное содержимое в указанный файл
--start-datetime
Показать содержимое журнала в указанном интервале времени
--stop-datetime
--start-position
Показать содержимое журнала в указанном интервале позиций
--stop-position
Получить текущий используемый файл лога двоичного журнала
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | Файл | Позиция | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 строка в наборе (0.00 с)
Использование двоичных логов для восстановления данных
Синтаксис очень прост:
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
После mysqlbinlog можно добавить --start-datetime, --stop-datetime, start-position, stop-position и другие параметры.
--start-datetime и --stop-datetime эти параметры позволяют восстановить данные на основе точки времени;
start-position и stop-position позволяют выполнить более детализированную операцию точки данных для восстановления данных;
MySQL двоичные логи и параметры
mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ВКЛЮЧЕН | | binlog_row_image | ПОЛНЫЙ | | binlog_rows_query_log_events | ОТКЛЮЧЕН | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | ИГНОРОВАТЬ ОШИБКУ | | innodb_api_enable_binlog | ОТКЛЮЧЕН | | innodb_locks_unsafe_for_binlog | ОТКЛЮЧЕН | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | ОТКЛЮЧЕН | | sync_binlog | 0 | +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize размер одного двоичного журнала. Если размер превышает это значение, создается новый файл с суффиксом +1;
binlog_cache_size
binlogcachesize размер кэша двоичных журналов в памяти
sync_binlog
sync_binlog записывает несколько двоичных журналов в кэше, а затем начинает синхронизировать их обновление на внешнее хранилище (жесткий диск).
log_slave_updates
logslvaeupdates для репликации主-слейва
Очистка двоичных журналов
В принципе, сначала необходимо выполнить физическое резервное копирование готовых к очистке журналов на другое хранилище, чтобы их можно было хранить постоянно. Затем рекомендуется использовать два следующих метода очистки с низким уровнем риска:
第一种：
purge master logs before '2017-02-16 00:00:00';
Второй вариант:
Прямо в конфигурационном файле MySQL my.cnf установите параметр expire_logs_days, чтобы установить количество дней до истечения срока действия двоичных файлов, после истечения срока действия двоичные файлы будут автоматически удалены. Рекомендуется запустить другую периодическую задачу перед удалением, чтобы регулярно выполнять резервное копирование двоичных файлов. Чтобы избежать ситуации, когда через несколько дней после обнаружения ошибки двоичные файлы автоматически удаляются.
expire_logs_days=90
Восьмой раздел, лог транзакций InnoDB
Логи транзакций InnoDB отличаются от упомянутых выше логов, логи транзакций InnoDB поддерживаются самим двигателем хранения InnoDB и их содержимое не может быть читаемо администратором базы данных. MySQL использует кэш как можно эффективнее, чтобы повысить эффективность доступа к данным. Таким образом, любая высокопроизводительная система должна использовать кэш, с различных точек зрения, кэш играет огромную роль. Поднимемся на один уровень выше и обобщим: кэш и очередь - это обязательный путь к высокопроизводительности. Для базы данных это, тем не менее, сложная проблема, чтобы обеспечить более эффективное чтение и хранение данных, необходимо использовать кэш. Но чтобы обеспечить一致性 данных, необходимо обеспечить, чтобы все данные были точно и безошибочно записаны в базу данных, даже если произойдет инцидент, также необходимо обеспечить возможность восстановления данных. Мы знаем, что InnoDB - это транзакционно безопасный двигатель хранения, а一致性 - это важная характеристика ACID транзакций. Двигатель хранения InnoDB в основном реализует консистентность данных через лог транзакций InnoDB, который включает лог повторения (redo) и лог откатов (undo).
Логи повторения (redo)
Логи повторения principalmente записывают завершенные транзакции, то есть логи, выполненные commit, в стандартных условиях значения логов повторения записываются в iblogfile0 и iblogfile1 логов повторения.
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
Логи откатов (undo)
Логи откатов principalmente записывают незавершенные транзакции, которые частично завершены и записаны на жесткий диск, в стандартных условиях информация логов откатов записывается в файлы таблицы пространства, общие файлы таблицы пространства ibdata1 или файлы индивидуального пространства未见 ibd.
Из上图 мы можем понять, что логи откатов по умолчанию записываются в ibdta1. Версия моей системы MySQL: 5.6.24.
Механизм проверки точки останова (Checkpoint)
После того как MySQL сервер выйдет из строя, при перезапуске MySQL сервисы, благодаря наличию логов redo и undo, InnoDB выполняет операцию откатов (rollback) всех частично завершенных транзакций, которые были записаны на жесткий диск. Затем все транзакции из логов redo выполняются заново, чтобы восстановить все данные. Однако, если объем данных большой, для сокращения времени восстановления InnoDB вводит механизм проверки точки останова (Checkpoint).
Грязная страница (dirty page)
Когда транзакция требует изменения определенной записи, InnoDB сначала читает блок данных, в котором находится эта запись, с внешнего носителя на жесткий диск. После завершения транзакции InnoDB изменяет запись в странице данных, в этот момент данные на странице缓存 уже не совпадают с данными на внешнем носителе, и эта страница缓存 называется "грязной страницей" (dirty page). Грязная страница刷新ится на внешний носитель и станет "чистой страницей" (clean page).
Примечание: Стандартный размер страницы памяти составляет 4K или кратное 4K. Вы можете представить память как книгу, которую можно стирать. Каждый раз, когда MySQL читает данные, он запрашивает несколько чистых страниц памяти и записывает на них. После того как данные будут записаны на жесткий диск, эти страницы памяти сразу же стираются и могут быть использованы другими программами.
Номер последовательности лога (log sequence number)
Номер последовательности лога (LSN) - это точка завершения каждого лога в логовом пространстве, выраженная в виде смещения в байтах, используется при проверке точки останова и восстановлении.
Механизм проверки точки останова (Checkpoint) Принимается, что в определенный момент времени все страницы, содержащие данные (dirty page), были刷新ены на жесткий диск. До этого момента все логи redo не требуют повторного выполнения. Система использует этот момент как точку останова (Checkpoint), до которой все логи redo не требуют повторного выполнения и могут быть безопасно удалены. Для более эффективного использования пространства логов redo InnoDB использует стратегию轮流ого использования пространства логов redo, поэтому минимальное количество лог-файлов redo в InnoDB составляет 2. Через механизм проверки точки останова, через логи redo (redo log) выполняется операция повторного выполнения (undo) транзакций, которые были завершены, но еще не полностью записаны на внешний носитель, что гарантирует целостность данных и сокращает время восстановления.
Параметры лога redo InnoDB
innodb_log_buffer_size: Установлено размер буфера логов redo.
innodb_log_files_in_group : Установлено количество лог-файлов в группе логов redo.
innodb_log_file_size: устанавливает размер файла журнала redo, чем больше файл, тем дольше занимает восстановление.
innodb_mirrored_log_groups: количество групп файлов журналов, которые можно镜像ировать, может быть установлено только 1.
innodb_log_group_home_dir: устанавливает каталог для хранения групп файлов журналов, по умолчанию в корневой директории базы данных.
Параметры журнала обратного хода InnoDB (undo)
innodb_undo_directory: устанавливает каталог для хранения журнала обратного хода.
innodb_undo_logs: устанавливает размер сегмента журнала обратного хода, по умолчанию 128k
innodb_undo_tablespace: устанавливает, из多少人 файлов журнала обратного хода состоит журнал обратного хода, по умолчанию 0.
Внимание: после установки MySQL необходимо настроить параметры журнала обратного хода в файле my.cnf. Если после создания базы данных будет настроен параметр журнала обратного хода, MySQL выдаст ошибку, и после создания журнала обратного хода его нельзя будет изменить или добавить.
Девять: резервное копирование файлов журналов
Во время резервного копирования можно использовать flush logs, чтобы закрыть все текущие файлы журналов и затем создать новые файлы журналов. После закрытия файлов журналов можно выполнить резервное копирование физическим способом. Кроме того, flush logs можно использовать для добавления конкретного типа журналов:
flush error logs flush general logs flush binary logs flush slow logs
Заявление: контент этой статьи был получен из Интернета, авторские права принадлежат соответствующему автору. Контент был предоставлен пользователями Интернета в добровольном порядке и загружен самостоятельно. Этот сайт не обладает правами собственности на этот контент, не производил его редактирование и не несет ответственности за него. Если вы обнаружите содержимое,涉嫌侵犯版权， пожалуйста, отправьте письмо по адресу: notice#oldtoolbag.com (во время отправки письма, пожалуйста, замените # на @) для сообщения о нарушении и предоставьте соответствующие доказательства. Если факт будет подтвержден, сайт незамедлительно удалит涉嫌侵权的内容。