mysql常見問題總結

前段時間都在準備面試,對mysql常見的問題進行總結,希望可以幫助需要的人

MySQL版本類問題
使用者管理類問題
伺服器配置類問題
日誌類問題
儲存引擎相關問題
MySQL架構類問題
備份恢復類問題
管理及監控類問題
優化及異常處理

MySQL版本類問題

你之前工作中使用的是什麼版本的MySQL？為什麼要選擇這個版本？

在工作中我所使用的mysql版本為5.7,使用該版本的原因是該版本功能齊全,是有經歷過大規模使用的穩定版本,基本上不會觸發致命bug。對於版本的選取主要的依據包括版本是否穩定,是否相容專案功能,是否為官方釋出的穩定版本,該版本前後幾個月是否有大的bug修復版本等。

如何決定是否要對MySQL進行升級？如何進行升級？

在升級mysql版本之前,首先要檢視最新要升級的版本的更新變化,瞭解清楚是否符合我們的升級需求,包括是否穩定,是否有功能滿足我們的需求,效率是否有所提升等或是當前版本不穩定,存在bug。
關於如何升級:
1.匯出所有使用者的許可權
2.匯出所有資料並恢復到新版本資料庫中
3.恢復使用者許可權到新資料庫中

最新版本MySQL版本是什麼？它有什麼特性比較吸引你？

Mysql最新版本為8.0
新特性包括:

一、賬戶與安全

1.使用者的建立與授權

在MySQL5.7的版本：

grant all privileges on . to ‘使用者名稱‘@’主機’ identified by ‘密碼’；

在MySQL8.0需要分開執行：

create user ‘使用者名稱‘@’主機’ identified by ‘密碼’；

grant all privileges on . to ‘使用者名稱‘@’主機’；

用以前的一條命令在8.0裡面建立使用者，會出現sql語法錯誤

2.認證外掛更新

• MySQL5.7預設身份外掛是mysql_native_password

• MySQL8.0預設的身份外掛是caching_sha2_password

3.密碼管理

MySQL8.0的密碼管理策略有3個變數

• password_history 修改密碼不允許與最近幾次使用或的密碼重複，預設是0，即不限制
• password_reuse_interval 修改密碼不允許與最近多少天的使用過的密碼重複，預設是0,即不限制
• password_require_current 修改密碼是否需要提供當前的登入密碼，預設是OFF,即不需要；如果需要，則設定成ON

4.角色管理

角色：一組許可權的集合
一組許可權賦予某個角色，再把某個角色賦予某個使用者，那使用者就擁有角色對應的許可權

二、優化器索引

1.隱藏索引（invisible index）

應用場景：

1）軟刪除

刪除索引，線上上，如果刪除錯了索引，只能通過建立索引的方式將其新增回來，對於一些大的資料庫而言，是比較耗效能的；為了避免刪錯，可以先將其設定為不可見，優化器這時候就不會使用它，但是後臺仍然在維護，確定後，再刪除。

2）灰度釋出

與軟刪除差不多，如果想要測試一些索引的功能或者隨後可能會使用到這個索引，可以先將其設定為隱藏索引，對於現有的查詢不會產生影響，測試後，確定需要該索引，可以將其設定成可見索引。

2.降序索引（descending index）

MySQL8.0開始真正支援降序索引，只有InnoDB引擎支援降序所以，且必須是BTREE降序索引，MySQL8.0不在對group by操作進行隱式排序。

3.函式索引

索引中使用函式表示式,支援JSON資料節點的索引,函式索引是基於虛擬列的功能實現的

三、通用表表示式

四、視窗函式

五、InnoDB增強

1. 整合資料欄位
2. 原子ddl操作
3. 自增列持久化
4. 死鎖檢查控制
5. 鎖定語句選項

六、JSON增強

1. 內聯路徑操作符
2. JSON聚合函式
3. JSON實用函式
4. JSON合併函式
5. JSON表函式

MySQL8.0新特性

使用者管理類問題

如何在給定場景下為某使用者授權？

