binlog日志_MySQL系列InnoDB核心特性之日志管理

系列文章说明

MySQL系列文章包含了软件安装、具体使用、备份恢复等内容，主要用于记录个人的学习笔记，主要使用的MySQL版本为5.7.28，服务器系统版本为CentOS 7.5。本章节内容为InnoDB核心特性的binlog记录和恢复。

错误日志

        错误日志主要是记录MySQL从启动依赖，所有的状态：警告、错误等。为用户定位数据库问题提供帮助。

  错误日志默认是开启状态，默认情况下在用户指定的datadir目录下，可用以下方式进行查询

select @@datadir;

    错误日志文件具体路径为：

@@datadir/${hostname}.err

    如果需要将错误日志放置在其它目录，可以在my.cnf中进行设置：

vi /etc/my.cnf[mysqld] log_error=/目录/文件

    请确保该目录有读写权限

慢日志slowlog

        慢日志的作用是记录MySQL运行过程中较慢的语句，通过一个文本的文件记录下来，帮助我们进行语句优化工具查询。

  慢日志默认是没有开启的，可以通过以下语句进行查询慢日志的状态：

select @@slow_query_log;

        值为0表示没有开启，值为1表示开启。

  用户可以使用以下命令临时开启慢日志记录：

set global slow_query_log=1;

        如需要慢查询日志配置永久生效，可在my.cnf中添加以下内容即可：

vi my.cnfslow_query_log=1   # 启动慢日志slow_query_log_file=/data/3306/db01-slow.log  #设置慢日志文件位置long_query_time=0.1   # 操作时间超过0.1s的事件记录到慢日志中log_queries_not_using_indexes=1   # 记录没有使用到索引的查询语句

        配置后，应重启mysqld服务生效。

  查询慢日志文件所在路径：

select @@slow_query_log_file;

slowlog

模拟慢日志

        导入测试表t100w(微信公众号发送t100w获取该文件)

source t100w.sqluse test;show tables;

        模拟慢查询：

select * from t100w where id>10 and id <20;

        该语句执行用了0.42s，超过了我们定义的0.1s，所以会被记录到慢日志用。

  查看慢日志：

mysqldumpslow  db01-slow.log 

        如果慢日志过多的时候，可以使用以下命令统计出现最多个5个慢语句类别：

mysqldumpslow -s c -t 5 db01-slow.log

        如：select * from t100w where id > 10 and id < 20 和 select * from t100w where id >100 and id < 200是同类别的。

binlog

        binlog的作用是记录数据库变化(DDL，DCL、DML)性质的日志，binlog是逻辑层性质日志。在数据恢复，主从复制中应用。

 在mysql8.0之前的版本，binlog默认是没有开启的，生产中建议开启binlog，并将日志信息保存到导读的盘或者分区中。

 查看binlog的状态：

mysql> select @@log_bin;+-----------+| @@log_bin |+-----------+|         0 |            # 0为未开启，1未开启+-----------+1 row in set (0.00 sec)

        开启binlog：

vi my.cnf[mysqld]server_id=6  # 设置服务器idlog_bin=on # 启动binloglog_bin=/binlog/3306/mysql-bin  # 设置文件位置和文件前缀sync_binlog=1           # 每次事务提交，必然保证binlog cache中的日志落到磁盘binlog_format=row       # 以row方式记录

        重启mysqld服务即可生效。

MySQL“双一模式”：

sync_binlog=1;

    取值为0：log buffer每秒写入日志文件log file并刷新flush到磁盘。这种情况下，MySQL的日志刷写操作和事务提交操作没有关系。因此MySQL的性能是最好的时刻。不过不安全

    取值为1：每次事务提交时，log buffer会被写入到日志文件并且还要刷写到磁盘上。由于每次事务都要提交到I/O设备，因此会慢一点，不过是最安全的。

    取值为2：0和1的中间效果，即每次的事务提交会写入log buffer，而刷写到磁盘则是一秒进行一次。性能属于一般。

