mysql 日志总结

mysql 根据日志的功能，分6种

1. 慢查询日志：记录所有执行时间超过 long_query_time 的所有查询，方便我们对查询进行优化
2. 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，
   对我们复原操作的实际场景、发现问题、设置是对数据库操作的审计都有很大的帮助
3. 错误日志：记录mysql服务的启动、运行或停止mysql服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护
4. 二进制日志：记录所有**更改（delete、update）**数据的语句，可以用于主从服务器之间的数据同步，
   以及服务器遇到故障时数据 的无损恢复
5. 中继日志：用于主从服务器架构中，从服务器用来存放主服务二进制日志内容的一个中间文件。
   从服务器通过读取中继日志的内容，来同步主服务器上的操作
6. 数据定义语句日志：记录数据定义语句执行的元数据操作

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于mysql数据目录中

日志的弊端

1. 日志功能会降低mysql数据库的性能：例如，在查询非常频繁的mysql数据库系统中，如果开启了通过查询日志和慢查询日志，mysql数据库会花费很多时间记录日志

2. 日志会 占用大量的磁盘空间：对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大

日志的刷新（包含所有种类的日志，若默认名称的日志不存在了，会重新生成一个。前提对应的日志要开启）

mysqladmin -uroot -p flush-logs；

linux系统有时日志刷新报错解决方案

需要先执行 instal -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

慢查询日志 slow query log

通用查询日志 general query log

通过查询日志用来记录用户的所有操作，包括启动和关闭mysql服务、所有用户的连接开始时间和截止时间、发给mysql数据库服务器的所有sql指令等。当我们的数据库发生异常，查看通用查询日志，还原操作时的具体场景。
默认是关闭的。

错误日志 error log

在mysql数据库中，错误日志功能是默认开启的，而且，错误日志无法被禁止
默认情况下，错误日志存储在mysql数据库的数据文件下，名称默认为mysqld.log(linux系统) Windows 系统 是hostname.err
如果需要定制文件名，则需要在my.cnf 或my.ini 中配置：

[mysqld] ## 分组
log-error=[path/[filename]] # path 为日志文件所在的目录路径，filename为日志文件名

show variables like ‘%log_error%’;

二进制日志 又称变更日志 binary log 简称 bin log

binlog 作用

1. 数据恢复

2.主从复制

show variables like’%log-bin%';

日志参数设置

方式1 永久性方式 linux my.cnf windows my.ini
[mysqld]
log-bin=[path/]basename ##路径省略 默认是 mysql安装的数据路径
binlog_expire_logs_seconds=60s
max_binlog_size=100M

查看日志

show binary logs;

binlog 2种格式

**mysqlbinlog 工具查看 **

在binlog日志所在路径，cmd
mysqlbinlog -v 日志文件

**show binlog events 工具查看 **
show binlog events [in ‘log_name’] [from pos] [limit [offset,] row_count]

在恢复数据之前 要刷新日志 flush logs，因为恢复数据，也会产生binlog日志， 刷新之后，恢复数据操作会记录在新的日志文件中

flush logs;
mysql数据库文件 cmd
从position 恢复 选了 show binlog events 查看日志方式
mysqlbinlog --start-position=xx --stop-position=xxxx --database=数据库名 日志文件 | mysql -uroot -p密码 -v 数据库名

根据时间戳 恢复 mysqlbinlog
mysqlbinlog --start-datetime=‘2023-01-01 15：39：22’ --stop-datetime=‘2023-01-01 15：40：00’–database=数据库名 日志文件 | mysql -uroot -p密码 -v 数据库名

删除二进制日志
mysql 的二进制文件可以配置自动删除如下：

同时mysql也提高了安全的手动删除二进制文件的方法。purge master logs 只删除指定部分的二进制日志文件，Rest master 删除所有的二进制日志文件。
** purge master logs** 删除指定日志文件

purge {master | binary } logs TO ‘指定日志文件名’
purge master logs ‘xxxxlog.0000xx’; 删除xxxxlog.0000xx之前创建的所有日志不包自身
purge {master | binary } logs before ‘指定日期’
show binary logs ； 信息中没有日期，需要借助 mysqlbinlog 工具
mysqlbinlog --no-defaults “日志文件名”
purge master logs before “20230915”；20230915之前创建的日志文件会被删除。 不包含本身

** reset master 删除所有二进制日志文件 **

reset master
使用 reset master 语句，清空所有的binlog日志。 mysql同时会重新创建二级制文件，新的日志文件扩展名 将重新
从 xxx.00001 开始编号

binlog 写入机制

mysql 根据日志的功能，分6种

1. 慢查询日志：记录所有执行时间超过 long_query_time 的所有查询，方便我们对查询进行优化
2. 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，
   对我们复原操作的实际场景、发现问题、设置是对数据库操作的审计都有很大的帮助
3. 错误日志：记录mysql服务的启动、运行或停止mysql服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护
4. 二进制日志：记录所有**更改（delete、update）**数据的语句，可以用于主从服务器之间的数据同步，
   以及服务器遇到故障时数据 的无损恢复
