MySQL-日志机制二

有梦想的年轻人6174

已于 2023-09-19 01:14:32 修改

阅读量291

点赞数

文章标签： mysql 数据库

于 2023-09-18 23:51:31 首次发布

本文链接：https://blog.csdn.net/weixin_58482311/article/details/133002482

版权

CPU、内存和磁盘的简单模型

redo log重做日志关键参数

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。

innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

设置0：只保存在redo log buffer中，数据库宕机丢失数据

设置1：保存到OS缓存pageCache中，写磁盘日志文件，默认设置，线上系统推荐

设置2：保存到OS缓存pageCache中，不用写磁盘日志文件

0效率最高，1效率最低安全最高，2兼顾

redo log写入策略：

-- 查看innodb_flush_log_at_trx_commit参数值：

show variables like 'innodb_flush_log_at_trx_commit';

-- 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：

set global innodb_flush_log_at_trx_commit=1;

生产环境一般设置1；也分业务场景，生产上不同的业务有不同的库。有些场景比如说想要更高的并发，丢失数据不敏感，宕机丢点数据无所谓，像记录数据日志业务日志可设置0，核心数据库不能丢数据要设置1；2的性能也比较高，写到内存里

binlog二进制归档日志

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

记录修改操作，包括数据修改和表结构修改，MySQL宕机后用来恢复数据

binlog通常都要开启，可以恢复数据和实现主从复制

MySQL5.7 版本中，binlog默认关闭，8.0版本默认打开。因为实际业务场景经常开启

参数说明：

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

MySQL5.7 默认关闭binlog功能，打开需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

在配置文件中增加如下配置:

# 设置binlog的存放位置，绝对路径/相对路径，
# 相对路径binlog文件默认会放在data数据目录下，mysql-binlog就是生成的bin log日志文件的前缀
log-bin=mysql-binlog
# Server Id是数据库服务器id，在mysql集群环境中唯一，不唯一时启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数，默认为0表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为1GB

重启数据库后data数据目录多出两个文件，mysql-binlog.000001是binlog日志文件，mysql-binlog.index是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

binlog 的日志格式

用参数 binlog_format 可以设置binlog日志的记录格式，mysql支持三种格式类型：

STATEMENT：记录每一条修改数据的sql到master机器的bin-log中，优点：日志量小，节约IO开销，提高性能，缺点：执行过程中才能确定结果的函数，会导致主从同步的数据不一致。如UUID()、SYSDATE()等函数。
ROW：记录每一行被修改的数据，优点：可以解决函数、存储过程等在slave机器的复制问题，缺点：日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

STATEMENT记录更新的sql；ROW记录更新的每行数据；MIXED混合模式，推荐这一种

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

为0的时候，表示每次提交事务都只 write 到page cache，系统自行判断什么时候执行 fsync 写入磁盘。优点：性能得到提升，缺点：机器宕机，page cache里面的 binlog 会丢失。
也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

0：只写入pageCache最快，可能丢数据；1：每次写入磁盘。最慢不丢数据；N>1：写pageCache，积累N个写磁盘，可能丢失N个数据

发生以下事件时, binlog日志文件重新生成：

服务器启动或重新启动
服务器刷新日志，执行命令flush logs
日志文件大小达到 max_binlog_size 值，默认值为 1GB

每次执行flush logs都会生成一个新的日志文件mysql-binlog.00000X，日志信息会记录在最新的一个文件里面

删除 binlog 日志文件

# 删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件，
purge master logs before '2023-09-18 23:59:59';

reset master 是恢复日志的初始状态；第二种文件名不存在时报错；第三种全部删除时最少保留一个最新的日志文件

查看 binlog 日志文件

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容，注意是命令行执行

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\mysql-binlog.000001

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\mysql-binlog.000001 start-datetime="2023-09-15 00:00:00" stop-datetime="2023-09-18 00:00:00" start-position="5000" stop-position="20000"

执行命令：

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\mysql-binlog.000008

查出来的binlog日志文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230918 23:18:28 server id 1  end_log_pos 123 CRC32 0x50235ab2  Start: binlog v 4, server v 5.7.43-log created 230918 23:18:28
# Warning: this binlog is either in use or was not closed properly.
# at 123
#230918 23:18:28 server id 1  end_log_pos 154 CRC32 0xa4d1e6a3  Previous-GTIDs
# [empty]
# at 154
#230918 23:20:41 server id 1  end_log_pos 219 CRC32 0x7d9ea5df  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230918 23:20:41 server id 1  end_log_pos 292 CRC32 0x5c885b0b  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695050441/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 292
#230918 23:20:41 server id 1  end_log_pos 347 CRC32 0x2d811125  Table_map: `nacos`.`account` mapped to number 109
# at 347
#230918 23:20:41 server id 1  end_log_pos 411 CRC32 0x42a872ca  Update_rows: table id 109 flags: STMT_END_F
### UPDATE `nacos`.`account`
### WHERE
###   @1=2
###   @2='liu'
###   @3=10000
### SET
###   @1=2
###   @2='liu'
###   @3=500
# at 411
#230918 23:20:41 server id 1  end_log_pos 442 CRC32 0x180fe5f8  Xid = 166
COMMIT/*!*/;
# at 442

... ...

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*/;

能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子

