MySQL-日志管理

1、錯(cuò)誤日志

1.1 作用

記錄MySQL啟動(dòng)及工作過程中，狀態(tài)、報(bào)錯(cuò)、警告。

1.2 怎么設(shè)置？

1> 修改配置文件，并重啟MySQL
-- 配置日志
vim /etc/my.cnf
log_error=/data/3306/data/mysql.log   #這里的路徑和文件名稱可以隨便定義

-- 重啟MySQL生效
/etc/init.d/mysqld restart
2> 查看錯(cuò)誤日志
wenjuan[(none)]>select @@log_error;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------+
| @@log_error               |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>

1.3 如何查看錯(cuò)誤日志？

關(guān)注[ERROR]的上下文.

2、二進(jìn)制日志（重要）

2.1 作用

數(shù)據(jù)恢復(fù)必備的日志。
主從復(fù)制依賴的日志。

2.2 怎么設(shè)置？

2.2.1 修改配置文件
vim /etc/my.server_id
server_id=6
log_bin=/data/3306/binlog/mysql-bin

說明：
server_id      是5.7之后開二進(jìn)制日志必加的參數(shù)
log_bin=       /data/3306/binlog/    mysql-bin    
打開二進(jìn)制功能     指定存放路徑          文件名前綴
2.2.2 創(chuàng)建目錄并授權(quán)
[root@db01 /data/3306]# mkdir -p /data/3306/binlog/
[root@db01 /data/3306]# chown -R mysql.mysql /data/3306/*
2.2.3 重啟數(shù)據(jù)庫
[root@db01 /data/3306]# /etc/init.d/mysqld restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 

[root@db01 /data/3306]# ll binlog/
total 8
-rw-r----- 1 mysql mysql 768 Aug 14 20:02 mysql-bin.000001
-rw-r----- 1 mysql mysql  35 Aug 14 18:18 mysql-bin.index
[root@db01 /data/3306]# 

說明：
mysql-bin    是在配置文件配置的前綴
000001      MySQL每次重啟，重新生成新的

2.3 二進(jìn)制日志記錄了什么？

2.3.1 引入

除了查詢類的語句，都會(huì)記錄，即所有數(shù)據(jù)庫變更類的語句。

2.3.2 記錄語句的種類

DDL（數(shù)據(jù)定義語言）：create、drop
DCL（數(shù)據(jù)控制語言）
DML（數(shù)據(jù)操作語言）：insert、update、delete

2.3.3 不同語句的記錄格式說明

DDL、DCL直接以語句（statement）方式記錄 .
DML語句有三種模式：SBR、RBR、MBR

wenjuan[(none)]>select @@binlog_format;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

wenjuan[(none)]>

說明：
statement---->SBR：做什么記錄什么，即SQL語句
row---------->RBR：記錄數(shù)據(jù)行的變化（默認(rèn)模式，推薦）
mixed-------->MBR：自動(dòng)判斷記錄模式

面試題：說明SBR和RBR的區(qū)別？

區(qū)別項(xiàng) SBR RBR（默認(rèn)、推薦）
記錄內(nèi)容 SQL語句 記錄數(shù)據(jù)行的變化
可讀性 較強(qiáng) 差
日志量 小 大
日志記錄準(zhǔn)確性 數(shù)據(jù)誤差 沒有誤差
2.3.4 binlog events（二進(jìn)制日志事件）

1> 簡介

二進(jìn)制日志內(nèi)容以事件為最小記錄單元。
對(duì)于DDL和DCL，一個(gè)語句就是一個(gè)事件。
對(duì)于DML（標(biāo)準(zhǔn)的事務(wù)語句），只記錄已提交的事務(wù)的DML語句

begin ;    事件1
a          事件2
b          事件3
commit;    事件4

2> 事件的構(gòu)成（為了截取日志）

[root@db01 /data/3306/binlog]# mysqlbinlog mysql-bin.000001 
# at 219               事件開始的位置(position)
end_log_pos 319        事件結(jié)束的位置(position)
#190814 18:46:35       事件發(fā)生的時(shí)間
create database oldboy 事件內(nèi)容
2.3.4 二進(jìn)制日志的基礎(chǔ)查看

1> 查看二進(jìn)制日志的配置信息

wenjuan[(none)]>show variables like '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/3306/binlog/mysql-bin       |
| log_bin_index                   | /data/3306/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
| sql_log_bin                     | ON                                |
+---------------------------------+-----------------------------------+
6 rows in set (0.00 sec)

wenjuan[(none)]>

說明：
log_bin              開啟二進(jìn)制日志的開關(guān)
log_bin_basename     位置
sql_log_bin          臨時(shí)開啟或關(guān)閉二進(jìn)制日志的小開關(guān)

2> 查看二進(jìn)制日志的基本信息
（1）打印出當(dāng)前MySQL的所有二進(jìn)制日志，并且顯示最后使用到的position

