MySQL 第六章日志管理

1.錯誤日志

1.1作用

MySQL 啟動及工作過程中募狂，狀態(tài)惯裕、報錯秦爆、警告。

1.2配置

命令行：

vim /etc/my.cnf
log_error=/data/3306/data/mysql.log(路徑)

數(shù)據(jù)庫操作宙彪、查看語句：

mysql[(none)]>select @@log_error;
+---------------------------+
| @@log_error               |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)

排錯方法矩动，查看日志，[ERROR]中括號報錯日志

[root@db01 ~]# tail -5 /data/3306/data/mysql.log 
2019-08-14T17:58:33.363554Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/3306/data/ib_buffer_pool
2019-08-14T17:58:33.365008Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190815  1:58:33
2019-08-14T17:58:33.370941Z 0 [Note] Event Scheduler: Loaded 0 events
2019-08-14T17:58:33.371046Z 0 [Note] /application/mysql/bin/mysqld: ready for connections.
Version: '5.7.26'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

2.二進(jìn)制日志

2.1作用

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

2.2配置

修改配置文件

vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin

log_bin詳解：
/data/bin_log/     路徑 
/mysql-bin   文件前綴

創(chuàng)建目錄授權(quán)

[root@db01 ~]# mkdir -p /data/binlog
[root@db01 ~]# chown -R mysql.mysql /data/binlog

[root@db01 /data]# ls -l binlog
total 8
-rw-r----- 1 mysql mysql 154 Aug 15 02:16 mysql-bin.000001
-rw-r----- 1 mysql mysql  30 Aug 15 02:16 mysql-bin.index

重啟數(shù)據(jù)庫

  [root@db01 ~]# systemctl restart mysqld

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

2.3.1引入

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

2.3.2記錄語句的種類

DDL ，DCL 男图，DML

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

DDl示姿，DCL：直接以語句（statement）的方式記錄
DML：insert，update逊笆，delete（三種模式）

mysql[(none)]>select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

模式	解
SBR：	statement栈戳，做什么記錄什么
RBR：	row，記錄數(shù)據(jù)行的變化览露，默認(rèn)模式荧琼，推薦
MBR：	mixed，自動判斷記錄模式

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

SBR：statement命锄，做什么記錄什么,記錄的就是SQL，可讀性較強偏化，日志量相對脐恩，日志記錄可能不準(zhǔn)確
RBR：row，記錄數(shù)據(jù)行的變化侦讨，默認(rèn)模式驶冒，推薦；可讀性差韵卤，日志量大骗污，日志記錄準(zhǔn)確

2.3.5 binlog events（二進(jìn)制日志事件）

1）簡介
二進(jìn)制日志內(nèi)容以事件為最小記錄單元
對于DDL和DCL，一個DDL語句就是一個事件
對于DML（標(biāo)準(zhǔn)的事務(wù)語句）：只記錄已提交的事務(wù)的DML語句.
事件1：begin;
事件2：a
事件3：b
事件4：commit;
2）事件的構(gòu)成

[root@db01 binlog]# mysqlbinlog mysql-bin.000001 
#at 219                    事件開始的位置(position)
#190814 18:46:58           事件發(fā)生的時間
create database xinixn     事件內(nèi)容
# End of log file          事件結(jié)束的位置
>>中間的set可以省略

2.3.6二進(jìn)制信息的基本查看

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

