Linux
Nginx,MySQL,PHP/Python

MySQL日志管理

错误日志(log_error)

作用
记录启动,关闭,日常运行过程中,状态信息,警告,错误
配置
默认是开启的:/数据路径下/hostname.err
手工设定:
vim /etc/my.cnf
log_error=/var/log/mysql.err
log_timestamps=system    时区,设置为系统时间
重启生效

binlog(binary logs) 二进制日志*****

作用
1.备份恢复必须依赖二进制日志
2.主从环境必须依赖二进制日志基础参数:
基础参数

开关 
3306 [(none)]>select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

日志路径及名字
3306 [(none)]>select @@log_bin_basename;
+------------------------+
| @@log_bin_basename     |
+------------------------+
| /data/binlog/mysql-bin |
+------------------------+
1 row in set (0.00 sec)

服务ID号
3306 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

二进制日志格式
3306 [(none)]>select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

双一标准之二
3306 [(none)]>select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
配置
注意,mysql默认是没有开启二进制日志的

创建日志目录
mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog
修改配置文件
vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin    此处可以直接写路径
binlog_format=row

重启生效

1.server_id
主要是在主从复制过程中必须加的,但是在5.7版本中,要用一下参数,开启binlog日志,即使是单机,也是必须加的
2.log_bin=/data/binlog/mysql-bin
开启二进制日志功能
设置二进制日志目录及名称前缀
3.binlog_format=row
binlog的记录格式

binlog记录了什么

binlog是sql层的功能,记录的是变更sql语句,不记录查询语句
记录SQL语句种类
DDL:原封不动的记录当前DDL(statement语句方式)
DCL:原封不动的记录当前DCL(statement语句方式)
DML:只记录已经提交的事务DML
DML三种记录格式
binlog_format=
1.statement(5.6默认),SBR(statement based replication):原封不动的记录当前DML
2.ROW(5.7默认)RBR(ROW based replication):记录数据行的变化(用户看不懂,需要工具分析)
3.mixed(混合)MBR(mixed based replication):以上两种模式的混合

SBR和RBR模式的对比
STATEMENT:可读性较高,日志量少,但是不够严谨
ROW:可读性很低,日志量大,足够严谨
建议使用ROW记录模式

event(事件)

简介
二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句讲,只记录已提交的事务
event组成
1.事件的开始标识
2.事件内容
3.事件的结束标识
position号
开始标识:at 111
结束标识:end_log_pos  222
某个事件在binlog中的相对应位置号
为了方便截取日志

日志操作

查看有多少个binlog
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      2256 |
| mysql-bin.000002 |       217 |
| mysql-bin.000003 |    690524 |
| mysql-bin.000004 |      1501 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |      6168 |
| mysql-bin.000007 |       217 |
| mysql-bin.000008 |       194 |
+------------------+-----------+
8 rows in set (0.00 sec)

