MySQL 日志管理
日志管理
错误日志
作用：
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
默认位置：
$MySQL_base/data/
开启方式:（MySQL安装完后默认开启)
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/user/local/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';
一般查询日志
作用：
记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。
默认位置：
$MySQL_base/data/
开启方式:（MySQL安装完之后默认不开启）
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on 或者1
general_log_file=/user/local/mysql/ata/$hostnamel.log
#查看方式
mysql> show variables like '%gen%';
之前说过,undo log记录的是修改之前的数据，提供回滚的能力。redo log记录的是修改之后的数据，提供了崩溃恢复的能力。
那binlog是干什么的呢？binlog记录的是修改之后的数据，用于归档。和redo log日志类似，binlog也有着自己的刷盘策略，通过sync_binlog参数控制：
sync_binlog = 0 ：每次提交事务前将binlog写入os cache，由操作系统控制什么时候刷到磁盘
sync_binlog =1 ：采用同步写磁盘的方式来写binlog，不使用os cache来写binlog
sync_binlog = N ：当每进行n次事务提交之后，调用一次fsync将os cache中的binlog强制刷到磁盘
那么问题来了，binlog和redo log都是记录的修改之后的值，这两者有什么区别呢？有redo log为什么还需要binlog呢？
首先看两者的一些区别：
binlog是逻辑日志，记录的是对哪一个表的哪一行做了什么修改；
redo log是物理日志，记录的是对哪个数据页中的哪个记录做了什么修改，如果你还不了解数据页，你可以理解成对磁盘上的哪个数据做了修改。
binlog是追加写；redo log是循环写，日志文件有固定大小，会覆盖之前的数据。
binlog是Server层的日志；redo log是InnoDB的日志。如果不使用InnoDB引擎，是没有redo log的。
二进制日志
在了解二进制日志之前我们先看下一条SQL如何入库的:
作用：
记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
记录所有DDL、DCL等语句(不会记录DQL语句)
总之，二进制日志会记录所有对数据库发生修改的操作
二进制日志模式:
statement：语句模式（默认模式）。
row：行级模式，数据行的变化过程。
mixed：混合模式(statement+row)推荐。
*优缺点:*
statement模式：
优点：容易被看懂，就是sql语句，记录时不需要太多的磁盘空间。
缺点：记录不够严谨。
row模式：
优点：记录更加严谨。
缺点：有可能会需要更多的磁盘空间，不容易读懂。
binlog的作用:
1）数据的备份恢复
2）数据的复制
开启二进制 ,查看binlog
编写MySQL配置文件
[mysqld]
log-bin=mysql-bin
binlog_format=row 设置binlog模式
expire_logs_days=60 只保留60天的日志
对二进制日志操作
show variables like 'binlog_format' 查看binlog模式
show variables like 'log_bin' 查看binlog是否开启
show binlog events in 'mysql_bin.000001'; show binlog events in 'mysql_binlog.000001' from 414 limit 3; 查看binlog事件
show master logs | show binary logs 显示二进制日志文件
mysqlbinlog --base64-output=decode-rows -vvv /data/mysql_bin.000001
详细查看row模式下二进制文件
### INSERT INTO `row`.`row01`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */ @1等于第一列
### INSERT INTO `row`.`row01`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
root@db02 ~]# ll /data/ mysql重启binlog会刷新000002....以此类推
-rw-rw---- 1 mysql mysql 524 Jun 30 22:28 mysql_bin.000001
-rw-rw---- 1 mysql mysql 19 Jun 30 22:19 mysql_bin.index
事件介绍
1）在binlog中最小的记录单元为事件(event)
2）一个事务会被拆分成多个事件（event）(事务里一条sql语句就是一个event)
事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。结束位置点是143(如果是143说明日志文件没内容)
row模式下数据恢复
准备测试环境
create database db1;
use db1;
create table t1;
insert into t1 values (1),(2),(3);
insert into t1 values (4),(5),(6);
insert into t1 values (7),(8),(9);
删除数据等于1的数据
delete from t1 where id=1;
update t1 set id=10 where id=2;
delete from t1 where id in (3,8);
delete from t1 where id between 4 and 7;
drop table t1;
drop database db1;
开始恢复
数据恢复到 delete from t1 where id=1;之前
查看binlog日志找到delete 位置点at
mysqlbinlog [-d 可指定数据库 ]--base64-output=decode-rows -vvv /data/mysql_bin.000002
### DELETE FROM `db1`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 1050
#210630 23:42:39 server id 2 end_log_pos 1081 CRC32 0xd3cf8c5f Xid = 60
COMMIT/*!*/;
at 1081(结束位置点) 全新环境起始位置点120
截取binlog文件
mysqlbinlog [-d 可指定数据库] --start-position=120 --stop-position=1081 /data/mysql_bin.000002 > /tmp/binlog.sql
导入数据
mysql -uroot -p
mysql>set sql_log_bin=0 关闭当前会话binlog日志
mysql>source /tmp/binlog.sql
数据已找回
mysql> select * from t1;
+------+
| id |
+------+
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+------+
bin-log 恢复存在问题:
1.多个bin_log日志
2.被误删除的表或者库是很久之前的
删除和刷新binlog
一.根据时间删除日志
1.临时生效
set global expire_logs_days=60;
2.永久生效(修改配置文件)
[mysqld]
expire_logs_days=60
二.使用purge命令删除
格式: purge {binary|master} logs {to 'log_name'|before datetime}
purge binary logs to mysql-bin.00006 before '2022-01-05 09:10:00';
purge binary logs before now() - interval 3 days 从现在开始只保留3天的
purge binary to 'mysql-bin.000006'; 根据文件名删除(6之前的全部删除)
三.使用reset master
reset master; 重置binlog
四.刷新binlog
1.flush logs 刷新binlog日志
2.重启mysql服务
3.mysqladmin -uroot -p -S /tmp/mysql.sock flush-logs
4.达到文件大小上限
慢查询日志
作用: 1.记录mysql数据库中影响性能的sql语句
2.通过分析这个sql语句,改进提高数据库性能
默认位置:
MySQL_basedir/data/hostname-slow.log
开启方式:
vim 配置文件
[mysqld]
slow_query_log= 1 开启慢日志
slow_query_log_file=/data/slow.log 指定存放位置
long_query_time=5 设定慢查询的阈值(默认10s)
log_queries_not_using_indexes 不使用所以的慢查询日志是否记录
mysqldumpslow分析慢日志
参数:
-s 排序方式: c 记录次数, t 时间, l 查询时间, r 返回记录数 加a 表示倒序 如:at,al,ar
-t 返回前面几条数据
-g 后面可跟正在表达式
mysqldumpslow -s at -t 10 /data/mysql-slow.log
ps:推荐第三方慢日志分析工具pt-query-digest(percona toolkit),mysqlsla
下载地址https://www.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/
