• 一.搜索引擎
  • InnoDB存储引擎逻辑存储结构
  • buffer pool 缓冲池
  • 事务
    • 隔离级别
• 二.日志
  • 1.错误日志 - error log
    • 作用:
    • 配置:
  • 2.慢日志 - slow log
    • 开启慢日志
  • 3.二进制日志 - bin log
    • 开启二进制日志:
    • event 事件
  • 4.数据恢复
    • 根据position号恢复数据,忽略GTID
    • 根据GTID恢复数据
      • 开启GTID
      • 基于GTID的bin log数据恢复示例
  • 5.日志过期策略
    • 自动清理
    • 手动清理
    • 如何让bin log文件序号重置从0开始呢？
    • bin log日志是如何滚动的

一.搜索引擎

参考网址: https://www.cnblogs.com/Neeo/articles/13883976.html#锁

InnoDB主要功能

多版本并发控制(MVCC)

事务

行级锁

外键

备份(热备)和恢复

自动故障恢复(ACSR)
....

InnoDB存储引擎逻辑存储结构

在InnoDB存储引擎中，所有数据都存放在表空间(tablespace)中，表空间由段(segment)、区(extent)、页(page)、行（Row）组成

buffer pool 缓冲池

事务

隔离级别

RR:RR级别解决了脏读问题并和MVCC一起解决"幻读"问题，该级别保证了在同一个事物中多次读取同样记录的结果是一致的。

该级别是MySQL的默认事务隔离级别。

RC:大多数数据库系统的默认隔离级别都是RC(但MySQL不是！)，RC满足前面提到的隔离性的简单定义：一个事务开始时，只能"看见"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。

RR与RC测试:

-- 支持 session 和 global 级别设置
set transaction_isolation='READ-UNCOMMITTED';
set transaction_isolation='READ-COMMITTED';
set transaction_isolation='REPEATABLE-READ';
set transaction_isolation='SERIALIZABLE';

-- 也支持配置文件设置 vim /etc/my.cnf
[mysqld]
transaction_isolation=READ-COMMITTED

-- 设置自动提交
set autocommit=0;
select @@autocommit; -- 查看是否自动提交
select @@tx_isolation; -- 查看隔离级别

二.日志

1.错误日志 - error log

-- MySQL默认将错误日志输出到控制台，而不是文件中stderr
-- 查看错误日志输出到哪里,如果是stderr,说明没有配置错误日志
select @@log_error;
+---------------------------------+
| @@log_error                     |
+---------------------------------+
| /data/mysql/3306/logs/mysql.log |
+---------------------------------+

作用:

错误日志的作用就是记录启动、日常运行、关闭过程中，MySQL的相关状态信息、警告信息、错误信息。

配置:

我们也可以通过在配置文件(/etc/my.cnf)中配置

• log_error：错误日志位置。
• log_error_verbosity，MySQL5.7.20版本更高版本中，用来替代log_warnings参数，用来控制服务器将错误，警告和注释消息写入错误日志的详细程度，默认值为3：

log_error_verbosity=1，将Error messages输入到文件。

log_error_verbosity=2，将Error and warning messages输入到文件。

log_error_verbosity=3，将Error, warning, and information messages输入到文件。

来配置一下，然后重启MySQL服务生效：

[root@cs ~]# vim /etc/my.cnf 

[mysqld]
log_error=/data/mysql/3306/logs/mysql_error.log
# log_error_verbosity=3

[root@cs ~]# systemctl restart mysqld

flush logs 命令用来动态的刷新错误日志文件

比如失手删除了错误日志，那么可以在不停机的情况下，来刷新日志，即该命令会检查错误日志是否存在，不存在则创建，如果文件存在则不会重新创建：

[root@cs ~]# rm -rf /data/mysql/3306/logs/mysql_error.log
[root@cs ~]# mysqladmin -uroot -p123 flush-logs		# 通过这个命令刷新log日志
[root@cs ~]# ls /data/mysql/3306/logs/

2.慢日志 - slow log

慢日志(Slow Log)，也称慢查询日志。主要记录低效的SQL语句，便于我们后续对这些低效的SQL进行针对性的优化，以提高MySQL的性能。

开启慢日志

# 默认该功能是关闭的
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+

# 开启慢日志，vim /etc/my.cnf，然后重启MySQL:
[mysqld]
slow_query_log=1
slow_query_log_file=/data/mysql/3306/logs/slow.log
long_query_time=0.2
log_queries_not_using_indexes=1