重启一个新binlog
3306 [(none)]>flush logs;
Query OK, 0 rows affected (0.02 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      2256 |
| mysql-bin.000002 |       217 |
| mysql-bin.000003 |    690524 |
| mysql-bin.000004 |      1501 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |      6168 |
| mysql-bin.000007 |       217 |
| mysql-bin.000008 |       241 |
| mysql-bin.000009 |       194 |
+------------------+-----------+
9 rows in set (0.00 sec)

查看正在使用那个文件
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 |      194 |              |                  | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
查看某个日志内容
3306 [(none)]>show binlog events in 'mysql-bin.000009';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                      |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000009 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4     |
| mysql-bin.000009 | 123 | Previous_gtids |         6 |         194 | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
Log_name:binlog文件名
Pos:开始的position号
Event_type:事件类型
  Format_desc:格式描述,每一个日志文件的第一个事件,多用户没有意义,MySQL识别binlog必要信息
Server_id:MySQL服务号标识
End_log_pos:事件的结束位置号
Info:事件内容

日志查看

查看某个文件
[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000006 |head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619  8:55:42 server id 6  end_log_pos 123 CRC32 0x74764639 	Start: binlog v 4, server v 5.7.20-log created 190619  8:55:42 at startup
ROLLBACK/*!*/;
BINLOG '
DogJXQ8GAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAOiAldEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATlGdnQ=
'/*!*/;
# at 123
#190619  8:55:42 server id 6  end_log_pos 194 CRC32 0x6d9bb0ea 	Previous-GTIDs


转换上面命令乱码信息
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000006|head -20
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190619  8:55:42 server id 6  end_log_pos 123 CRC32 0x74764639 	Start: binlog v 4, server v 5.7.20-log created 190619  8:55:42 at startup
ROLLBACK/*!*/;
# at 123
#190619  8:55:42 server id 6  end_log_pos 194 CRC32 0x6d9bb0ea 	Previous-GTIDs
# 8d397b30-8791-11e9-acbd-000c2902fc28:1-28

查看日志中指定库的内容
[root@db01 ~]# mysqlbinlog  -d world /data/binlog/mysql-bin.000006

查看指定时间内的日志
[root@db01 ~]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  /data/binlog/mysql-bin.000006

根据position号截取日志

--start-position=xxx
--stop-position=xxx
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

binlog日志的GTID新特性

介绍
5.6版本新家的特性,在5.7中做了加强
5.6版本中没有这个功能
5.7版本中,即使不开启也会自动生成
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 |      194 |              |                  | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID = source_id : transaction_id
8d397b30-8791-11e9-acbd-000c2902fc28:1-51
配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库
3306 [(none)]>3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 194 | | | 8d397b30-8791-11e9-acbd-000c2902fc28:1-51 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

3306 [(none)]>create database test;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000010 |      353 |              |                  | 8d397b30-8791-11e9-acbd-000c2902fc28:1-52 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
具备GTID后,截取查看某些事物日志
--include-gtids截取几号到几号
--exclude-gtids排除几号
3306 [(none)]>mysqlbinlog --include-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:1-52' --exclude-gtids='8d397b30-8791-11e9-acbd-000c2902fc28:10'  /data/binlog/mysql-bin.00004046
GTID的幂等性
开启GTID后,MySQL恢复binlog是,重复的GTID事务不会在执行
就像恢复,怎么办?
mysqlbinlog时加上--skip-gtids
进行source恢复时先设置set  sql_log_bin=0
在source
恢复后切记改回来sql  sql_log_bin=1
二进制日志自动清理
3306 [(none)]>select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                 0  |
+--------------------+
1 row in set (0.00 sec)
默认是不清理
企业建议,至少保留两个全备周期+1天的binlog
set global expire_logs_days=15
永久生效:
vim /etc/my.cnf
expire_logs_days=15
手动清理
3306 [(none)]> purge master logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.00 sec)
3306 [(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000010 |       353 |
+------------------+-----------+
1 row in set (0.00 sec)
注意:不要手工rm binlog文件
1.my.cnf binlog关闭,启动
2.把数据库关闭,开启binlog启动数据库
删除所有binlog,从000001开始重新记录
!!!!reset master,在主从关系中,主库执行此操作,主从环境必崩!!!!!!
日志是怎么滚动的
flush logs;手动滚动
重启mysql也会重新滚动一个
日志文件大小到达设定值(max_binlog_dize)

slow_log慢日志

作用
记录慢SQL语句的日志,定位低效SQL语句的工具日志
配置

开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/slow/slow.log
设置慢查询的时间标准
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
查看
mysqldumpslow -s c -t 10 /data/slow/slow.log
-s:表示按何种方式排序,c,t,l,r分别是按照记录次数,时间,查询时间,返回的记录数,来排序,ac,at,al,ar表示相应的倒序
-t:是top n的意思
-g:后边可以写一个正则匹配模式,大小写不敏感
赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

LNMP社群 不仅仅是技术

关于我们联系我们