當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL日志管理（十一）

發布時間：2024/3/26 数据库 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL日志管理（十一）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

- 1. binlog文件內容查看及數據恢復
- - 1.1 binlog事件查看
  - 1.2 binlog內容查看
  - 1.3 binlog日志截取恢復
- 2. binlog維護操作
- - 2.1 binlog日志滾動
  - 2.2 binlog日志刪除
  - - 2.2.1 自動刪除機制
    - 2.2.2 手工刪除
    - 2.2.3 全部清空
- 3. binlog的GTID模式管理
- - 3.1 GTID介紹
  - 3.2 GTID(Global Transaction ID)
  - 3.3 GTID重要參數介紹
  - 3.4 GTID操作實戰演練
  - 3.5 基于GTID進行查看binlog
  - 3.6 GTID數據恢復實戰演練
  - - 3.6.1 錄入測試數據
    - 3.6.2 恢復測試數據
    - 3.6.3 GTID冪等性
- 4. slowlog慢日志
- - 4.1 slowlog作用
  - 4.2 slowlog配置
  - 4.3 模擬慢語句
  - 4.4 慢語句分析
  - 4.5 慢日志工具擴展
- 5. 日志管理小總結
- - 5.1 錯誤日志
  - 5.2 二進制日志
  - 5.2 慢日志

1. binlog文件內容查看及數據恢復

1.1 binlog事件查看

# (1) 查看最新mysql正在使用最新的日志文件 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 885 | | | | +------------------+----------+--------------+------------------+-------------------+# (2) 查看最新日志內容 mysql> 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.30-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 | 338 | create database dongkun charset utf8mb4 | | mysql-bin.000001 | 338 | Anonymous_Gtid | 6 | 403 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000001 | 403 | Query | 6 | 476 | BEGIN | | mysql-bin.000001 | 476 | Table_map | 6 | 534 | table_id: 108 (world.city) | | mysql-bin.000001 | 534 | Delete_rows | 6 | 854 | table_id: 108 flags: STMT_END_F | | mysql-bin.000001 | 854 | Xid | 6 | 885 | COMMIT /* xid=40 */ | +------------------+-----+----------------+-----------+-------------+----------------------------------------(3) 查看DDL語句，用的頻率比較高 [root@db01 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql-bin.000001'" | grep DROP

1.2 binlog內容查看

# (1) binlog日志不能使用vim進行查看，mysqlbinlog專門用來查看binlog日志文件內容，重定向普通文件后才可以查看 [root@db01 binlog]# mysqlbinlog /data/binlog/mysql-bin.000001 > /tmp/a.sql# (2) 查看重定向后的binlog DDL日志內容，DDL直接可以看到sql語句 ## at是每個事件的開頭位置。下一個at的開始，就是上一個at的結束點。at下面的set命令不用關注，重點看的是sql語句 [root@db01 binlog]# vim /tmp/a.sql # at 219 #221205 23:11:45 server id 6 end_log_pos 338 create database dongkun charset utf8mb4 /*!*/; # at 338# (3) 查看重定向后的binlog DML日志內容，begin開始的一個事務 [root@db01 binlog]# vim /tmp/a.sql # at 403 #221205 23:19:58 server id 6 end_log_pos 476 BEGIN # at 476 #221205 23:19:58 server id 6 end_log_pos 534 # at 534 #221205 23:19:58 server id 6 end_log_pos 854 CRC32 0xff40056c BINLOG ' HgyOYxMGAAAAOgAAABYCAAAAAGwAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+I/4D/hQAw3TN lA== HgyOYyAGAAAAQAEAAFYDAAAAAGwAAAAAAAEAAgAF/+ABAAAABUthYnVsA0FGRwVLYWJvbCApGwDg AgAAAAhRYW5kYWhhcgNBRkcIUWFuZGFoYXK8nwMA4AMAAAAFSGVyYXQDQUZHBUhlcmF0sNkCAOAE AAAADk1hemFyLWUtU2hhcmlmA0FGRwVCYWxraDjzAQDgBQAAAAlBbXN0ZXJkYW0DTkxEDU5vb3Jk LUhvbGxhbmRAKAsA4AYAAAAJUm90dGVyZGFtA05MRAxadWlkLUhvbGxhbmSpDQkA4AcAAAAESGFh ZwNOTEQMWnVpZC1Ib2xsYW5kRLoGAOAIAAAAB1V0cmVjaHQDTkxEB1V0cmVjaHRTkwMA4AkAAAAJ RWluZGhvdmVuA05MRA1Ob29yZC1CcmFiYW50cxQDAGwFQP8= '/*!*/; # at 854 #221205 23:20:15 server id 6 end_log_pos 885 COMMIT/*!*/;# (4) 因為binlog使用的是row模式，DML人類不可讀，可以使用--base64-output=decode-rows -vvv可以看個大致內容 ## mysqlbinlog --base64-output=decode-rows -vvv 這條命令僅用于查看，如果要截取日志的話不能加這個--base64-output=decode-rows -vvv參數 [root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001 > /tmp/b.sql [root@db01 binlog]# vim /tmp/b.sql # at 534 #221205 23:19:58 server id 6 end_log_pos 854 CRC32 0xff40056c Delete_rows: table id 108 flags: STMT_END_F ### DELETE FROM `world`.`city` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='Kabul' /* STRING(35) meta=65059 nullable=0 is_null=0 */ ### @3='AFG' /* STRING(3) meta=65027 nullable=0 is_null=0 */ ### @4='Kabol' /* STRING(20) meta=65044 nullable=0 is_null=0 */ ### @5=1780000 /* INT meta=0 nullable=0 is_null=0 */ ### DELETE FROM `world`.`city`# (5) 可以看mysqlbinlog命令的使用幫助，命令參數記不住可以使用--help來查詢 [root@db01 binlog]# mysqlbinlog --help--base64-output=name Determine when the output statements should bebase64-encoded BINLOG statements: 'never' disables it andworks only for binlogs without row-based events;'decode-rows' decodes row events into commentedpseudo-SQL statements if the --verbose option is alsogiven; 'auto' prints base64 only when necessary (i.e.,for row-based events and format description events). Ifno --base64-output[=name] option is given at all, thedefault is 'auto'.

