二进制日志

记录执行的SQL语句(对数据进行修改，增加，删除等)

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、DELETE、UPDATE…）的二进制日志。

二进制日志不是存储引擎管理的，是MySQL内部的相关线程去完成

1. 作用

  • 主从复制
    因为从服务器需要到主服务器里拷贝二进制日志，然后根据二进制日志的内容去执行SQL，从而达到主从服务器里的数据一模一样。
  • 用来恢复数据
  1. 9：00创建了一个库"sanchaung t1"，进行了备份
  2. 9~10点中间发生了很多事，如DML操作
  3. 10：01误操作，删除了sanchuang库：drop database sanchuang
  4. 恢复：
  • 先恢复9：00的备份
  • 根据二进制日志重做一遍DML操作。

mysql查看二进制日志文件保存多久_mysql

  • 日志审计：
    用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

MySQL的注入攻击：

SQL注入攻击是黑客对数据库进行攻击的常用手段之一。

SQL注入攻击是一种利用Web应用程序对SQL语句的输入验证不严格的漏洞，将恶意代 码插入到SQL语句中的攻击行为。通过这种攻击，攻击者可以绕过应用程序的认证和授权机制，直接访问数据库中的敏感信息，或者执行恶意的SQL语句，导致数据库数据被破坏、泄漏或者被盗取。

攻击原理：

攻击者在输入框中输入特殊字符或SQL语句，以欺骗应用程序，使其将输入的字符当成 SQL语句的一部分来执行。例如，攻击者可以在输入框中输入以下内容：

' OR 1=1 #

这个输入的含义是让数据库返回所有记录，因为1=1永远是成立的，注释符#表示忽略后面的语句。如果应用程序的代码没有正确处理这样的输入，那么它就会将这个字符串作为SQL查询语句的一部分，导致攻击者可以访问数据库中的敏感信息，甚至控制整个系统。

原文链接：

2. 默认关闭

(1)存放位置

/data/mysql/hostname-bin.00000*

(2) 开启

vim /etc/my.cnf
	log_bin
	server_id=1
service mysqld restart
root@(none) 16:18  mysql>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
  • server_id 是服务器的唯一标识，在主从复制的时候使用，每台服务器的id不能一样，不然会导致主从复制失败

3. 查看二进制日志

  • mysqlbinlog
[root@ln-mysql mysql]# mysqlbinlog ln-mysql-bin.000001
# at 123
#230318 16:51:49 server id 1  end_log_pos 154 CRC32 0xac61a6d2 	Previous-GTIDs
时间                主机           位置
  • -vv：看到的信息更加详细
  • 记录具体的事件的位置，时间点。方便恢复数据
  • ln-mysql-bin.index：
    记录一共有多少个二进制日志文件，是二进制文件的索引
[root@ln-mysql mysql]# cat ln-mysql-bin.index 
./ln-mysql-bin.000001
./ln-mysql-bin.000002
./ln-mysql-bin.000003
  • 二进制文件大小
    最大为1G
root@(none) 16:25  mysql>show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

root@(none) 16:29  mysql>select 1073741824/1024/1024/1024;
+---------------------------+
| 1073741824/1024/1024/1024 |
+---------------------------+
|            1.000000000000 |
+---------------------------+
1 row in set (0.00 sec)
  • 查看所有的二进制文件和大小
SHOW BINARY LOGS
  • 查看当前正在使用那个二进制文件和位置
SHOW MASTER STATUS
SHOW BINLOG EVENTS

4. 产生新的二进制日志文件

产生后会写入到新的日志文件，不会再写入到旧的了。

  1. service mysqld restart
  2. 当日志达到最大值1G时
  3. flush logs

5. 删除所有的二进制日志

  1. reset master
    还留下了唯一一个：%.000001
  2. 手动清理
#将指定时间之前的日志清理
purge binary logs before '2023-02-01 12:00:00';

#将指定日志文件之前的日志清除
purge binary logs to 'Mysql-bin.000003'
  1. 自动清除日志
#一次性的（不推荐：因为服务重启，配置失效）：
set global expire_logs_days=15;

#一劳永逸（改配置）：
expire_logs_days=15
...
root@(none) 16:51  mysql>show variables like "%expire_logs_days%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

#0代表永久不失效

6. 格式

6.1 row level

记录操作的每一行数据 --》表里的哪些行的数据发生了变化

root@(none) 17:13  mysql>select version();
+------------+
| version()  |
+------------+
| 5.7.40-log |
+------------+
1 row in set (0.00 sec)

root@(none) 17:17  mysql>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
  • 默认是row
  • 比statement好：
    如果记录SQL语句，当主机输入一条SQL语句时，从主机会跟着做，但是会产生时间差。
    但是row不会，什么时候操作的它就会记录下来同样的时间操作。

6.2 statement level

记录用户输入的SQL语句

6.3 mixed level

混合

7. 日志记录过程

二进制日志保存下来有2个过程：

  • flush：将二进制日志写到binlog_buffer里
  • sync：将binlog_buffer里的内容刷盘到disk里binlog file

mysql查看二进制日志文件保存多久_mysql_02

7.1 刷盘

将缓存中的日志刷到磁盘上。

（1）刷盘时机

是指什么时候，通过什么策略将内存日志写入到磁盘中。

（2）刷盘时机参数
  • sync_binlog=[N]: 表示写缓冲多少次，刷一次盘。默认值为0。取值是 0、1 和 N 三种值。
  • sync_binlog=0: 表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘，这个性能最好。

OS Buffer满了之后才会将日志写入到磁盘中。容易丢失数据，如停电。

  • sync_binlog=1: 表示每次事务提交都要调用fsync()，刷新binlog写入到磁盘。

——能快速存储数据，不容易丢失数据

  • sync_binlog=N: 表示 N个事务提交，才会调用 fsync()进行一次binlog刷新，写入磁盘。
  • binlog_cache_size: 二进制日志缓存部分的大小，默认值32k。设置过大，会造成内存浪费。设置过小，会频繁将缓冲日志写入临时文件。
root@(none) 19:22  mysql>show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |                     默认
+---------------+-------+
1 row in set (0.00 sec)

root@(none) 19:22  mysql>show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)

mysql查看二进制日志文件保存多久_数据库_03

附带 innodb架构图（来源于MySQL官网）

mysql查看二进制日志文件保存多久_mysql_04