mysql[(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin (開關(guān))                  | ON                           |
| log_bin_basename  （位置 ）      | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/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.01 sec)

2）二進(jìn)制日志的基本信息

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

3）當(dāng)前正在使用的二進(jìn)制

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

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

mysql[(none)]>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 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

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

2.4.1 內(nèi)容查看命令

簡單查看
[root@db01 /data/binlog]# mysqlbinlog /data/binlog/mysql-bin.000003
人類可讀查看
[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vv /data/binlog/mysql-bin.000003
查詢幫助
[root@db01 /data/binlog]#  mysqlbinlog --help

2.4.2日志的截取

  --start-position
  --stop-position

語法：

mysqlbinlog --start-position=xx  --stop-position=xx /data/binlog/mysql-bin.000003 >/tmp/m.sql

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

oldguo[(none)]>create database binlog charset utf8mb4;
oldguo[(none)]>use binlog;
oldguo[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;

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

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

(2)搞破壞

oldguo[binlog]>drop database binlog;

(3）確認(rèn)起點和終點:

查看當(dāng)前二進(jìn)制文件
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1320 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看事件
oldguo[(none)]>show binlog events in 'mysql-bin.000006';
起點
| mysql-bin.000003 |  219 | Query          |         6 |         335 | create database binlog charset utf8mb4    
終點
| mysql-bin.000003 | 1222 | Query          |         6 |        1320 | drop database binlog

(4)截取日志

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

(5)恢復(fù)日志

[root@db01 /data/binlog]# set sql_log_bin=0;  ##  臨時關(guān)閉當(dāng)前會話的binlog記錄

mysql[(none)]>source /tmp/bin.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......

2.5 基于gtid的binlog的管理（擴展）

2.5.0引入

5.6版本以后沈条，binlog加入了新的日志記錄方式需忿，GTID
主要作用：簡化binlog截取
提供在主從復(fù)制中的感激功能

5.7版本之后，
進(jìn)行了GTID增強
主從性能高可用環(huán)境蜡歹，集群

2.5.1什么是gtid （global transaction ID）

全局唯一的事務(wù)編號
冪等性
GTID：server_uuid:Tx_id
b9a89249-b7e5-11e9-a416-000c29a21234:1

2.5.2配置

  enforce_gtid_consistency=true  ## 強制GTID一致性
  gtid_mode=on  ##  開關(guān)
  log_slave_updates=1  ## 主從復(fù)制中從庫記錄logbin屋厘，并同意GTID信息

2.5.3查看gtid信息

DDL,DCL 一個操作就是GTID
DML 一組事務(wù)一個GTID

mysql[(none)]>show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000006 |      300 |              |                  | b9a89249-b7e5-11e9-a416-000c29a21234:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

2.5.4基于gtid截取日志

--include-gtids= 截取
--exclude-gtids=排除
--skip-gtids 跳過已記錄的gtid信息

截取1-3號事務(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號和8號事務(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)創(chuàng)建庫，表月而，添加信息
2）查看記錄信息


mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000006 |     1748 |              |                  | b9a89249-b7e5-11e9-a416-000c29a21234:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql[(none)]>show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 |  876 | Gtid           |         6 |         941 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:5' |
| mysql-bin.000006 |  941 | Query          |         6 |        1051 | create database gtid charset utf8mb4                              |
| mysql-bin.000006 | 1051 | Gtid           |         6 |        1116 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:6' |
| mysql-bin.000006 | 1116 | Query          |         6 |        1229 | use `gtid`; create table gtid (id int)engine=innodb               |
| mysql-bin.000006 | 1229 | Gtid           |         6 |        1294 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:7' |
| mysql-bin.000006 | 1294 | Query          |         6 |        1366 | BEGIN                                                             |
| mysql-bin.000006 | 1366 | Table_map      |         6 |        1413 | table_id: 110 (gtid.gtid)                                         |
| mysql-bin.000006 | 1413 | Write_rows     |         6 |        1463 | table_id: 110 flags: STMT_END_F                                   |
| mysql-bin.000006 | 1463 | Table_map      |         6 |        1510 | table_id: 110 (gtid.gtid)                                         |
| mysql-bin.000006 | 1510 | Write_rows     |         6 |        1560 | table_id: 110 flags: STMT_END_F                                   |
| mysql-bin.000006 | 1560 | Xid            |         6 |        1591 | COMMIT /* xid=29 */                                               |
| mysql-bin.000006 | 1591 | Gtid           |         6 |        1656 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:8' |
| mysql-bin.000006 | 1656 | Query          |         6 |        1748 | drop database gtid

3)恢復(fù)日志

[root@db01 /tmp]# set sql_log_bin=0; 

[root@db01 /tmp]# mysqlbinlog --skip-gtids --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:5-7' /data/binlog/mysql-bin.000006>/data/gtid.sql

mysql[(none)]>source /data/gtid.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
................

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

2.6.1 自動清理日志

show variables like '%expire%';
expire_logs_days  0

自動清理時間,是要按照全備周期+1

set global expire_logs_days=8;

永久生效:

my.cnf
expire_logs_days=15;
企業(yè)建議,至少保留兩個全備周期+1的binlog

2.6.2 手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';

注意:不要手工 rm binlog文件

my.cnf binlog關(guān)閉掉,啟動數(shù)據(jù)庫
2.把數(shù)據(jù)庫關(guān)閉,開啟binlog,啟動數(shù)據(jù)庫
刪除所有binlog,并從000001開始重新記錄日志
*reset master; 主從關(guān)系中汗洒，主庫執(zhí)行此操作，主從環(huán)境必崩

2.6.3 binlog的滾動

oldguo[(none)]>flush logs;

2.6.4 binlog的滾動機制

flush logs;
重啟數(shù)據(jù)庫
select @@max_binlog_size;
備份時,某些參數(shù)會觸發(fā).

3.慢日志（llow-log）

簡介

記錄運行比較慢的語句記錄在slowlog中
功能是輔助優(yōu)化的工具日志
應(yīng)激性的慢 ---> show processlist父款；
一段時間慢 ---> slow 記錄溢谤，統(tǒng)計

配置

slow_query_log=1
slow_query_log_file =/data/3306/data/db01-slow.log
long_query_time=10.000000(默認(rèn))
log_queries_not_using_indexes

分析處理慢語句

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

Reading mysql slow query log from /data/3306/data/db01-slow.log
Count: 4  Time=0.84s (3s)  Lock=0.00s (0s)  Rows=10.0 (40), root[root]@localhost
  select * from t100w where k2 !='S' order by   num desc limit N

Count: 1  Time=0.91s (0s)  Lock=0.00s (0s)  Rows=10.0 (10), root[root]@localhost
  select * from t100w where k2 !='S' order by   num limit N

Died at /application/mysql/bin/mysqldumpslow line 161, <> chunk 5.

擴展

pt-query-digest /data/3306/data/db01-slow.log
集成: pt-query-digest+Anemometer=WEB方式:(分析慢日志,二進(jìn)制日志,錯誤日志...)

最后編輯于：2019.08.15 16:21:19

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末瞻凤，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子溯香，更是在濱河造成了極大的恐慌鲫构，老刑警劉巖，帶你破解...
沈念sama閱讀 217,907評論 6贊 506
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件玫坛，死亡現(xiàn)場離奇詭異结笨，居然都是意外死亡，警方通過查閱死者的電腦和手機湿镀，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,987評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門炕吸，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人勉痴，你說我怎么就攤上這事赫模。” “怎么了蒸矛？”我有些...
開封第一講書人閱讀 164,298評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵瀑罗，是天一觀的道長。經(jīng)常有香客問我雏掠，道長斩祭，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,586評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任乡话，我火速辦了婚禮摧玫，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘绑青。我一直安慰自己诬像，他們只是感情好，可當(dāng)我...
茶點故事閱讀 67,633評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布闸婴。她就那樣靜靜地躺著坏挠，像睡著了一般。火紅的嫁衣襯著肌膚如雪邪乍。梳的紋絲不亂的頭發(fā)上降狠，一...
開封第一講書人閱讀 51,488評論 1贊 302
城市分裂傳說
那天，我揣著相機與錄音溺欧，去河邊找鬼。笑死柏肪，一個胖子當(dāng)著我的面吹牛姐刁，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播烦味，決...
沈念sama閱讀 40,275評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼聂使，長吁一口氣：“原來是場噩夢啊……” “哼壁拉！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起柏靶，我...
開封第一講書人閱讀 39,176評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤弃理，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后屎蜓，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體痘昌，經(jīng)...
沈念sama閱讀 45,619評論 1贊 314
?護(hù)林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,819評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年炬转，在試婚紗的時候發(fā)現(xiàn)自己被綠了辆苔。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 39,932評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡扼劈，死狀恐怖驻啤，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情荐吵，我是刑警寧澤骑冗，帶...
沈念sama閱讀 35,655評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站先煎，受9級特大地震影響贼涩，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜榨婆，卻給世界環(huán)境...
茶點故事閱讀 41,265評論 3贊 329
男人毒藥：我在死后第九天來索命
文/蒙蒙一磁携、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧良风，春花似錦谊迄、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,871評論 0贊 22
一樁弒父案统诺，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至疑俭，卻和暖如春粮呢，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背钞艇。一陣腳步聲響...
開封第一講書人閱讀 32,994評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工啄寡，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人哩照。一個月前我還...
沈念sama閱讀 48,095評論 3贊 370
代替公主和親
正文我出身青樓挺物，卻偏偏與公主長得像，于是被迫代替她去往敵國和親飘弧。傳聞我的和親對象是個殘疾皇子识藤，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 44,884評論 2贊 354

MySQL 第六章 日志管理