六.吊打面试官系列-数据库优化-深入Mysql日志机制

置顶 已于 2024-04-23 13:53:29 修改
阅读量1k 收藏 31
点赞数 8
分类专栏： 《升职加薪之深入Mysql性能优化》 《升职加薪系列》 文章标签： 数据库 mysql android
于 2024-04-21 17:06:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014494148/article/details/137865837
版权

前言

在Mysql中有三个非常重要的日志文件：Undolog ,Redolog,Binlog。Undolog是用作原子性保证的日志文件，MVCC多版本快照也是存储在Undolog日志文件中。Redolog是Mysql持久化日志文件,如果Mysql宕机数据丢失可以通过Redolog还原数据，Binlog也可以用作数据还原以及实现主从复制。本篇文章我们一起来研究一个下三种日志文件底层的执行原理以及相关参数配置。

Mysql执行流程

这里我把Mysql的执行流程，包括底层的三个日志文件如何协作的整个细节用一个图画出来，再配上文字进行理解

在这里插入图片描述
mysql主要分为Server层和存储引擎层，Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。InnoDB 5.5版本作为默认引擎。

Server层

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

查看Mysql是否启用缓存使用下面的SQL

show global variables like "%query_cache_type%";

需要修改查询缓存配置在my.cnf 中修改

 query_cache_type=2

query_cache_type有3个值

  • 0代表关闭查询缓存OFF，
  • 1代表开启ON，
  • 2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒,SQL语句经过分析器分析之后，会生成一个这样的语法树
在这里插入图片描述

完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用存储引擎的接口，返回接口执行的结果。

存储层

IDB磁盘文件

Mysql的数据存储在IDB磁盘文件中，默认在C盘，比如我有一个 test数据库中有一个t_user 表，那么在C盘就会有这样一个文件 C:\ProgramData\MySQL\MySQL Server 5.7\Data\test:如下
在这里插入图片描述

  • frm : 是表结构文件，定义了t_user表的结构，有哪些字段等
  • idb : 是数据文件，如果是InnoDB引擎数据和索引都在改文件中。

如果是MyIsam的话应该是三个文件：FRM 表结构，MYD ：数据文件，MYI:索引文件，这里不展开讨论，在索引篇中有说道。

那么当执行器在执行 一条SQL比如：update set amount = 200 from account where id = 1 的时候，会先从IDB磁盘数据文件中加载改数据到 buffer pool 中，当然Mysql是以一个数据页的方式进行加载的。

buffer pool 是Mysql的缓存空间，所有的数据修改都是基于这个缓存空间完成修改的。这个空间一般要设置大一点,但不是说设置到最大就最好，还需要给系统留一些内存，一般设置为机器内存的：60%左右。在my.cnf配置文件中修改buffer pool如下

[mysqld]
innodb_buffer_pool_size = 2048M
UndoLog日志

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，每个事务只会使用一个undo log segment。

在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

下面是设置undolog 日志的指令

  • innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
[mysqld]
innodb_undo_directory = /path/to/undo_dir

在这里插入图片描述

  • innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
[mysqld]
innodb_undo_logs = 128
  • innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。
[mysqld]
innodb_undo_tablespaces=4

undo log日志什么时候删除? 新增类型的，在事务提交之后就可以清除掉了 ; 修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

RedoLog日志

RedoLog日志的作用是用作数据的持久化，在Mysql提交事务之前会优先把数据写入RedoLog，如果MySql内存中的数据丢失可以采用RedoLog来还原数据，RedoLog的顺序IO在性能上得到保证。至于buffer pool中的数据是在系统空闲时异步写入IDB数据文件的。

所以：为什么不直接把数据刷盘到IDB数据文件中，而是要使用RedoLog持久化呢？主要原因是提高性能。Mysql的IDB数据文件是属于随机写，而且体积较大，相比小体积的RedoLog顺序写方式性能差很多。我们可以试想一下如果每次更新数据都写IDB然后进行随机刷盘相对而言性能是很差的。

RedoLog也有一个RedoLog buffer 缓存，在写RedoLog的时候会把数据写入buffer,然后再把buffer中的数据写入RedoLog磁盘文件,目的也是为了提高性能，RedoLog有有三种同步方式可以通过show variables like 'innodb_flush_log_at_trx_commit';来查看，如果要修改使用set global innodb_flush_log_at_trx_commit=1; 参数来控制

  1. 设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数
    据。
  2. 设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
  3. 设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

