Detailed introduction to the 4 types of logs in Mysql

Mysql has 4 types of logs: Error Log, General Query Log, Binary Log and Slow Query Log**

1. Error Log

Records Errors during Mysql operation. Warning, Note and other information, if there is a system error or a problem with a certain record, you can check the Error log.

• Mysql’s error log is stored in the Mysql log directory as hostname.err by default. It can be viewed through the following statement:

  mysql> show variables like "log_error";
  +---------------+----------------+
  | Variable_name | Value          |
  +---------------+----------------+
  | log_error     | /tmp/mysql.log |
  +---------------+---------------

• Modify the error log You can add --log-error = [filename] to /etc/my.cnf to enable the mysql error log. Mine is:

   log_error = /tmp/mysql.log

• Let’s check it out first: tail -f /tmp/mysql.log

  bash-3.2# tail -f /tmp/mysql.log 
  2015-12-23T02:22:41.467311Z 0 [Note] IPv6 is available.
  2015-12-23T02:22:41.467324Z 0 [Note]   - '::' resolves to '::';
  2015-12-23T02:22:41.467350Z 0 [Note] Server socket created on IP: '::'.
  2015-12-23T02:22:41.584287Z 0 [Note] Event Scheduler: Loaded 0 events
  2015-12-23T02:22:41.584390Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: ready for connections.
  Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
  2015-12-23T02:22:42.540786Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151223 10:22:42
  151223 10:22:51 mysqld_safe A mysqld process already exists
  2015-12-23T02:25:30.984395Z 2 [ERROR] Could not use /tmp/mysql_query.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL GENERAL_LOG=ON" or restart the MySQL server.
  2015-12-23T07:28:03.923562Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 61473ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

The amount of information is relatively large, temporarily No more analysis. . . . Of course, if there is an error in mysql configuration or connection, you can still track the log through tail -f

2. General Query Log

Record the daily log of mysql, including queries, modifications, updates, etc. Each sql.

• First check whether mysql has query log enabled: show global variables like "%genera%"

  mysql> show global variables like "%genera%";
  +----------------------------------------+----------------------+
  | Variable_name                          | Value                |
  +----------------------------------------+----------------------+
  | auto_generate_certs                    | ON                   |
  | general_log                            | OFF                  |
  | general_log_file                       | /tmp/mysql_query.log |
  | sha256_password_auto_generate_rsa_keys | ON                   |
  +----------------------------------------+----------------------+
  4 rows in set (0.00 sec)

  I have configured log output here File: /tmp/mysql_query.log, and the log function is turned off

• The output file of the query log can be added in /etc/my.cnfgeneral -log-file = [filename]

• After Mysql opens the general log log, all query statements can be output in the general log file. If it is opened, the file will be very large. , it is recommended to turn it on during debugging and turn it off at ordinary times

  mysql> set global general_log = on;
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> set global general_log = off;
  Query OK, 0 rows affected (0.01 sec)

• Note:

  If the log function is turned on but no log is written, then It is possible that mysql does not have enough permissions on the log file, so you need to specify the permissions. My log file is /tmp/mysql_query.log, then:

  chown mysql:mysql /tmp/mysql_query.log

3. Binary Log

Binary log contains some events, which describe database changes, such as table creation, data changes, etc., mainly used for backup, recovery, rollback operations, etc.

1. Function:

• Contains all updated data or potentially updated data (such as a DELETE that does not match any rows)

• Contains information about each updated database (DML )'s execution time information

• does not include statements that do not modify any data. If you need to enable this option, you need to enable the general log function

• The main purpose is to restore the database to the point of database failure as much as possible, because the binary log contains all updates made after the backup

• is used to record on the master replication server all updates that will be sent to the slave Server statement

• Enabling this option reduces database performance by 1%, but database integrity is guaranteed. For important databases, it is worth trading performance for integrity

2. Format

Binlog has 3 formats

• STATMENT: Every sql that will modify the data will be recorded in the master's binlog, and the sql process will be copied when the slave is copying It will be parsed into the same SQL as the original execution on the master side and then executed.
  A little bit: In statement mode, the shortcomings of row mode are first solved. There is no need to record changes in each row of data, which reduces the amount of binlog logs, saves I/O and storage resources, and improves performance. Because he only needs to stimulate the details of the statement executed on the master and the context information when the statement is executed.
  Disadvantages: In statement mode, since it is a recorded execution statement, in order for these statements to be executed correctly on the slave side, it must also record some relevant information when each statement is executed, and It is context information to ensure that all statements when executed on the slave side can obtain the same results as when executed on the master side. In addition, because mysql is developing rapidly now, many new functions are constantly added, which makes the replication of mysql encounter a lot of challenges. Naturally, the more complex content is involved in replication, the easier it is for bugs to appear. In the statement, it has been found that many situations will cause problems with Mysql replication, mainly when certain functions or functions are used when modifying data. For example: the sleep() function cannot be used in some versions. is copied correctly, and the last_insert_id() function is used in the stored procedure, which may cause inconsistent ids on the slave and master, etc.