1.3 binlog日志截取恢復

不管是事件查看還是內容查看都是為了后面做鋪墊的，大部分事件會用來數據庫故障恢復數據，想要恢復一個數據的時候，可以通過日志的截取工作，然后在恢復這個日志，然后達到數據恢復的目的
看binlog日志，然后在截取位置點，去恢復數據。

日志恢復案例：

# 顯示全部的二進制日志文件 show binary logs; # (1) 滾動一個新的日志 (會生成一個新的日志文件，內容會在這個新的日志中寫入，老的日志文件不會在寫入內容) flush logs; # (2) 模擬數據環境 mysql> create database bindb charset utf8mb4; mysql> use bindb; mysql> create table t1 (id int); mysql> begin; mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> begin; mysql> insert into t1 values(11),(22),(33); mysql> commit mysql> begin; mysql> insert into t1 values(111),(222),(333); mysql> commit mysql> drop database bindb; # (3) 數據恢復 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 497 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> show binlog events in 'mysql-bin.000002';mysql-bin.000002 | 219 | 332 | create database bindb charset utf8mb4mysql-bin.000002 | 1357 | 1452 | drop database bindb# 截取日志: [root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000002 > /tmp/bin.sql# 恢復日志: ## 在此會話里面臨時關閉binlog，臨時不記錄binlog，不會影響到別人。 mysql> set sql_log_bin=0; ## binlog日志進行恢復 mysql> source /tmp/bin.sql; ## 恢復完成后，打開binlog mysql> set sql_log_bin=1;# 驗證數據: mysql> select * from bindb.t1;

思考問題：如果是生產環境中，此種恢復手段會有什么弊端

（1） binlog記錄不單單是一個數據庫的操作，可能對其他數據庫也有操作，在binlog日志中如何去精確的截取對某一個庫的操作？

# -d bindb 只截取跟這個bindb庫相關的日志, 從mysql-bin.000002文件截取出只對bindb數據庫的操作 [root@db01 binlog]# mysqlbinlog -d bindb --start-position=219 --stop-positon=1357 /data/binlog/mysql-bin.000002;

（2）需要的日志在多個文件中分布