-----
wenjuan[(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

wenjuan[(none)]>

（2）查看當(dāng)前正在使用的二進(jìn)制日志

show binary logs;
show master status;（常用）

wenjuan[(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>

3> 查看二進(jìn)制日志的事件信息

wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>

wenjuan[(none)]>show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219 | Query          |         6 |         322 | create database wwjtest               |
| mysql-bin.000001 | 322 | Anonymous_Gtid |         6 |         387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 387 | Query          |         6 |         481 | create database heee                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
6 rows in set (0.00 sec)

2.4 內(nèi)容的查看和截取

2.4.1 內(nèi)容查看命令
[root@db01 ~]# mysqlbinlog  /data/3306/binlog/mysql-bin.000003
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/binlog/mysql-bin.000003

說明：記不住參數(shù)可以去mysqlbinlog --help中查看
2.4.2 日志的截取
--start-position
--stop-position

語法:
mysqlbinlog --start-position=xxx  --stop-position=xxx /data/3306/binlog/mysql-bin.000003>/data/bin.sql

================演練：===================
1> 準(zhǔn)備數(shù)據(jù)

wenjuan[(none)]>create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

wenjuan[(none)]>use binlog;
Database changed
wenjuan[binlog]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

wenjuan[binlog]>
wenjuan[binlog]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[binlog]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[binlog]>commit;
Query OK, 0 rows affected (0.01 sec)

wenjuan[binlog]>

wenjuan[binlog]>update t1 set id=10 where id>10;

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

wenjuan[binlog]>commit;
Query OK, 0 rows affected (0.01 sec)

wenjuan[binlog]>

wenjuan[binlog]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   10 |
|   10 |
+------+
6 rows in set (0.00 sec)

wenjuan[binlog]>

2> 搞破壞

wenjuan[binlog]>drop database binlog;
Query OK, 1 row affected (0.00 sec)

wenjuan[(none)]>

3> 數(shù)據(jù)恢復(fù)
（1）確認(rèn)起點(diǎn)和終點(diǎn)

wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1610 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>

wenjuan[(none)]>show binlog events in 'mysql-bin.000003';

起點(diǎn)：
| mysql-bin.000003 |  488 | Query          |         6 |         604 | create database binlog charset utf8mb4                              |

終點(diǎn)：
| mysql-bin.000003 | 1512 | Query          |         6 |        1610 | drop database binlog                                                |

（2）截取日志

[root@db01 ~]# mysqlbinlog --start-position=488  --stop-position=1512 /data/3306/binlog/mysql-bin.000003>/data/bin.sql

（3）恢復(fù)日志

wenjuan[(none)]>set sql_log_bin=0;   ##  臨時(shí)關(guān)閉當(dāng)前會(huì)話的binlog記錄

wenjuan[(none)]>source /data/bin.sql;
wenjuan[(none)]>set sql_log_bin=1;   ##  打開當(dāng)前會(huì)話的binlog記錄

2.5 基于gtid的binlog管理（擴(kuò)展）

2.5.1 什么是gtid（Global Transaction ID）？

全局唯一的事務(wù)編號(hào)。
冪等性。
GtID包括兩部分：
Server_uuid：
Tx_id：

2.5.2 配置
wenjuan[(none)]>show variables like '%gtid%';

vim /etc/init.d/my.cnf

gtid_mode=on                  ----開啟開關(guān)
enforce_gtid_consistency=true    ----強(qiáng)制GTID一致性
log_slave_updates=1           ----主從復(fù)制中從庫記錄binlog，并統(tǒng)一GTID信息

重啟數(shù)據(jù)庫：/etc/init.d/mysqld restart
2.5.3 基于gtid截取日志

對(duì)于DDL和DCL一個(gè)操作就是一個(gè)GTID。
對(duì)于DML，一個(gè)完整的事務(wù)就是已給GTID。

wenjuan[(none)]>wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 |      489 |              |                  | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>


wenjuan[(none)]>wenjuan[(none)]>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, 
| mysql-bin.000005 | 123 | Previous_gtids |         6 |         154 |                         
| mysql-bin.000005 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT=
| mysql-bin.000005 | 219 | Query          |         6 |         308 | drop database dbtest    
| mysql-bin.000005 | 308 | Gtid           |         6 |         373 | SET @@SESSION.GTID_NEXT=
| mysql-bin.000005 | 373 | Query          |         6 |         489 | create database dbtest c
+------------------+-----+----------------+-----------+-------------+-------------------------
6 rows in set (0.00 sec)

wenjuan[(none)]>
2.5.4 基于gtid截取日志
--include-gtids=       ----包含
--exclude-gtids=       ------排除
--skip-gtids

截取1-3號(hào)事務(wù):
[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql

截取 1-10 gtid事務(wù),跳過6號(hào)和8號(hào)事務(wù).
[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10 --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8'    /data/binlog/mysql-bin.000009>/data/gtid.sql
2.5.5 演練

1>準(zhǔn)備環(huán)境

wenjuan[(none)]>create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

wenjuan[(none)]>use gtid;
Database changed
wenjuan[gtid]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

wenjuan[gtid]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[gtid]>commit;
Query OK, 0 rows affected (0.00 sec)

wenjuan[gtid]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

wenjuan[gtid]>commit;
Query OK, 0 rows affected (0.00 sec)

wenjuan[gtid]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+
6 rows in set (0.00 sec)

wenjuan[gtid]>

2>搞破壞

wenjuan[gtid]>drop database gtid;
Query OK, 1 row affected (0.01 sec)

wenjuan[(none)]>

3> 找起點(diǎn)和終端(gtid)

wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 |     1244 |              |                  | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-11 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

wenjuan[(none)]>show binlog events in 'mysql-bin.000008';
| mysql-bin.000008 |  194 | Gtid           |         6 |         259 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:7'  |
| mysql-bin.000008 |  259 | Query          |         6 |         369 | create database gtid charset utf8mb4                               |

| mysql-bin.000008 | 1087 | Gtid           |         6 |        1152 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:11' |
| mysql-bin.000008 | 1152 | Query          |         6 |        1244 | drop database gtid                                                 |

4> 截取日志（僅供參考）

[root@db01 ~]# mysqlbinlog  --skip-gtids --include-gtids='936b9a3f-b75a-11e9-bd16-000c290143b9:7-10' /data/3306/binlog/mysql-bin.000008>/data/gtid.sql

4> 恢復(fù)數(shù)據(jù)

wenjuan[(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

wenjuan[(none)]>

wenjuan[(none)]>source /data/gtid.sql

2.6 二進(jìn)制日志其他操作

2.6.1 自動(dòng)清理日志
show variables like '%expire%';
expire_logs_days  0  

自動(dòng)清理時(shí)間,是要按照全備周期+1
set global expire_logs_days=8;
永久生效:
my.cnf
expire_logs_days=15;
企業(yè)建議,至少保留兩個(gè)全備周期+1的binlog
2.6.2 手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';

注意:不要手工 rm binlog文件
\1. my.cnf binlog關(guān)閉掉,啟動(dòng)數(shù)據(jù)庫
2.把數(shù)據(jù)庫關(guān)閉,開啟binlog,啟動(dòng)數(shù)據(jù)庫
刪除所有binlog,并從000001開始重新記錄日志
*reset master;     主從關(guān)系中，主庫執(zhí)行此操作，主從環(huán)境必崩
2.6.3 binlog 的滾動(dòng)
wenjuan[(none)]>flush logs;
重啟數(shù)據(jù)庫
 select @@max_binlog_size;
備份時(shí)，某些參數(shù)會(huì)觸發(fā)

3、慢日志（slow-log）

3.1 簡介

記錄運(yùn)行較慢的語句記錄slowlog中。
功能是輔助優(yōu)化的工具日志。
應(yīng)激性的慢---------->可以通過show processlist進(jìn)行監(jiān)控
一段時(shí)間的慢------>可以進(jìn)行slow記錄、統(tǒng)計(jì)

3.2 配置

wenjuan[(none)]>show variables like '%slow_query%';
wenjuan[(none)]>select @@long_query_time;
wenjuan[(none)]>show variables like '%log_queries_not_using_indexes%';

vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1  默認(rèn)配置10秒鐘
log_queries_not_using_indexes=1

重啟mysql：
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@db01 ~]#

3.3 慢語句模擬

set sql_log_bin=0;
source /tmp/t100w.sql; 
set sql_log_bin=1;

3.4 分析處理慢語句

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

參數(shù)：
    -t   top前幾個(gè)
    -s   order的排序
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 日志文件對(duì)于一個(gè)服務(wù)器來說是非常重要的，它記錄著服務(wù)器的運(yùn)行信息，許多操作都會(huì)寫日到日志文件，通過日志文件可以監(jiān)視...
    時(shí)待吾閱讀 1,123評(píng)論 0贊 5
  • 1.錯(cuò)誤日志 1.1作用 MySQL 啟動(dòng)及工作過程中，狀態(tài)、報(bào)錯(cuò)、警告。 1.2配置 2.二進(jìn)制日志 2.1作用...
    白茶清歡R閱讀 328評(píng)論 0贊 0
  • mysql日志(默認(rèn)存放在datadir): 同大多數(shù)關(guān)系型數(shù)據(jù)庫一樣，日志文件是MySQL數(shù)據(jù)庫的重要組成部分。...
    運(yùn)維阿文閱讀 1,079評(píng)論 0贊 0
  • mysql日志——記錄著mysql數(shù)據(jù)庫運(yùn)行期間的操作和信息 日志作用 (1)當(dāng)數(shù)據(jù)庫遭到意外損害，可以通過日志文...
    SkTj閱讀 712評(píng)論 0贊 1
  • 1. 上節(jié)遺留問題 2.重點(diǎn)參數(shù) innodb_flush_log_at_trx_commit =======...
    Ffvc閱讀 753評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容