• ROW: The log will record the modified form of each row of data, and then modify the same data on the slave side. Only the data to be modified will be recorded, only the value, not the value. There will be situations where multiple SQL tables are associated.
  Advantages: In row mode, the bin-log does not need to record the context-related information of the executed SQL statement. It only needs to record which record was modified and what the modification was, so the row log content will be The details of each row of data modification are recorded very clearly, making it very easy to understand. Moreover, there will be no problem that stored procedures and functions under certain circumstances, as well as trigger calls and triggers cannot be copied correctly.
  Disadvantages: In row mode, when all executed statements are recorded in the log, they will be recorded as modifications to each row, which may generate a large amount of log content.

• MIXED：MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种

3. 配置

• 查看mysql中二进制文件的配置情况：show variables like "%log_bin%";

  mysql> show variables like "%log_bin%";
  +---------------------------------+-------+
  | Variable_name                   | Value |
  +---------------------------------+-------+
  | log_bin                         | OFF   |
  | log_bin_basename                |       |
  | log_bin_index                   |       |
  | log_bin_trust_function_creators | OFF   |
  | log_bin_use_v1_row_events       | OFF   |
  | sql_log_bin                     | ON    |
  +---------------------------------+-------+

log_bin : 用于设定是否启用二进制日志, 由此看是未开启

• 配置文件仍然是在 /etc/my.cnf 中， 修改/etc/my.cnf， 增加日志文件目录：

  log_bin = /tmp/mysql-bin.log

• 重启mysql ：

  bash-3.2# mysql.server start;
  Starting MySQL
  . ERROR! The server quit without updating PID file (/usr/local/Cellar/mysql/5.7.9/data/mysql.pid).

• 又报错，查看错误日志，我的配置在/tmp/mysql.log

  151224 00:37:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
  2015-12-23T16:37:34.643998Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  2015-12-23T16:37:34.644124Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_pISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
  2015-12-23T16:37:34.644129Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
  2015-12-23T16:37:34.644189Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
  2015-12-23T16:37:34.644226Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld (mysqld 5.7.9-log) starting as process 24268 ...
  2015-12-23T16:37:34.646468Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
  2015-12-23T16:37:34.646945Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
  2015-12-23T16:37:34.646978Z 0 [ERROR] Aborting
  
  2015-12-23T16:37:34.646991Z 0 [Note] Binlog end
  2015-12-23T16:37:34.647068Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: Shutdown complete
  
  151224 00:37:34 mysqld_safe mysqld from pid file /usr/local/Cellar/mysql/5.7.9/data/mysql.pid ended

重点：

You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation

说明需要配置一个server-id， 再拿这句话百度，果然是这样。所以在 配置文件/etc/my.cn中添加 server-id = 1，再重启mysql，解决问题。而且在配置的bin-log同级目录增加了mysql-bin.000001  mysql-bin.index   mysql-bin.log 三个文件，前两个是自动生成。

参数：

log_bin：设置此参数表示启用binlog功能，并指定路径名称

log_bin_index：设置此参数是指定二进制索引文件的路径与名称

binlog_do_db：此参数表示只记录指定数据库的二进制日志
binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size：此参数表示binlog使用的内存最大的尺寸

binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。binlog_cache_use：使用二进制日志缓存的事务数量

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size：Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog：这个参数直接影响mysql的性能和完整性

sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘        同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

• 登录mysql，再次查看bin-log的状态，属于启用状态

  mysql> show variables like "%log_bin%";
  +---------------------------------+----------------------+
  | Variable_name                   | Value                |
  +---------------------------------+----------------------+
  | log_bin                         | ON                   |
  | log_bin_basename                | /tmp/mysql-bin       |
  | log_bin_index                   | /tmp/mysql-bin.index |
  | log_bin_trust_function_creators | OFF                  |
  | log_bin_use_v1_row_events       | OFF                  |
  | sql_log_bin                     | ON                   |
  +---------------------------------+----------------------+

• binlog的删除
  binlog的删除可以手工删除或自动删除

自动删除binlog