根據最新的mysql8.0版本的操作來,在8.0之前的版本使用者的建立和授權可以寫在一個語句中,而8.0後,使用者的建立和授權必須語句必須分開寫。
首先我們必須建立使用者,通過語句 CREATE USER '使用者名稱'@'訪問host' IDENTIFIED BY '使用者密碼';
再使用grant命令對使用者授權 grant 許可權列表 on 資料庫.資料表 to 使用者名稱@訪問主機;
當然我們還可以通過revoke命令撤銷使用者許可權 revoke 許可權列表 on 資料庫名.表名 from 使用者名稱@訪問主機
當我們為使用者分配許可權後,還需要執行flush privileges重新整理許可權讓其立刻生效

為新使用者分配許可權時,應遵循最小許可權原則,使用grant命令對使用者授權，使用revoke撤銷許可權

常用的許可權包括:

Admin許可權:

• Create User 建立新的使用者的許可權
• Grant option 為其他使用者授權的許可權
• Super 管理伺服器的許可權

DDL許可權

• Create 新建資料庫，表的許可權
• Alter 修改表結構的許可權
• Drop 刪除資料庫和表的許可權
• Index 建立和刪除索引的許可權

DML許可權

• Select 查詢表中資料的許可權
• Insert 向表中插入資料的許可權
• Update 更新表中資料的許可權
• Delete 刪除表中資料的許可權
• Execute 執行儲存過程的許可權

如何保證資料庫賬號的安全？

• 最小許可權原則
• 密碼強度策略
• 密碼過期原則
• 限制歷史密碼重用原則

伺服器配置類問題

請分析一個Group By語句的異常原因

可能原因是sql_mode引數設定問題,sql_mode是個很容易被忽視的變數,它定義了你MySQL應該支援的sql語法，對資料的校驗等等,預設值是空值,在這種設定下是可以允許一些非法操作的,比如允許一些非法資料的插入。在生產環境必須將這個值設定為嚴格模式,所以開發、測試環境的資料庫也必須要設定,這樣在開發測試階段就可以發現問題.

sql_model 常用來解決下面幾類問題

(1) 通過設定sql_mode, 可以完成不同嚴格程度的資料校驗，有效地保障資料準備性。

(2) 通過設定sql_model為寬鬆模式，來保證大多數sql符合標準的sql語法，這樣應用在不同資料庫之間進行遷移時，則不需要對業務sql 進行較大的修改。

(3) 在不同資料庫之間進行資料遷移之前，通過設定sql_mode可以使MySQL上的資料更方便地遷移到目標資料庫中。

sql_mode常用值如下:

• ONLY_FULL_GROUP_BY:對於GROUP BY聚合操作,如果在SELECT中的列,沒有在GROUP BY中出現,那麼這個SQL是不合法的,因為列不在GROUP BY從句中
• NO_AUTO_VALUE_ON_ZERO:該值影響自增長列的插入。預設設定下,插入0或NULL代表生成下一個自增長值。如果使用者 希望插入的值為0,而該列又是自增長的,那麼這個選項就有用了。
• STRICT_TRANS_TABLES:在該模式下,如果一個值不能插入到一個事務表中,則中斷當前的操作,對非事務表不做限制
• NO_ZERO_IN_DATE:在嚴格模式下,不允許日期和月份為零
• NO_ZERO_DATE:設定該值,mysql資料庫不允許插入零日期,插入零日期會丟擲錯誤而不是警告。
• ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE過程中,如果資料被零除,則產生錯誤而非警告。如果未給出該模式,那麼資料被零除時MySQL返回NULL
• NO_AUTO_CREATE_USER:禁止GRANT建立密碼為空的使用者
• NO_ENGINE_SUBSTITUTION:如果需要的儲存引擎被禁用或未編譯,那麼丟擲錯誤。不設定此值時,用預設的儲存引擎替代,並丟擲一個異常
• PIPES_AS_CONCAT: 將”||”視為字串的連線操作符而非或運算子,這和Oracle資料庫是一樣的,也和字串的拼接函式Concat相類似
• ANSI_QUOTES:啟用ANSI_QUOTES後,不能用雙引號來引用字串,因為它被解釋為識別符

