MySQL日志管理

港南四大炮亡

已于 2023-01-24 23:56:04 修改

阅读量404

点赞数

文章标签： mysql 数据库 java

于 2023-01-24 22:13:22 首次发布

本文链接：https://blog.csdn.net/lpx1249115962/article/details/128758570

版权

9.0. 什么是日志?
计算机遇到错误\不能使用以及需要管理员知道的信息等等这类问题,
也要和管理员汇报.汇报的方式就是写到文件里,这文件往往就叫日志文件

9.1 MySQL常用日志种类：
错误日志（Error log）
#当数据库启动、运行、停止时产生该日志 ==>error.log

普通查询日志（General query log） #==>nginx access.log
客户端连接数据库执行语句时产生该日志

*****二进制日志（Binary log）
当数据库内容[发生改变时产生该日志]，也被用来实现主从复制功能

*****慢查询日志（Slow query log） #==>
SQL语句在数据库查询超过指定时间产生该日志

9.2 错误日志

9.2.1 介绍
记录数据库从启动以来,状态\报错\警告等.

[root@db01 ~]# ll /data/3306/data/db01.err
-rw-r----- 1 mysql mysql 23653 4月 21 10:57 /data/3306/data/db01.err

配置:my.cnf
[mysqld_safe]
log-error = /data/3306/data/oldboy.err

[root@db01 ~]# /etc/init.d/mysqld restart

默认路径:/data/3306/data/oldboy.err

9.2.3 错误日志怎么用
每天定时巡检,主要关注[ERROR]和[WARNING]。

9.2.4 错误日志切割
[root@db01 data]# cd /data/3306/data/
[root@db01 data]# mv oldboy.err oldboy-$(date +%F).err
[root@db01 data]# mysqladmin -uroot -poldboy123 flush-logs

##切割结果
[root@db01 /data/3306/data]# ls /data/3306/data/|grep 2022
oldboy-2022-07-07.err
oldboy-2022-07-08.err
##按天分析,报警.也可以直接收集到ELK里.

9.2.4 排查错误
新手安装数据库时，遇到数据库无法启动时的排查方法为：
1)先把错误日志文件清空，然后重新启动MySQL服务，
再查看日志文件报有什么错误，并根据错误日志进行处理。

2)如果无法解决，则删除数据文件，重新初始化数据库。

3)遇到错误，关键字谷歌其次百度。

9.3. 普通日志(access.log) ##了解

9.3.1 介绍
默认是关闭的,因为会大量消耗磁盘IO.
可以记录MySQL中发生过的所有操作日志.一般用来调试.

9.3.2 如何配置
[mysqld]
general_log = ON
general_log_file = /data/3306/data/oldboy.log

[root@oldboy ~]# cat /data/3306/data/oldboy.log
/usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2021-12-12T03:15:48.572994Z   8 Connect   root@localhost on using Socket
2021-12-12T03:15:48.573597Z   8 Query   select @@version_comment limit 1
2021-12-12T03:15:49.916995Z   8 Query   show variables like '%general_log%'
2021-12-12T03:15:54.772789Z   8 Query   show variables like '%general_log%'
2021-12-12T03:16:02.494293Z   8 Query   show databases
2021-12-12T03:16:03.967361Z   8 Quit

9.3.3 普通查询日志作用
审计
调试
真正要审计,在客户端用堡垒机,跳板机jumpserver审计.
实际生产工作中极少使用普通查询日志,最多临时开一下..

9.4 二进制日志 (binlog) *****

9.4.1 介绍
以events形式,记录MySQL数据库中【变更类】的SQL操作日志(DDL\DCL\DML).

举例：
不会记录select,show
会记录crate,drop,alter,insert,update

9.4.2 作用

1.数据增量备份与恢复
2.主从复制功能nfs+sersync

mysql> show variables like '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_format | ROW |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+------------------------------------------------+----------------------+

其他binlog参数：
1)sync_binlog=1 #数据安全参数,数据落盘。性能差.
重点面试题:
mysql的双一是什么?

重点|面试1: sync_binlog=1
#【双一】的第一个1
1.保证事务提交前刷新binlog到磁盘。

#【双一】的第2个1
事务redo日志落盘，见后文。

2)一堆
max_binlog_size #最大binlog文件最大1G,如果超过则自动切割.

max_binlog_cache_size #最大binlog缓存大小
binlog_cache_size #当前binlog缓存大小.

原文链接：https://blog.csdn.net/lianggx3/article/details/116355284

3)binlog日志格式
binlog_format=row/statement/mixed
#binlog日志记录格式,默认ROW格式,早期statement格式.

重点|面试2: binlog_format=row/statement/mixed 格式有几种,区别.
格式区别:
1.行模式row(RBR)(5.6以后开始默认的格式,5.6是语句模式)
a.即使执行的SQL语句是一条,但是更新了很多行,binlog日志会记录每一行数据的真实变化，所以日志量会比较大.占磁盘空间大,消耗IO大，但是记录足够准确,数据不丢失.
b.只影响DML语句(按行记录),DDL和DCL仍然是Statement模式(按语句)记录.

