Mysql日志系统-mysql serve层

Mysql日志系统-服务层的日志

mysql给我们提供了很多有用的日志有mysql服务层提供的，有innodb引擎层提供的，下表是mysql服务层给我们提供的：

日志类型	写入日志的信息
二进制日志	记录了对MySQL数据库执行更改的所有操作
慢查询日志	记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
错误日志	记录在启动，运行或停止mysqld时遇到的问题
通用查询日志	记录建立的客户端连接和执行的语句
中继日志	从复制主服务器接收的数据更改

一、bin log日志

1、概述

二进制日志（binnary log）以【事件形式】记录了对MySQL数据库执行更改的所有操作。

binlog：

1. 记录了所有数据库【表结构】变更（例如CREATE、ALTER TABLE…）的二进制日志。
2. 记录了所有数据库【表数据】修改（INSERT、UPDATE、DELETE…）的二进制日志。
3. 但是不会不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有的更新操作的日志记录。

binlog是在事务最终commit前写入的。

我们执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。

binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

binlog有两个常用的使用场景：

1. 主从复制：我们会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。
2. 数据恢复：通过mysqlbinlog工具来恢复数据。

mysql8中的binLog默认是开启的，5.7默认是关闭的，可以通过参数log_bin控制;

2、数据恢复

（1）确认binlog开启，log_bin变量的值为ON代表binlog是开启状态：

show variables like '%log_bin%';

（2）为了防止干扰，我们flush刷新log日志，会结束当前bin log，自此刻会产生一个新编号的binlog日志文件：

flush logs;

（3）查看所有binlog日志列表：

show master logs;

（4）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值，这一步可有可无：

（5）执行sql

先创建表，并插入一些数据：

DROP TABLE IF EXISTS my_student;
CREATE TABLE `my_student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd');
INSERT INTO `my_student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');

执行删除操作，假装误删除，直接全部删除也可以，把表删了都行，一样的道理：

delete from my_student;

（6）查看binlog日志，我们因为刷新了日志，所以本次操作都会在最新的日志文件上：

因为 binlog 的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容：

cmd打开输入mysqlbinlog：

进入到binlog的目录内,打开cmd输入下列命令，查看全部的日志信息

# 进入到binlog的目录内,打开cmd输入下列命令，查看全部的日志信息
mysqlbinlog -v SAYHELLO-bin.000096

指定位置/时间查看binlog：

# 指定位置范围
mysqlbinlog -v SAYHELLO-bin.000096 --start-position=0 --stop-position=1000
# 指定时间范围
mysqlbinlog -v SAYHELLO-bin.000096 --start-datetime="2023-03-21 21:47:08" --stop-datetime="2023-03-21 21:50:08" 

真实的情况下，我们的日志文件比较复杂，内容比较多使用时间范围查询后任然可能需要花费时间去排查问题，这里我们找到了误删除的位置：

（7）执行恢复，通过上一步的操作，我们找到了删除的位置3972（即第一个蓝框），执行下面的语句：

在这里插入图片描述

mysqlbinlog -v SAYHELLO-bin.000096 --stop-position=3972 -v | mysql -uroot -p

报错：ERROR 1007 (HY000) at line 36: Can't create database 'bin_test'; database exists，因为它会将我们binlogw文件里面的数据再从头执行一遍到3972的位置，所以我们可以先备份数据库，再删除数据库。

恢复成功。

（8）至此，数据已完全恢复了：

binlog的数据恢复的本质：

binlog的数据恢复的本质就是将之前执行过的sql，从开始到指定位置全部执行一遍，如果报错【当前表已经存在】，就将数据库的表删除，重新恢复。

当然，也可以指定位置/时间段进行恢复。

3、格式分类

binlog 有三种格式， 使用变量binlog_format查看当前使用的是哪一种：

1. Statement（Statement-Based Replication，SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
2. Row（Row-Based Replication，RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
3. Mixed（Mixed-Based Replication，MBR）：Statement 和 Row 的混合体，当前默认的选项，5.7中默认row。

例子：

我们举一个例子来说明row和statement的区别，在下面的插入语句中我们有一个函数uuid()，如果日志文件仅仅保存sql语句，下一次执行的结果可能不一致，所以Row格式的文件，他保存的是具体哪一行，修改成了什么数据，记录的是数据的变化，不是简单的sql：

#下图是statement格式，uuid()每次执行得到的结果不一样-》结果不一致问题
insert into my_student values (8,UUID(),45,'d');

::: warning Statement和row的优劣：

:::

• Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。
• 由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。
• 从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
• 不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

4、日志格式

• binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。
  • header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等。
  • data部分提供的是针对该event的具体信息，如具体数据的修改。

常见的事件类型有：

