Kill Anxiety-MySQL日志文件

1. 准备数据库

docker-compose.yml

version: '2'
services:
  MySQL56:
    image: mysql:5.6
    container_name: MySQL56
    hostname: MySQL56
    privileged: true
    environment:
      - MYSQL_ROOT_PASSWORD=1
      - TZ=Asia/Shanghai
    ports:
      - "3306:3306"
    volumes:
      - "./conf/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf"
      - "./data:/var/lib/mysql"
    command: /bin/bash -c "chmod 644 /etc/mysql/mysql.conf.d/mysqld.cnf && /entrypoint.sh mysqld"
    networks:
      - mysql_net
networks:
  mysql_net:
    driver: bridge

my.cnf

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

## 开启二进制日志功能
log-bin=mysql-bin

2. 慢日志

3. 查询日志

4. 二进制日志（binary log）

二进制日志(binary log) 记录了执行更改的所有操作，但不包括SELECT和SHOW这类操作，因为这类操作对数据本身没有修改。

另外，若操作本身没有导致数据库发生变化，该操作可能也会写入二进制日志。

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      309 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.51-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query       |         1 |         214 | create database test                  |
| mysql-bin.000004 | 214 | Query       |         1 |         309 | use `test`; create table t(a int)     |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> update t set a=1 where a=2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                   |
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
| mysql-bin.000004 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.51-log, Binlog ver: 4  |
| mysql-bin.000004 | 120 | Query       |         1 |         214 | create database test                   |
| mysql-bin.000004 | 214 | Query       |         1 |         309 | use `test`; create table t(a int)      |
| mysql-bin.000004 | 309 | Query       |         1 |         388 | BEGIN                                  |
| mysql-bin.000004 | 388 | Query       |         1 |         488 | use `test`; update t set a=1 where a=2 |
| mysql-bin.000004 | 488 | Query       |         1 |         568 | COMMIT                                 |
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                   |
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
| mysql-bin.000004 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.51-log, Binlog ver: 4  |
| mysql-bin.000004 | 120 | Query       |         1 |         214 | create database test                   |
| mysql-bin.000004 | 214 | Query       |         1 |         309 | use `test`; create table t(a int)      |
| mysql-bin.000004 | 309 | Query       |         1 |         388 | BEGIN                                  |
| mysql-bin.000004 | 388 | Query       |         1 |         488 | use `test`; update t set a=1 where a=2 |
| mysql-bin.000004 | 488 | Query       |         1 |         568 | COMMIT                                 |
+------------------+-----+-------------+-----------+-------------+----------------------------------------+
6 rows in set (0.01 sec)

二进制文件的作用：

• 恢复（recovery）
• 复制（replication）
• 审计（audit），用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

参数log-bin[=name]可以启动二进制日志。若不指定，则默认二进制日志文件名为主机名，后缀为序列号，所在路径为数据库所在目录(datadir)

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> system ls -ll /var/lib/mysql |grep 'mysql-bin';
-rw-rw---- 1 mysql mysql    69417 Mar  3 12:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql  1641540 Mar  3 12:07 mysql-bin.000002
-rw-rw---- 1 mysql mysql  7018132 Mar  3 12:07 mysql-bin.000003
-rw-rw---- 1 mysql mysql      568 Mar  3 12:09 mysql-bin.000004
-rw-rw---- 1 mysql mysql       76 Mar  3 12:07 mysql-bin.index

默认情况下没有开启bin-log，需要手动指定参数来启动。

根据 MySQL 官方手册中的测试表明，开启二进制日志，会使性能下降1%。但考虑到可以使用复制和point-in-time的恢复，这个损失是可以接受的。

5. 重做日志

默认情况下，数据目录中的 ib_logfile0,ib_logfile1 两个文件就是重做日志文件。

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> system ls /var/lib/mysql;
MySQL56-slow.log  ib_logfile0  mysql             mysql-bin.000003  mysql-bin.000006  performance_schema
MySQL56.pid       ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  test
auto.cnf          ibdata1      mysql-bin.000002  mysql-bin.000005  mysql-bin.index

重做日志文件记录了 InnoDB存储引擎的事务日志。

当实例或介质失败时（如：主机掉电导致实例失败），InnoDB会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

重做日志与二进制日志的区别

不同点	二进制日志	重做日志
记录对象不同	记录所有与MYSQL数据库有关的日志，包括InnoDB,MyISAM,Heap等其它存储引擎的日志。	只记录InnoDB引擎的的事务日志。
记录内容不同	只记录一个事务的具体操作内容，是逻辑日志。	记录关于每个页（Page）的更改的物理情况。
记录时间不同	仅在事务提交前进行提交。不论该事务有多大，只写磁盘一次。	事务进行过程中，不断有重做日志条目写入到重做日志文件。

参考：
MySQL技术内幕(InnoDB存储引擎)第2版