2.statement(SBR)(5.7以前默认):
   a.记录发生的语句本身,不会按更改的行记录.日志量相对少.
   b.记录有可能不准确.例如特殊功能\函数\触发器\视图\存储过程
   c,有100万行记录,delete全删,记录一行delete语句日志.

   3.mixed混合模式
   智能记录

企业倾向选择:row格式,mysql官方也推荐行格式
因为数据库数据安全更重要.
默认是row:
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+

row模式测试:
#注释:
[root@db01 /data/3306/data]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
#default-character-set=utf8mb4

mysql> update oldboy.stu set sname="test" where age>20;

[root@oldboy data]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000030
一个更新语句在binlog日志里记录很多行,改变了4行记录,使用50行日志
### UPDATE `oldboy`.`stu`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboy' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @3=28 /* TINYINT meta=0 nullable=0 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @5='111' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */

statement模式测试:
mysql> set global binlog_format=statement;
退出再登陆
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
mysql> update oldboy.stu set sname="oldboy" where age>20;

[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000019|grep update
update oldboy.stu set sname="oldboy" where age>20

==========================================
35岁月薪4w 怎么被淘汰的:
20%牛逼能力给旁边价值观优秀能力也强给他加5000
80%普通老男孩学生1.5W

2万钱把4w的淘汰
==========================================

9.5 binlog应用

9.5.1 查看和分析binlog

1）查看二进制日志文件和大小.
mysql> show binary logs;
   +---------------+-----------+-----------+
   | Log_name | File_size | Encrypted |
   +---------------+-----------+-----------+
   | binlog.000001 | 179 | No |
   | binlog.000002 | 179 | No |
   | binlog.000003 | 179 | No |
   | binlog.000004 | 179 | No |

文件层面查看:
ls /data/3306/data -l|grep binlog

2）查看当前库记录二进制的文件及位置点.
   mysql> show master status;
   +---------------+----------+--------------+------------------+-------------------+
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +---------------+----------+--------------+------------------+-------------------+
   | binlog.000020 | 490 | | | |
   +---------------+----------+--------------+------------------+-------------------+
作用:
a.是备份文件和备份后更新的binlog的临界点
b.主从复制同步数据的临界点,也是起始位置

3）查看某个日志具体信息
mysql> show binlog events in 'binlog.000024';
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
| binlog.000020 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| binlog.000020 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000020 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000020 | 235 | Query | 1 | 324 | BEGIN |
| binlog.000020 | 324 | Query | 1 | 459 | update oldboy.stu set sname="oldgirl" where age>20 |
| binlog.000020 | 459 | Xid | 1 | 490 | COMMIT /* xid=33 */ |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+

4）文件层面查看与分析binlog
mysqlbinlog #解析binlog命令

--base64-output=decode-rows #行模式查看,真正恢复数据的时候,不要加此参数.
-vvv #输出,真正恢复的时候,不要加此参数.

binlog.000018
例子：
mysqlbinlog --base64-output=decode-rows -vvv binlog.000018

截取部分binlog数据:
a.文件大
b.个数多
c.不好查看

填充测试数据:

例子:
create database m;
use m;
create table m(id int);
insert into m values(2);
insert into m values(3);

create database n;
use n;
create table n(id int);
insert into n values(2);
insert into n values(3);

截取binlog日志,命令mysqlbinlog
日志记录形式:
1.时间
2.位置

#按时间截取日志参数
--start-datetime="2022-09-26 16:29:00"
--stop-datetime="22022-09-26 16:29:06"

例子:linux命令行
cd /data/3306/data
mysqlbinlog --base64-output=decode-rows --start-datetime="2022-09-26 16:29:00" --stop-datetime="2022-09-26 16:29:06" binlog.000024 #给定开始时间到给定结束时间

mysqlbinlog --start-datetime="2022-09-26 16:29:00" --stop-datetime="2022-09-26 16:29:06" binlog.000024 > /opt/bin.sql

mysql> source /opt/bin.sql

mysqlbinlog --base64-output=decode-rows --start-datetime="2022-09-26 16:29:00" binlog.000024 #到文件结尾
mysqlbinlog --base64-output=decode-rows --stop-datetime="22022-09-26 16:29:06" binlog.000024 #从开头到指定的时间

注意:
1.截取时间不一定是写着的时间,大于,小于几秒.
2.时间截取不精确的,生产不用.
3.https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-positions.html
4.官方建议:
仅使用 --start-datetime和 --stop-datetime 选项来帮助您找到感兴趣的实际事件位置。不建议使用这两个选项来指定要应用的二进制日志段的范围：使用这些选项时丢失二进制日志事件的风险更高。使用 --start-positionand --stop-position 代替。

#按位置截取日志参数（推荐）
--start-position=156
--stop-position=1706

如何找位置点?
方法1:
mysql> show binlog events in 'binlog.000024';
+---------------+-------+------------+-----------------------------------------------+
| Log_name | Pos |End_log_pos | Info |
+---------------+-------+------------+-----------------------------------------------+
| binlog.000024 | 18181 | 18276 | drop database n /* xid=722 */ |
| binlog.000024 | 18276 | 18353 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18353 | 18448 | drop database m /* xid=724 */ |
| binlog.000024 | 18448 | 18525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18525 | 18624 | create database m /* xid=726 */ |----
| binlog.000024 | 18624 | 18701 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18701 | 18805 | use `m`; create table m(id int) /* xid=731 */ |
| binlog.000024 | 18805 | 18884 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18884 | 18956 | BEGIN |
| binlog.000024 | 18956 | 19000 | table_id: 112 (m.m) |
| binlog.000024 | 19000 | 19040 | table_id: 112 flags: STMT_END_F |
| binlog.000024 | 19040 | 19071 | COMMIT /* xid=732 */ |
| binlog.000024 | 19071 | 19150 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19150 | 19222 | BEGIN |
| binlog.000024 | 19222 | 19266 | table_id: 112 (m.m) |
| binlog.000024 | 19266 | 19306 | table_id: 112 flags: STMT_END_F |
| binlog.000024 | 19306 | 19337 | COMMIT /* xid=733 */ |
| binlog.000024 | 19337 | 19414 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19414 | 19513 | create database n /* xid=734 */ |
| binlog.000024 | 19513 | 19590 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19590 | 19694 | use `n`; create table n(id int) /* xid=739 */ |
| binlog.000024 | 19694 | 19773 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19773 | 19845 | BEGIN |
| binlog.000024 | 19845 | 19889 | table_id: 113 (n.n) |
| binlog.000024 | 19889 | 19929 | table_id: 113 flags: STMT_END_F |
| binlog.000024 | 19929 | 19960 | COMMIT /* xid=740 */ |
| binlog.000024 | 19960 | 20039 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20039 | 20111 | BEGIN |
| binlog.000024 | 20111 | 20155 | table_id: 113 (n.n) |
| binlog.000024 | 20155 | 20195 | table_id: 113 flags: STMT_END_F |
| binlog.000024 | 20195 | 20226 | COMMIT /* xid=741 */ |
| binlog.000024 | 20226 | 20303 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20303 | 20398 | drop database m /* xid=742 */ |
| binlog.000024 | 20398 | 20475 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20475 | 20570 | drop database n /* xid=744 */ |
+---------------+-------+------------+-----------------------------------------------+
237 rows in set (0.00 sec)

方法2:
cd /data/3306/data
mysqlbinlog binlog.000024

例子:
mysqlbinlog --skip-gtids --start-position=18525 --stop-position=20226 binlog.000024 >/tmp/bin.sql
mysql> source /tmp/bin.sql
######
mysqlbinlog --skip-gtids --stop-position=1588 binlog.000035 >/tmp/bin.sql
mysqlbinlog --skip-gtids --start-position=1489 binlog.000035 >/tmp/bin.sql

错误坑:如果解析不加--skip-gtids,会报错.
ERROR 1790 (HY000) at line 198: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

注意:
1.位置点没有引号.
2.推荐此方式查看binlog:show binlog events in 'binlog.000035';
3.mysqlbinlog binlog.000035 ###不推荐

记录SQL语句在binlog的位置:
1.时间记录
2.数字位置
3.GTID格式记录,5.6新功能.

#gtid参数
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID

#-d 参数,恢复指定库的binlog
需求:只恢复一个库(drop database oldboy;)
解答:
mysqlbinlog -d oldboy binlog.000xxxx 恢复指定库的binlog

例子:只恢复m库的binlog
mysqlbinlog -d m --skip-gtids --start-position=18525 --stop-position=20226 binlog.000024 >/tmp/bin.sql

如何从binlog中截取一个表的日志:
同一个表的所有更新语句必然包含表名
1.grep,awk
2.binlog2sql工具.

一张表10亿条记录,删了10行,恢复.

9.5.2 数据损坏模拟和恢复--binlog应用增量恢复

故障模拟:
mysql> flush logs; ##从新文件,从156位置开始.

测试数据:
mysql> drop database oldboy;
mysql> create database oldboy;
mysql> use oldboy;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> select * from oldboy.t1;
mysql> drop table t1; #破坏语句

恢复思路：
恢复方法1:根据位置点精确恢复
指定位置点精确恢复,恢复到drop命令位置点以前的数据,drop语句不恢复.

1.确认对应的binlog日志
mysql> show master status; ###确认日志文件命名为binlog.000038

2.确定日志位置
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000038 | 3232 | | | |
+---------------+----------+--------------+------------------+-------------------+
mysql> show binlog events in 'binlog.000038'; ##分析要恢复的日志确定起始和结束位置.

| binlog.000038 | 534 | 611 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 611 | 726 | use `oldboy`; create table t1(id int) /* xid=63 */ |
| binlog.000038 | 726 | 805 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 805 | 882 | BEGIN |
| binlog.000038 | 882 | 932 | table_id: 100 (oldboy.t1) |
| binlog.000038 | 932 | 972 | table_id: 100 flags: STMT_END_F |
| binlog.000038 | 972 | 1003 | COMMIT /* xid=64 */ |
| binlog.000038 | 1003 | 1082 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 1082 | 1159 | BEGIN |
| binlog.000038 | 1159 | 1209 | table_id: 100 (oldboy.t1) |
| binlog.000038 | 1209 | 1249 | table_id: 100 flags: STMT_END_F |
| binlog.000038 | 1249 | 1280 | COMMIT /* xid=65 */ |
| binlog.000038 | 1280 | 1359 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 1359 | 1436 | BEGIN |
| binlog.000038 | 1436 | 1486 | table_id: 100 (oldboy.t1) |
| binlog.000038 | 1486 | 1526 | table_id: 100 flags: STMT_END_F |
| binlog.000038 | 1526 | 1557 | COMMIT /* xid=66 */ |
| binlog.000038 | 1557 | 1634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 1634 | 1765 | use `oldboy`; DROP TABLE `t1` /* generated by server */ /* xid=68 */ |
确定起始611,结束1634.

3.将记录的binlog文件解析成sql文件,并观察解析结果
cd /data/3306/data
[root@db01 /data/3306/data]# mysqlbinlog --skip-gtids --base64-output=decode-rows --start-position=611 --stop-position=1634 -vvv binlog.000038

4.生成文件,要去掉观察的参数(--base64-output=decode-rows -vvv)
[root@db01 /data/3306/data]# mysqlbinlog --skip-gtids --start-position=611 --stop-position=1634 binlog.000038 >/tmp/bin.sql

5.恢复到数据库
恢复方法1:
[root@db01 /data/3306/data]# mysql -uroot -poldboy123 oldboy </tmp/bin.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
恢复方法2
mysql -uroot -poldboy123 oldboy -e "source /tmp/bin.sql"

6.检查结果
[root@db01 /data/3306/data]# mysql -uroot -poldboy123 -e "select * from oldboy.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
证明binlog可以实现数据恢复

恢复思路2:(了解,后面会讲)
适合drop database oldboy;

1.先确定找出什么命令导致的破坏,drop database oldboy;

2.恢复的时候,包含删除破坏的drop命令,通过vim,sed去掉drop命令,然后在恢复.

恢复:
重新flush log\建库\建表去测试;

1.确认了执行了drop database oldboy
cd /data/3306/data
mysqlbinlog --skip-gtids --start-position=233 binlog.000039 >/tmp/bin1.sql

2.清除SQL文件中的drop database oldboy语句
[root@db01 /data/3306/data]# sed -i '/drop database oldboy/d' /tmp/bin1.sql
[root@db01 /data/3306/data]# grep drop /tmp/bin1.sql

3.导入
mysql -uroot -poldboy123 </tmp/bin1.sql
或
mysql> soruce /tmp/bin1.sql

4.检查
mysql -uroot -poldboy123 -e "select * from oldboy.t1;"

binlog日志企业应用一些问题:

1.一个库的日志,在多个日志文件里,如何恢复?
时间位置
binlog.000037 起始,文件结尾
binlog.000038 整个文件
binlog.000039 结束时间

命令:
mysqlbinlog --start-datetime="2022/07/11 9:17:34" binlog.000037 #给定开始时间到给定结束时间
mysqlbinlog binlog.000038 #给定开始时间到给定结束时间
mysqlbinlog --stop-datetime="2022/07/11 11:17:34" binlog.000039 结束时间
3个文件合并

100个binlog文件截取:
mysqlbinlog --start-datetime="2022/07/11 9:17:34" binlog.000037 #给定开始时间到给定结束时间
mysqlbinlog binlog.000001 binlog.000002 binlog.000003 .... >/bin/c.sql
mysqlbinlog --stop-datetime="2022/07/11 11:17:34" binlog.000039 结束时间

100个文件截取:例子:
mysqlbinlog binlog.000001 binlog.000002 binlog.000003 binlog.000004 binlog.000005 binlog.000006 binlog.000007 binlog.000008 binlog.000009 binlog.000010 binlog.000011 binlog.000012 binlog.000013 binlog.000014 binlog.000015 binlog.000016 binlog.000017 binlog.000018 binlog.000019 binlog.000020 binlog.000021 binlog.000022 binlog.000023 binlog.000024 binlog.000025 binlog.000026 binlog.000027 binlog.000028 binlog.000029 binlog.000030 binlog.000031 binlog.000032 binlog.000033 binlog.000034 binlog.000035 binlog.000036 binlog.000037 binlog.000038 binlog.000039 >/opt/a.sql
或
mysqlbinlog binlog.00* >/opt/b.sql
实际中使用位置截取

2.binlog是记录所有库表的日志,需要截取一个库的日志,怎么办?
mysqlbinlog -d 库名

3.binlog是记录所有库表的日志,需要截取一个表的日志,怎么办?
1)关键字：表名
grep 表名 /tmp/bin.sql