# 起點: 假如 mysql-bin.000001 起始號：4600 # 終點: 一般是最后一個文件，假設mysql-bin.000002 終點號：980 # 小提示: position號，單文件是連續的，多文件是不連續的# (1) 通過時間點來確認?？缥募慕厝热?#xff0c;恢復的話，知道起始位置號后，找出時間點，然后就行截取 mysqlbinlog --start-datetime= --stop-datetime= mysql-bin.000001 mysql-bin.000002# (2) 通過時間和位置點來確認。因為每秒鐘執行了多個sql，時間有可能不太準確，參數可以是一個position,一個為時間 mysqlbinlog --start-position= --stop-datatime= mysql-bin.000001 mysql-bin.000002# (3) 如果時間都不準確，用笨辦法，把文件分開弄

（3）創建了幾年，期間一直在用的數據庫，插入數據的操作從bin_log.000001到bin_log.000123之中都有的庫，被刪了，如何恢復？
假設：每周六做全備份23：00，binlog每天備份23：00
故障點：周三 10點 drop操作。

全備+binlog 進行恢復
binlog實際上是我們數據恢復時配合備份一起恢復數據的手段。

2. binlog維護操作

2.1 binlog日志滾動

# (1) 滾動一個新的日志 mysql> flush logs; # (2) 滾動一個新的日志 [root@db01 binlog]# mysqladmin -uroot -p123456 flush-logs # (3) 一個binlog日志文件大小默認結果是一個T，如果日志文件達到一個T，自動滾動一次，可以設置小一些（建議設置一個binlog日志大小為100~200M） mysql> select @@max_binlog_size; # (4) 自動滾動日志，可用可不用 mysqldump -F # (5) 重啟數據庫自動滾動

2.2 binlog日志刪除

注意：不要用rm去刪除binlog日志文件，會造成不好的影響，比如主從會受到影響。

2.2.1 自動刪除機制

# (1) 默認是0，代表永不刪除。單位是天 mysql> select @@expire_logs_days; # (2) 設置多少天何時？閾值？一次全備周期。 7+1天，生產一般建議最少2個全備周期+1

2.2.2 手工刪除

# 如果忘記了命令，可以使用help幫助 mysql> help purge； mysql> help purge binary logs; # (1) 以區間進行刪除。例如下面的栗子會刪除mysql-bin.000001~mysql-bin.000005的文件 PURGE BINARY LOGS TO 'mysql-bin.000006'; # (2) 以日期進行刪除。刪除到2019-04-02 22:46:26日期之前的日志 PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'

2.2.3 全部清空

# 危險命令，在主庫執行此操作，主從必宕機 mysql> reset master

3. binlog的GTID模式管理

3.1 GTID介紹

5.6 版本新加的特性，5.7，8.0中做了加強
5.6 中不開啟，沒有這個功能

# 5.7中GTID，即使不開也會有自動生成,匿名的GTID，內置的，類似于聚簇索引，隱藏存在的，是由自己維護的 SET @@SESSION.GTID_NEXT='ANONYMOUS'

小提示： GTID主要對主從復制來說的，對單機來說給的功能還是比較少的

3.2 GTID(Global Transaction ID)

是對于一個已提交事務的標號，并且是一個全局唯一的編號。
他的官方定義如下：

GTID = server_uuid : transaction_id 638fec45-e734-11ec-bcd2-000c29d09be0:1

小提示：
（1）GTID的事務ID和innodb的事務ID還是不一樣的
，innodb的事務ID主要是針對DML語句的，GTID的事務ID主要是binlog中的事件來命名的，像DDL、DCL 也算一個事務id
（2）對于DML傳統事務來講，一個事務一個GTID的號碼

3.3 GTID重要參數介紹

# (1) 開啟GITD [root@db01 ~]# vim /etc/my.cnf gtid-mode=on # 打開gtid的功能 enforce-gtid-consistency=true # 強制GTID的一致性，在開啟gtid之前或者啟動gtid之前，啟動數據庫之前，都要校驗所有GTID的一致性問題，GTID主要保證主從之間的一致性的，而不是單機的，單機的比較弱# (2) 重啟mysql生效 [root@db01 ~]# /etc/init.d/mysqld restart# (3) 查看GTID的開啟狀態，ON是開啟 mysql> select @@gtid_mode; +-------------+ | @@gtid_mode | +-------------+ | ON | +-------------+ 1 row in set (0.00 sec)# (4) mysql實例的唯一標識uuid，638fec45-e734-11ec-bcd2-000c29d09be0:1 mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 638fec45-e734-11ec-bcd2-000c29d09be0 | +--------------------------------------+ 1 row in set (0.00 sec)

