MySQL数据库日志管理_mysql日志表报错信息字段-优快云博客

本文链接：https://blog.youkuaiyun.com/Yosigo_/article/details/118909772

MySQL数据库日志分类

查询MySQL服务器系统变量值：mysqladmin -uroot -p123 variables

在这里插入图片描述

错误日志

首次启动会提示错误日志位置

# 查询错误日志存放路径
[root@db01 ~]# mysqladmin -uroot -p123 variables | grep -w log_error

或者
[root@db01t ~]# mysql -uroot -p123
mysql> show variables like '%log_error%';

配置错误日志（默认就是启用的）

相当于把/var/log/messages(系统日志)中mysql服务端相关的信息分离到/var/log/mysql.errlog中。

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
# 绝对路径
log_error=/var/log/mysql.errlog
# 相对路径
#log_error=mysql.errlog		

[root@localhost ~]# touch /var/log/mysql.errlog
[root@localhost ~]# chmod 640 /var/log/mysql.errlog
[root@localhost ~]# chown mysql.mysql /var/log/mysql.errlog
[root@localhost ~]# systemctl restart mysql

MySQL中，其中log_error定义是否启用错误日志的功能和错误日志的存储位置，此外还可以用参数控制是否将告警信息（warning messages）也写入错误日志。在不同的版本中改控制参数有所不同。在MySQL 5.6版本中用log_warnings参数：
log_warnings=0		 	表示不记录告警信息。

log_warnings=1(默认值)	 	表示告警信息写入错误日志。

log_warnings大于1		表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。

mysql5.7新增的log_error_verbosity参数
它有三个可选值，分别对应：

log_error_verbosity=1			错误信息；
  
log_error_verbosity=2			错误信息和告警信息；(推荐)

log_error_verbosity=3(默认值就是3)			错误信息、告警信息和通知信息。

事务的日志

事务日志主要分为redo log日志和undo log日志。

redo log刷盘策略

undo log表空间

一般查询日志

一般查询日志默认是关闭的，一般不会开启。
因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常非常占地方，从来都不启动，要看操作去bin log。