• slow_query_log：默认为0表示关闭，设置为1表示开启慢日志记录功能。
• slow_query_log_file：慢日志记录的存储位置。当然，它默认存储在你MySQL的数据目录内，名叫主机名-slow.log。但我们一般都是日志和数据分离，所以都选择自定义日志位置。
• 符合以下条件的才记录到慢日志中：
  • long_query_time：查询语句查询时间超过long_query_time时，默认是10秒。这个参数需要根据实际的应用场景来设置。
  • log_queries_not_using_indexes：查询语句没走索引时，1表示开启该功能。当然，你也可以直接将这个参数填写到配置文件中，而不用赋值，也可以的。

随便写一些查询语句，让慢语句被记录：

[root@cs ~]# cat /data/mysql/3306/logs/slow.log | head -n 1

# Time: 2021-05-13T10:34:41.712516Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 1.576822  Lock_time: 0.000166 Rows_sent: 1000000  Rows_examined: 1000000
use db1;
SET timestamp=1620902081;
select * from idb.pressure;

慢日志中主要记录了：

• 这条语句的执行时间。
• 这条语句的内容。
• 执行这条语句的客户端。
• 语句的查询时间。
• 锁定时间。
• 返回了多少行。

3.二进制日志 - bin log

二进制日志(Bin Log)，也称之为复制日志(Replication Log)，是MySQL中最重要的日志，它的主要作用是：

• 备份恢复必须依赖二进制日志
• 主从环境必须依赖二进制日志

开启二进制日志:

bin log默认是关闭的。

select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+

想要开启bin log，首先要创建一个存储路径binlog：

[root@cs ~]# mkdir /data/mysql/3306/logs/binlog
[root@cs ~]# chown -R mysql:mysql /data/mysql/3306/logs/binlog

# 修改配置文件
[root@cs ~]# vim /etc/my.cnf
[mysqld]
# mysql-bin 日志名前缀
log_bin=/data/mysql/3306/logs/binlog/mysql-bin
binlog_format=row

# 重启服务
[root@cs ~]# systemctl restart mysqld
[root@cs ~]# ll /data/mysql/3306/logs/binlog/

event 事件

什么是事件(event)？

事件是binlog的最小记录单元。

对于DDL、DCL，一个语句就是一个事件。

对于DML语句来说，一个（已提交的）事务就是一个事件

事件的作用

用于binlog的截取、分析、恢复。

一个事件有三部分组成：

• 事件的开始位置，在binlog中用pos标识(position)。注意，一个事件的开始其实也是上一个事件的结束位置。
• 事件的内容。
• 事件的结束，在binlog中用end_log_pos标识，注意，一个事件的结束其实也是下一个事件的开始位置。

那在什么情况下MySQL会自动的拆分binlog呢？

• 手动flush logs;时，该命令可以重复运行。
• MySQL服务重启后。

查看

show binary logs; -- 返回log_basename下所有的binlog文件
flush logs;	-- 手动刷新log，MySQL会自动创建一个新的bin log文件
show master status;	-- 查询MySQL正在使用的bin log文件
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1139 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

-- 然后使用show binlog events in 'bin log文件名'来查看文件内容
show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

bin log文件的position从4开始到154，都是算是头信息

4.数据恢复

根据position号恢复数据,忽略GTID

1. 进行一些操作,误删库,恢复数据

 create database db2 charset utf8;
 use db2
 create table t1(id int);
 insert into t1 values(1),(2);
 drop database db2;

**2. 截取日志 从二进制日志文件定位到上一步操作position 起始号 结束号 **

-- 1.确认当前使用的binlog日志
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

-- 2.查看当前使用的binlog日志
show binlog events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4    | Format_desc    | 6         | 123         | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123  | Previous_gtids | 6         | 154         |                                       |
| mysql-bin.000001 | 154  | Anonymous_Gtid | 6         | 219         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219  | Query          | 6         | 323         | create database db2 charset utf8      |
| mysql-bin.000001 | 323  | Anonymous_Gtid | 6         | 388         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 388  | Query          | 6         | 483         | use `db2`; create table t1(id int)    |
| mysql-bin.000001 | 483  | Anonymous_Gtid | 6         | 548         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 548  | Query          | 6         | 619         | BEGIN                                 |
| mysql-bin.000001 | 619  | Table_map      | 6         | 663         | table_id: 236 (db2.t1)                |
| mysql-bin.000001 | 663  | Write_rows     | 6         | 703         | table_id: 236 flags: STMT_END_F       |
| mysql-bin.000001 | 703  | Xid            | 6         | 734         | COMMIT /* xid=37 */                   |
| mysql-bin.000001 | 734  | Anonymous_Gtid | 6         | 799         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 799  | Query          | 6         | 870         | BEGIN                                 |
| mysql-bin.000001 | 870  | Table_map      | 6         | 914         | table_id: 236 (db2.t1)                |
| mysql-bin.000001 | 914  | Write_rows     | 6         | 954         | table_id: 236 flags: STMT_END_F       |
| mysql-bin.000001 | 954  | Xid            | 6         | 985         | COMMIT /* xid=38 */                   |
| mysql-bin.000001 | 985  | Anonymous_Gtid | 6         | 1050        | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 1050 | Query          | 6         | 1139        | drop database db2                     |
+------------------+------+----------------+-----------+-------------+---------------------------------------+