3.4 GTID操作實戰演練

# (1) Executed_Gtid_Set 被執行過的GTID的集合 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)# (2) 開啟gitd之后，創建一個數據庫 mysql> create database gtid_text; Query OK, 1 row affected (0.00 sec)# (3) Executed_Gtid_Set生成gtid的信息 mysql> mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000003 | 328 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec)# (4) 在沒開gtid之前， SET @@SESSION.GTID_NEXT= 'ANONYMOUS'不用管，這個匿名的GITD是由系統自己管理的，個人是使用不了的。 # 一旦開啟了GTID，在每一個事件之前都會有一個GTID的號碼，有了它之后不需要使用position號，直接用GTID就可以管理二進制日志，截取日志恢復。 mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' | | mysql-bin.000003 | 219 | Query | 6 | 328 | create database gtid_text | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+# (5) 繼續創建數據庫，每個事件都會有一個GTID號 # SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1'， 638fec45-e734-11ec-bcd2-000c29d09be0,每一個數據庫實例初始化完成之后，自動生成的一個uuid，能唯一代表一個實例。 mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 638fec45-e734-11ec-bcd2-000c29d09be0 | +--------------------------------------+mysql> create database gtid_text1; Query OK, 1 row affected (0.00 sec)mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' | | mysql-bin.000003 | 219 | Query | 6 | 328 | create database gtid_text | | mysql-bin.000003 | 328 | Gtid | 6 | 393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' | | mysql-bin.000003 | 393 | Query | 6 | 505 | create database gtid_text1 | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 6 rows in set (0.00 sec)mysql> create database gtid_text2; Query OK, 1 row affected (0.03 sec)mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' | | mysql-bin.000003 | 219 | Query | 6 | 328 | create database gtid_text | | mysql-bin.000003 | 328 | Gtid | 6 | 393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' | | mysql-bin.000003 | 393 | Query | 6 | 505 | create database gtid_text1 | | mysql-bin.000003 | 505 | Gtid | 6 | 570 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:3' | | mysql-bin.000003 | 570 | Query | 6 | 682 | create database gtid_text2 | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0.00 sec)# (6) 對DDL(創建數據庫)來講一個事件就是一個GTID。對于DML來說，一個事務就是一個GTID，從begin到commit算一個GTID信息，SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:4'，第四號GTID信息 mysql> use world; mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> delete from city where id=4078; Query OK, 1 row affected (0.00 sec)mysql> commit; Query OK, 0 rows affected (0.00 sec)mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:1' | | mysql-bin.000003 | 219 | Query | 6 | 328 | create database gtid_text | | mysql-bin.000003 | 328 | Gtid | 6 | 393 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:2' | | mysql-bin.000003 | 393 | Query | 6 | 505 | create database gtid_text1 | | mysql-bin.000003 | 505 | Gtid | 6 | 570 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:3' | | mysql-bin.000003 | 570 | Query | 6 | 682 | create database gtid_text2 | | mysql-bin.000003 | 682 | Gtid | 6 | 747 | SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:4' | | mysql-bin.000003 | 747 | Query | 6 | 820 | BEGIN | | mysql-bin.000003 | 820 | Table_map | 6 | 878 | table_id: 108 (world.city) | | mysql-bin.000003 | 878 | Delete_rows | 6 | 940 | table_id: 108 flags: STMT_END_F | | mysql-bin.000003 | 940 | Xid | 6 | 971 | COMMIT /* xid=41 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 13 rows in set (0.00 sec)

3.5 基于GTID進行查看binlog

具備GTID后，截取查看某些事務日志：

# 包含要執行的gtid --include-gtids# 排除不執行的gtid --exclude-gtids# 如果要用gtid恢復數據，mysqlbinlog截取日志的時候必須要加此參數。具體原因看GTID的冪等性。 --skip-gtids

GTID的應用：

GTID號是遞增的，在多binlog日志文件之間也是遞增的，直接根據gtid號進行數據恢復。因為還沒有主從復制，單機帶來的便利之處就是方便了數據恢復（這不同于position號，在單個binlog日志文件是遞增的，恢復數據的時候不好確定position號和時間區間，所以有gtid就不用管position號了）。

