MYSQL日志管理

Mysql日志管理：

介绍：错误日志、二进制日志、慢日志

1、错误日志

配置方式：在配置文件中（my.cnf）中添加log_error=path 即可
错误日志默认就是开启的，默认存放在数据目录下，日志名称主机名.err 的文件
**注意事项：此日志的存放位置需要存放在启动进程用户有权限管理的目录下，并且日志文件
本身属主、属组是服务进程管理用户也就是配置文件中user等于后面的用户
查看错误日志时只需要查看文件中[ERROR]的行即可

[root@db01 /data/mysql/data]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
[mysql]
socket=/tmp/mysql.sock

[root@db01 /data/mysql/data]# ll error.log 
-rw-r-----. 1 mysql mysql 10866 Aug 28 21:52 error.log
[root@db01 /data/mysql/data]#

2、二进制日志（binlog）

2.1 二进制日志作用：数据恢复、主从复制

2.2 二进制日志记录了什么：记录数据库所有变化的操作，包括DDL、DCL、DML，其实就是SQL语句

2.3 二进制日志的配置：

server_id=6 #主从复制需要用到
log_bin=/data/binlog/mysql-binlog/mysql-bin #指定存放位置mysql-bin名称前缀
binlog_format=row #日志格式

[root@db01 /data/binlog]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
binlog_format=row
log_bin=/data/binlog/mysql-bin
[mysql]
socket=/tmp/mysql.sock

[root@db01 /data/binlog]#

2.4 二进制日志格式

DDL和DCL，以statement（语句）方式直接记录SQL
DML（insert、update、delete），记录的是已经提交的事物
SBR ：statement，记录具体语句（5.6以下版本常用）
RBR ：Row ,记录数据行的变化（常用）
MBR ：mixed,混合模式（一般不用）

2.5 日志内容

以事件(event) 作为记录的最小单元

以下（截取）为一个事件，以一个at开始，到下一个at结束

[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000001 
# at 534
#190829 15:02:50 server id 6  end_log_pos 625 CRC32 0xaf1341d1  Query   thread_id=3    exec_time=0  error_code=0
create database bbb

2.6 mysql客户端下二进制日志的基本查看

查看二进制日志有多少个

mysql> show binary logs; 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       625 |
+------------------+-----------+
1 row in set (0.00 sec)

flush logs 刷新二进制日志

mysql> flush logs; #刷新二进制日志
Query OK, 0 rows affected (0.15 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       672 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

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

mysql>

查看正在使用哪个二进制日志

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>

查看某二进制日志的事件信息
说明：
对于DDL、DCL操作，一个操作（info列）就是一个事件（ Pos 892 ---End_log_pos 999）。
对于DML语句在info列，以BEGIN 开始一个事物，以COMMIT结束一个事物（ Pos 390---End_log_pos 712）。

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                          |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4         |
| mysql-bin.000005 | 123 | Previous_gtids |         6 |         154 |                                               |
| mysql-bin.000005 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 219 | Query          |         6 |         325 | use `aaa`; create table aa(
id int not null ) |
| mysql-bin.000005 | 325 | Anonymous_Gtid |         6 |         390 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 390 | Query          |         6 |         461 | BEGIN                                         |
| mysql-bin.000005 | 461 | Table_map      |         6 |         505 | table_id: 108 (aaa.aa)                        |
| mysql-bin.000005 | 505 | Write_rows     |         6 |         545 | table_id: 108 flags: STMT_END_F               |
| mysql-bin.000005 | 545 | Xid            |         6 |         576 | COMMIT /* xid=45 */                           |
| mysql-bin.000005 | 576 | Anonymous_Gtid |         6 |         641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 641 | Query          |         6 |         712 | BEGIN                                         |
| mysql-bin.000005 | 712 | Table_map      |         6 |         756 | table_id: 108 (aaa.aa)                        |
| mysql-bin.000005 | 756 | Write_rows     |         6 |         796 | table_id: 108 flags: STMT_END_F               |
| mysql-bin.000005 | 796 | Xid            |         6 |         827 | COMMIT /* xid=48 */                           |
| mysql-bin.000005 | 827 | Anonymous_Gtid |         6 |         892 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000005 | 892 | Query          |         6 |         999 | create database ccc charset utf8mb4           |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
16 rows in set (0.00 sec)

mysql>

2.7 二进制日志的内容查看和截取

(1)查看
查看 mysqlbinlog mysql-bin.000005
mysql日志详细显示mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000005

(2)截取二进制日志恢复数据

例子:

mysql> create database bak charset utf8mb4;
mysql> use bak
mysql> create table t1 (id int) charset utf8mb4;
mysql> insert into t1 values(1),(2),(3);
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql> drop database bak;

第二步恢复过程:

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1764 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

    
mysql> show binlog events in 'mysql-bin.000004'
| mysql-bin.000004 | 1065 | Query          |         6 |        1172 | create database bak charset utf8mb4     
| mysql-bin.000004 | 1675 | Query          |         6 |        1764 | drop database bak   

mysqlbinlog --start-position=1065 --stop-position=1675   mysql-bin.000004 >/tmp/bin.sql     
[root@db01 /data/binlog]# mysql -uroot -p123456</tmp/bin.sql

mysqlbinlog --start-position=1065 --stop-position=1675 mysql-bin.000004 >/tmp/bin.sql
-b database :mysqlbinlog 中-b参数后面加库名，指明过滤某个库的二进制日志

2.8 日志滚动

第一种：/etc/init.d/mysqld restart
第二种：mysql> flush logs;
mysql> show binary logs;
mysql> select @@max_binlog_size/1024/1024;

2.9删除二进制日志

mysql>help purge;
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004'; 删除000004号之前的所有文件

mysql>PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; #按日期删除

可以在配置文件中定制二进制文件的过期时间（一般最少设定为数据库全备期间）

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql>

3、slow log（慢日志）

3.1 作用：记录MYSQL运行期间执行较慢的语句

3.2 查看慢日志是否打开

slow_query_log 状态为ON 表示打开

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | ON                             |
| slow_query_log_file       | /data/mysql/data/db01-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)

mysql>

3.3 查看慢日志记录时间

当查询语句大于等于long_query_time这个值得时间(单位S)就会被定义为查询慢的语句，就会被慢日志记录起来

mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

3.4查看是否开启未使用索引记录功能

当log_queries_not_using_indexes 这个功能被开启，会把未使用索引查询的语句记录到慢日志中

mysql> show variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

3.5 配置

slow_query_log=ON #打开慢日志
slow_query_log_file=/data/mysql/data/db01-slow.log #默认路径可手动指定
long_query_time=5 #超过多长时间被定义为慢语句
log_queries_not_using_indexes=ON #开启此功能，会将未走索引的语句记录到慢日志中

3.5慢日志分析工具

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

最后编辑于：2019.09.25 22:11:46

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 215,539评论 6赞 497
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,911评论 3赞 391
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 161,337评论 0赞 351
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,723评论 1赞 290
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,795评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,762评论 1赞 294
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,742评论 3赞 416
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,508评论 0赞 271
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,954评论 1赞 308
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,247评论 2赞 331
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,404评论 1赞 345
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,104评论 5赞 340
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,736评论 3赞 324
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,352评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,557评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,371评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,292评论 2赞 352