Redolog采用轮询覆盖的方式来写入文件，Redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写。它维护了两个位置：write pos 写入位置，一边写一边后移，checkpoint 是当前要擦除的位置。
在这里插入图片描述

RedoLog相关参数如下

  1. innodb_log_buffer_size ：RedoLog buffer大小参数，默认16M ，最大值是4096M，最小值为1M。可以通过 show variables like '%innodb_log_buffer_size%'; 来查看，

  2. innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件 ，可以通过 show variables like '%innodb_log_group_home_dir%'; 来查看
    在这里插入图片描述

  3. innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1… iblogfileN。默认2个，最大100个。可以通过show variables like '%innodb_log_files_in_group%'; 来查看

  4. innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G，可以通过 show variables like '%innodb_log_file_size%'; 来查看

BinLog日志

BinLog的目的是用来还原IDB数据，如果磁盘文件被误删除了可以使用BinLog进行还原，它是一个二进制文件，记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。binlog文件默认会放在data数据目录下
在这里插入图片描述

启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。通过 show variables like '%log_bin%'可以查看BinLog日志文件 。在5.x版本BinLog默认是关闭的，在Mysql8.0之后默认打开，可以通过下面配置来控制

log‐bin=mysql‐binlog #设置log-bin的存放位子，默认在data目录
server‐id=1  #服务ID，不允许重复

binlog_format = row # 日志文件格式
expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

binlog_format = row # 日志文件格式，有三种选择 STATEMENT ，ROW，MIXED

  • STATEMENT ：记录的是写SQL(增删改),有点是体积小性能高,对上对于SYSDATE()等函数无法确定一个固定值，在不同的时间点结果会不一样
  • ROW : 基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修
    改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，这种方式日志较大，性能低于STATEMENT
  • MIXED ：混合模式，Sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

BinLog刷盘也有三种模式，通过 sync_binlog = 0来设置

  • sync_binlog = 0 (默认) ： 把数据写入page cache，由系统决定什么时候刷盘，Mysql宕机page cache里面的 binlog 会丢失
  • sync_binlog = 1 ： 表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
  • sync_binlog = 5 ：当值大于0表示：数据写入到page cache，累积5个事务后再刷盘，又有数据丢失风险

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容:mysqlbinlog --no-defaults --base64-output=decode-rows mysql-binlog.000001 内容如下