3.6 GTID數據恢復實戰演練

用gtid來進行數據恢復，多個binlog日志文件進行恢復。跨多文件獲取我們想要的日志。

3.6.1 錄入測試數據

# (1) 第一波命令 mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000003 | 971 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-4 | +------------------+----------+--------------+------------------+------------------------------------------+ mysql> create database gtdb charset utf8mb4; mysql> use gtdb; mysql> create table t1(id int); mysql> begin; mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000003 | 1571 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-7 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)# (2) 第二波命令 ## flush logs新起一個binlog日志，后面的日志內容都打到這個新的binlog日志里面 mysql> flush logs; mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 194 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-7 | +------------------+----------+--------------+------------------+------------------------------------------+ mysql> create table t2(id int); mysql> begin; mysql> insert into t2 values(1),(2),(3); mysql> commit;# (3) 第三波命令 mysql> flush logs; mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000005 | 194 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-9 | +------------------+----------+--------------+------------------+------------------------------------------+ mysql> create table t3(id int); mysql> begin; mysql> insert into t3 values(1),(2),(3); mysql> commit; mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000005 | 619 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-11 | +------------------+----------+--------------+------------------+-------------------------------------------+ mysql> drop database gtdb; mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000005 | 776 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-12 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec)

3.6.2 恢復測試數據

截取日志

# 起點（確定起點，看binlog日志比較麻煩，要結合備份） mysql> show binlog events in 'mysql-bin.000003'; SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:5' create database gtdb charset utf8mb4 # 終點 mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000005 | 776 | | | 638fec45-e734-11ec-bcd2-000c29d09be0:1-12 | +------------------+----------+--------------+------------------+------------------------------------------ mysql> show binlog events in 'mysql-bin.000005'; SET @@SESSION.GTID_NEXT= '638fec45-e734-11ec-bcd2-000c29d09be0:12' | drop database gtdb

起點的GITD是5，終點的GTID是12，又因為12刪庫操作，那么要恢復的終點是11。
所以GTID的范圍是：5-11
涉及到的binlog日志文件：mysql-bin.000003 mysql-bin.000004 mysql-bin.000005

開始恢復：(錯誤演示)

[root@db01 ~]# cd /data/binlog/ # 截取日志(下面為了演示報錯，沒有加--skip-gtids。實際上必須加上--skip-gtids) [root@db01 binlog]# mysqlbinlog --include-gtids='638fec45-e734-11ec-bcd2-000c29d09be0:5-11' mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 > /tmp/gtid.sql [root@db01 binlog]# mysql -uroot -p123456 # 數據恢復失敗，因為需要在截取日志的時候加上--skip-gtids。具體原因看GTID的冪等性 mysql> source /tmp/gtid.sql;

3.6.3 GTID冪等性

開啟GTID后，MySQL恢復Binlog時，重復GTID的事務不會在執行了，如果先要執行需要加--skip-gtids。

理解什么是冪等性：同一個操作在冪等性的機制下，是不能重復運行的。比如ansible的劇本，有十個步驟，有可能運行到第五步出現問題了，修復好了在跑一次這個劇本，1-4步運行過的就不會在運行了，這就是冪等性的原則。

GITD冪等性：在這個數據庫里面有執行過gtid的事件，在去運行同一個gtid的操作的時候會直接跳過5-11的這個幾個gtid的事務，在這個數據庫里面已經執行過了。show master status;已經存在5-12，包含5-11了，有一個自動檢測的功能，如果已經重復了，就不再操作了。所以加上–skip-gtids, 用mysqlbinlog導出的sql去掉了gtid的信息（去掉了SET *關于GTID的信息），防止冪等性的產生。vimdiff /tmp/gtid.sql(加–skip-gtids) /tmp/gtid1.sql(沒有加) 對比兩個文件的不同。

開始恢復數據： (正確演示)