-- 3.定位到position起始号219~结束号985

-- 4.根据position号截取日志 -- mysqlbinlog
-- 起始position号 - 结束position号 - 想要截取的日志文件 - 截取到哪个文件
[root@cs ~]# mysqlbinlog --start-position=219 --stop-position=985 /data/mysql/3306/logs/binlog/mysql-bin.000001 > /tmp/bin.sql
[root@cs ~]# ls /tmp/binlog*

3. 数据恢复

-- 关闭binlog日志服务
set sql_log_bin=0;
-- 数据恢复
source /tmp/bin.sql;
-- 恢复binlog日志服务
set sql_log_bin=1;

-- 查看恢复的数据
select * from db2.t1;

根据GTID恢复数据

什么是GTID?

​ GTID，全称是Global Transation ID(全局事务ID)，它的特点是全局且唯一，也就是它为每个事务生成一个全局的唯一ID，多用于备份恢复中,

​ GTID的另一个特点是具有幂等性，简单来说，在开启GTID后，MySQL在根据bin log做数据恢复时，重复的GTID事务不会再执行了，多用于备份恢复中

开启GTID

# 编辑mysql配置文件 
[root@cs ~]# vim /etc/my.cnf

[mysqld]
gtid-mode=on		
enforce-gtid-consistency=true	-- 强制gtid的一致性

# 重启mysql服务
[root@cs ~]# systemctl restart mysqld

查看gtid

-- 由于MySQL服务重启，又生成了一个新的 binlog 文件
-- 查看当前使用日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

-- 操作数据
mysql> create database db3 charset=utf8;

-- 再次查看 - Executed_Gtid_Set 就是GTID
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 |      323 |              |                  | 98ddc71a-b12d-11eb-b85f-000c29b6f740:1 |
+------------------+----------+--------------+------------------+----------------------------------------+

上面的GTID的我们用:分为两部分

• 左半部分，是唯一的UUID，这个UUID是哪来的？这个UUID来自于MySQL初始化时或者MySQL重启时，得到的一个UUID，然后存储到了MySQL数据目录中的auto.cnf中，该UUID是当前MySQL实例的唯一的UUID，因为具有唯一性，所以GTID也用了它。
• 至于右半部分，则表示说，自从开启GTID后，bin log中总共记录了多少个事务。而我们在开启GTID后，创建了数据操作，它算一个事务，所以，右半部分是1。注意，它记录的是完整的事务。

基于GTID的bin log数据恢复示例

1. 伪造事故,进行一些操作, 误删表和库

create database db4 charset utf8;
use db4
create table t1(id int);
insert into t1 values(1),(2),(3);
create table t2(id int);
create table t3(id int);
insert into t3 values(1),(2);
drop table t3;
insert into t2 values(1),(2);
drop database db4;

2. 确定备份需要的GTID事件号, 截取日志

-- 1.查看当前使用的binlog日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000002 |     2077 |              |                  | 98ddc71a-b12d-11eb-b85f-000c29b6f740:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+