5. 中继日志：用于主从服务器架构中，从服务器用来存放主服务二进制日志内容的一个中间文件。
   从服务器通过读取中继日志的内容，来同步主服务器上的操作
6. 数据定义语句日志：记录数据定义语句执行的元数据操作

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于mysql数据目录中

日志的弊端

1. 日志功能会降低mysql数据库的性能：例如，在查询非常频繁的mysql数据库系统中，如果开启了通过查询日志和慢查询日志，mysql数据库会花费很多时间记录日志

2. 日志会 占用大量的磁盘空间：对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大

日志的刷新（包含所有种类的日志，若默认名称的日志不存在了，会重新生成一个。前提对应的日志要开启）

mysqladmin -uroot -p flush-logs；

linux系统有时日志刷新报错解决方案

需要先执行 instal -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

慢查询日志 slow query log

通用查询日志 general query log

通过查询日志用来记录用户的所有操作，包括启动和关闭mysql服务、所有用户的连接开始时间和截止时间、发给mysql数据库服务器的所有sql指令等。当我们的数据库发生异常，查看通用查询日志，还原操作时的具体场景。
默认是关闭的。

错误日志 error log

在mysql数据库中，错误日志功能是默认开启的，而且，错误日志无法被禁止
默认情况下，错误日志存储在mysql数据库的数据文件下，名称默认为mysqld.log(linux系统) Windows 系统 是hostname.err
如果需要定制文件名，则需要在my.cnf 或my.ini 中配置：

[mysqld] ## 分组
log-error=[path/[filename]] # path 为日志文件所在的目录路径，filename为日志文件名

show variables like ‘%log_error%’;

二进制日志 又称变更日志 binary log 简称 bin log

binlog 作用

1. 数据恢复

2.主从复制

show variables like’%log-bin%';

日志参数设置

方式1 永久性方式 linux my.cnf windows my.ini
[mysqld]
log-bin=[path/]basename ##路径省略 默认是 mysql安装的数据路径
binlog_expire_logs_seconds=60s
max_binlog_size=100M

查看日志

show binary logs;

binlog 2种格式

**mysqlbinlog 工具查看 **

在binlog日志所在路径，cmd
mysqlbinlog -v 日志文件

**show binlog events 工具查看 **
show binlog events [in ‘log_name’] [from pos] [limit [offset,] row_count]

在恢复数据之前 要刷新日志 flush logs，因为恢复数据，也会产生binlog日志， 刷新之后，恢复数据操作会记录在新的日志文件中

flush logs;
mysql数据库文件 cmd
从position 恢复 选了 show binlog events 查看日志方式
mysqlbinlog --start-position=xx --stop-position=xxxx --database=数据库名 日志文件 | mysql -uroot -p密码 -v 数据库名

根据时间戳 恢复 mysqlbinlog
mysqlbinlog --start-datetime=‘2023-01-01 15：39：22’ --stop-datetime=‘2023-01-01 15：40：00’–database=数据库名 日志文件 | mysql -uroot -p密码 -v 数据库名

删除二进制日志
mysql 的二进制文件可以配置自动删除如下：

同时mysql也提高了安全的手动删除二进制文件的方法。purge master logs 只删除指定部分的二进制日志文件，Rest master 删除所有的二进制日志文件。
** purge master logs** 删除指定日志文件

purge {master | binary } logs TO ‘指定日志文件名’
purge master logs ‘xxxxlog.0000xx’; 删除xxxxlog.0000xx之前创建的所有日志不包自身
purge {master | binary } logs before ‘指定日期’
show binary logs ； 信息中没有日期，需要借助 mysqlbinlog 工具
mysqlbinlog --no-defaults “日志文件名”
purge master logs before “20230915”；20230915之前创建的日志文件会被删除。 不包含本身

** reset master 删除所有二进制日志文件 **

reset master
使用 reset master 语句，清空所有的binlog日志。 mysql同时会重新创建二级制文件，新的日志文件扩展名 将重新
从 xxx.00001 开始编号

binlog 写入机制

show variables like ‘binlog_cache_size’;

write 和 fsync的时间，可以由参数 sync_binlog控制，默认是0，
0：表示每次提交事务都只 write，由系统自行判断什么时候执行 fsync。虽然性能得到提升，但是宕机，page cache里面的binlog会丢失
1：表示每次提交事务都会执行 fsync，就如同 redo log 刷盘流程一样。
N： 可以设置为N（N>1）,表示每次执行提交事务都 write，但累积N个事务才 fsync.
show variables like ‘sync_binlog’

sync_binlog =1 如下图

fsync_binlog=N

binlog vs redo log

1. redo log 是物理日志，记录内容都是在某个数据页上做了什么修改，属于innodb存储引擎层产生的
2. binlog 是逻辑日志， 记录内容是语句的原始逻辑，类似于 给ID=2 这一行的 c 字段+1，属于mysql server层。

虽然他们都属于持久化的保证，但是侧重点不同：
3. redo log 让 innodb 存储引擎拥有了崩溃恢复能力
4. binlog 保证mysql集群架构的数据一致性

中继日志 relay log