初学mysql：日志管理（二）

 

Mysql的日志类型

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

1、Error log

记录mysql启动、运行、停止的错误日志，配置方法：

vim /etc/my.cnf

log-error=/var/log/mysqld.log

 

2、Binary log

记录mysql 数据变更的日志，可用于增量备份恢复，配置方法：

vim /etc/my.cnf

[mysqld]

log-bin=/var/log/mysql-bin

server-id=1

注：当重启Mysql、flush logs会截断日志。reset master和purge binary logs可删除日志

二进制日志查看：

[root@mysql-node1 logs]# mysqlbinlog mysql-bin-3306.000005

 

3、Relay log

master库传输过来的二进制日志，可用于主从复制，配置方法：

vim /etc/my.cnf

[mysqld]

relay_log=/var/log/mysql-relay-bin

server_id=2

 

4、DDL log

记录mysql DDL的日志，是个二进制文件在data目录下。

 

 

5、General query log 

记录所有查询日志

vim /etc/my.cnf

[mysqld]

log/var/log/mysql-query.log

 

6、 Slow query log

记录所有慢日志，可通过参数long_query_time定义慢查询时间,可通过参数log-queries-not-use-indexes记录不使用索引查询日志

vim /etc/my.cnf

[mysqld]

log_slow_queries=/var/log/mysql-slow-query.log

long_query_time=2

log-queries-not-use-indexes=1

 

To disable or enable general query logging for the current connection, set the session sql_log_off variable to ON or OFF.

会话中可通过 set sql_log_off=on关闭日志