2)方法2
binlog2sql(Python工具)

3)方法3:借助临时库恢复
a.导出所有表结构恢复到测试库
b.然后把所有binlog数据恢复到临时数据库.
c.然后导出要恢复的某个表数据，就相当于过滤了一个表的binlog。

4.从建库开始,binlog不会一直保留,每天全备后,全备时刻以前的binlog就可以删掉了.
binlog保存多久?
公司每日全备:binlog保留3-7天
公司每周全备:binlog保留7-15天

恢复完整数据条件:
某一个时刻全备+这一时刻以后到下一次全备之前的所有binlog,就可以恢复完整数据.

5.如何恢复binlog sql文件时候不记录binlog
恢复binlog解析后的sql文件时候,可以临时不记录binlog
mysql> set sql_log_bin=0; ##临时不记录binlog,是session级别.
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; ##开启binlog.

6.位置点记录binlog问题:
位置是相对的,在每个文件里的位置是唯一的,如果换了新的binlog文件,
将来恢复可能是多个binlog文件,
或者多台mysql实例的binlog文件,每个文件当中的位置点都会重复.
位置点恢复不能跨越binlog文件.

有么有所有binlog文件都是唯一的,所有服务器的binlog文件每个位置都是唯一的?
答:5.6加的功能,GTID功能

