Логи MySQL

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

Журналы MySQL в основном включают: двоичный журнал, общий журнал запросов, журнал медленных запросов, журнал ошибок, журнал транзакций и т. д.

двоичный журнал

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

Есть две основные функции бинарника:

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

Проверьте, включено ли ведение двоичного журнала

двоичный файл можно передатьshow variables like 'log_bin';Проверьте, не открыт ли он уже.

Следующий возврат указывает, что он закрыт:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

Настроить двоичный журнал MySQL под Windows

Сначала убедитесь, что сервер MySQL установлен. Автор установил 64-разрядную службу MySQL5.7 в операционной системе Windows 10. Имя службы MySQL:MySQL57.

После успешной установки проверьте версию MySQL:

C:\>mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)

ОткрытьC:\ProgramData\MySQL\MySQL Server 5.7(Обратите внимание, что каталог указан правильно) файл my.ini в каталоге и в[mysqld]Добавьте следующую конфигурацию ниже группы конфигурации:

# MySQL二进制日志配置
# bin log输出的目录及文件
log-bin="C:/1workspace/develop/db/mysql/mysql57/logs/binlog"
# bin log清理时间
expire_logs_days=7
# 每个bin log文件大小
max_binlog_size=200m
#binlog缓存大小
binlog_cache_size=10m
#最大binlog缓存大小
max_binlog_cache_size=20m

пройти черезрежим администратораОткройте cmd и перезапустите службу MySQL:

C:\>net stop MySQL57
MySQL57 服务正在停止.
MySQL57 服务已成功停止。


C:\>net start MySQL57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

Подключиться к сервису MySQL:

C:\>mysql -uroot -pmysqltao

Ниже показано, что бинарный журнал MySQL включен:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

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

И индексный файл двоичного журнала: binlog.index.

Просмотр бинарного журнала

Каждый раз, когда вы перезапускаете MySQL, создается новый файл binlog черезshow binary logsОператор может видеть все файлы binlog, а возвращаемое содержимое совпадает с количеством файлов binlog, записанных в файле binlog.index.

Теперь есть 3 файла binlog:

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       177 |
| binlog.000003 |       154 |
+---------------+-----------+

Выполните оператор обновления:

update demo_java_db_1.t_user set name='Jack' where id=1;

пройти сноваmysqlbinlogЗаявление для просмотра текущего файла binlog:

C:\1workspace\develop\db\mysql\mysql57\logs>mysqlbinlog binlog.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190704 17:19:28 server id 1  end_log_pos 123 CRC32 0xaf193fc6  Start: binlog v 4, server v 5.7.17-log created 190704 17:19:28 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
oMQdXQ8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACgxB1dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcY/Ga8=
'/*!*/;
# at 123
#190704 17:19:29 server id 1  end_log_pos 154 CRC32 0xc916f3aa  Previous-GTIDs
# [empty]
# at 154
#190704 17:39:43 server id 1  end_log_pos 219 CRC32 0xcecca4bf  Anonymous_GTID  last_committed=0    sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#190704 17:39:43 server id 1  end_log_pos 287 CRC32 0xb6a3de97  Query   thread_id=7     exec_time=0 error_code=0
SET TIMESTAMP=1562233183/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 287
#190704 17:39:43 server id 1  end_log_pos 358 CRC32 0xd1ce52d6  Table_map: `demo_java_db_1`.`t_user` mapped to number 115
# at 358
#190704 17:39:43 server id 1  end_log_pos 445 CRC32 0x7a57aa7a  Update_rows: table id 115 flags: STMT_END_F

BINLOG '
X8kdXRMBAAAARwAAAGYBAAAAAHMAAAAAAAEADmRlbW9famF2YV9kYl8xAAZ0X3VzZXIABQgPDw8P
CGAAYABgAGAAHtZSztE=
X8kdXR8BAAAAVwAAAL0BAAAAAHMAAAAAAAEAAgAF///wAQAAAAAAAAAFZmlyc3QGcHdkMTIzA01B
TvABAAAAAAAAAARKYWNrBnB3ZDEyMwNNQU56qld6
'/*!*/;
# at 445
#190704 17:39:43 server id 1  end_log_pos 476 CRC32 0x6e49c227  Xid = 42
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Через бинлог можно посмотреть в17:39:43измененныйdemo_java_db_1библиотекаt_userповерхность.

Восстановление данных через бинлог

пройти черезshow binlog eventsКоманда может просматривать события, записанные в бинлоге:

mysql> show binlog events in 'binlog.000003'\G
*************************** 1. row ***************************
   Log_name: binlog.000003
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.17-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000003
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000003
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000003
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 287
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000003
        Pos: 287
 Event_type: Table_map
  Server_id: 1
End_log_pos: 358
       Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 6. row ***************************
   Log_name: binlog.000003
        Pos: 358
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 445
       Info: table_id: 115 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: binlog.000003
        Pos: 445
 Event_type: Xid
  Server_id: 1
End_log_pos: 476
       Info: COMMIT /* xid=42 */
*************************** 8. row ***************************
   Log_name: binlog.000003
        Pos: 476
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 541
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 9. row ***************************
   Log_name: binlog.000003
        Pos: 541
 Event_type: Query
  Server_id: 1
End_log_pos: 609
       Info: BEGIN
