day06-日志管理

日志管理

general log 普通日志

   1.1 作用 
   记录从登陆开始,所有的操作日志.
   默认没有开启.调试时会开启.
   1.2 配置 
   mysql> show variables like '%general%';
   +------------------+--------------------------+
   | Variable_name    | Value                    |
   +------------------+--------------------------+
   | general_log      | OFF                      |
   | general_log_file | /data/3306/data/db01.log |
   +------------------+--------------------------+

mysql> set global general_log=1;
tail -f db.log

error log 错误日志

2.1 作用 
记录数据库启动以来,状态 报错 警告....
默认开启.

2.2 配置方法 
log_error=xxx
log_error_verbosity=3

搜索 [ERROR] 上下文.

binary log 二进制日志

1 作用 
以二进制格式,记录所有数据库修改类的操作日志.
数据备份,主从.

2 配置  ## 8.0版本之后，自动开启
log_bin            			开关       
log_bin_basename  			存放路径的文件名前缀 /data/3306/binlog/mysql_bin

mkdir -p /data/3306/binlog 
chown -R mysql. /data 

vim /etc/my.cnf 

#添加参数
server_id=51
log_bin=/data/3306/binlog/mysql-bin

3 存储格式 ****** 
binlog 是以event（事件）作为最小单元,记录日志.
针对: DDL DCL,事件直接以Statement(语句模式)
针对: DML语句 (update  delete  insert ),记录格式受binlog_format参数控制
格式种类: 
		statement : 语句模式.做啥记录啥.
		row       : 行模式.记录行变化.
		mixed     : 混合模式. 大几率是statement

区别:  
	  statement : 日志量少.可读性强,随机函数类操作时,记录有可能错误数据.
	  		## 例子：当使用关于时间的随机函数时，那么相同的操作，不同的时间得的就是不同的值。
	  ROW       : 日志量大.可读性弱.数据记录准确. ## 针对DML 
	  				

生产建议 ROW .默认也是row 5.6版本之后


4 查询日志  
mysql> show binary logs; 	刷新多次
mysql> show binary logs;	展示binlog日志 ，都只会往最后一个日志文件里写
mysql> show master status ;  查看当前使用文件的状态
		## Position ： 偏移量，可以理解为语句操作之后，改变了多少字节，从哪个偏移点开始，到哪个偏移点结束。
		## 当我们在执行begin，又执行DML的insert时，只要没有commit，那么就不会进行数据增加
		
