二进制日志

二进制日志

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

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

https://www.cnblogs.com/liuhaidon/archive/2019/09/09/11493292.html

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

1. 作用

• 主从复制

  因为从服务器需要到主服务器里拷贝二进制日志，然后根据二进制日志的内容去执行SQL，从而达到主从服务器里的数据一模一样。

  

• 用来恢复数据

  1. 9：00创建了一个库"sanchaung t1"，进行了备份

  2. 9~10点中间发生了很多事，如DML操作

  3. 10：01误操作，删除了sanchuang库：drop database sanchuang

  4. 恢复：

     • 先恢复9：00的备份
     • 根据二进制日志重做一遍DML操作。

     

• 日志审计：

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

  MySQL的注入攻击：

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

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

  ​ 攻击原理：

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

  ' OR 1=1 #
  

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

  原文链接：https://blog.csdn.net/qq_44798703/article/details/129224471

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'
   

3. 自动清除日志

   #一次性的（不推荐：因为服务重启，配置失效）：
   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

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)

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