C:\ProgramData\MySQL\MySQL Server 5.7\Data>mysqlbinlog --no-defaults --base64-output=decode-rows mysql-binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; ... 省略...
BEGIN
/*!*/;
# at 291
#240421 13:13:20 server id 1  end_log_pos 357 CRC32 0x6f2ca4cd  Table_map: `test`.`t_user` mapped to number 112
# at 357
#240421 13:13:20 server id 1  end_log_pos 488 CRC32 0xd6ecd40e  Update_rows: table id 112 flags: STMT_END_F
# at 488
#240421 13:13:20 server id 1  end_log_pos 519 CRC32 0x44f30f44  Xid = 28
COMMIT/*!*/;
# at 519
#240421 13:13:24 server id 1  end_log_pos 584 CRC32 0xf63fc3d7  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 584
#240421 13:13:24 server id 1  end_log_pos 656 CRC32 0x2da3d5b9  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676404/*!*/;
BEGIN
/*!*/;
# at 656
#240421 13:13:24 server id 1  end_log_pos 722 CRC32 0xea014ff9  Table_map: `test`.`t_user` mapped to number 112
# at 722
#240421 13:13:24 server id 1  end_log_pos 851 CRC32 0x68790d50  Update_rows: table id 112 flags: STMT_END_F
# at 851
#240421 13:13:24 server id 1  end_log_pos 882 CRC32 0xca05217d  Xid = 35
COMMIT/*!*/;
# at 882
#240421 13:20:08 server id 1  end_log_pos 947 CRC32 0x92015bd0  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 947
#240421 13:20:08 server id 1  end_log_pos 1019 CRC32 0x31001467         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676808/*!*/;
BEGIN
/*!*/;
# at 1019
#240421 13:20:08 server id 1  end_log_pos 1085 CRC32 0x48b947e3         Table_map: `test`.`t_user` mapped to number 112
# at 1085
#240421 13:20:08 server id 1  end_log_pos 1169 CRC32 0x57fa794b         Delete_rows: table id 112 flags: STMT_END_F
# at 1169
#240421 13:20:08 server id 1  end_log_pos 1200 CRC32 0x92d88b90         Xid = 108
COMMIT/*!*/;
# at 1200
#240421 13:20:38 server id 1  end_log_pos 1265 CRC32 0xd25571c3         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1265
#240421 13:20:38 server id 1  end_log_pos 1337 CRC32 0x96831df1         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 1337
#240421 13:20:38 server id 1  end_log_pos 1403 CRC32 0xf2d728da         Table_map: `test`.`t_user` mapped to number 112
# at 1403
#240421 13:20:38 server id 1  end_log_pos 1484 CRC32 0x68c2ab73         Delete_rows: table id 112 flags: STMT_END_F
# at 1484
#240421 13:20:38 server id 1  end_log_pos 1515 CRC32 0x40d45c05         Xid = 115
COMMIT/*!*/;
# at 1515
#240421 13:20:38 server id 1  end_log_pos 1580 CRC32 0x14066476         Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1580
#240421 13:20:38 server id 1  end_log_pos 1652 CRC32 0x18ddeff7         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 1652
#240421 13:20:38 server id 1  end_log_pos 1718 CRC32 0xdbfd6465         Table_map: `test`.`t_user` mapped to number 112
# at 1718
#240421 13:20:38 server id 1  end_log_pos 1803 CRC32 0x45839dfd         Delete_rows: table id 112 flags: STMT_END_F
# at 1803
#240421 13:20:38 server id 1  end_log_pos 1834 CRC32 0x75881932         Xid = 116
COMMIT/*!*/;
# at 1834
#240421 13:20:38 server id 1  end_log_pos 1899 CRC32 0x810f7c3c         Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1899
#240421 13:20:38 server id 1  end_log_pos 1971 CRC32 0x1a7162a7         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 1971
#240421 13:20:38 server id 1  end_log_pos 2037 CRC32 0xa3b984fa         Table_map: `test`.`t_user` mapped to number 112
# at 2037
#240421 13:20:38 server id 1  end_log_pos 2099 CRC32 0x050b4823         Delete_rows: table id 112 flags: STMT_END_F
# at 2099
#240421 13:20:38 server id 1  end_log_pos 2130 CRC32 0x476461ea         Xid = 117
COMMIT/*!*/;
# at 2130
#240421 13:20:38 server id 1  end_log_pos 2195 CRC32 0x69248f3c         Anonymous_GTID  last_committed=6        sequence_number=7       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2195
#240421 13:20:38 server id 1  end_log_pos 2267 CRC32 0xaec9731f         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 2267
#240421 13:20:38 server id 1  end_log_pos 2333 CRC32 0x42bed06f         Table_map: `test`.`t_user` mapped to number 112
# at 2333
#240421 13:20:38 server id 1  end_log_pos 2414 CRC32 0xbf44c754         Delete_rows: table id 112 flags: STMT_END_F
# at 2414
#240421 13:20:38 server id 1  end_log_pos 2445 CRC32 0x2a3b2f9b         Xid = 119
COMMIT/*!*/;
# at 2445
#240421 13:20:38 server id 1  end_log_pos 2510 CRC32 0x8be3fa62         Anonymous_GTID  last_committed=7        sequence_number=8       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2510
#240421 13:20:38 server id 1  end_log_pos 2582 CRC32 0x21e0fc74         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 2582
#240421 13:20:38 server id 1  end_log_pos 2648 CRC32 0xd4ab07a0         Table_map: `test`.`t_user` mapped to number 112
# at 2648
#240421 13:20:38 server id 1  end_log_pos 2730 CRC32 0xd7ba4556         Delete_rows: table id 112 flags: STMT_END_F
# at 2730
#240421 13:20:38 server id 1  end_log_pos 2761 CRC32 0x16c42913         Xid = 120
COMMIT/*!*/;
# at 2761
#240421 13:20:38 server id 1  end_log_pos 2826 CRC32 0x58103da7         Anonymous_GTID  last_committed=8        sequence_number=9       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2826
#240421 13:20:38 server id 1  end_log_pos 2898 CRC32 0x459a70cf         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 2898
#240421 13:20:38 server id 1  end_log_pos 2964 CRC32 0x09babdb4         Table_map: `test`.`t_user` mapped to number 112
# at 2964
#240421 13:20:38 server id 1  end_log_pos 3045 CRC32 0xc2cf2312         Delete_rows: table id 112 flags: STMT_END_F
# at 3045
#240421 13:20:38 server id 1  end_log_pos 3076 CRC32 0x0fbb9e18         Xid = 121
COMMIT/*!*/;
# at 3076
#240421 13:20:38 server id 1  end_log_pos 3141 CRC32 0x57807853         Anonymous_GTID  last_committed=9        sequence_number=10      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3141
#240421 13:20:38 server id 1  end_log_pos 3213 CRC32 0xc42fad29         Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1713676838/*!*/;
BEGIN
/*!*/;
# at 3213
#240421 13:20:38 server id 1  end_log_pos 3279 CRC32 0xc5028680         Table_map: `test`.`t_user` mapped to number 112
# at 3279
#240421 13:20:38 server id 1  end_log_pos 5721 CRC32 0xc466015a         Delete_rows: table id 112 flags: STMT_END_F
# at 5721
#240421 13:20:38 server id 1  end_log_pos 5752 CRC32 0xcff67b6f         Xid = 122
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