*************************** 10. row ***************************
   Log_name: binlog.000003
        Pos: 609
 Event_type: Table_map
  Server_id: 1
End_log_pos: 680
       Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 11. row ***************************
   Log_name: binlog.000003
        Pos: 680
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 765
       Info: table_id: 115 flags: STMT_END_F
*************************** 12. row ***************************
   Log_name: binlog.000003
        Pos: 765
 Event_type: Xid
  Server_id: 1
End_log_pos: 796
       Info: COMMIT /* xid=69 */
*************************** 13. row ***************************
   Log_name: binlog.000003
        Pos: 796
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 861
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 14. row ***************************
   Log_name: binlog.000003
        Pos: 861
 Event_type: Query
  Server_id: 1
End_log_pos: 929
       Info: BEGIN
*************************** 15. row ***************************
   Log_name: binlog.000003
        Pos: 929
 Event_type: Table_map
  Server_id: 1
End_log_pos: 1000
       Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 16. row ***************************
   Log_name: binlog.000003
        Pos: 1000
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 1085
       Info: table_id: 115 flags: STMT_END_F
*************************** 17. row ***************************
   Log_name: binlog.000003
        Pos: 1085
 Event_type: Xid
  Server_id: 1
End_log_pos: 1116
       Info: COMMIT /* xid=101 */
*************************** 18. row ***************************
   Log_name: binlog.000003
        Pos: 1116
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 1181
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 19. row ***************************
   Log_name: binlog.000003
        Pos: 1181
 Event_type: Query
  Server_id: 1
End_log_pos: 1249
       Info: BEGIN
*************************** 20. row ***************************
   Log_name: binlog.000003
        Pos: 1249
 Event_type: Table_map
  Server_id: 1
End_log_pos: 1320
       Info: table_id: 115 (demo_java_db_1.t_user)
*************************** 21. row ***************************
   Log_name: binlog.000003
        Pos: 1320
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 1405
       Info: table_id: 115 flags: STMT_END_F
*************************** 22. row ***************************
   Log_name: binlog.000003
        Pos: 1405
 Event_type: Xid
  Server_id: 1
End_log_pos: 1436
       Info: COMMIT /* xid=107 */
22 rows in set (0.00 sec)

Log_name: Этот журнал существует в каком файле.Из приведенного выше видно, что эти два журнала существуют в файле mysql_bin.000001.
Pos: начальная позиция журнала в bin-log
Event_type: тип информации журнала
Server_id: вы можете просмотреть server_id в конфигурации, указав, какой сервер генерирует журнал, а server_id можно настроить в my.ini.
End_log_pos: конечная позиция журнала в bin-log
Инфо: Некоторые замечания лога, можно интуитивно посмотреть что сделано

пройти черезmysqlbinlogкоманда для восстановления данных,mysqlbinlogКоманда поддерживает добавление параметров для восстановления указанного события:

mysqlbinlog --start-date="2019-07-04 18:00:00" --stop-date="2019-07-04 18:10:00" binlog.000003 |mysql -uroot -pmysqltao

журнал ошибок

Журнал ошибок MySQL записывает, когда mysqld запускается и останавливается, а также любые серьезные ошибки, с которыми сталкивается сервер во время работы.

Как и binlog, настройте в файле my.ini:

# error log输出的目录及文件
log-error="C:/1workspace/develop/db/mysql/mysql57/logs/errorlog"

Общий журнал запросов

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

Настройте общий журнал запросов в группе [mysqld] файла my.ini:

#通用查询日志输出格式 [none|file|table|file,table]
log_output=file
#是否启用通用查询日志[on|off]        
general_log=on
#通用查询日志位置及名字
general_log_file="C:/1workspace/develop/db/mysql/mysql57/logs/generallog"

журнал медленных запросов

Журнал медленных запросов записывает журнал запросов медленных операторов SQL и может использоваться для оптимизации производительности операторов SQL с длительным временем выполнения и низкой эффективностью выполнения.

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

set global slow_query_log = on;

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

set global slow_query_log = off;

2. Временно установите критическую точку медленного времени запроса

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

Все sql, которые выполняются более 1 секунды, будут регистрироваться в журнале медленных запросов:

set long_query_time = 1;

Как настроить медленное хранилище запросов

set globle log_output = file;

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

Запросите статус открытия журнала медленных запросов и место хранения журнала медленных запросов.

show variables like '%quer%';

Дополнение: Журнал транзакций

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

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

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

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

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

Просмотр параметров журнала транзакций:

mysql> SHOWGLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+-----------------------------------+
| Variable_name                           | Value                             |
+-----------------------------------------+-----------------------------------+
| innodb_flush_log_at_trx_commit          | 1             |
|innodb_locks_unsafe_for_binlog          |OFF                               |
| innodb_log_buffer_size                  | 8388608                           |
|innodb_log_file_size                    |5242880                           |
| innodb_log_files_in_group               | 2                      |
|innodb_log_group_home_dir               |./            |
|innodb_mirrored_log_groups              |1              |

Суммировать

Журналы MySQL в основном включают:

Журнал ошибок, общий журнал запросов, журнал медленных запросов, журнал транзакций, двоичный журнал

Обычно существует три способа вывода журнала MySQL:

file (файл), table (таблица), none (не сохранять)

Журналы MySQL в основном используются для:

Мониторинг исключений, оптимизация производительности, восстановление данных, синхронизация master-slave