Error Log Error Log

mysql> show variables like 'log_error'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | Log_error | / var/log/mysql/mysqld. Log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) cat / var/log/mysql/mysqld. Log T04:2021-05-29 and the 333904 z 0 [System] [MY - 011323] (Server) X Plugin ready for connections. Bind-address: '::' port: 33060, socket: / var/lib/mysql/mysqlx sock T04:2021-05-29 and the 596739 z 0 [Warning] [MY - 010068] [Server] CA certificate CA. The pem is self 2021-05-29T04.34:31.596920z 0 [System] [my-013602] [Server] Channel mysql_main configured to support TLS. Encrypted Connections are now supported for this channel. 2021-05-29T04.34:31.598120z 0 [ERROR] [my-010262] [Server] Can't start server: Bind on TCP/IP port: Address already in use 2021-05-29T04:34:31.598287z 0 [ERROR] [my-010257] [Server] Do you already have another mysqld server running on port: 3306 ? 2021-05-29T04.34:31.598755z 0 [ERROR] [my-010119] [Server] AbortingCopy the code

Error Log Records System information, Warning information, and Error information about the MySQL running process.

General Log

mysql> show variables like '%general_log%'; +------------------+--------------------------------------------+ | Variable_name | Value | +------------------+--------------------------------------------+ | general_log | ON | | general_log_file | /var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z.log | +------------------+--------------------------------------------+ 2 rows in Set (0.00 SEC) cat/var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z log usr/libexec/mysqld, Version: 8.0.21 (Source distribution). Started with: Tcp port: 3306 Unix socket: / var/lib/mysql/mysql. The sock Time Id Command Argument T04:2021-05-29 hosts. The 120882 z Query 10 show the variables like '% general_log % 2021-05-29 T04: down. 803937 z 10 QuitCopy the code

General Log is turned off by default. SET GLOBAL general_log = ON is used; Manually enable. The General Log records all requests received during MySQL running.

The log_output variable controls log formats, including FILE (default) and TABLE.

Slow Query Log Slow Query Log

mysql> show variables like '%slow_query_log%'; +---------------------+-------------------------------------------------+ | Variable_name | Value | +---------------------+-------------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z-slow.log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC) cat /var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z-slow.log Tcp port: 3306 Unix socket: The/var/lib/mysql/mysql. The sock Time Id Command Argument # Time: 2021-05-29 T05:02:27. 120937 z # User @ Host: Root [root] @localhost [] Id: 12 # Query_time: 0.000149 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1622264547; SET long_query_time = 0;Copy the code

Slow Query Log is turned off by default, using SET GLOBAL slow_query_log = ON; Manually enable. Slow Query Log Records commands whose Query time exceeds long_query_time. SET long_query_time = 0; Changed the slow query threshold to 10 by default.

The log_output variable controls log formats, including FILE (default) and TABLE. The log_QUERies_NOT_USING_INDEXES variable is disabled by default. If no indexes are used in a query after this function is enabled, the indexes are displayed in slow query logs.

Binary Log Bin Log

The Bin Log records all changes made to MySQL and is mainly used for recovery and replication.

mysql> show variables like '%bin%'; +------------------------------------------------+-----------------------------+ | Variable_name | Value | +------------------------------------------------+-----------------------------+ | binlog_format | ROW | | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | | sync_binlog | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 37 rows in the set (0.00 SEC)Copy the code
  • Binlog_format: STATEMENT (SQL) | ROW change (rows) | MIXED (first two mix).
  • Log_bin: indicates whether to enable binary logs.
  • Log_bin_basename: Bin Log file name prefix, for example, binlog.000001.
  • Log_bin_index: Bin Log Index file name, which stores all Bin Log files.
  • Sync_binlog: 0 | 1 said whether each transaction has been completed immediately brush set, N said every N transaction brush plate.

Use mysqlbinlog to view the Bin Log file, mysqlbinlog binlog.000001.

Relay Log Relay Log

For master -> bin log -> relay log -> slave replication.

mysql> show variables like '%relay%'; +---------------------------+--------------------------------------------------------+ | Variable_name | Value | +---------------------------+--------------------------------------------------------+ | relay_log | iZm5ebzz76jb4bnhkzg6i3Z-relay-bin | | relay_log_basename | /var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z-relay-bin | | relay_log_index | /var/lib/mysql/iZm5ebzz76jb4bnhkzg6i3Z-relay-bin.index | | relay_log_info_file | relay-log.info | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 11 rows in the set (0.01 SEC)Copy the code
  • Relay_log: indicates the log file name. If this parameter is left blank, the log file name is disabled.
  • Relay_log_info_file: records information about log files.

Transaction Log Transaction Log

The implementation of transactions is based on innoDB storage engine, and transactions have four properties of ACID. Isolation is achieved through locks, and atomicity, consistency, and persistence are achieved through Transaction logs (redo logs, undo logs).

Redo log is used to implement transaction persistence, undo log is used to implement atomicity. The consistency of transactions can only be ensured by ensuring isolation, persistence and atomicity.

redo log

As transactions continually modify the contents of the data page, the redo log records changes made to a location on the page. When the MySQL instance crashes, load the pre-crash data and redo the data page according to the redo log.

An InnoDB engine contains at least one redo log group, and a redo log group contains at least two redo logs. Redo log writes ib_logfile1 first, ib_logfile2 at the end of the file, ib_logfile1 at the end of the file.

variable meaning
innodb_log_group_home_dir Log the path where the redo log exists
innodb_log_files_in_group Log number of redo logs. Default is 2 (ib_logfile0, ib_logfile1)
innodb_log_file_size Size of redo log
innodb_log_buffer_size Redo log cache size

Redo log flush policy:

variable meaning
innodb_flush_log_at_timeout The default value is 1, and the main thread is flushed once per second
innodb_flush_log_at_trx_commit The default value is 1, which controls the flushing policy of the committed transaction :(0) no flushing, (1) flushing, and (2) handing over to the system buffer
innodb_flush_method The default value is fsync. Flush the system buffer to disk

redo log & bin log

bin log redo log
The log level The system log Engine log
Write the timing Transaction commit During transaction execution
idempotence no is

Double one write? Both innodb_flush_LOG_at_trx_COMMIT and sync_binlog are set to 1, which results in poor performance but higher security.

Redo log two-phase commit

The redo log is written in the prepare and commit phases. The write sequence is as follows: 1. (2) bin log; ③ Redo log (update to commit phase)

This is why the transaction is not committed and the redo log is flushed. If the redo log is in the prepare stage but there is nothing in the bin log, the change is discarded.

undo log

Undo log is not like a traditional log, but more like a data backup, that is, backing up data before a transaction is committed.

Before MySQL5.6, undo logs were stored in a shared tablespace. You can also enable innodb_file_per_table to have undo logs stored in a separate tablespace for each table.

mysql> show variables like '%innodb_undo%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_undo_directory | ./ | | innodb_undo_log_encrypt | OFF | | innodb_undo_log_truncate | ON | | Innodb_undo_tablespaces | 2 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 4 rows in the set (0.01 SEC)Copy the code

After MySQL5.6, you can specify the undo log tablespace storage location via the innodb_undo_directory variable. Innodb_undo_tablespaces Specifies the number of undo logs (undo_001, undo_002).