C:\ProgramData\MySQL\MySQL Server 5.7\Data>

PS: beign ，commit : 是事务的开始和提交 ， at 5721 是日志的位置信息，

这样看到的数据比较多，如果要查看具体某部分的日志可以增加时间过滤条件，或者偏移量位置条件比如：

mysqlbinlog --no-defaults  --base64-output=decode-rows mysql-binlog.000001 
start‐datetime="2024-4-21 11:30:33" stop‐datetime="2024-4-21 13:30:22"  
start-position="1000" stop-position="20000"

除此之外我们还可以通过表格查看：show binlog events in 'mysql-binlog.000001'
在这里插入图片描述

恢复数据

当数据被误删除，想要恢复数据就可以使用binLog日志，用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子，我现在插入一条数据insert into t_user(id,name) values(1,"xx");,然后执行 delete from t_user where id = 122 把这条数据删除。通过时间范围查看BinLog日志

mysqlbinlog --no-defaults -v --base64-output=decode-rows 
mysql-binlog.000001 start‐datetime="2024-4-21 15:19:33" 
stop‐datetime="2024-4-21 15:22:22"

在这里插入图片描述
我们从日志中找到insert语句的时间范围，然后进行回复

mysqlbinlog  --no-defaults --start-datetime="2024-4-21 15:19:21" 
--stop-datetime="2024-4-21 15:19:23" 
--database=test mysql-binlog.000001 | mysql -uroot -p123456 -v test

当然也可以把时间修改为point进行恢复，道理是一样的

这里要注意一个问题：如果BinLog的日志达到了自动删除时间就会被自动删除掉，数据就无法恢复了。

Mysql重要参数配置

MySQL是一个流行的关系型数据库管理系统，其性能和行为可以通过配置文件中的多个重要项目进行定制和优化。这些配置项目在my.cnf（或mysqld.cnf，取决于操作系统和安装方式）文件中进行设置，并且它们各自具有特定的作用。以下是一些重要的MySQL配置项目及其作用：

1.max_connections：

max_connections定义了MySQL服务器允许的最大并发连接数。设置过低可能导致连接被拒绝，而设置过高则可能耗尽系统资源。您需要根据实际应用的需求和系统的负载情况进行调整。如果应用程序有大量并发请求，可能需要增加此值；反之，如果并发请求较少，则可以降低此值以节省资源

2. max_allowed_packet：

作用：定义客户端和服务器之间传输的最大数据包大小。默认值通常是一个相对较小的值，可能需要根据应用需求进行调整。当传输大型BLOB或TEXT值时，可能需要增加此值。

3. innodb_buffer_pool_size：

innodb_buffer_pool_size是InnoDB存储引擎用于缓存数据和索引的内存区域大小。由于InnoDB是MySQL的默认存储引擎，并且缓存数据和索引可以显著提高数据库性能，因此为innodb_buffer_pool_size分配足够的内存是非常重要的。一般设置为机器内存的70%，比如：服务器有32G内存，建议将大部分内存（例如24G）分配给innodb_buffer_pool_size，以确保足够的缓存空间。