# (1) 截取日志(下面為了演示報錯，沒有加--skip-gtids。實際上必須加上--skip-gtids) [root@db01 ~]# cd /data/binlog/ # 如果指定截取具體庫的操作加參數 [-d gtdb] [root@db01 binlog]# mysqlbinlog --skip-gtids --include-gtids='638fec45-e734-11ec-bcd2-000c29d09be0:5-11' mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 > /tmp/gtid.sql# (2) 執行導出的日志 [root@db01 binlog]# mysql -uroot -p123456 #暫時設置為0，恢復數據時不寫入binlog，恢復完之后在設置成1 mysql> set sql_log_bin=0; # 執行成功 mysql> source /tmp/gtid.sql; mysql> set sql_log_bin=1;# (3) 開始驗證數據，恢復成功 mysql> use gtdb; Database changed mysql> show tables; +----------------+ | Tables_in_gtdb | +----------------+ | t1 | | t2 | | t3 | +----------------+ mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ mysql> select * from t2; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ mysql> select * from t3; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.37 sec)

小提示：

(1) 如果使用mysqlbinlog截取日志具體庫的操作需要加參數 [-d 數據庫名稱]

(2) 查看gtid的server_uuid的兩種方式

mysql> select @@server_uuid +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 638fec45-e734-11ec-bcd2-000c29d09be0 | +--------------------------------------+[root@db01 ~]# cat /data/3306/auto.cnf [auto] server-uuid=638fec45-e734-11ec-bcd2-000c29d09be0

4. slowlog慢日志

4.1 slowlog作用

記錄MySQL運行過程中較慢的語句，通過一個文本的文件記錄下來。幫助我們進行語句優化的工具日志。

4.2 slowlog配置

默認慢日志沒有開啟。

日志參數查看：

# (1) 慢日志是否開啟 mysql> select @@slow_query_log; +------------------+ | @@slow_query_log | +------------------+ | 0 | +------------------+# (2) 慢日志文件存放位置 mysql> select @@slow_query_log_file; +--------------------------+ | @@slow_query_log_file | +--------------------------+ | /data/3306/db01-slow.log | +--------------------------+# (3) 慢語句認定時間閾值(默認10s) mysql> select @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 10.000000 | +-------------------+# (4) 不走索引的語句記錄 mysql> select @@log_queries_not_using_indexes; +---------------------------------+ | @@log_queries_not_using_indexes | +---------------------------------+ | 0 | +---------------------------------+

進行參數永久配置：

# (1) 進行配置 [root@db01 ~]# vim /etc/my.cnf slow_query_log=1 # 日志和數據的存放路徑一般分開的 slow_query_log_file=/data/3306/db01-slow.log long_query_time=0.1 log_queries_not_using_indexes=1# (2) 重啟mysql生效 [root@db01 ~]# /etc/init.d/mysqld restart

小提示：
（1）優化是循序漸進的，慢慢的優化
（2）如果超過0.1s或者沒有索引的sql，都會記錄到慢日志當中，這個兩個滿足一個條件即可

4.3 模擬慢語句

測試數據：t100w表

use test;select * from t100w limit 500000,10; select * from t100w limit 600000,10; select * from t100w limit 600000,1; select * from t100w limit 600000,2;select id,count(num) from t100w group by id limit 10; select id,count(num) from t100w group by id limit 5; select id,count(num) from t100w group by id limit 2; select id,count(num) from t100w group by id limit 2; select id,count(k1) from t100w group by id limit 1; select id,count(k2) from t100w group by id limit 1;select k2,sum(id) from t100w group by k2 limit 1; select k2,sum(id) from t100w group by k2,k1 limit 1; select k2,sum(id) from t100w group by k2,k1 limit 1; select k1,sum(id) from t100w group by k2,k1 limit 1; select k1,count(id) from t100w group by k1 limit 0;

4.4 慢語句分析

兩個問題：
（1）有語句是類似的，能不能把他們聚在一堆？mysqldumpslow命令
（2）需要有個輕重緩急，應該先去處理最影響我們性能的這些語句，如何高效率的去分析
次數和執行時間哪個最影響用戶體驗的語句？次數比較影響，先優化次數比較多的sql語句，然后把次數多的語句拿出來，看一下執行計劃，看一下是索引問題還是語句本身問題

# -s 排序 c 計數 -t 顯示前五條，最應該優化的語句 [root@db01 ~]# mysqldumpslow -s c -t 5 /data/3306/db01-slow.logReading mysql slow query log from /data/3306/db01-slow.log Count: 4 Time=0.45s (1s) Lock=0.00s (0s) Rows=5.8 (23), root[root]@localhostselect * from t100w limit N,NCount: 4 Time=6.89s (27s) Lock=0.00s (0s) Rows=4.8 (19), root[root]@localhostselect id,count(num) from t100w group by id limit NCount: 2 Time=3.28s (6s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhostselect k2,sum(id) from t100w group by k2,k1 limit NCount: 1 Time=6.02s (6s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhostselect id,count(k2) from t100w group by id limit NCount: 1 Time=3.25s (3s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhostselect k1,sum(id) from t100w group by k2,k1 limit N

