search

lihongjie0209 / myblog

4 stars 0 forks source link

MySQL: 日志 #48

Open lihongjie0209 opened 4 years ago

lihongjie0209 commented 4 years ago
Log Type Information Written to Log
Error log Problems encountered starting, running, or stopping mysqld
General query log Established client connections and statements received from clients
Binary log Statements that change data (also used for replication)
Relay log Data changes received from a replication master server
Slow query log Queries that took more than long_query_time seconds to execute
DDL log (metadata log) Metadata operations performed by DDL statements
lihongjie0209 commented 4 years ago

错误日志

The error log contains a record of mysqld startup and shutdown times. It also contains diagnostic messages such as errors, warnings, and notes that occur during server startup and shutdown, and while the server is running. For example, if mysqld notices that a table needs to be automatically checked or repaired, it writes a message to the error log.

On Unix and Unix-like systems, mysqld uses the --log-error option to determine whether mysqld writes the error log to the console or a file, and, if to a file, the file name:

If --log-error is not given, mysqld writes the error log to the console. // 不配置默认输出到console

If --log-error is given without naming a file, mysqld writes the error log to a file named host_name.err in the data directory. // 配置了但不是文件, 输出到data文件夹

If --log-error is given to name a file, mysqld writes the error log to that file (with an .err suffix added if the name has no suffix), located under the data directory unless an absolute path name is given to specify a different location. // 配置到文件

If --log-error is given in an option file in a [mysqld], [server], or [mysqld_safe] section, mysqld_safe finds and uses the option, and passes it to mysqld.

lihongjie0209 commented 4 years ago

二进制日志

数据库变动记录日志, 主要用于:

  1. 主从同步
  2. 备份恢复

启用二进制日志

To enable the binary log, start the server with the --log-bin[=base_name] option.

创建二进制文件

mysqld appends a numeric extension to the binary log base name to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time any of the following events occurs:

日志的记录时间

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

Updates to nontransactional tables are stored in the binary log immediately after execution.

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

日志格式

  1. 记录SQL
  2. 记录行数据

按照SQL记录的日志可能会有一些和执行环境相关的函数, 导致备份无法完全恢复, 最好的方式还是按照行记录一行一行的拷贝.

--binlog-format=ROW
lihongjie0209 commented 4 years ago

查询日志

记录所有可以端的非变更操作.

To disable or enable the general query log or change the log file name at runtime, use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.