MySQL系列:(十)MySQL日志管理

admin 2021年01月11日 918次浏览

1、错误日志

1.1、作用

​ 排查MySQL运行过程中的故障

1.2、查看错误日志路径

mysql> select @@log_error;

1.3、配置错误日志路径

  • MySQL的错误日志时是认开启的,其路径在$datadir/[hostname].err

  • 自定义错误日志路径,在配置文件中添加以下参数

    # 参数格式
    log_error=/PATH/ERR_LOG_NAME
    
    # 示例
    log_error=/var/log/mysql_err.log
    

2、二进制日志

2.1、作用

  • 做主从需要依赖二进制日志
  • 数据恢复时,需要依赖二进制日志

2.2、配置二进制日志

MySQL默认是没有开启二进制日志的,如果需要使用二进制日志,需要在配置文件中添加以下参数

# 参数格式
## server_id取值范围:0~65535
server_id=ID

## PATH需要提前创建好,并且有对应的用户及用户组权限;
## LOG_NAME为二进制日志文件名的前缀。例如:mysql_bin.0001
log_bin=/PATH/LOG_NAME

## 二进制日志格式设置(5.7以后,默认格式就为row)
binlog_format=row

# 示例
server_id=1
log_bin=/data/bin_log/mysql_bin

2.3、二进制日志记录的内容

二进制日志记录的是数据库所有变更类的操作日志(DDL、DCL、DML)

  • 对于DDL和DCL语句

    以语句的方式原样记录

  • 对于DML语句

    • 记录的是已经提交的记录

    • DML记录的格式支持statement、row、mixed,通过binlog_format参数控制

      • statement(SBR):语句模式记录日志,记录命令就是执行命令

      • row(RBR):行模式,记录数据行的变化

      • mixed(MBR):混合模式

    • SBR与RBR的区别

      • SBR的可读性较强,对于范围操作,日志量较少,但是可能会出现记录不准确的情况
      • RBR的可读性较差,对于范围操作,日志量较大,但是不会出现记录错误的情况

2.4、二进制日志记录单元

  • event事件

    二进制日志的最小记录单元,对于DDL语句,每一条语句就是一个事件;对于DML语句,一个事务中,如果包含多个语句,则每个语句就是一个事件

  • event事件的开始和结束

    其中用是方便从日志中截取部分日志事件

2.5、二进制日志管理

  • 查看二进制日志位置
mysql> SHOW VARIABLES LIKE '%log_bin%';
  • 查看所有已经存在的二进制日志
mysql> SHOW BINARY LOGS;
  • 查看正在使用的二进制日志
mysql> SHOW MASTER STATUS;
  • 查看正在使用的二进制日志所有事件
# 语法
mysql> SHOW BINLOG EVENTS IN 'bin_log_NAME' [LIMIT [n];
# 示例
mysql> show BINLOG EVENTS IN 'binlog.000048';

## 说明
pos:事件的开始位置
end_log_pos:事件结束位置
  • 查看二进制日志事件内容
# 语法
[root@localhost]# mysqlbinlog --base64-output=decode-rows bin_log_NAME

# 示例
[root@localhost]# mysqlbinlog --base64-output=decode-rows binlog.000048
  • 查看指定范围内二进制事件内容
# 语法
[root@localhost]# mysqlbinlog --start-position=POS --stop-position=END_LOG_POS bin_log_NAME

# 示例
[root@localhost]# mysqlbinlog --start-position=425 --stop-position=631 binlog.000048
  • 通过二进制日志恢复数据

    一般创建不久的数据库可以使用二进制日志恢复数据,如果数据库建立时间比较长,不建议使用该方法恢复数据

    • 分析和截取二进制日志
    # 确认正在使用的日志文件
    mysql> show master status;
    
    # 查看所有事件,找到起点和终点,进行截取
    mysql> show binlog events in 'bin_log.000048' ;
    mysqlbinlog --start-position=409 --stop-position=1521 /data/binlog/bin_log.000048 >/tmp/bin.sql
    
    • 使用二进制日志恢复数据
    # 临时关闭恢复时产生的新日志
    mysql> set sql_log_bin=0;
    mysql> source /tmp/bin.sql
    
    # 恢复二进制日志
    mysql> set sql_log_bin=1
    

2.6、二进制日志的GTID记录模式管理

对于二进制日志中的每一个事务,都会生成一个DTID号

  • GTID组成

    server_uuid:TID

    • server_id在/$datadir/auto.cnf中
    • TID是一个从1开始自增长的数据
  • GTID的幂等性
    如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,如果有相同的就自动跳过,GTID会影响到binlog恢复和主从复制

  • GTID的开启和配置

    在配置文件中添加以下配置

    [root@localhost]# vim /etc/my.cnf
    # 开启GTID模式
    gtid-mode=on
    # 强制GTID一致性
    enforce-gtid-consistency=true
    
  • 查看GTID信息

mysql> show master status;
  • 基于GTID二进制日志恢复
# 截取日志
## 在截取日志时,需要使用--skip-gtids参数忽略原有的GTID信息,恢复时生成最新的gtid信息,否则会报错
[root@localhost]# mysqlbinlog --skip-gtids --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' mysql-bin.000050 >/tmp/gtid.sql

# 恢复 
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql;
mysql> set sql_log_bin=1;
  • GTID相关参数

    --skip-gtids:忽略原有GTID信息

    --include-gtids:包含的GTID编号

    --exclude-gtids:需要排除的GTID编号

2.7、二进制日志清理

  • 自动清理

    # 配置二进制日志自动清理
    ## 在MySQL配置文件中,添加以下参数
    ## 关于自动清理日志时间,一般建议设置为不小于两轮数据库全备时间
    
    expire_logs_days=[days]
    
  • 手动清理

    手动清理二进制日志时,需要暂停业务

    # 删除指定文件之前的二进制日志
    mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
    
    # 删除指定日期之前的二进制日志
    mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
    
    # 清除所有二进制日志(危险命令,在主从环境下,主库切勿执行该操作)
    mysql> RESET MASTER;
    
  • 生成新日志

    • 执行 flush logs;
    • 重启数据库
  • 设置二进制日志默认大小

    # 在配置文件中添加以下参数,字节为单位,默认为1G
    max_binlog_size=[byte]
    

3、慢日志(slow-log)

3.1、作用

记录运行较慢的语句,是优化过程中常用的工具日志

3.2、配置慢日志

  • 查看慢查询时间
mysql> SELECT @@long_query_time;
  • 配置慢日志

在配置文件中添加以下参数

# 启用慢日志
slow_query_log=1 

# 文件位置及名字 
slow_query_log_file=/data/mysql/slow.log

# 设定慢查询时间
long_query_time=0.1

# 记录没走索引的语句
log_queries_not_using_indexes

3.3、分析慢日志

[root@localhost]# mysqldumpslow -s c -t 10 LOG_NAME

# 参数说明
-s(sort):排序
-c(count):次数
-t(top):前多少条