如何比較系統執行配置和配置檔案中的配置是否一致？

在資料庫執行時,我們會通過set命令動態地修改資料庫配置,這時系統執行配置和配置檔案的配置就會存在不一致的情況。

使用set命令配置動態引數,可分為三種方式動態地修改資料庫配置,session為只作用於當前程式;global為只作用於當前執行資料庫,若資料庫重啟則失效;persist為8.0版本推出的永久修改配置
set [session | @@session.] system_var_name = expr
set [global | @@global .] system_var_name = expr
set [persist| @@persist.] system_var_name = expr

比較系統執行配置和配置檔案配置可以使用pt-config-diff工具比較配置檔案

pt-config-diff u=root,p=xxxx,h=localhost /etc/my.cnf

舉幾個MYSQL中關鍵效能引數

1、伺服器配置引數

2、儲存引擎引數

日誌類問題

常用的MySQL日誌有哪些?我們在什麼情況下使用這些日誌?

• 錯誤日誌(error_log):記錄mysql在啟動、執行或停止時出現的問題,可用於分析排除MySQL執行錯誤,記錄未經授權的訪問
• 常規日誌(general_log):記錄所有發向mysql的請求,可用於分析客戶端傳送到MySQL的實際請求
• 慢查詢日誌(slow_query_log):記錄所有執行時間超過long_query_time的所有查詢或不使用索引的查詢。可用於將執行成功並符合條件的查詢記錄到日誌中,找到需要優化的SQL
• 二進位制日誌(binary_log):記錄全部有效的額資料修改日誌，可用於記錄所有對資料庫中資料的修改，基於時間點的備份和恢復，主從複製
• 中繼日誌(relay_log):用於主從複製,臨時儲存從主庫同步的二進位制日誌

錯誤日誌(error_log):
在MySQL配置檔案中，錯誤日誌所記錄的資訊可以通過log-error和log-warnings來定義，其中log-err定義是否啟用錯誤日誌功能和錯誤日誌的儲存位置log-warnings定義是否將警告資訊也記錄到錯誤日誌中。

常規日誌(general_log):
常規日誌會記錄mysqld所有相關操作，當clients連線或斷開連線時，伺服器將資訊寫入此日誌，並記錄從clients收到的每個 SQL語句。當你懷疑client中的錯誤並想要確切知道client傳送給mysqld的內容時，通用查詢日誌非常有用。預設情況下，general log是關閉的，開啟通用查詢日誌會增加很多磁碟I/O， 所以如非出於除錯排錯目的，不建議開啟通用查詢日誌。
相關引數配置介紹如下：

general_log = 0 #預設值是0，即不開啟，可設定為1
general_log_file = /data/mysql/logs/general.log #指定日誌位置及名稱
sql_log_off=off  #在session級別控制是否啟用一般查詢日誌，預設為off，即啟用

慢查詢日誌(slow_query_log):
查詢超出變數long_query_time指定時間值的為慢查詢。但是查詢獲取鎖(包括鎖等待)的時間不計入查詢時間內。
mysql記錄慢查詢日誌是在查詢執行完畢且已經完全釋放鎖之後才記錄的，因此慢查詢日誌記錄的順序和執行的SQL查詢語句順序可能會不一致(例如語句1先執行，查詢速度慢，語句2後執行，但查詢速度快，則語句2先記錄)。
相關引數配置介紹如下：

long_query_time=10 # 指定慢查詢超時時長(預設10秒)，超出此時長的屬於慢查詢
log_output={TABLE|FILE|NONE} # 定義一般查詢日誌和慢查詢日誌的輸出格式，預設為file
log_slow_queries={yes|no}    # 是否啟用慢查詢日誌，預設不啟用
slow_query_log={1|ON|0|OFF}  # 也是是否啟用慢查詢日誌，此變數和log_slow_queries修改一個另一個同時變化
slow_query_log_file=/mydata/data/hostname-slow.log  #預設路徑為庫檔案目錄下主機名加上-slow.log
log_queries_not_using_indexes=OFF # 查詢沒有使用索引的時候是否也記入慢查詢日誌