# 配置一般查询日志
[root@db01 ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log

# 可以使用set global general_log=on;设置
[root@localhost ~]# touch /var/log/select.log
[root@localhost ~]# chmod 640 /var/log/select.log
[root@localhost ~]# chown mysql.mysql /var/log/select.log
[root@localhost ~]# systemctl restart mysql

查看一般查询日志
[root@db01 ~]# mysqladmin -uroot -p123 variables|grep general_log

或者
[root@db01 ~]# mysql -uroot -p123
mysql> show variables like '%gen%';

慢查询日志

慢日志的作用

将mysql服务器中影响数据库性能的相关SQL语句(不论是什么语句，增删改查)记录到日志文件
通过对这些特殊的SQL语句分析并改进，提高数据库性能

配置慢日志（默认是不启用的）

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 指定是否开启慢查询日志
slow_query_log = 1
# 指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/slow.log
# 设定慢查询的阀值(默认10s)
long_query_time=0.05
# 不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
# 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，少于100行的sql语句查询慢的话不记录，一般不使用
# min_examined_row_limit=100 

[root@db01 ~]# touch /var/log/slow.log
[root@db01 ~]# chmod 640 /var/log/slow.log
[root@db01 ~]# chown mysql.mysql /var/log/slow.log
[root@db01 ~]# systemctl restart mysql

- 测试:benchmark(count,expr)
select benchmark(50000000,2*3);

# 如果执行卡死，查看执行的sql执行时间，如果停不下来 可以 kill id
show processlist;
kill 2;

- 查看方式
mysqldumpslow -s r -t 10 /var/log/slow.log
得到按照时间排序的前10条里面含有左连接的查询语句

参数说明:
-s, 是表示按照何种方式排序，
	c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，
	ac、at、al、ar，表示相应的倒叙;
-t, 是top n的意思，即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式，大小写不敏感的。

二进制日志(bin log日志)

二进制日志即bin log日志，记录了mysql数据库所有的DML，DDL语句事件（不包含select）。记录增删改，也可以记录SQL语句及行记录变化，还可以记录这些操作事件；总之会记录所有修改操作的SQL语句。

不要混淆以下三种日志：
1. general log：记录数据库里的所有SQL操作记录

2. redo log：只记录innodb存储引擎的修改日志

3. bin log：只记录server层面内部的修改情况。	--select /show  不记录

开启bin log日志的好处

数据恢复：可以基于时间点恢复，以及根据其进行增量与差异备份。
mysql主从复制，通过bin log实现数据复制。

二进制日志的工作模式

bin log日志的工作模式分为语句模式、行级模式和混合模式。

1. 语句模式 : binlog_format=statement（Mysql5.7.6之前的默认级别）
记录对数据库做出修改的sql语句，不记录该sql的上下文信息。
例如：
1. 开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先设置配置项 
set global log_bin_trust_function_creators=true;

2. 然后自定义函数 
delimiter // 
create function f1( 
    i1 int, 
    i2 int) 
returns int 
BEGIN 
    declare num int; 
    set num = i1 + i2; 
    return(num); 
END //
delimiter ;

3. 最后执行一条插入语句 
insert into t1(name) values(concat("nana",f1(1,2)));

如果使用statement模式，那么这条insert语句将会被记录到二进制日志中，而sql语句中依赖的f1的定义是不会记录下来的，f1只存在于当前库。
优点：不需要记录细到每一行数据的更改变化，因此，bin log日志量小，IO压力小，性能较高。
缺点：日志中记录的sql语句可能有上下文依赖，此时脱离了当前数据库环就无法运行了，因此该模式下容易出现主从不一致的问题。
例如：
主库记录的某条sql语句引用了主库中的函数、触发器、存储过程等特殊功能。

在从库上接收了该sql之后，可能就无法正确运行，从而主从库数据不一致的问题。
应用场景：sql语句对mysql内置功能依赖比较少：不使用存储过程/触发器/函数，可以使用该模式，否则还是推荐行级模式。

2. 行级模式：binlog_format=row（mysql5.7.6之后+8.0的默认级别）
记录每一行数据修改的细节，即哪一条记录被修改了，修改成什么样了。
例如：
1. 开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先设置配置项 
set global log_bin_trust_function_creators=true;

2. 然后自定义函数 
delimiter //                        
create function f1( 
    i1 int, 
    i2 int) 
returns int  
BEGIN 
    declare num int; 
    set num = i1 + i2; 
    return(num); 
END //
delimiter ;

3. 最后执行一条插入语句 
insert into t1(name) values(concat("nana",f1(1,2)));

如果使用row模式，那么日志中会记录插入了一条新记录，记录中的name字段值为'nana'。
优点：相当于把上下文依赖都记录了下来，可以更方便查看每一条数据修改的细节，并且不会出现某些特定情况下的存储过程、函数以及触发器调用和触发无法被正确复制的问题，即该模式下主从复制强一致，数据最安全。
缺点：日志量大。

例如：
一条语句修改了100万行，语句模式下只需要记录一条语句即可。
  
而行级模式却修改记录下100万行的修改记录，binlog日志的量可能会大得惊人。
应用场景：sql语句对mysql内置功能依赖比较多，希望数据最安全，复制强一致的场景推荐行级模式

3. 混合模式：binlog_format=mixed，一般不用

混合（mixed-based）模式结合了statement与row的优点，默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
例如： 
1. 当函数中包含uuid()时。

2. 当表中有自增列(auto_increment)被更新时。

3. 当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。

4. 当found_rows()、row_count()、user()、current_user()等执行时。
应用场景：看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用row模式。

bin log日志相关概念

二进制日志文件，顾名思义，它是二进制的，所以我们不能直接使用cat命令进行查看，而是需要通过一些别的命令查看其内容，而且，二进制日志文件，有"事件"和"位置"的概念。

事件events

通俗的讲，我们可以把bin log中的每一段记录当做一个"事件"，因为bin log记录了所有对数据库进行的修改，所以，我们可以认为，数据库的修改被记录到二进制日志中，这些记录每一条都可以理解为一个"事件"。

位置position

由于二进制日志文件是二进制的，所以，我们可以把整个二进制文件想象成一个字节序列。假设，二进制日志文件刚开始是空的，从第1个字节开始记录，假设记录第一个"事件"（第一条记录），需要15个字节，那么第一个事件的开始"位置"就是1，结束"位置"就是15。由于前15个字节已经被第一个事件占用，那么当我们想要通过二进制日志记录第二个事件时，则需要从第15个字节向后开始记录，假设记录第二个"事件"需要20个字节，那么第二个事件在bin log中的起始"位置"就是15，结束"位置"就是35，以此类推。

二进制日志相关参数

开启bin log(默认是关闭的)

# 配置开启bin log 
vim /etc/my.cnf 
[mysqld] 
server_id=1 				 -- 服务ID,主从库必须不一样,必须指定
log-bin=/service/mysql/mybinlog 		-- 此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF
binlog_format='row'			-- (row,statement,mixed)，不建议随意去修改binlog工作模式 
binlog_rows_query_log_events=on 		 -- 打开才能查看详细记录，默认为off
max_binlog_size=100M			-- 设置单个二进制日志文件的最大大小，超出100M。MySQL默认会新建一个文件记录日志

其他参数

# 打开才能查看详细记录，默认为off
binlog_rows_query_log_events=on
  
# 表示自动删除10天以前的日志
expire_logs_days=10
  	
# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog_row_image=full 				--（full,minimal,noblob）

sql_log_bin(默认为ON)

此变量用于标识当前会话中的操作是否会被记录于二进制日志。
此变量值设置为ON，则表示在当前数据库连接中，对数据库进行修改的语句将会被记录到bin log中。
此变量值设置为OFF，则表示在当前数据库连接中，对数据库进行的修改的语句将不会被记录到bin log中。

查看binlog配置项

show variables like '%log_bin%'; 
show variables like '%binlog%'; 
show variables like '%binlog_format%'; 
show variables like '%server%'; 
show variables like 'expire_logs_days';			 -- 过期日志天数  

或者 
[root@db01 ~]# mysqladmin -uroot -p123 variables |grep -w log_bin

测试语句模式和行级模式

# 创建数据
create table t1(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);

insert into t1(name) values
('nana'),('lala'),
('haha'),('xixi');

测试语句模式statement

# 配置开启bin log 
[root@db01 ~]# vim /etc/my.cnf 
[mysqld] 
server_id=1 
log-bin=/service/mysql/mybinlog 
binlog_format='statement'		
binlog_rows_query_log_events=on 
max_binlog_size=100M

# 重启mysql数据库
[root@db01 ~]# systemctl restart mysql

# 查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令mysqlbinlog
# 重启之后,mysql默认目录会生成mybinlog.000001二进制文件
[root@db01 ~]# ls /service/mysql/
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000001

# 开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先设置配置项
mysql> set global log_bin_trust_function_creators=true;

# 自定义函数
mysql> delimiter //                        
mysql> create function f1( 
    -> i1 int, 
    -> i2 int) 
    -> returns int  
    -> BEGIN 
    -> declare num int; 
    -> set num = i1 + i2; 
    -> return(num); 
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

# 执行一条插入语句,并且调用f1函数
mysql> insert into t1(name,age) values("NANA",f1(8,10));

# 查看bin log日志
# 可以查看到我们定义的函数和执行的sql语句insert。
# 但是只记录sql语句,不记录执行结果,对sql语句的上下文依赖性强。
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000001

测试行级模式row

# 配置开启bin log 
[root@db01 ~]# vim /etc/my.cnf 
[mysqld] 
server_id=1 
log-bin=/service/mysql/mybinlog 
binlog_format='row'		
binlog_rows_query_log_events=on 
max_binlog_size=100M

[root@db01 ~]# rm -rf  /service/mysql/mybinlog.*
# 重启mysql,默认重新生成mybinlog.000001文件
[root@db01 ~]# systemctl restart mysql
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000001

# 执行一条插入语句,并且调用f1函数
mysql> insert into t1(name,age) values("bao",f1(9,10));
Query OK, 1 row affected (0.00 sec)

# 使用行级模式,sql语句的执行结果是加密的,所以我们需要加上参数--base64-output=decode-rows -vvv
# 我们查看行级模式bin log日志的记录,可以发现bin log不但记录了sql语句,还记录了sql语句的执行结果。
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000001 --base64-output=decode-rows -vvv

查看binlog日志

查看日志日志名、状态、事件

show binary logs; 
show master logs; 
show master status; 		-- bin log的当前position(位置)
show binlog events in 'mybinlog.000001';			-- 查看事件详细信息

查看日志内容

# 查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令
查看全部：mysqlbinlog /service/mysql/mybinlog.000001

按时间：mysqlbinlog /service/mysql/mybinlog.000001 --start-datetime="2022-11-05 10:02:56" --stop-datetime="2022-11-05 11:02:54"

按字节数：mysqlbinlog /service/mysql/mybinlog.000001 --start-position=337 --stop-position=662

如果是行级模式，想要看懂详细内容则需要加上额外参数，但是仅用于看懂内容，如果要用于还原数据，还是应该去掉额外的参数并将内容定位到文件中。

# 仅用于查看，不能用于日后的数据恢复
# -vvv 显示详细信息		--base64-output 显示模式
mysqlbinlog --base64-output=decode-rows -vvv /service/mysql/mybinlog.000001   		

# /tmp/1.sql可用于日后的数据恢复
mysqlbinlog /service/mysql/mybinlog.000001 --start-position=100 > /tmp/1.sql

使用二进制日志恢复数据

人为因素导致的数据丢失，我们可以使用bin log日志进行数据还原。

# 配置开启bin log 
[root@db01 ~]# vim /etc/my.cnf 
[mysqld] 
server_id=1 
log-bin=/service/mysql/mybinlog 
binlog_format='row'		
binlog_rows_query_log_events=on 
max_binlog_size=100M

[root@db01 ~]# systemctl restart mysql

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

mysql> use db01;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database db01;
Query OK, 1 row affected (0.01 sec)

# 查看事件详细信息
mysql> show binlog events in 'mybinlog.000001';
[root@db01 ~]# mysqlbinlog /service/mysql/mybinlog.000001

# 数据恢复
[root@db01 ~]# cd /service/mysql
# 查看binlog数据的起始点与要恢复到的位置点，导出成SQL
[root@db01 mysql]# mysqlbinlog mybinlog.000001 --start-position=120 --stop-position=560 > /tmp/1.sql 

导入数据库方式一:
[root@db01 mysql]# mysql -uroot -p123 < /tmp/1.sql
- 该数据还原方式,是可以直接将数据还原成功的，相当于将bin log日志的sql语句拿到数据库中重新执行一次。
- 这就会导致bin log日志会再次记录一次数据还原过程中所使用的sql语句，使bin log日志的记录变得混乱。
- 我们可以暂时关闭参数sql_log_bin。

导入数据库方式二:
# 关闭sql_log_bin选项
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

# 导入数据
mysql> source /tmp/1.sql;

# 开启sql_log_bin选项
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

刷新与清除二进制日志

1. 刷新binlog

# 刷新binlog：关闭当前的二进制日志文件并创建一个新文件 
1. 手动执行命令刷新 
flush logs; 		# 手动刷新之后会在mysql默认目录下生成一个新的二进制文件用来记载bin log日志
或者 
mysqladmin -uroot -p123 flush-logs; 
或者 
mysql -uroot -p123 -e 'flush logs' 

2. 重启数据库时会刷新 
3. 二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新

2. 清除binlog

# 清除二进制日志原则 
在存储能力范围内，能多保留则多保留 
基于上一次全备前的可以选择删除 

1. 删除所有binlog，相当于重置 
reset master; 
  	
2. 删除指定binlog名之前的所有binlog(保留指定的binlog) 
purge binary logs to 'mybinlog.00003'; 		-- mybinlog.00003之前的都删除掉 
  	
3. 删除日期之前的日志：手动执行 
PURGE {MASTER | BINARY} LOGS BEFORE 'date'   --用于删除日期之前的日志，BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式 

如：（MASTER 和BINARY 在这里都是等效的） 
PURGE MASTER LOGS TO 'mybinlog.00003'; 
purge binary logs before '2021-07-13 19:11:00'; 
  	
还可以做减法：如只保留3天的 
PURGE BINARY LOGS BEFORE now() - INTERVAL 3day; 
  	
4. 删除日期之前的日志：修改配置参数，让mysql自动执行 
删除7天前的binlog
# 临时生效 
SET GLOBAL expire_logs_days = 7;
  	
# 永久生效 
[root@db01 data]# vim /etc/my.cnf 
[mysqld] 
expire_logs_days = 7