mysql> show binlog  events in 'mysql-bin.000005';  
		## 展示的是日志文件状态。一行就是一个事件。前两行不用看，是头部信息。
		pos:     			一个事件起始的偏移数
		End_log_pos:		这个事件完结的偏移数。


		`使用专门的二进制命令进行对文件的查看
			简单查看，会直接输入到面板上，，at 后是我们的偏移点
[root@db01 binlog]# mysqlbinlog mysql-bin.000005    
			如果想详细的看，begin中的DML语句，可以进行加载参数，进行详细的翻译
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000005


3.5 数据恢复 
a. 分析 
确认起点和终点
b. 截取日志
mysqlbinlog --start-position=xx   --stop-position=yy /data/3306/binlog/mysql-bin.000005 >/tmp/bin.sql （命令行）
c. 恢复日志 
set sql_log_bin=0;    ## 临时关闭当前会话二进制日志的开关。因为本身就是在日志中截取的一段内容，所以在执行的时候，没必要让他在产生日志数据，就忽略掉。
source /tmp/bin.sql
set sql_log_bin=1;



彩蛋: 以上场景,如果在生产中会有什么痛点?

1. 数据库运行了很多年了,怎么恢复?
   备份恢复+binlog恢复   ##先找到最近的备份恢复的时间，先恢复到最近的，然后在进行binlog恢复

2. 需要的日志跨了多个文件 ? 

pos不支持跨文件.只在本文件中生效。。
	可以使用：时间或者GTID.  
		## 但是时间是不准确的，因为一秒钟会做很多的操作。所以如果想一次性提取，就用GTID

3. binlog记录了所有库,所有表的 
   mysqlbinlog -d oldguo ,可以解决单独一个库的日志过滤
   binlog2sql 工具 可以这针对表的级别,截取日志.

4. drop操作之后还有新的操作.
	这样就需要分段截取了，一段一段，截取自己想要的那一段


## 扩展
 GTID （5.6才出的，是5.7火起来的）
	不管你有多少文件，不管你这些文件从哪里来，我都会给你打上一个事务的标记（唯一的）。它这个号码都是不变的，是单调递增的。然后到时候提取的时候，就可以直接提取标号，也就可以随意跨文件了

1 介绍 
server_uuid:TID（自己自增的数字，从1开始）
## UUID是在/data/3306/data/auto.cnf中存的就是uuid，是在初始化的时候生成的。全局唯一的
	
幂等性：其实就是对于一致性的不操作
	## 在开启了GTID，那么在进行一个二进制日志的恢复的时候，就会先检查GTID的幂等性 是否已经操作过了。如果操作过了，就会跳过。

2 配置 
	## 5.7 之后，如果不手动开启GTID，会自动生成一个匿名的，但是我们使用不了。
vim /etc/my.cnf 
enforce_gtid_consistency=on  强制GTID的一致性
gtid_mode=on				GTID的开关
log_slave_updates=ON		默认是打开的

当你重启配置文件，进行全局生效之后。
mysql> mysql
mysql> create database oldlei;
mysal> show master status
Executed_Gtid_Set                     
--------------------------------------
bd3c75c7-3db3-11eb-8cac-000c29033daa:1
	## 就会在最后，看到这个的一个编号，前边是UUID，冒号后边是单调递增的数，从1开始

mysql> use oldlei;
mysql> create table t1(id int);
mysal> show master status
Executed_Gtid_Set                        
-----------------------------------------
bd3c75c7-3db3-11eb-8cac-000c29033daa:1-2 
	## 当你在切入到数据库，创建一个表，然后在次查看，会发现，1变成2了.1-2的意思就是从1开始，现在是2.
	## 对于一个DDL语句，一个操作就是一个事件。对于DML，需要重begin 到 commit 的结束，才算一个事件的结束。


	当使用GTID的编号的时候，就需要查看
	show binlog  events in 'mysql_bin.000006';
		info
		SET @@SESSION.GTID_NEXT= 'bd3c75c7-3db3-11eb-8cac-000c29033daa:1'
		## 这个现在就不能忽略了，这个就是你需要使用的GTID编号，这个是在你的操作之上的
	
		
3 基于GTID的数据恢复（这里有一个幂等性的问题，所以需要跳过gtid。让它重新进行加载，否则报错）
[root@db01 data]# mysqlbinlog --skip-gtids --include-gtids='95972e36-43fe-11eb-a366-000c2905f029:1-3' /data/3306/binlog/mysql-bin.000006 >/tmp/gtid.sql


跳过某一个事件：
mysqlbinlog --skip-gtids --include-gtids='95972e36-43fe-11eb-a366-000c2905f029:1-10' --exclude-gtids='95972e36-43fe-11eb-a366-000c2905f029:4'   ......

扩展内容： binlog2

binlog2sql 工具使用 

a. 作用
将日志格式化成SQL,辅助我们查看分析日志内容.
误操作的闪回.


b. 安装 
[root@db01 opt]# unzip  binlog2sql-master.zip
[root@db01 binlog2sql-master]# yum install -y python3 
[root@db01 opt]# pip3 install -r requirements.txt -i https://mirrors.aliyun.com/pypi/simple/
	## PyMySQL==0.9.3


c. 单独过滤某张表的binlog
mysql> create table t2 (id int not null primary key auto_increment ,name varchar(32) not null);
mysql> insert into t2 values(1,'a'),(2,'b'),(3,'c');

[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uremote -p123 -d oldguo -t t2 --start-file='mysql-bin.000008'



d. 单独过滤某些类型的binlog
[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=delete  --start-file='mysql-bin.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5172 end 5416 time 2020-09-18 09:17:48 gtid 

[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=update  --start-file='mysql-bin.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4882 end 5141 time 2020-09-18 09:17:35 gtid 

[root@db01 binlog2sql-master]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test1 -t t1 --sql-type=insert  --start-file='mysql-bin.000003'


e. 生成指定事件回滚语句
应用场景： 3000万数据，误删10行数据，怎么恢复？

[root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1  --start-file='mysql-bin.000003'   --sql-type=delete --start-position=932 --stop-position=1198 -B

[root@db01 binlog2sql]# python3 binlog2sql.py  -h 10.0.0.51 -P3306 -uroot -p123 -d test -t t1  --start-file='mysql-bin.000003'   --sql-type=delete --start-position=932 --stop-position=1198 -B>/tmp/flashback.sql

slow log

1 作用 
记录运行过程中较慢的语句.
辅助管理员进行优化.

2 配置 
默认没有开启. 
mysql> select @@slow_query_log;

设置开关:
mysql> set global slow_query_log=1;

设置位置点:
mysql> select @@slow_query_log_file;
+-------------------------------+
| @@slow_query_log_file         |
+-------------------------------+
| /data/3306/data/db01-slow.log |
+-------------------------------+

设置阈值: 
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> set global long_query_time=0.01;

不走索引的: 
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+


4.3 模拟慢语句.
添加一些查询的语句，尽量多而且慢，不走索引的。

4.4 分析 
[root@db01 data]# mysqldumpslow -s c  -t 3  db01-slow.log
	##  -s 排序。是按照出现的次数排的
	##  -t 前几名