二進位制日誌(binary_log):
二進位制日誌包含了引起或可能引起資料庫改變(如delete語句但沒有匹配行)的事件資訊，但絕不會包括select和show這樣的查詢語句。
語句以”事件”的形式儲存，所以包含了時間、事件開始和結束位置等資訊。
二進位制日誌是以事件形式記錄的，不是事務日誌(但可能是基於事務來記錄二進位制日誌)，不代表它只記錄innodb日誌，myisam表也一樣有二進位制日誌。
對於事務表的操作，二進位制日誌只在事務提交的時候一次性寫入(基於事務的innodb二進位制日誌)，提交前的每個二進位制日誌記錄都先cache，提交時寫入。所以，對於事務表來說，一個事務中可能包含多條二進位制日誌事件，它們會在提交時一次性寫入。
而對於非事務表的操作，每次執行完語句就直接寫入。

相關引數配置介紹如下：

log_bin     #指定binlog是否開啟及檔名稱。
server_id     #指定伺服器唯一ID，開啟binlog 必須設定此引數。
binlog_format     #指定binlog模式，建議設定為ROW。
max_binlog_size     #控制單個二進位制日誌大小，當前日誌檔案大小超過此變數時，執行切換動作。
expire_logs_days     #控制二進位制日誌檔案保留天數，預設值為0，表示不自動刪除，可設定為0~99。

中繼日誌(relay_log):
中繼日誌用於主從複製架構中的從伺服器上，從伺服器的slave程式從主伺服器處獲取二進位制日誌的內容並寫入中繼日誌，然後由 IO 程式讀取並執行中繼日誌中的語句。

相關引數配置介紹如下：

relay_log     #定義relay log的位置和名稱。
relay_log_purge     #是否自動清空不再需要中繼日誌，預設值為1(啟用)。
relay_log_recovery     #當slave從庫當機後，假如relay log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay log ，並且重新從master上獲取日誌，這樣就保證了relay log的完整性。預設情況下該功能是關閉的，將 relay_log_recovery 的值設定為1可開啟此功能。

如何通過日誌來審計使用者活動

可以開啟常規日誌和錯誤日誌,用於分析記錄使用者的訪問情況,執行了什麼行為等等

儲存引擎相關問題

說一說你瞭解的mysql儲存引擎及其適用場景

MYISAM引擎的特點:

• 非事務型儲存引擎
• 以堆表方式儲存
• 使用表級鎖
• 支援Btree索引,空間索引,全文索引

適用場景:

• 讀操作遠遠大於寫操作的場景
• 不需要使用事務的場景

CSV引擎的特點:

• 非事務型儲存引擎
• 資料以CSV格式儲存
• 所有列都不能為NULL
• 不支援索引

適用場景:

• 做為資料交換的中間表使用

Archive引擎的特點:

• 非事務型儲存引擎
• 表資料使用zlib壓縮
• 只支援Insert和Select
• 只允許在自增ID上建立索引

適用場景:

• 日誌和資料採集類應用
• 資料歸檔儲存

Memory引擎的特點:

• 非事務型儲存引擎
• 資料儲存在記憶體中
• 所有欄位長度固定
• 支援Btree和Hash索引

適用場景:

• 用於快取欄位對映表
• 快取週期性分析資料

Innodb引擎的特點:

• 事務型儲存引擎支援ACID
• 資料按主鍵聚集儲存
• 支援行級鎖及MVCC
• 支援Btree和自適應Hash索引
• 支援全文和空間索引

適用場景:

• 適用於絕大多數場景

NDB引擎的特點:

• 事務型儲存引擎
• 資料儲存在記憶體中
• 支援行級鎖
• 支援高可用叢集
• 支援Ttree索引

在什麼情況下Innodb無法線上修改表結構

線上DDL存在的問題:

• 有部分語句不支援線上DDL
• 長時間的DDL操作會引起嚴重的主從延遲
• 無法對DDL操作進行資源限制