4.join_buffer_size

用于优化全表扫描中的连接操作。对于大多数系统，默认值（256K）可能太小，特别是在处理大型数据集时。然而，也不建议设置得过大，因为每个线程都会分配一个这样的缓冲区，所以总内存使用量会是线程数乘以join_buffer_size。比如join_buffer_size设置为2M ，500个链接同时执行，那么就会消耗 500*2=1G 接近1个G的内存。

确定join_buffer_size设置多大合适需要考虑多个因素，包括查询的复杂性、表的大小、并发连接数以及服务器的总内存大小，基于上述信息，你可以设置一个初始的join_buffer_size比如：2M，然后在测试环境中运行你的查询。观察查询的性能和join_buffer的使用情况。如果需要，可以逐渐增加join_buffer_size的值，直到你找到一个平衡点，即查询性能提升但内存消耗仍在可接受的范围内。

5. sort_buffer_size

sort_buffer_size用于排序操作的缓冲区大小。当MySQL需要对结果进行排序时，它会使用这个缓冲区。由于排序操作可能会消耗大量内存，特别是在处理大量数据时，因此适当增加此值可能有助于提升排序操作的性能。和 join_buffer_size 一样每个线程都会分配一个这样的缓冲区

6.query_cache_size：

作用：定义查询缓存的大小，用于缓存SELECT语句的结果。查询缓存的作用很低，因为数据的修改频率是比较高的，每次修改了表数据查询缓存就会清除带哦，所以8.0+中默认关闭查询缓存。适当的查询缓存大小可以提高重复查询的性能，但也可能在写密集型工作负载中导致性能下降。

7. log_bin：

作用：启用二进制日志并记录更改到磁盘上。用作误删除数据还原和主从复制
默认值：通常禁用。8.0默认开启
影响：这对于复制和点时间恢复非常重要。启用二进制日志可以允许MySQL服务器在故障时从上一个已知的完好状态恢复。

8. innodb_thread_concurrency：

此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能
9. innodb_lock_wait_timeout：

行锁等待超时时间，默认是50s , 根据业务容忍时间来设置

10. innodb_flush_log_at_trx_commit：

redolog日志刷盘策略

  • redoglog 刷盘策略设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数
    据。
  • 设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
  • 设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

11. sync_binlog:

BinLog日志刷盘策略也有三种模式，通过 sync_binlog = 0来设置

  • sync_binlog = 0 (默认) ： 把数据写入page cache，由系统决定什么时候刷盘，Mysql宕机page cache里面的 binlog 会丢失
  • sync_binlog = 1 ： 表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
  • sync_binlog = 5 ：当值大于0表示：数据写入到page cache，累积5个事务后再刷盘，又有数据丢失风险

12. innodb_thread_concurrency

innodb_thread_concurrency用于限制InnoDB存储引擎的并发线程数。然而，在现代的多核服务器上，通常建议将此值设置为0，以允许InnoDB存储引擎根据系统的工作负载自动调整并发线程数。这样可以充分利用服务器的多核优势，提高并发性能。若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能。然而，这个参数在 MySQL 的后续版本中已经被弃用，并且在 MySQL 5.6.3 及更高版本中，该参数默认值为 0，表示禁用此限制。

本篇文章就写到这里把，如果文章对你有说帮助请点赞收藏+好评