-- 2.查看日志文件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000002 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4                              |
| mysql-bin.000002 |  123 | Previous_gtids |         6 |         154 |                                                                    |
| mysql-bin.000002 |  154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:1'  |
| mysql-bin.000002 |  219 | Query          |         6 |         323 | create database db3 charset=utf8                                   |
| mysql-bin.000002 |  323 | Gtid           |         6 |         388 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:2'  |
| mysql-bin.000002 |  388 | Query          |         6 |         492 | create database db4 charset utf8                                   |
| mysql-bin.000002 |  492 | Gtid           |         6 |         557 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:3'  |
| mysql-bin.000002 |  557 | Query          |         6 |         652 | use `db4`; create table t1(id int)                                 |
| mysql-bin.000002 |  652 | Gtid           |         6 |         717 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:4'  |
| mysql-bin.000002 |  717 | Query          |         6 |         788 | BEGIN                                                              |
| mysql-bin.000002 |  788 | Table_map      |         6 |         832 | table_id: 237 (db4.t1)                                             |
| mysql-bin.000002 |  832 | Write_rows     |         6 |         882 | table_id: 237 flags: STMT_END_F                                    |
| mysql-bin.000002 |  882 | Xid            |         6 |         913 | COMMIT /* xid=16 */                                                |
| mysql-bin.000002 |  913 | Gtid           |         6 |         978 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:5'  |
| mysql-bin.000002 |  978 | Query          |         6 |        1073 | use `db4`; create table t2(id int)                                 |
| mysql-bin.000002 | 1073 | Gtid           |         6 |        1138 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:6'  |
| mysql-bin.000002 | 1138 | Query          |         6 |        1233 | use `db4`; create table t3(id int)                                 |
| mysql-bin.000002 | 1233 | Gtid           |         6 |        1298 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:7'  |
| mysql-bin.000002 | 1298 | Query          |         6 |        1369 | BEGIN                                                              |
| mysql-bin.000002 | 1369 | Table_map      |         6 |        1413 | table_id: 238 (db4.t3)                                             |
| mysql-bin.000002 | 1413 | Write_rows     |         6 |        1458 | table_id: 238 flags: STMT_END_F                                    |
| mysql-bin.000002 | 1458 | Xid            |         6 |        1489 | COMMIT /* xid=19 */                                                |
| mysql-bin.000002 | 1489 | Gtid           |         6 |        1554 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:8'  |
| mysql-bin.000002 | 1554 | Query          |         6 |        1667 | use `db4`; DROP TABLE `t3` /* generated by server */               |
| mysql-bin.000002 | 1667 | Gtid           |         6 |        1732 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:9'  |
| mysql-bin.000002 | 1732 | Query          |         6 |        1803 | BEGIN                                                              |
| mysql-bin.000002 | 1803 | Table_map      |         6 |        1847 | table_id: 239 (db4.t2)                                             |
| mysql-bin.000002 | 1847 | Write_rows     |         6 |        1892 | table_id: 239 flags: STMT_END_F                                    |
| mysql-bin.000002 | 1892 | Xid            |         6 |        1923 | COMMIT /* xid=21 */                                                |
| mysql-bin.000002 | 1923 | Gtid           |         6 |        1988 | SET @@SESSION.GTID_NEXT= '98ddc71a-b12d-11eb-b85f-000c29b6f740:10' |
| mysql-bin.000002 | 1988 | Query          |         6 |        2077 | drop database db4                                                  |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+

-- 3.筛选需要的GTID号 - 2~9 不要8
-- 4.截取日志 
mysqlbinlog --skip-gtids --include-gtids='98ddc71a-b12d-11eb-b85f-000c29b6f740:2-9' --exclude-gtids='98ddc71a-b12d-11eb-b85f-000c29b6f740:8' /data/mysql/3306/logs/binlog/mysql-bin.000002 > /tmp/gtid.sql

参数:

• --skip-gtids : 截取的数据导出到文件时，忽略掉GTID，那么在恢复时，MySQL就会认为是新操作，达到数据恢复的目的
• --include-gtids : 截取日志包括的gtid号
• --exclude-gtids : 截取日志不包含的gtid号, 多个逗号分隔

3. 恢复数据

-- 关闭binlog日志服务
set sql_log_bin=0;
-- 数据恢复
source /tmp/gtid.sql;
-- 恢复binlog日志服务
set sql_log_bin=1;

-- 查看db4数据库恢复情况
show databases;
select * from db4.t1;

5.日志过期策略

我们还需要了解如何清理bin log，也就是了解bin log日志的过期策略

首先，不能使用系统的rm或者别的手段来清理日志，而是要采用MySQL的命令来清理日志

自动清理

-- 查看 0代表永不过期
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+

-- 设置过期时间
set global expire_logs_days=15;

-- 或者配置文件设置 vim /etc/my.cnf
[mysqld]
expire_logs_days=15

手动清理

-- 根据当前时间，删除指定天数以前的日志
purge binary logs before now() - interval 3 day;

-- 将指定bin log文件序号前的都删除，如将000010号文件之前的文件都删除
purge binary logs to 'mysql-bin.000010';

如何让bin log文件序号重置从0开始呢？

-- 慎用(禁止)的命令，尤其是主从环境下禁止在主库使用，但单实例情况下可以用
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+

bin log日志是如何滚动的

这里总结下bin log日志的滚动方式：

• 重启MySQL时。
• 当文件大小达到max_binlog_size值的上限时。

-- 默认是1G大小
mysql> select @@max_binlog_size /1024/1024;
+------------------------------+
| @@max_binlog_size /1024/1024 |
+------------------------------+
|                1024.00000000 |
+------------------------------+

• 通过flush logs;滚动日志。可以设置定时刷新。
• 备份时，也可以通过参数设置自动滚动。