MySQL日志管理

MySQL日志管理

错误日志

log_error
#作用
记录错误,警告,状态
#查看方式 
默认: DATADIR/hostname.err
找日志[ERROR]的上下文

#自定义配置
[root@mysql data]# vim /etc/my.cnf
log_error=/tmp/mysql.log
重启生效
#查看数据库日志路径
mysql> select @@log_error;

二进制日志

作用:
SQL层日志,存储的是SQL语句,属于逻辑层日志.
#配置方法
[root@mysql data]# vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin
说明:
/data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限
mysql-bin 	 : 二进制日志文件名的前缀
binlog_format=row --->5.7版本默认配置是row,可以省略
重启后生效

二进制文件记录内容

记录的数据库所有变更类的操作日志
DDL
DCL
DML

#DDL和DCL
直接以SQL语句的方式来记录.
#DML
记录的是已提交的事务DML语句
DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
statement(SBR):以语句模式记录日志,做说明命令,记录什么命令
row(RBR)      :行模式,记录数据行的变化
mixed(MBR)    :混合模式

二进制日志记录单元

#event事件
二进制日志的最小单元
DDL :  
create database oldguo;  事件1
对于DDL等语句是每一个语句就是一个事件

DML: 一个事务包含了多个语句
begin;    	 事件1
a 			 事件2
b			 事件3
commit;      事件4 

#event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.

二进制日志的管理

#查看二进制日志位置
mysql> show variables like '%log_bin%';

#查看所有已存在的二进制日志
mysql> show binary logs;

mysql> flush logs;#滚动一个新的日志

#查看正在使用的二进制日志
mysql> show master status;

#查看二进制日志事件
mysql> create database binlog charset utf8mb4;
mysql> use binlog
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      501 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)

mysql> commit;
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
| mysql-bin.000002 | 501 | Anonymous_Gtid |         6 |         566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 566 | Query          |         6 |         640 | BEGIN                                  |
| mysql-bin.000002 | 640 | Table_map      |         6 |         687 | table_id: 108 (binlog.t1)              |
| mysql-bin.000002 | 687 | Write_rows     |         6 |         727 | table_id: 108 flags: STMT_END_F        |
| mysql-bin.000002 | 727 | Xid            |         6 |         758 | COMMIT /* xid=14 */                    |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)

mysql> 

#查看二进制日志内容
[root@mysql binlog]# mysqlbinlog /data/binlog/mysql-bin.000002
#翻译数据行
[root@mysql binlog]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002

#截取二进制日志
[root@mysql binlog]# mysqlbinlog --start-position=219 --stop-position=335 /data/binlog/mysql-bin.000002

#单独过滤某个库的二进制日志
[root@mysql binlog]# mysqlbinlog -d ceshi mysql-bin.000002

#日志的删除
(1) 自动清理
mysql> select @@expire_logs_days;
(2) 手工清理
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'
(3) 全部清空
reset master;

通过二进制日志恢复数据

#模拟数据
mysql> create database ceshi charset utf8mb4;
mysql> use ceshi
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> 

#模拟故障
mysql> drop database ceshi;

#分析和截取binlog
mysql> show master status;	--确认使用的是哪一个日志
mysql> show binlog events in 'mysql-bin.000002';	--查看事件
#找到启点和终点进行恢复
[root@mysql binlog]# mysqlbinlog --start-position=823 --stop-position=1420 /data/binlog/mysql-bin.000002 > /mnt/bin.sql	

#恢复binlog
mysql> set sql_log_bin=0;	--临时关闭恢复时产生的新日志
mysql> source /mnt/bin.sql
mysql> set sql_log_bin=1;	--恢复日志

binlog的gtid记录模式的管理

GTID介绍

对于binlog中的每一个事务,都会生成一个GTID号码

DDL,DCL 一个event就是一个事务,就会有一个GTID号

DML语句来讲,begin到commit,是一个事务,就是一个GTID号

#GTID的组成
severi_uuid:TID
[root@mysql binlog]# cat /data/mysql/data/auto.cnf 
[auto]
server-uuid=d980595d-c934-11e9-bbfc-000c29d70b6d
[root@mysql binlog]# 
TID是一个:自增长的数据,从1开始
d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15
#GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制.

GTID的开启和配置

[root@mysql binlog]# vim /etc/my.cnf 
gtid-mode=on
enforce-gtid-consistency=true
[root@mysql binlog]# /etc/init.d/mysqld restart
[root@mysql binlog]# mysql -uroot -p123456
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 |      329 |              |                  | d980595d-c934-11e9-bbfc-000c29d70b6d:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

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

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      744 |              |                  | d980595d-c934-11e9-bbfc-000c29d70b6d:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database gtid;

#基于GTID的binlog恢复
#截取日志
[root@mysql binlog]# cd /data/binlog/
[root@mysql binlog]# mysqlbinlog --skip-gtids --include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:1-3' mysql-bin.000003 >/mnt/gtid.sql
--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
#恢复
mysql> set sql_log_bin=0;
mysql> source /mnt/gtid.sql;
mysql> set sql_log_bin=1;

#GTID相关参数
2.6.7 GTID相关的参数
#跳过GTID检查
--skip-gtids 

#包含
--include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:6','d980595d-c934-11e9-bbfc-000c29d70b6d:8'

#排除
--exclude-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:6','d980595d-c934-11e9-bbfc-000c29d70b6d:8'

慢日志

#作用
记录运行较慢的语句,优化过程中常用的工具日志
#配置
## 开关
slow_query_log=1 
## 文件位置及名字 
slow_query_log_file=/data/mysql/slow.log
## 设定慢查询时间
long_query_time=0.1
## 没走索引的语句也记录
log_queries_not_using_indexes

[root@mysql binlog]# vim /etc/my.cnf 
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
[root@mysql binlog]# /etc/init.d/mysqld restart

#分析慢语句
[root@mysql ~]# cd /data/mysql/
[root@mysql mysql]# vim slow.log 
[root@mysql mysql]# mysqldumpslow -s c -t 10 /data/mysql/slow.log