mysql_6 日志管理
mysql_6 日志管理
标签（空格分隔）： mysql
错误日志
作用
记录了mysql从启动以来，所有的状态，警告，错误。 帮组管理员定位 追踪数据库问题
配置方法
默认开启
位置 /数据文件夹/datadir/hostname.err
自定义 在 my.cnf 里面去写
log-error=/var/log/mariadb/mariadb.log
查看错误日志
[error] 包括error的
双一标准
innodb_flush_log_at_trx_commit=1 每次事务提交 比如log buffer 中 redo 落到磁盘
sync_binlog = 1 每次事务提交 必然保证binlog cache 中的日志落到磁盘
binlog二进制日志
作用：
数据恢复，主从复制中应用
主要记录数据库变化(DDL,DCL,DML)性质的日志.是逻辑层性质日志。
@@log_bin
如何开启
默认：8.0版本以前没有开启
8.0 之后 自动开启
配置方法
server_id= #服务 主机编号 binlog需要 主从需要
log_bin = /data/binlog/mysql-bin #日志位置+日志名前缀 列如： mysql-bin.000001
sync_binlog = 1 #binlog 日志刷写的策略，双一中的第二个1.每次事务提交立即刷写binlog到磁盘。
binlog_format=row #binlog的记录格式为row模式
binlog 记录的是变更sql语句，不记录查询语句
记录sql语句种类
DDL 原封不动的记录当前DDL statement语句
DCL 原封不动的记录当前DCL statement语句
DML 只记录已经提交的事务DML insert update delete
DML三种记录方式
binlog_format 参数影响
1.statement 5.6默认 SBR 语句模式原封不动的记录当前DML
2.row 5.7 PBR 记录数据行的变换 用户看不懂 需要工具分析
3.mixed 混合 MBR 以上两种模式的混合
row 和 statement 对比
SBR 与 PBR模式的对比
statement 可读性比较高 日志量小 不够严谨
row 可读性比较小 日志量大 足够严谨
update t1 set xxx = xx where id > 100
为什么row模式严谨
id name intime
insert into t1 values(1,'zs',now())
row 记录了时间
statement 只是记录了函数
event 事件 是什么？
事件的简介
二进制日志的最小记录单元
对于DDL DCL 一个语句是就一个event
对于DML语句来讲 只记录已提交的事务
例如以下列子，就被分为了4个event
begin 120 -340
dml1 340- 460
dml2 460- 550
commit 550- 760
event 的组成
三部分构成：
- 时间的开始标识
- 时间内容
- 时间的结束标识
position
开始标识 at 194
结束标识 end_log_pos 254
194 254
某个事件在binlog中的相对位置号
位置号的作用是什么
为了方便我们去截取事件
binlog的查看
文件查看开启情况
select @@log_bin;
select @@log_bin_basename;
文件查看
/etc/my.cnf 写的文件夹 cd 进去 ls就可以看见了
二进制内置查看命令
查看目前有几个日志文件
show binary logs;
查看当前正在使用的日志文件
show master status;
查看二进制日志事件的功能
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
| mariadb-bin.000001 | 4 | Format_desc | 1 | 256 | Server ver: 10.5.3-MariaDB-log, Binlog ver: 4 |
| mariadb-bin.000001 | 256 | Gtid_list | 1 | 285 | [] |
| mariadb-bin.000001 | 285 | Binlog_checkpoint | 1 | 330 | mariadb-bin.000001 |
+--------------------+-----+-------------------+-----------+-------------+-----------------------------------------------+
show binlog events in "前面命令（show master status）查询出来的值"
日志grep
mysql -u root -p -e "show binlog events in 'mysql-bin'" | grep DROP
binlog 如何打开
mysqlbinlog 位置 > /tmp/a.sql 变成sql
# at 219
时间 end_log_pos 338
# at 338
mysqlbinlog --base64-output=decode-rows -vvv 位置 > /tmp/a.sql 变成sql
日志截取回复
flush logs 刷新新的日志文件 用于写入日志 原来的日志就不适用了
通过日志数据恢复
show binlog events in 'mysql-bin'
binlog 维护操作
flush logs; #滚动一个新的日志
select @@max_binlog_size;
mysqladmin -u root -p flush-logs
mysqldump -F
日志的删除
不要使用rm命令 删除日志
自动删除机制
select @@expire_logs_days;
默认是0 单位是天 代表永不删除.
截取日志
mysqlbinlog --start-position=开始点 --stop-position 结束点 /data/binlog/mysql-bin.00005 > /tmp/bin.sql
恢复日志
set sql_log_bin=0
source /tmp/bin.sql
set sql_log_bin=1
问题：
1.数据多了怎么办
2.binlog记录的不仅仅是想要回复的库的操作记录，还有其他库的操作。
mysqlbinlog -d bindb 指定 只需要的库
3.数据库创建了几年，bin_log 不够怎么办
4.binlog 有删数据数据的操作
5.断断续续的截取
6.需要的日志文件在多个文件中分布
起点 加入在1号 mysql-bin.00001 4600
终点 一般是最后一个文件 10号 mysql-bin.000010 890
mysqlbinlog --start-datetime= --stop-datetime= mysql-bin.00001 mysql-bin.00002
7.数据库创建了几年，bin_log 太多了 数据也多
假设： 每周六做全备份 binlog 每天备份2次。
binlog 实际上使我们数据恢复时 配合备份一起恢复数据的手段
所以还是需要备份手段的 可以使用docker 给快照 raid 硬件 。
如何恢复
1.滚动一个新的日志
2.分析binlog
确认起点 终点 找到你需要的
导出为sql
mysqlbinlog --start-position=开始点 --stop-position 结束点 /data/binlog/mysql-bin.00005 > /tmp/bin.sql
恢复日志
set sql_log_bin=0
source /tmp/bin.sql
set sql_log_bin=1
手动删除binlog
purge binary logs to 'mysql-bin.0000010';
purge binary logs before '2020-08-17'
全部binlog清空
reset master
主 从 比崩
查看锁情况
show engine innodb status
show variables like '%deadlock%'
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
得到 trx_id thread id 还有sql 开始解决死锁
show master status ;
查看binlog 地点
MGR PAXOS 分布式一致性协议
Redis 事务
mongodb replication set
如何查看binlog 变成sql
mysqlbinlog mysql-bin.0000002 > /tmp/a.sql
mysqlbinlog --base64-outpu=decode-rows -vvv mysql-bin.0000002 > /tmp/a.sql
mysqlbinlog --help
binlog 的GTID模式管理
GTID介绍
5.6版本 新加的特性 5.7 8.0中做了加强
5.6 中不开启 没有这个功能
5.7 中的GTID 即使不开也会有自动生成
set @@SESSION.GTID_NEXT = "anonymous"
是对于一个已提交事务的编号，并且是一个全局唯一的编号
他的官方定义:
GTID = server_uuid : transaction_id
7474848SERF-65E4-ERF4-1C16QWUTYJGH
重要参数介绍
vim /etc/my.cnf
gtid-mode= on
enforce-gtid-consistency = true
show master status
show binlog events in "binlog名字"
GTID 截取binlog
--include-gtids
--exclude-gitds
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" /data/binlog/mysql-bin.000510
截取多个文件
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql
source recover.sql
GTID的幂特性
开启GTID后 mysql恢复binlog时，重复的gtid的事务不会执行了
想要恢复加参数
--skip-gtids
mysqlbinlog --include-gtids= "gtid1" --exclude-gtids="gtid2" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql
set sql_log_bin=0;
source recover.sql
set sql_log_bin=1;
mysqlbinlog --include-gtids= "xxx:gtid1-14" --exclude-gtids="xxx:gtid6-7" mysql-bin.000510 mysql-bin.000511 mysql-bin.000512 > /tmp/recover.sql
slowlog 慢日志
作用
记录mysql运行过程中 较慢的语句
帮组我们进行语句优化的工具日志
如何配置
是否开启
select @@slow_query_log
存放的位置
select @@slow_query_log_file
多慢算满sql
select @@long_query_time
my.cnf 中写入
slow_query_log = 1
slow_query_log_file = /data/xxx/db1slow.log
long_query_time = 5
log_queries_not_using_indexes = 1
mysqldumpslow
mysqldumpslow
-s
-c 统计次数
-t 5 前5条
mysqldumpslow -s -c -t 5 /data/slow.log
扩展
可视化展示slow-log
pt-query-digest + Amemometer