墨家巨子@俏如来
关注
  • 8
    点赞
  • 踩
  • 31
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
MySQL 日志管理、备份与恢复（图文详解）
愿你成为自己喜欢的模样，不抱怨，不将就，有自由，有光芒！
01-29 2147
文章目录MySQL 日志管理、备份与恢复准备工作MySQL日志管理一、MySQL常用日志类型及开启二、查看日志状态MySQL备份与恢复一、数据备份的重要性二、数据库备份的分类1、从物理与逻辑的角度，备份可分为2、从数据库的备份策略角度，备份可分为三、常见的备份方法四、MySQL完全备份与恢复MySQL 完全备份完全备份恢复MySQL 增量备份与恢复MySQL 增量备份at 302at 430at 461at 526at 609at 742MySQL 增量备份恢复at 302at 430at 461at 52
mysql-connector-java-5.1.49.jar
04-28
总的来说，"mysql-connector-java-5.1.49.jar"是连接Java应用（如JIRA）和MySQL数据库的关键组件，它确保了两者间的数据交互得以顺畅进行。对于依赖MySQL的Java项目，选择合适版本的MySQL Connector/J并确保其正确...
Mysql日志-写入机制
qq_35519587的博客
04-21 800
binlog的写入机制 事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。 一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。 系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这...
MySQL日志机制
jyc1020的博客
03-13 1077
MySQL日志机制 在各种系统中，凡是要使用什么，我们都要为其建立一个数据结构，借助数据结构，我们能更深入的了解。 MySQL中有几种非常重要的日志： binlog：归档日志，MySQL的server层实现，所有引擎都可以使用，是逻辑日志，记录的是数据操作的原始逻辑。 redo log：重做日志，InnoDB引擎实现，是物理日志，记录的是在某个数据页上的操作。 undo log：回滚日志，MySQL的server层实现，是逻辑日志，记录的是数据操作的反逻辑，如：delete一条记录，binlog记录的
mysql 日志管理
m0_73464307的博客
12-04 162
MySQL的日志默认保存位置为/usr/local/mysql/data 可以修改配置文件，自定义日志文件的保存位置。错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启。 通用查询日志 通用查询日志，用来记录MysQL的所有连接和语句，默认是关闭的 二进制日志 二进制日志（binlog），用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启 二进制日志的格式 例： ROW：基于行记录二进制日志，写入和恢复速度较STA
mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目
最新发布
01-20
【MySQL面试题】在面试MySQL相关的职位时，面试官可能会问到一系列关于数据库基础、SQL语法、事务处理、索引优化以及性能调优的问题。以下是一些可能的面试重点： 1. **数据库基本概念**： - 数据库是用于存储和...
mysql-5.7.43-winx64
07-31
安装“mysql-5.7.43-winx64”压缩包时，通常会包含以下组件：MySQL Server、MySQL Client、MySQL Workbench（数据库管理工具）、配置工具等。用户需要按照官方提供的安装指南进行设置，包括配置服务器参数、创建...
MySQL面试题大全（吊打面试官）.rar
02-04
本资料“MySQL面试题大全（吊打面试官）”涵盖了从基础到高级的各类面试问题，旨在帮助求职者全面了解MySQL的面试知识点。 1. **基础概念** - **SQL**：了解SQL语言的基本语法，包括查询、插入、更新、删除数据的...
mysql-connector-java-8.0.15.jar
01-06
MySQL Connector/J 8.0.15 版本是针对 MySQL 8.0 数据库系列的，这意味着它已经过优化，可以充分利用 MySQL 8.0 的新特性和性能改进。例如，它支持新的 JSON 数据类型，增强的性能和并发性，以及改进的行级锁定机制...
精通MySQL日志管理
09-06
精通MySQL日志管理，这对处理故障与优化太关键了
MySQL日志管理
十五十六
02-13 541
错误日志 记录MySQL数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志 配置方式 [mysqld] log_error=err.log 然后重启会报错 [root@localhost data]# mysqld 2019-01-18T22:18:00.613170Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is ...
MySQL 日志管理
m0_60379130的博客
06-12 383
默认情况下，所有日志创建于mysqld数据目录中。rpm安装的日志是在 /var/lib/mysql其他方式安装的为自定义路径。备份之前需要打开慢查询日志，指定慢查询路径，指定慢查询时间。打开二进制日志，同时指定id.打开之后会有记录数据 （每一种安装方式下的安装路径不一致，具体以查询为准。） 永久更改，须在配置中更改，如下：在配置文件中增加以下三行信息： 再重启服务：没有开时再配置文件中开启： 重启之后再次连接,显示已开启。多久之后慢查询呢？ 更改慢查询时间： myusql5.6版本之上的版本一定要先配
mysql日志管理
蒙强的博客
09-19 397
MySQL日志管理详解 1，错误日志：记录启动、运行或停止时出现的问题，一般也会记录警告信息。 2，一般查询日志：记录建立的客户端连接和执行的语句。 3，慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询，可以帮我们定位服务器性能问题。 1，错误日志 错误日志：记录启动、运行或停止时出现的问题，一般也会记录警告信息。 错误日
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

墨家巨子@俏如来

你的鼓励是我最大的动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值