9.6 GTID

9.6.1 简介
GTID全称Global Transaction ID，全局事务ID，5.6开始起用的功能。

9.6.2 作用
1)在整个binlog中(所有主机的binlog文件)每行日志记录都是唯一ID值.
2)不管有多少个binlog文件,ID都是连续生成的.
3)具备幂等性(运行过的就不会在运行了).

9.6.3 特点:
1)长啥样
1ced0886-23d4-11eb-9768-000c29f4772b:1
server_uuid :NO.

2)server_uuid,唯一的标识一个实例的ID号
server_uuid是自动生成的
#查看
[root@db01 data]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=3d5846bb-bec3-11ec-bd1d-000c296ebb8f
#如需改变它,删除/data/3306/data/auto.cnf后重启

9.6.4 参数配置
mysql> show variables like "%gtid_%";

set global gtid_mode=ON; #开启GTID记录日志格式开关
set global enforce_gtid_consistency=ON; #强制一致性。。

报错解决:
mysql> set global gtid_mode=ON;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
mysql>
mysql> set global gtid_mode=OFF_PERMISSIVE;

mysql> set global gtid_mode=ON_PERMISSIVE;

mysql> set global enforce_gtid_consistency=ON;

mysql> set global gtid_mode=ON;

永久开:vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=ON

