9. MySQL - 搜索引擎 - 日志
目录
一.搜索引擎
参考网址: 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;滚动日志。可以设置定时刷新。 - 备份时，也可以通过参数设置自动滚动。
