一、基本介紹
業務生產環境中,我們經常會遇到一些比較棘手的異常問題,比如,DB頻發crash卻無法定位到原因;業務庫QPS或IO吞吐非常高,但是業務量并未有增長等等情況,今天我將借助general_log日志抓取mysqldump的執行過程,來向大家介紹一下general_log功能的用法。
友情提示:開啟general_log會記錄MySQL數據庫執行的所有SQL語句,會額外增加數據庫的性能消耗。對于比較空閑的數據庫影響不大,但針對比較繁忙的、IO吞吐量比較大的數據庫來說,可能會感知比較明顯。請根據業務容忍度,謹慎評估,開啟時機和開啟時長。
官網地址:https://dev.mysql.com/doc/refman/5.7/en/query-log.html
二、實驗操作
2.1 配置general_log
</>復制代碼
#(1)開啟general_log
mysql> set global general_log=on;
mysql> show variables like '%general%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | ON |
| general_log_file | /data/mysql/log/mysqld.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)
#(2)設置general_log存儲格式為FILE
mysql> set global log_output=file;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.01 sec)
注:general_log和slow_log一樣,支持兩種存儲模式,TABLE和FILE,這里我為了方便vim編輯/過濾和查看,就使用了File模式。
2.2 抓取mysqldump在從庫備份時的參數特性
官網介紹:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_dump-slave
注1:根據mysqldump的特性,mysqldump在從庫備份時,加入了--dump-slave的參數,這個參數會在備份過程中自動斷開sql_thread,這個時候從庫是沒有新的數據寫入的(主庫過來的事務無法被寫入)
注2:該參數用于在從服務器導出dmp文件的同時,記錄主服務器的二進制文件及位置,便于執行時間點恢復操作。
</>復制代碼
#(1)執行備份操作
[root@blogs-v2 ~]# mysqldump -uroot -h192.168.0.24 -pUcloudcn --single-transaction --dump-slave=2 --databases starcto > starcto.sql
#(2)查看備份期間general_log日志輸出
[root@192-168-0-103 ~]# vim /data/mysql/log/mysqld.log
2022-08-08T11:38:42.753759+08:00 1363 Connect root@10.25.25.25 on using TCP/IP
2022-08-08T11:38:42.753922+08:00 1363 Query /*!40100 SET @@SQL_MODE='' */
2022-08-08T11:38:42.754984+08:00 1363 Query SHOW VARIABLES LIKE 'gtid\_mode'
2022-08-08T11:38:42.756997+08:00 1363 Query SELECT @@GLOBAL.GTID_EXECUTED
2022-08-08T11:38:42.757109+08:00 1363 Query SHOW SLAVE STATUS
2022-08-08T11:38:42.757249+08:00 1363 Query UNLOCK TABLES
2022-08-08T11:38:42.757384+08:00 1363 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('starcto'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2022-08-08T11:38:42.758173+08:00 1363 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('starcto')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2022-08-08T11:38:42.758651+08:00 1363 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2022-08-08T11:38:42.760160+08:00 1363 Init DB starcto
2022-08-08T11:38:42.760252+08:00 1363 Query SHOW CREATE DATABASE IF NOT EXISTS `starcto`
2022-08-08T11:38:42.760345+08:00 1363 Query SAVEPOINT sp
2022-08-08T11:38:42.760429+08:00 1363 Query show tables
2022-08-08T11:38:42.760570+08:00 1363 Query show table status like 'account'
2022-08-08T11:38:42.760768+08:00 1363 Query SET SQL_QUOTE_SHOW_CREATE=1
2022-08-08T11:38:42.760863+08:00 1363 Query SET SESSION character_set_results = 'binary'
2022-08-08T11:38:42.760953+08:00 1363 Query show create table `account`
2022-08-08T11:38:42.761057+08:00 1363 Query SET SESSION character_set_results = 'utf8'
2022-08-08T11:38:42.761147+08:00 1363 Query show fields from `account`
2022-08-08T11:38:42.761373+08:00 1363 Query show fields from `account`
2022-08-08T11:38:42.761597+08:00 1363 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `account`
2022-08-08T11:38:42.761778+08:00 1363 Query SET SESSION character_set_results = 'binary'
2022-08-08T11:38:42.761865+08:00 1363 Query use `starcto`
2022-08-08T11:38:42.761951+08:00 1363 Query select @@collation_database
2022-08-08T11:38:42.762045+08:00 1363 Query SHOW TRIGGERS LIKE 'account'
2022-08-08T11:38:42.762278+08:00 1363 Query SET SESSION character_set_results = 'utf8'
2022-08-08T11:38:42.762363+08:00 1363 Query ROLLBACK TO SAVEPOINT sp
2022-08-08T11:38:42.762452+08:00 1363 Query RELEASE SAVEPOINT sp
2022-08-08T11:38:42.762535+08:00 1363 Query SHOW SLAVE STATUS
2022-08-08T11:38:42.762665+08:00 1363 Query START SLAVE
2022-08-08T11:38:42.763592+08:00 1363 Quit
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://m.specialneedsforspecialkids.com/yun/128104.html
閱讀 425·2024-11-07 18:25
閱讀 130640·2024-02-01 10:43
閱讀 922·2024-01-31 14:58
閱讀 888·2024-01-31 14:54
閱讀 82909·2024-01-29 17:11
閱讀 3215·2024-01-25 14:55
閱讀 2032·2023-06-02 13:36
閱讀 3128·2023-05-23 10:26
极致性价比!云服务器续费无忧!
Tesla A100/A800、Tesla V100S等多种GPU云主机特惠2折起,不限台数,续费同价。
NVIDIA RTX 40系,高性价比推理显卡,满足AI应用场景需要。
乌兰察布+上海青浦,满足东推西训AI场景需要