9.6.5 练习

mysql> drop database m;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000043 | 328 | | | c41d951e-00e8-11ed-9d81-000c290a8470:1 | ###GTID
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

测试:
create database oldboy2;
use oldboy2;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000023 | 1592 | | | 3d5846bb-bec3-11ec-bd1d-000c296ebb8f:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in "binlog.000043";
+---------------+------+-----------------------------------------------------------------------+
| Log_name | Pos | Info |
+---------------+------+-----------------------------------------------------------------------+
| binlog.000043 | 4 | Server ver: 8.0.26, Binlog ver: 4 |
| binlog.000043 | 125 | |
| binlog.000043 | 156 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:1' |
| binlog.000043 | 233 | drop database r /* xid=15 */ |
| binlog.000043 | 328 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:2' |
| binlog.000043 | 405 | drop database p /* xid=18 */ |
| binlog.000043 | 500 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:3' |
| binlog.000043 | 577 | drop database o /* xid=21 */ |
| binlog.000043 | 672 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:4' |
| binlog.000043 | 749 | create database oldboy2 /* xid=30 */ |
| binlog.000043 | 866 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:5' |
| binlog.000043 | 943 | use `oldboy2`; create table t1 (id int) /* xid=35 */ |
| binlog.000043 | 1061 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:6' |
| binlog.000043 | 1140 | BEGIN |
| binlog.000043 | 1218 | table_id: 94 (oldboy2.t1) |
| binlog.000043 | 1269 | table_id: 94 flags: STMT_END_F |
| binlog.000043 | 1309 | COMMIT /* xid=36 */ |
| binlog.000043 | 1340 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:7' |
| binlog.000043 | 1419 | BEGIN |
| binlog.000043 | 1497 | table_id: 94 (oldboy2.t1) |
| binlog.000043 | 1548 | table_id: 94 flags: STMT_END_F |
| binlog.000043 | 1588 | COMMIT /* xid=37 */ |
| binlog.000043 | 1619 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:8' |
| binlog.000043 | 1698 | BEGIN |
| binlog.000043 | 1776 | table_id: 94 (oldboy2.t1) |
| binlog.000043 | 1827 | table_id: 94 flags: STMT_END_F |
| binlog.000043 | 1867 | COMMIT /* xid=38 */ |
| binlog.000043 | 1898 | SET @@SESSION.GTID_NEXT= 'c41d951e-00e8-11ed-9d81-000c290a8470:9' |
| binlog.000043 | 1975 | use `oldboy2`; DROP TABLE `t1` /* generated by server */ /* xid=39 */ |
+---------------+------+-----------------------------------------------------------------------+
29 rows in set (0.00 sec)

9.6.6 基于GTID模式截取binlog日志

截取1-6:6个事务
--skip-gtids ##忽略GTID

--include-gtids ##包含GTID

