MySQL log file

MySQL mainly has the following types of logs:

• The error log--mysql information and other error and warning messages during service startup and shutdown. The default is in the data directory.

• General query log--logs that are used to record select query statements. General_log, General_log_file closed by default, recommended to close.

• Slow query log--log-slow-queries records all SQL statements that exceed long_query_time time.

• Binary Log--logs any operations that cause data changes for backup and restore. It is stored by default in the data directory, and the binary log is scrolled when the refresh and service restarts.

• Relay Log--the event copied from the primary server's binaries and saved as a binary file, in the same format as the binary log.

• Transaction log-ensures transactional consistency.

Slow query log:

Mysql> Show variables like "long%"; #查看慢查询日志信息

+-----------------+----------+

| variable_name | Value |

+-----------------+----------+

| Long_query_time | 3.000000 |

+-----------------+----------+

1 row in Set (0.13 sec)

Mysql> Show variables like "slow%"; #查看慢查询日志设置

+---------------------+-----------------------------+

| variable_name | Value |

+---------------------+-----------------------------+

| Slow_launch_time | 2 |

| Slow_query_log | OFF |

| Slow_query_log_file | /data/mysql/slave2-slow.log |

+---------------------+-----------------------------+

3 Rows in Set (0.06 sec)

Mysqldumpslow-s c-t 10/database/mysql/mysql06_slow.log #查看访问最多的10个SQL

Mysql> set long_query_time=5; #慢查询时间设置, permanent changes need to be changed

Query OK, 0 rows affected (0.25 sec) configuration file

Binary log:

Mysql> Show global variables like "%log%"; #查看日志相关变量

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 |

+---------------------------------+-------+

[Email protected] mysql]# VIM/ETC/MY.CNF

Log_bin #去掉注释, open binary log

[Email protected] mysql]# service mysqld restart

Mysql> show binary logs; #查看所有二进制日志文件

+-------------------+-----------+

| Log_name | File_size |

+-------------------+-----------+

|       slave2-bin.000001 | 168 |

|       slave2-bin.000002 | 120 |

+-------------------+-----------+

Mysql> Show master status; #查看当前使用的二进制日志

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+-------------------+----------+--------------+------------------+-------------------+

|      slave2-bin.000002 |              120 |                  |                   | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.03 sec)

mysql> flush logs; #刷新二进制日志

Query OK, 0 rows affected (0.17 sec)

[Email protected] mysql]# Mysqlbinlog mysql-bin.00001; #显示二进制文件内容

Mysql> show global status like '%slow_queries% '; #查看慢查询日志数目

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1919034

MySQL log file