通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog

    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       869 |
    +------------------+-----------+
    1 row in set (0.00 sec)

    mysql> show variables like 'expire_logs_days' ;
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 0     |
    +------------------+-------+
    1 row in set (0.00 sec)

    mysql> ;set global expire_logs_days=3;
    ERROR: 
    No query specified

    Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like 'expire_logs_days' ;
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 3     |
    +------------------+-------+
    1 row in set (0.00 sec)

手工删除binlog

    mysql> reset master;   //删除master的binlog
    mysql> reset slave;    //删除slave的中继日志
    mysql> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
    mysql> purge master logs to 'mysql-bin.000001';   //删除指定日志文件的日志索引中binlog日志文件

或者直接用操作系统命令直接删除

    mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
    mysql> show master logs; //查看master的binlog日志 
    mysql> show binary logs; //查看master的binlog日志
    mysql> show master status; //用于提供master二进制日志文件的状态信息
    mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

• blog查看：通过mysqlbinlog 查看日志文件

  bash-3.2# mysqlbinlog /tmp/mysql-bin.log

四、Slow Query Log

记录Mysql 慢查询的日志
修改配置文件 /etc/my.cnf

1. Mysql 慢查询配置相关命令：

• 查看日志功能是否开启：show variables like "%slow%";

  mysql> show variables like "%slow%";
  +---------------------------+----------------------------------------------------+
  | Variable_name             | Value                                              |
  +---------------------------+----------------------------------------------------+
  | log_slow_admin_statements | OFF                                                |
  | log_slow_slave_statements | OFF                                                |
  | slow_launch_time          | 2                                                  |
  | slow_query_log            | OFF                                                |
  | slow_query_log_file       | /usr/local/var/mysql/tongkundeMacBook-Pro-slow.log |
  +---------------------------+----------------------------------------------------+

  slow_query_log 配置为OFF ， 说明未开启慢日志

• 打开慢日志功能：set global slow_query_log = on;

  mysql> set global slow_query_log = on;
  Query OK, 0 rows affected (0.06 sec)

• 查看下默认设置的慢查询的时间：show variables like "%long_query%";

  mysql> show variables like "%long_query%";
  +-----------------+-----------+
  | Variable_name   | Value     |
  +-----------------+-----------+
  | long_query_time | 10.000000 |
  +-----------------+-----------+

  可以看出，默认是10秒，按照这个配置，数据得上n亿才能达到，为了测试我们修改一下

2. 修改Mysql配置文件的方式

• 打开/etc/my.cnf , 加入慢查询配置文件

  slow-query-log = 1
  slow-query-log-file = /tmp/mysql-slow.log
  long_query_time = 1 #设置满请求时间, 设置查多少秒的查询算是慢查询

  保存退出后要重启mysql

  mysql.server restart;

• 通过mysql命令查看配置：

  mysql> show variables like "%slow%";
  +---------------------------+---------------------+
  | Variable_name             | Value               |
  +---------------------------+---------------------+
  | log_slow_admin_statements | OFF                 |
  | log_slow_slave_statements | OFF                 |
  | slow_launch_time          | 2                   |
  | slow_query_log            | OFF                 |
  | slow_query_log_file       | /tmp/mysql-slow.log |
  +---------------------------+---------------------+

  这里显示慢日志功能还未开启

• 打开慢日志功能

  mysql> set global slow_query_log = on;
  ERROR 29 (HY000): File '/tmp/mysql-slow.log' not found (Errcode: 13 - Permission denied)

  恩，报错了，说明什么呢，权限不够，那就给权限，退出mysql 执行：

   chown mysql:mysql /tmp/mysql-slow.log

  回到mysql，再次打开慢日志：

  mysql> set global slow_query_log = on;
  Query OK, 0 rows affected (0.00 sec)

  ok, 解决。

3. 测试一下

• 先监控下日志： tail -f /tmp/mysql-slow.log

• 在mysql中分别执行两句查询：

  mysql> SELECT 2;
  +---+
  | 2 |
  +---+
  | 2 |
  +---+
  1 row in set (0.00 sec)
  
  mysql> SELECT sleep(3);
  +----------+
  | sleep(3) |
  +----------+
  |        0 |
  +----------+
  1 row in set (3.01 sec)

• 查看一下日志文件的输出：
  

  # Time: 2015-12-23T15:50:44.140140Z
  # User@Host: root[root] @ localhost []  Id:     2
  # Query_time: 3.003542  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
  SET timestamp=1450885844;
  SELECT sleep(3);

  基本上查询的所有信息都有显示，就不多白花了。

The above is the detailed content of Detailed introduction to the 4 types of logs in Mysql. For more information, please follow other related articles on the PHP Chinese website!