--exclude-gtids ##排除GTID

#取3-6,然后对比上面结果看.
mysqlbinlog --skip-gtids --include-gtids="c53c4773-3d90-11ed-906c-000c29d43ef6:3-6" binlog.000031|egrep -v "/\*|#"

#恢复1-4,排除2
mysqlbinlog --skip-gtids --include-gtids="c53c4773-3d90-11ed-906c-000c29d43ef6:1-4" --exclude-gtids="c53c4773-3d90-11ed-906c-000c29d43ef6:2" binlog.000031

注意:如果需要截取的日志需要在原库恢复,过滤时需要加--skip-gtids参数,跳过导出文件gtid的记录.

GTID:全局唯一事务ID:GTID幂等性
1.binlog日志的SQL语句运行过了,通过binlog恢复,会有GTID标识,在执行就不让执行了.
2.原库恢复,过滤时需要加--skip-gtids参数,去SQL语句GTID标识,这样过滤的BINLOG对应的SQL语句就可以执行了.

测试对比例子:
mysqlbinlog --include-gtids="c53c4773-3d90-11ed-906c-000c29d43ef6:1-4" binlog.000031 >/opt/a.sql
mysqlbinlog --skip-gtids --include-gtids="c53c4773-3d90-11ed-906c-000c29d43ef6:1-4" binlog.000031 >/opt/b.sql
vimdiff /opt/b.sql /opt/a.sql #发现b少了GTID标识语句.

9.6.5 binlog日志切割：

1.为什么需要日志切割？
1.太大。
2.分析不方便
3.传输到从库，备份。

2.日志切换文件的触发机制:4种. 面试题：

1)mysql内部命令刷新,启动新binlog记录日志
mysql> flush logs;

2)命令行mysqladmin刷新
[root@oldboy data]# mysqladmin -uroot -poldboy123 flush-logs

3）文件大小达到1G,自动切割
mysql> select @@max_binlog_size;

4）重启数据库

9.6.5 binlog日志删除

1.为什么要删除binlog日志?
答:太大,占空间,而且一旦有每日全备份,备份时刻前的binlog就无用了.

2.什么删除策略？
每天/每周/每月,保留3天/7天??

   生产binlog删除策略:
   1)取决于数据库的备份频率:
   每天全备一次，理论只留一天binlog日志就够了,此时留建议3-7天。
   每周备份一次，理论只留7天binlog日志就够了,建议7-15天。
GB每日备份,TB级别每周备份,PB,EB数据甚至一个月一次全备,binlog也要备份。

中小企业按天全备,30-60GB级别,binlog日志留3-7天.

2.删除方法

binlog_expire_logs_seconds=2592000 ###30天过期，默认值是一个月.

binlog_expire_logs_seconds=3600*24*7 ###7天过期

mysql> select 3600*24*7;
+-----------+
| 3600*24*7 |
+-----------+
| 604800 |
+-----------+

b.改为7天,配置方法
临时:
mysql> set global binlog_expire_logs_seconds=604800;
永久:
[root@db01 ~]# vim /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
binlog_expire_logs_seconds=604801

b.方法2:手工清理

#查询binlog
show BINARY LOGS;

##基于文件清理,删除05以前,不含05.
purge binary logs to 'binlog.000005';

##按时间清理
PURGE BINARY LOGS BEFORE '2022-09-21 00:00:00'; ###实际删除的是21日以前,不含21日

c.全部重置
reset master;
[root@db01 /data/3306/data]# ls binlog* -l
-rw-r----- 1 mysql mysql 156 7月 11 16:30 binlog.000001
-rw-r----- 1 mysql mysql 16 7月 11 16:30 binlog.index

问题:能用rm -f在binlog日志目录下删除么? 尽量不要..
如果非要删,最后进DB里去reset master,重新生成;

9.6.6 binlog如何备份?

1)单机如何备份binlog：
文件系统上层:
1.rsync定时每分钟(丢1分钟binlog)
2.rsync+sersync实时，基于文件系统。

3.mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/

解释如下：
--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。
binlog.000001 ：代表从哪个binlog开始复制。
--result-file=/opt/ :输出到哪个路径下.

实践远程备份(--read-from-remote-server)：
A51数据库服务器用户和授权:
mysql> create user repl@'10.0.0.%' identified by 'oldboy123';
mysql> grant replication slave on *.* to repl@'10.0.0.%';
mysql> flush privileges;

在B机器上执行；
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/ &

A机器上测试写入：
drop database oldboy3;
create database oldboy3;
use oldboy3;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;

B机器上查看结果；
[root@db01 ~]# cd /opt/
[root@db01 opt]# ll
总用量 893380
-rw-r----- 1 root root 4142 4月 22 10:37 binlog.000001
[root@db01 opt]# mysqlbinlog binlog.000001|tail -5
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*/;

注意:当数据库重启的时候,下面的日志获取进程会被停止,需要重新指定从哪个文件开始备份
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never /data/3306/data/binlog.000002 --result-file=/opt/ &