在無法進行線上修改表結構的情況下，要如何操作?

Innodb是如何實現事務的?

什麼是事務?通俗來說就是一組SQL語句，而且這組SQL要麼同時都執行成功要麼同時都不成功。

事務的特性：

利用回滾日誌（undo log） 和 重做日誌（redo log） 兩種表實現事務，並實現 MVCC (多版本併發控制)；

在執行事務的每條SQL時，會先將資料原值寫入undo log 中， 然後執行SQL對資料進行修改，最後將修改後的值寫入redo log中。
redo log 重做日誌包括兩部分：1 是記憶體中的重做日誌緩衝 ；2 是重做日誌檔案。在事務提交時，必須先將該事務的所有日誌寫入到重做日誌檔案進行持久化，待事務commit操作完成才算完成。
當一個事務中的所有SQL都執行成功後，會將redo log 快取中的資料刷入磁碟，然後提交。
如果發生回滾，會根據undo log 恢復資料。

MVCC 多版本併發控制
查詢需要對資源加共享鎖（S），資料修改需要對資源加排他鎖（X）

利用undo log使讀寫不阻塞，實現了可重複讀。當一個事務正在對一條資料進行修改時，該資源會被加上排它鎖。在事務未提交時對加鎖資源進行讀操作時，讀操作無法讀到被鎖資源，通過一些特殊的標誌符去讀undo log 中的資料（過程很複雜），這樣讀到的都是事務執行之前的資料。

Innodb讀操作是否會阻塞寫操作?

如果我們在讀操作新增記錄鎖,間隙鎖,臨鍵鎖等操作是會阻塞寫操作的,如使用select * from t where id = 1 for update語句

MySQL架構類問題

mysql的主從複製是如何工作的

MySQL的主從複製是一個非同步的複製過程，資料庫從一個Master複製到Slave資料庫，在Master與Slave之間實現整個主從複製的過程是由三個執行緒參與完成的，其中有兩個執行緒(SQL執行緒和IO執行緒)在Slave端，另一個執行緒(IO執行緒)在Master端。

MySQL主從複製之前我們需要先啟動Master資料庫然後再啟動Salve資料庫，然後在Salve資料庫中執行start slave;，執行完成之後，流程就如下了：

Salve的IO執行緒會讀取mastr.info檔案中配置好的主庫資訊，比如說存放的有：Master資料庫的使用者名稱、密碼、埠、還有Master的binlog索引位置；
拿到資訊之後就帶著資訊去連結Master的主庫IO執行緒
當主庫的IO執行緒先檢查SLave傳過來的配置資訊是否正確，如果正確，就拿著Slave傳過來的binlog索引位置和Master庫的binlog檔案中最後一個索引位置進行對比，如果一致就陷入等待狀態，等待Master的binlog索引位置更新；
如果不一致就把Slave傳過來的binlog索引位置往後的所有SQL語句包括最後一條SQL語句的索引位置傳送個給Slave的IO執行緒；
Slave的IO執行緒拿到資訊之後，先把Master傳過來的binlog索引在Slave的master.info檔案中進行更新；
然後再把Master傳過來的SQL語句寫入到relay檔案中，然後繼續迴圈執行第二個步驟；
Slave的SQL執行緒會一直持續的觀察relay日誌檔案中是否有改動，如果沒有就繼續監聽；
如果發現relay中有變動，那麼就獲取變動的內容轉換為SQL語句，並且把SQL語句在Salve的資料庫中進行執行

主從配置可檢視我寫的這篇部落格簡單實踐實現 MySQL 主從複製

比較一下基於GTID方式的複製和基於日誌點的複製

基於日誌點的複製：從主伺服器的哪個二進位制日誌的偏移量進行增量同步，如果指定錯誤會造成遺漏或重複。
基於GTID的複製：從伺服器會告訴主伺服器，已經在從伺服器上已經執行完了哪些gtid值，然後主庫會把從庫未執行的事務gtid值傳送給從庫執行。同一個事務只在指定的從庫上執行一次。
GTID既全域性事務ID，其保證為每一個在主上提交的事務在複製叢集中可以生成一個唯一的ID