• FORMAT_DESCRIPTION_EVENT：该部分位于整个文件的头部，每个binlog文件都必定会有唯一一个该event
• WRITE_ROW_EVENT：插入操作。
• DELETE_ROW_EVENT：删除操作。
• UPDATE_ROW_EVENT：更新操作。记载的是一条记录的完整的变化情况，即从前量变为后量的过程
• ROTATE_EVENT：Binlog结束时的事件，用于说明下一个binlog文件。

一个event的结构如下，我们在恢复数据的时候已经看到了：

• 每个日志的最后都包含一个rotate event用于说明下一个binlog文件。
• binlog索引文件是一个文本文件，其中内容为当前的binlog文件列表，比如下面就是一个mysql-bin.index文件的内容。

5、binlog刷盘

二进制日志文件并不是每次写的时候同步到磁盘。

​ 因此当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这给恢复和复制带来了问题。
参数sync_binlog=[N]表示每写多少次就同步到磁盘：

• 如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志，就是每次写操作就会耍哦按。（备注：该值默认为0，采用操作系统机制进行缓冲数据同步）。

6、binlog实现主从同步

::: danger 数据库单点部署的问题：

:::

1. 服务器宕机，会导致业务停顿，影响客户体验。
2. 服务器损坏，数据丢失，不能及时备份，造成巨大损失。
3. 读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈。

那么我们就可以使用mysql的binlog搭建一个一主多从的mysql集群服务。

这样的服务可以帮助我们异地备份数据、进行读写分离，提高系统的可用性。

（1） 主从复制工作原理剖析

• Master 数据库只要发生变化，立马记录到Binary log 日志文件中
• Slave 数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志
• Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
• Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据

（2）怎么配置mysql主从复制

---

环境准备

安装两个mysql，使用vmvare安装两个linux系统就可以：

mysql1(master): 42.192.181.133:3306
mysql2(slave):  124.220.197.17:3306

mysql 配置文件配

mysql1（master）: 配置文件设置，开启bin_log（已经开启的可以忽略）且需要配置一个server-id

#mysql master1 config 
[mysqld]
server-id = 1            # 节点ID，确保唯一

# log config
log-bin = master-bin     #开启mysql的binlog日志功能

mysql2（slave）: 需要开启中继日志

[mysqld]
server-id=2
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

重启两个mysql，让配置生效。

第三步 在master数据库创建复制用户并授权

1.进入master的数据库，为master创建复制用户

CREATE USER 'repl'@'124.220.197.17' IDENTIFIED BY 'Root12345_';

2.赋予该用户复制的权利

grant replication slave on *.* to 'repl'@'124.220.197.17' 
FLUSH PRIVILEGES;

3.查看master的状态

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

4，配置从库

CHANGE MASTER TO 
MASTER_HOST = '42.192.181.133'，  
MASTER_USER = 'repl'， 
MASTER_PASSWORD = 'Root12345_'，
MASTER_PORT = 3306，
MASTER_LOG_FILE='mysql-bin.000020'，
MASTER_LOG_POS=2735，
MASTER_HEARTBEAT_PERIOD = 10000; 

# MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS=120 ， #与主库Position 保持一致

解释：MASTER_HEARTBEAT_PERIOD表示心跳的周期。当MASTER_HEARTBEAT_PERIOD时间之内，master没有binlog event发送给slave的时候，就会发送心跳数据给slave。

5.启动从库slave进程

mysql> start slave;
Query OK， 0 rows affected (0.04 sec)

6.查看是否配置成功

show slave status \G;

• Slave_IO_Running：从库的IO线程，用来接收master发送的binlog，并将其写入到中继日志relag log

• Slave_SQL_Running：从库的SQL线程，用来从relay log中读取并执行binlog。

• Slave_IO_Running、Slave_SQL_Running：这两个进程的状态需全部为 YES，只要有一个为 NO，则复制就会停止。

• Master_Log_File：要同步的主库的binlog文件名。

• Read_Master_Log_Pos：已同步的位置，即同步的 binlog 文件内的字节偏移量，该值会随着主从同步的进行而不断地增长。

• Relay_Log_File：从库的中继日志文件，对接收到的主库的 binlog 进行缓冲。从库的SQL线程不断地从 relay log 中读取 binlog 并执行。

• Relay_Log_Pos：relay log 中已读取的位置偏移量。

• Seconds_Behind_Master: 主从同步延时， 值为 0 为正常情况，正值表示已经出现延迟，数字越大从库落后主库越多。

7.在主库创建一个数据库、创建一张表，执行一些sql语句进行测试。

（3）可能遇到的问题

在配置mysql主从复制的时候可能出现一下错误：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

原因

如果你使用了两台虚拟机，一主一从，从库的mysql是直接克隆的。在mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接克隆data文件夹后server_uuid是相同的。

解决

找到data文件夹下的auto.cnf文件，修改里面的server_uuid值，保证各个db的server_uuid不一样，重启db即可。

 cd /www/server/data

修改server_uuid的值

使用

select uuid();

生成一个uuid即可，重启数据库。