2)主从复制
M---S
binlog日志自动复制到从库S,然后在从库可以记录BINLOG,同时把binlog解析出SQL,执行将数据放到数据库里.

运维4大职责；
1.数据不丢不泄露
2.服务7*24服务能力
3.用户体验，速度。
4.效能效率。

9.7 [慢]查询日志

9.7.1 什么是[慢]查询日志?
记录执行较慢的select查询语句的一种日志.

DBA工作中，找出慢的SQL，优化快了，是DBA的核心工作。----面试主流。

为什么要记录慢查询?
找到执行慢的查询语句,让数据库运行效率更高.

揪出[慢查询语句],提高数据库的【翻台率】,单位时间并发就大。

9.8.2 配置

导致SQL语句慢的尽可能都记录在案.
slow_query_log = ON #<==慢查询开启开关
long_query_time = 1 #<==记录大于1秒的SQL语句。
log_queries_not_using_indexes = ON #<==没有使用到索引的SQL语句。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
slow-query-log-file = /data/3306/data/slow.log #<==记录SQL语句的文件(ELK收集起来)。

[root@db01 ~]# cat /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
#慢查询
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON
slow-query-log-file = /data/3306/data/slow.log
min_examined_row_limit = 800

在线修改；
mysql> set global slow_query_log=on;
mysql> set global long_query_time=0.01;
mysql> set global log_queries_not_using_indexes=1;

在线查看；
mysql> select @@slow_query_log;
mysql> select @@long_query_time;
mysql> select @@log_queries_not_using_indexes;

9.8.3 模拟慢语句
1.导入100万条记录
t100w_oldboy.zip传到/opt下
unzip t100w_oldboy.zip
mysql> source /opt/t100w_oldboy.sql

2.查看
mysql> select count(*) from oldboy.t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.11 sec)

mysql> select * from oldboy.t100w limit 10;
+------+--------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+--------+------+------+---------------------+
| 1 | 25503 | 0M | IJ56 | 2019-08-12 11:41:16 |
| 2 | 756660 | rx | bc67 | 2019-08-12 11:41:16 |
| 3 | 876710 | 2m | tu67 | 2019-08-12 11:41:16 |
| 4 | 279106 | E0 | VWtu | 2019-08-12 11:41:16 |
| 5 | 641631 | At | rsEF | 2019-08-12 11:41:16 |
| 6 | 584039 | QJ | VWlm | 2019-08-12 11:41:16 |
| 7 | 541486 | vc | ijKL | 2019-08-12 11:41:16 |
| 8 | 771751 | 47 | ghLM | 2019-08-12 11:41:16 |
| 9 | 752847 | aQ | CDno | 2019-08-12 11:41:16 |
| 10 | 913759 | ej | EFfg | 2019-08-12 11:41:16 |
+------+--------+------+------+---------------------+
10 rows in set (0.00 sec)

3.模拟数据压测：Linux命令行
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from t100w where k2='CDno'" engine=innodb \
--number-of-queries=200 -uroot -poldboy123 -verbose

4.慢查询日志结果
[root@db01 /opt]# tail /data/3306/data/slow.log
# Time: 2022-07-11T09:00:34.674347Z
# User@Host: root[root] @ localhost [] Id: 64
# Query_time: 44.575963 Lock_time: 0.000088 Rows_sent: 1062 Rows_examined: 1000000
SET timestamp=1657529990;
select * from t100w where k2='CDno';
# Time: 2022-07-11T09:00:34.675657Z
# User@Host: root[root] @ localhost [] Id: 105
# Query_time: 50.121352 Lock_time: 0.000083 Rows_sent: 1062 Rows_examined: 1000000
SET timestamp=1657529984;
select * from t100w where k2='CDno';

9.8.4 分析慢日志
1.工具
mysqlsla和mysqldumpslow慢查询分析工具外，还有一些第三方分析工具，
如：pt-query-diges、 myprofi、mysql-explain-slow-log、mysqllogfilter，读者感兴趣可以深入研究。
========================================
几十万条,有完全重复的,有主体重复的.

2.优化策略:
ASQL,一天执行了10次,每次执行了10秒,一共待100秒====100秒.
BSQL 一天10万次,每次执行了2秒，20万秒
===>先优化BSQL

B是优先优化的语句
每次执行了10秒*执行次数,积累排名.按消耗的总时间优化SQL,而不是单个语句执行的总时间.

3.分析方法说明

1)mysqldumpslow分析方法:

[root@db01 /opt]# mysqldumpslow -s c -t 3 /data/3306/data/slow.log

Reading mysql slow query log from /data/3306/data/slow.log
Count: 200 Time=50.79s (10158s) Lock=0.31s (62s) Rows=1062.0 (212400), root[root]@localhost
select * from t100w where k2='S'

Died at /usr/local/mysql/bin/mysqldumpslow line 162, <> chunk 200.

[root@oldboy data]# mysqldumpslow -s c -t 3 slow.log

Reading mysql slow query log from oldboy-slow.log
Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (40), root[root]@localhost
select * from t100w where num<N limit N

Count: 3 Time=0.55s (1s) Lock=0.00s (0s) Rows=20.0 (60), root[root]@localhost
select count(*) from t100w where num<N group by k2,k1 limit N