比較一下MMM和MHA兩種高可用架構的優缺點

高可用架構都有如下的共同點：

• 對主從複製叢集中的Master節點進行監控
• 自動的對Master進行遷移，通過VIP。
• 重新配置叢集中的其它slave對新的Master進行同步

MMM架構:

需要兩個Master，同一時間只有一個Master對外提供服務，可以說是主備模式。

故障轉移步驟：

• Slave伺服器上的操作
• 完成原主上已經複製的日誌恢復
• 使用Change Master命令配置新主
• 主伺服器上操作
• 設定read_only關閉
• 遷移VIP到新主伺服器

優點：

• 提供了讀寫VIP的配置，試讀寫請求都可以達到高可用
• 工具包相對比較完善，不需要額外的開發指令碼
• 完成故障轉移之後可以對MySQL叢集進行高可用監控

缺點：

• 故障簡單粗暴，容易丟失事務，建議採用半同步複製方式，減少失敗的概率
• 目前MMM社群已經缺少維護，不支援基於GTID的複製

適用場景：

• 讀寫都需要高可用的
• 基於日誌點的複製方式

MHA架構

MHA採用的是從slave中選出Master，故障轉移：

從伺服器：

• 選舉具有最新更新的slave
• 嘗試從當機的master中儲存二進位制日誌
• 應用差異的中繼日誌到其它的slave
• 應用從master儲存的二進位制日誌
• 提升選舉的slave為master
• 配置其它的slave向新的master同步

優點：

• MHA除了支援日誌點的複製還支援GTID的方式

• 同MMM相比，MHA會嘗試從舊的Master中恢復舊的二進位制日誌，只是未必每次都能成功。如果希望更少的資料丟失場景，建議使用MHA架構。
  缺點：

• MHA需要自行開發VIP轉移指令碼。

• MHA只監控Master的狀態，未監控Slave的狀態

如何減小主從複製的延遲

主從延遲產生的原因及處理方法:

1. 網路延遲
   • 減小單次事務處理的資料量以減少產生的日誌檔案大小
   • 減少主上所同步的Slave的數量
2. 大事務
   • 化大事務為小事務,分批更新資料
   • 使用pt-online-schema-change工具進行DDL操作
3. 單執行緒恢復
   • 使用mysql5.7之後的多執行緒複製
   • 使用MGR複製架構

說說你對MGR的認識

MGR（MySQL Group Replication）簡稱：MGR(組複製)。它是官方推出的一種基於Paxos協議的狀態機複製，徹底解決了基於傳統的非同步複製和半同步複製中資料一致性問題無法保證的情況

MGR複製架構：

MGR複製實現原理：

MGR複製架構的優點:

• Group Replication組內成員基本無延遲
• 可以支援多寫操作,讀寫服務高可用
• 資料強一致,可以保證不丟失事務

MGR複製架構的缺點:

• 只支援InnoDB儲存引擎的表,並且每個月必須有一個主鍵
• 單主模式下很難確認下一個PRIMARY
• 只能用在gtid模式的複製形式下,且日誌格式必須為row

MGR複製架構的適用場景:

• 對主從延遲十分敏感的應用場景
• 希望可以對讀寫提供高可用的場景
• 希望可以保證資料強一致的場景

如何解決資料庫讀/寫負載大的問題

解決讀負載大問題步驟:

• 為原DB增加Slave伺服器
• 進行讀寫分離,把讀分擔到Slave
• 增加資料庫中間層,進行負載均衡

解決寫負載大問題:

• 進行分庫分表

備份恢復類問題

在之前的工作中是如何對資料庫進行備份的?

常用的備份工具:

mysqldump的優點:

• 備份結果為可讀的SQL檔案,可用於跨版本跨平臺恢復資料
• 備份檔案的尺寸小於物理備份,便於長時間儲存
• mysql發行版自帶工具,無需安裝第三方軟體

mysqldump的缺點:

• 只能單執行緒執行備份恢復任務,備份恢復速度較慢
• 為完成一致性備份需要對備份表加鎖,容易造成阻塞
• 會對Innodb Buffer Pool造成汙染

mysqlpump的優點:

• 語法同mysqldump高度相容,學習成本低
• 支援基於庫和表的並行備份,可以提高邏輯備份的效能
• 支援使用ZLIB和Lz4演算法對備份進行壓縮

mysqlpump的缺點:

• 基於表進行並行備份,對於大表來說效能較差
• 5.7.11之前版本不支援一致性並行備份
• 會對Innodb Buffer Pool造成汙染

xtrabackup的優點:

• 支援Innodb儲存引擎的線上熱備份,對Innodb緩衝沒有影響
• 支援並行對資料庫的全備和增量備份
• 備份和恢復效率比邏輯備份高

xtrabackup的缺點:

• 做單表恢復時比較複雜
• 完整的資料檔案備份,故備份檔案比邏輯備份大
• 對跨平臺和資料庫版本的備份恢復支援度不如邏輯備份

如何對mysql進行增量備份和恢復

可檢視我這裡寫的部落格如何刪庫以後不跑路

如何對binlog進行備份

• 利用cp命令進行離線備份
• 使用mysqlbinlog命令線上時實備份

管理及監控類問題

說說你都對mysql進行過哪些監控?

效能類指標:

功能類指標:

這些監控是如何實現的?

QPS(資料庫每秒鐘處理的請求數量)計算:

• show global status like ‘com%’
• sum(com_xxx)
• show global status like ‘Queries’
• QPS=(Queries2-Queries1)/時間間隔

TPS(每秒鐘處理的事務數量)計算:

• show global status where Variable_name in (‘com_insert’,’com_delete’,’com_update’)
• Tc=com_insert+com_delete+com_update
• TPS=(Tc2-Tc1)/(time2-time1)

併發數計算:

• show global status like ‘Threads_running’

連線數計算:

• show global status like ‘Threads_connected’
  報警閥值:
  • Threads_connected/max_connections > 0.8

Innodb快取命中率:

• (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100%
• Innodb_buffer_pool_read_requests:從快取池中讀取的次數
• Innodb_buffer_pool_reads:表示從物理磁碟讀取的次數

資料庫可用性:

• 週期性連線資料庫並執行select @@version
• Mysqladmin -uxxx -pxxx -hxxx ping

阻塞:

• mysql < 5.7

• select b.trx_mysql_thread_id as ‘被阻塞執行緒’,b.trx_query as ‘被阻塞SQL’,c.trx_mysql_thread_id as ‘阻塞執行緒’,c.trx_query as ‘阻塞SQL’,(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) as ‘阻塞時間’ from information_schema.innodb_lock_waits a join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) > 30

• mysql >= 5.7

• select waiting_pid as ‘被阻塞的執行緒’,waiting_query as ‘被阻塞的SQL’,blocking_pid as ‘阻塞執行緒’,blocking_query as ‘阻塞SQL’,wait_age as ‘阻塞時間’,sql_kill_blocking_query as ‘建議操作’ from sys.innodb_lock_waits where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started)) > 30

監控慢查詢:

• 通過慢查詢日誌監控
• 通過information_schema.PROCESSLIST表實時監控
  • select * from information_schema.processlist where time>60 and command <> ‘sleep’;

監控主從延時:

• pt-heartbeat –user=xx –password=xx -h master –create-table –database xxx –update –daemonize –interval=1
• pt-heartbeat –user=xx –password=xx -h slave –database crn –monitor –daemonize –log /tmp/slave_lag.log

監控主從狀態:

• show slave status

可通過編寫shell指令碼並執行定時任務對資料庫進行監控

優化及異常處理

請列舉三個你曾經處理過的讓你印象深刻的問題

• 資料庫伺服器負載過大
• 慢查詢造成的磁碟IO爆表
• 主從資料庫資料不一致

處理過哪些mysql主從複製異常

你會從哪些方面對mysql資料庫進行優化

本作品採用《CC 協議》，轉載必須註明作者和本文連結