innodb_flush_log_at_trx_commit=1;

取值为0：MySQL自己不主动同步，依赖操作系统本身不定期把文件内容刷新到磁盘。性能最佳

取值为1：每次事务提交后将binlog_cache中的数据强制写入磁盘bin log日志中，是最慢的，但是最安全

取值 >1：当进行n次事务提交后，MySQL将binlog_cache中的数据强制写入磁盘中。

当两个参数都设为1，这适用于那些数据安全要求比较高的场合，同时要求磁盘等也比较好才行。

        临时关闭当前会话的binlog记录：

set sql_log_bin=0;

binlog

binlog_format

        binlog_format参数设置了记录内容的方式

 1、statement(5.6默认)SBR(statement based replication)：语句模式原封不动的记录当前DML

 2、row(5.7默认值)RBR(ROW base replication)：记录数据行的变化(用户看不懂，需要工具分析)

 3、mixed(混合)MBR(mixed based replication)模式：以上两种模式的混合

SBR和RBR模式的对比

statement：可读性比较高，日志量比较少，但是不够严谨

row：可读性低，日志量大，足够严谨

比如update t1 set xxx=xxx where id>1000  此时statement只记录了该命令，而row会记录这些行的变化

为什么说row模式严谨

id       name    intime

insert into t1 values(1,'zs',now()) 如果是statement，now会是当前时间，而row记录了当时的时间

binlog

event事件

        事件是二进制日志的最小记录单元，对于DDL，DCL，一个语句就是event，对于DML来讲，只记录已提交的事务。

事件由三部分构成：

   1、事件的开始标识

   2、事件内容

           3、事件的结束标识

Position：开始标识：at 194结束标识：eng_log_pos 254开始和结束标识，是否个事件在binlog的相对位置号位置号(position)的作用是为了方便我们截取事件

        以下例子，就被分为了4个event

        startposition(开始)   stopposition(结束)begin;         120 - 340DML1         340 - 460DML2         460 - 550commit;       550 - 760

binlog

查询binlog

        查看binlog文件：

mysql> select @@log_bin_basename;+------------------------+| @@log_bin_basename     |+------------------------+| /binlog/3306/mysql-bin |+------------------------+

        查看所有的binlog文件：

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       201 || mysql-bin.000002 |      1931 || mysql-bin.000003 |      1599 || mysql-bin.000004 |      1337 || mysql-bin.000005 |     70048 || mysql-bin.000006 |  21103050 || mysql-bin.000007 |       194 |+------------------+-----------+7 rows in set (0.00 sec)

        查看当前使用的binlog文件：

mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql-bin.000007 |      194 |              |                  |                                            |+------------------+----------+--------------+------------------+--------------------------------------------+

        查看某个binlog文件的内容：

show binlog events in 'mysql-bin.000002';

        查看文件内容：

mysqlbinlog mysql-bin.000004 > /tmp/123  # 每个at都是一个事件的开始# DDL可读模式，仅限读mysqlbinlog --base64-output=decode-row -vvv mysql-bin.000004 > /tmp/456

binlog

binlog维护操作

        查看默认日志大小设置(超过会写到下一个文件)

mysql> select @@max_binlog_size;+-------------------+| @@max_binlog_size |+-------------------+|        1073741824 |+-------------------+1 row in set (0.00 sec)

        删除日志

# 手动删除purge binary logs to 'mysql-bin.00010' # 删除1-9reset master; # 全部清空，如果在主库上操作，主从必宕# 设置自动删除 默认是0天，代表永不删除mysql> select @@expire_logs_days;+--------------------+| @@expire_logs_days |+--------------------+|                  0 |+--------------------+1 row in set (0.00 sec)set global expire_logs_days=7;   # 设置删除周期为7天，生产中请设置合适的值

        滚动日志

# 生成一个新的日志flush logs;

end