Count: 3 Time=0.53s (1s) Lock=0.00s (0s) Rows=13.3 (40), root[root]@localhost
select count(*) from t100w where num<N group by num,k1 limit N

2)但使用mysqlsla处理后，结果呈现为：
Count : 23 (8.52%)
Time : 102 s total, 4.434783 s avg, 3 s to 7 s max (6.79%)
95% of Time : 88 s total, 4.190476 s avg, 3 s to 6 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 1 avg, 1 to 1 max (0.02%)
Rows examined : 11.53k avg, 5.70k to 17.60k max (1.07%)
Database : bbsdb
Users :
oldboy@ 172.16.1.51 : 86.96% (20) of query, 11.11% (30) of all users
bbs@ 172.16.1.52 : 13.04% (3) of query, 2.96% (8) of all users
Query abstract:
SELECT MIN(BBS_HI_id) AS BBS_HI_id FROM t_******** WHERE BBS_HI_isteammate=N AND BBS_HI_editor_user_id_encrypt='S';
Query sample:
select min(BBS_HI_ID) AS BBS_HI_ID from t_******** where BBS_HI_ISTEAMMATE=1 and BBS_HI_EDITOR_USER_ID_ENCRYPT='asdfEWERADFS';

3)企业可视化分析展示

9.8.5 运维人员如何优化？

运维人员:
1.建立一些基本索引,除此之外，把语句抛给开发。

2.DBA人员|高级运维:深入优化
1)建立合适索引
2)给出语句修改建议，DBA要和开发商量。

3.优化方法
1)建立合适索引
2)给出语句修改建议，DBA要和开发商量。
3)在从库查询,分析SQL作用,如果后台使用,使用从库查询或修改语句功能,
4)从产品设计，原来登录前可以查,能不能登陆后在搜索.
5)分析web日志,封ip
6)redis放在DB前面做缓存。
7)搜索用ES

9.8.6 项目案例:药房网数据库负载高案例:

1.数据库负载高\访问慢
2.show proceslist;或者看慢查询日志
select * from oldboy where yaoming like "%治疗艾滋秘方%"; ###来自前天搜索框.

3.爬虫导致的.
1)分析web日志或连接数,封ip
2)增加几个从库,专门做搜索,不影响主库和其他从库正常网页访问.

4.长期方案
3)从产品设计，原来登录前可以查,能不能登陆后在搜索.
4)redis放在DB前面做缓存。
5)搜索用ES,不要用数据库做搜索

9.8.7 SQL优化流程:建立一个微信群,SQL语句优化,SQL分析好--展示页面.每天优化一条.
运维[dba]-运维总监--研发项目程序员--[j2ee核心架构师]--研发经理--研发总监--CTO

运维[dba]--j2ee核心架构师交流，操作邮件给其他人。

9.8.8 SQL提交审核流程
开发提交----DBA审核----通过才能执行.
sql审核平台——Yearning
https://blog.csdn.net/qq_30285985/article/details/120738750

9.8.9 慢查询日志切割(了解),实际工作都是ELFK收集及可视化展示
Date=`date +%F -d -1day` #定义时间
cd /data/3306/data &&\ #切换路径
mv slow.log slow.log_$Date &&\ ##MV
mysqladmin -uroot -poldboy123 flush-log ##生成原始slow.log

慢查询切割
#!/bin/bash
#Author: oldboy
#Organization: www.oldboyedu.com
#Created Time : 2018-03-19 23:47:21
export PATH=/data/3306/data/slow/bin:/sbin:/bin:/usr/sbin:/usr/bin
Date=`date +%F -d -1day`

#cut slow log
cd /data/3306/data/slow &&\
mv slow.log slow.log_$Date &&\
mysqladmin -uroot -poldboy123 flush-log

#analyze slow log
#Time=`date +%F`
#Path=/usr/local/bin/mysqlsla
#cd /data/3306/data/slow &&\
#$Path/mysqlsla -lt slow slow.log_$Date >analyzed_slow_$Date.log 2>&1

#rsync analyzed_slow to backup server同步到备份服务器

#备份服务器发邮件
#send analyzed slow log to adminstrator on backup server by mail.

主流:自动化运维：devops平台:
ELK把慢查询日志收集到ELK平台,
当然还可以把日志收集到数据库中或使用ELK等流行的工具收集慢查询日志，
最后分析后可视化展现，图可视化展现.

9.8.10 MySQL日志管理篇面试题

1.5 MySQL日志管理篇面试题
1.5.1 错误日志功能，如何配置错误日志，如何查看?
1.5.2 如何配置二进制日志？
1.5.3 二进制日志如何查看事件？
1.5.4 二进制日志有哪些格式？有什么区别？你们公司用什么格式？
1.5.5 什么是双一标准？
1.5.6 什么是2PC？
1.5.7 你是如何截取二进制日志的？传统日志、GTID日志有什么不同点？
1.5.8 如何配置慢日志？
1.5.9 慢日志如何处理？慢日志在什么时候使用，如何用？