# 先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000009
flush logs;
# 执行两条插入语句
INSERT INTO `account` (`id`, `name`, `balance`) VALUES ('4', 'gc', '666');
INSERT INTO `account` (`id`, `name`, `balance`) VALUES ('5', 'gc2', '888');
# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id > 3;

现在需要恢复被删除的两条数据，我们先查看binlog日志文件

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:\mysql-binlog.000009

文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230919  0:00:43 server id 1  end_log_pos 123 CRC32 0x3356853f  Start: binlog v 4, server v 5.7.43-log created 230919  0:00:43
# Warning: this binlog is either in use or was not closed properly.
# at 123
#230919  0:00:43 server id 1  end_log_pos 154 CRC32 0x6a692065  Previous-GTIDs
# [empty]
# at 154
#230919  0:01:29 server id 1  end_log_pos 219 CRC32 0x6c2fbdab  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230919  0:01:29 server id 1  end_log_pos 292 CRC32 0xacb5682a  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695052889/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 292
#230919  0:01:29 server id 1  end_log_pos 347 CRC32 0xed132ff1  Table_map: `nacos`.`account` mapped to number 109
# at 347
#230919  0:01:29 server id 1  end_log_pos 395 CRC32 0xffe57581  Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `nacos`.`account`
### SET
###   @1=4
###   @2='gc'
###   @3=666
# at 395
#230919  0:01:29 server id 1  end_log_pos 426 CRC32 0xed13739e  Xid = 240
COMMIT/*!*/;
# at 426
#230919  0:01:29 server id 1  end_log_pos 491 CRC32 0x93b4637a  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 491
#230919  0:01:29 server id 1  end_log_pos 564 CRC32 0xc891862b  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695052889/*!*/;
BEGIN
/*!*/;
# at 564
#230919  0:01:29 server id 1  end_log_pos 619 CRC32 0xb2d40367  Table_map: `nacos`.`account` mapped to number 109
# at 619
#230919  0:01:29 server id 1  end_log_pos 668 CRC32 0xe7a8cfe4  Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `nacos`.`account`
### SET
###   @1=5
###   @2='gc2'
###   @3=888
# at 668
#230919  0:01:29 server id 1  end_log_pos 699 CRC32 0x67cec2d4  Xid = 241
COMMIT/*!*/;
# at 699
#230919  0:01:32 server id 1  end_log_pos 764 CRC32 0x413df26d  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 764
#230919  0:01:32 server id 1  end_log_pos 837 CRC32 0xce150675  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1695052892/*!*/;
BEGIN
/*!*/;
# at 837
#230919  0:01:32 server id 1  end_log_pos 892 CRC32 0xc5e92b64  Table_map: `nacos`.`account` mapped to number 109
# at 892
#230919  0:01:32 server id 1  end_log_pos 995 CRC32 0x31814ff3  Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `nacos`.`account`
### WHERE
###   @1=4
###   @2='gc'
###   @3=666
### DELETE FROM `nacos`.`account`
### WHERE
###   @1=5
###   @2='gc2'
###   @3=888
### DELETE FROM `nacos`.`account`
### WHERE
###   @1=9
###   @2='zhao'
###   @3=888
### DELETE FROM `nacos`.`account`
### WHERE
###   @1=15
###   @2='ma'
###   @3=3000
### DELETE FROM `nacos`.`account`
### WHERE
###   @1=30
###   @2='gc'
###   @3=2000
# at 995
#230919  0:01:32 server id 1  end_log_pos 1026 CRC32 0x515a2230         Xid = 248
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*/;

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 699

我们可以根据文件位置标识来恢复数据，执行如下sql：

mysqlbinlog  --no-defaults --start-position=219 --stop-position=699 --database=nacos D:/mysql-binlog.000009 | mysql -uroot -p123666 -v nacos

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1695052889，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1695052892，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-09-19 00:01:29" --stop-datetime="2023-09-19 00:01:32" --database=nacos D:/mysql-binlog.000009 | mysql -uroot -p123666 -v nacos

注意：要恢复大量数据，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库。

推荐每天(在凌晨后)做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

# nacos数据库名，备份整个数据库
mysqldump -u root -p nacos > D:/test.txt

# nacos数据库名，account表名，备份整个表
mysqldump -u root -p nacos account > D:/test2.txt

# nacos数据库名，恢复上面备份的数据表
mysql -u root -p nacos < D:/test2.txt

# test为新建的数据库名，恢复整个数据库
mysql -u root -p test < D:/test.txt

为什么会有redo log和binlog两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log是InnoDB引擎特有的，binlog是MySQL的Server层面的。redo log可以恢复MySQL宕机导致buffer pool来不及刷入磁盘的数据

undo log回滚日志

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

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

innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。

上面配置不能写在my.ini 文件中，写在my.ini文件中启动MySQL失败

undo log日志什么时候删除

新增类型的，在事务提交之后就可以清除掉了。

修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

为什么Mysql不能直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL了？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。

因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

早期没有固态硬盘，只有机械硬盘，而机械硬盘的读写要依赖于磁道、磁针的寻址，是一种随机读写，每一次都要随机读写的性能是很低的

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置

show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

general_log_file：通用查询日志记录的位置参数。

show variables like '%general_log%';

# 打开通用查询日志

SET GLOBAL general_log=on;

有梦想的年轻人6174

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-日志机制二

实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。，缺点：日志量较大，性能不如Statement。
复制链接

扫一扫