4.5 慢日志工具擴展

pt-query-digest(企業級分析慢語句的工具) + Amemometer(可視化的展示平臺)

目前MySQL配置文件內容：

[root@db01 ~]# cat /etc/my.cnf [mysqld] # 慢日志配置 slow_query_log=1 slow_query_log_file=/data/3306/db01-slow.log long_query_time=0.1 log_queries_not_using_indexes=1 # gtid配置 gtid-mode=on enforce-gtid-consistency=true # binlog日志配置 server_id=6 log_bin=/data/binlog/mysql-bin sync_binlog=1 binlog_format=row # 表明這個目錄是安全的 secure-file-priv=/tmp # 錯誤日志路徑 log_error=/tmp/mysql.loguser=mysql basedir=/app/database/mysql datadir=/data/3306/ port=3306 socket=/tmp/mysql.sock innodb_data_file_path=ibdata1:12M;ibdata2:128M;ibdata3:128M:autoextend autocommit=0[mysql] socket=/tmp/mysql.sock

5. 日志管理小總結

二進制日志和redo（數據持久化，原子性）非常重要的功能日志

5.1 錯誤日志

定位MySQL工作過程中的故障

log_error=/data/log/mysql.log [error] 上下文

5.2 二進制日志

作用：數據恢復、主從復制
配置：

server_id log_bin binlog_format: SBR RBR MRR(不常用) expire_logs_days # sync_binlog是為了我們的數據最終一致性，如果數據損壞(物理損壞/邏輯損壞)，依然能保證數據的一致,sync_binlog=1會更加安全 sync_binlog=0/1

雙一說明：(5.7版本中默認是雙一標準)

innodb_flush_log_at_trx_commit=1 每次事務提交，必然log buffer中redo落到磁盤 sync_binlog=1 每次事務提交，必然保證binlog cache中的日志落到磁盤

涉及的重點命令：

binlog基本操作（查看事件的目的就是日志截取）

show master status; show binlog events in 'mysql-bin.000001' limit 100; mysql -e "show binlog events in 'mysql-bin.000001'" grep xxxx

無GTID：

mysqlbinlog --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sqlmysqlbinlog -d [數據庫] --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sql# --start-datetime是以秒為單位，一秒鐘可能有多個sql操作，所以截取日志不是很準確 mysqlbinlog --start-datetime --stop-datetime mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

有GTID：

mysqlbinlog --skip-gtids --include-gtids='xxxxx:1-14' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql# gtid=6的sql不截取 mysqlbinlog --skip-gtids --include-gtids='xxxxx:1-5','xxxxx:7-10' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql# gtid=6的sql不截取 mysqlbinlog --skip-gtids --include-gtids='xxxxx:1-14' --exclude-gtids='xxxxx:6' mysql-bin.000002 mysql-bin.000003 > /tmp/binlog.sql

恢復日志：

# 臨時關閉二進制日志 set sql_log_bin=0; source /tmp/binlog.sql set sql_log_bin=0

滾動日志：

# 滾動日志。重啟也會滾動一個新日志。達到一個二進制日志存儲最大值也會滾動（可以設置） flush logs;

刪除日志：

purge binary logs to .... # reset master全部清空，重新記錄，謹慎操作，如果有主從使用這個命令必崩 reset master;

小提示：
參數log_bin和sql_log_bin的區別： https://www.cnblogs.com/sdadx/p/7685351.html
sql_log_bin可以動態關閉二進制日志

5.2 慢日志

配置：

slow_query_log slow_query_log_file long_query_time log_queries_not_using_indexes

分析慢日志：

mysqldumpslow -s c -t xxxx

額外擴展：

pt-query-digest

總結

以上是生活随笔為你收集整理的MySQL日志管理（十一）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

日志
MySQL

上一篇： CAP三缺一：不能同时存在
下一篇： mp3怎么在html上播放,网页上正在播