MySQL運維中的疑難問題解讀

MySQL運維中的疑難問題解讀1、性能問題排查Ｑ：MySQL如何排查CPU占用高的問題？問題描述：重點是關于通過哪些系統(tǒng)表或者常用的sql來確定導致問題的sql？感覺這方面的資料很少，不像Oralce的那些v$視圖，網(wǎng)上資料很多，sql語句也很多。答：可以通過將系統(tǒng)線程號與SQL對應來查看top-H-p<mysqld進程id>PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIMECOMMAND23974mysql2001658m358m12mR99.91.10:05.52mysqld12295mysql2001658m358m12mS0.31.10:02.44mysqldSELECTa.THREAD_OS_ID,b.user,b.host,b.db,mand,b.time,b.state,FROMperformance_schema.threadsa,information_cesslistbWHEREb.id=cesslist_id;THREAD_OS_IDUSERHOSTdbcommandTIMEstateinfo**23974**root01:21466sysQUERY29SendingDATASELECTa.*FROMtesta,testb,testc,testdORDERBYa.valueLIMIT0,1000Ｑ：MySQL數(shù)據(jù)庫內存使用率高，應該如何進行排查？問題描述：內存使用率，通過系統(tǒng)命令能定位到mysql占用的內存高，如何通過系統(tǒng)表或者相關的sql語句，定位到占用內存高的那部分sql？答：MysqlServerMemoryUsage=SumofGlobalBuffers(numberofConnection*Perthreadmemoryvariables)a）單個mysql連接線程的內存消耗統(tǒng)計，這里只是統(tǒng)計分配值（具體駐留內存占用值統(tǒng)計不到）selectb.thd_id,b.user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated,current_statementfrommemory_by_thread_by_current_bytesa,sessionbwherea.thread_id=b.thd_idlimit1;

b）統(tǒng)計top10的bufferpool占用內存的表select*frominnodb_buffer_stats_by_tableorderbypagesdesclimit10;Ｑ：MySQL數(shù)據(jù)庫磁盤IO使用高，請問如何進行排查？問題描述：通過系統(tǒng)能確定是數(shù)據(jù)庫的IO讀寫高，有哪些系統(tǒng)表或者sql聯(lián)合起來可以把關鍵的sql定位出來？答：mysql5.7版本為例，結合performance_schema來查看MySQL數(shù)據(jù)庫的各種指標。相當于Oracle數(shù)據(jù)庫中的各種性能視圖，可以查看幾乎所有的數(shù)據(jù)庫狀態(tài)。IO的話，可以查看這張表：performance_schema.file_instances：列出了文件I/O操作及其相關文件的工具實例排查思路：1、慢SQL排除2、硬件問題-RAID降級，磁盤故障等排除2、innodb_log、innodb_buffer_pool_wait相關配置和等待3、IO相關參數(shù)配置innodb_flush_method=O_DIRECTinnodb_file_per_table=1innodb_doublewrite=1delay_key_writeinnodb_read_io_threadsinnodb_read_io_threadsinnodb_io_capacityinnodb_flush_neighborssync_binlog主要關注：sync_binlog建議：最好部署相關的監(jiān)控平臺或者對比歷史性能記錄，結合業(yè)務以及負載來分析。2、優(yōu)化方法Q：MySQL優(yōu)化的常用方法有哪些？答：一、最常見是慢查詢優(yōu)化1、打開慢查詢記錄，設置記錄SQL的最短時間2、使用pt工具，分類統(tǒng)計慢查詢語句3、針對執(zhí)行次數(shù)多或者時間長的語句進行優(yōu)化（索引優(yōu)化、SQL改寫、業(yè)務邏輯優(yōu)化）ps:也可以在系統(tǒng)表中，查看全表掃描多的表等二、配置文件優(yōu)化1、內存使用量2、各種方面寫盤策略Q：MySQL中執(zhí)行計劃如何解讀？問題描述：1:執(zhí)行計劃如何解讀？db2中按照從下往上，從左到右的順序來解讀2：執(zhí)行計劃中需要關注的特殊標識有哪些？例如：usingwhereusingfilesortUsingtemporary等等答：1、執(zhí)行順序，看ID列id值相同執(zhí)行順序從上到下。id值不同時id值大的先執(zhí)行。2、關注的特殊標識SELECT_TYPE--執(zhí)行查詢類型，不同類型對應的Type：訪問類型，很重要possible_keys：索引使用關于explain輸出參數(shù)，可參考官方文檔：以MySQL5.7為例/doc/refman/5.7/en/explain-output.htmlQ：MySQL中關于表維護的操作（提升性能相關的）有哪些？問題描述：MySQL中關于表維護的操作（提升性能相關的）有哪些？例如db2中的表重組，db2rbind綁定包等操作答：MySQL的表維護語句：ANALYZETABLE：更新表統(tǒng)計信息。執(zhí)行該語句的時候innodb及myisam表會加上讀鎖，停止數(shù)據(jù)更新。該語句支持innodb，myisam及ndb表，針對myisam表，該語句等同myisamchk--analyzeOPTIMIZETABLE:整理數(shù)據(jù),表碎片CHECKTABLE:用來檢查數(shù)據(jù)庫表和索引是否損壞REPAIRTABLE：checktable語句可以檢查一個表中的的問題，若表或索引損壞，可以使用repairtable語句嘗試修正它Q：有哪些工具可以幫助優(yōu)化MySQL的？答1：SQL優(yōu)化主要還是看經驗和對慢查詢梳理。配置文件優(yōu)化，一般來說就幾個參數(shù)需要優(yōu)化，其他可以不動/major/MySQLTuner-perl答2：以下工具可以參考：pt-mysql-summarypt-variable-advisorpt-duplicate-key-checkerpt-deadlock-logger

或者tuning-primer.sh3、高可用問題Q：MySQL原廠有Ｏracle的cluster集群，有哪些主流的開源適合高并發(fā)集群呢？答：一、MySQL高可用方案MySQL以及各種開源數(shù)據(jù)庫，也有自身的集群方案，但是大多需要和業(yè)務以及借助第三方工具來實現(xiàn)?；蛘咄ㄟ^分布式來均衡高并發(fā)。主要的高可用集群架構可以分為如下幾種：1、基于共享存儲的高可用方案--SAN基于共享存儲的高可用，及使用傳統(tǒng)的基于SAN共享存儲，結合開源的Keeplive做主從同步，可避免除存儲外的組件損壞引起的宕機，部署相對簡單，對應用透明，但是存儲時單點，且存在性能瓶頸2、基于磁盤復制的高可用方案-DRBD保證主備的數(shù)據(jù)一致性，不依賴共享存儲，此方案處理failover的方式上依舊需要借助主機層面的高可用組件，如keeplive，Heartbeat等。不同的是，在數(shù)據(jù)共享方面，采用了基于塊級別的數(shù)據(jù)同步軟件DRBD來實現(xiàn)，，但是可擴展性較差。它并不共享存儲，而是通過服務器之間的網(wǎng)絡復制數(shù)據(jù)。適用于數(shù)據(jù)庫訪問量不太大，短期內訪問量增長不會太快，對數(shù)據(jù)庫可用性要求非常高的場景。3、基于MySQL自身的主從復制-Replication基于MySQL自身的主從復制，5.7以后的GTID，以及之前的replication。主從復制，部署簡單，但是只能有一個Master進行讀寫，其余都為備庫，還需要結合業(yè)務。并發(fā)量不大的情況下，可采取主從，管理簡單。4、MHA高可用方案MHA是一套MySQL高可用管理軟件，除了檢測Master宕機后，提升候選Slave為NewMaster之外（漂虛擬IP），還會自動讓其他Slave與NewMaster建立復制關系。MHAManager可以單獨部署在一臺獨立的機器上，并管理多個master-slave集群。但是，只支持一主多從架構，集群中必須最少有三臺數(shù)據(jù)庫服務器，要保持切換對應用透明，依然依賴于VIP，不適用于大規(guī)模集群部署，配置比較復雜。且MHA管理節(jié)點本身的HA無法保證。MySQL5.7之前數(shù)據(jù)不丟的前提是Master服務器還可以被MHAManager進行SSH連接，通過應用保存的binlog的方式來保證。MySQL5.7之后通過無損復制，僅僅是減少了丟數(shù)據(jù)的可能性，假如此時的狀態(tài)為切成異步的狀態(tài)，那就和之前一樣了（可以設置超時的時間很大）；當Master恢復的時候，最后一部分數(shù)據(jù)是否需要Flashback，MHA也是不負責這個事情，需要人工介入。5、基于zookeeper/consul的高可用方案借助zookeeper組件，結合MHA或者其他高可用架構場景，實現(xiàn)強制一致性的高可用集群分布，可適應大規(guī)模高并發(fā)場景，需要一定的技術實力，引入zookeeper，架構復雜度上升，但是整體擴展性非常好，可以管理大規(guī)模集群。保證了整個系統(tǒng)的高可用，主從的強一致依賴于MySQL本身，比如半同步，或者外圍工具的回補策略6、基于MMM高可用方案MMM提供了MySQL主主復制配置的監(jiān)控、故障轉移和管理的一套可伸縮的腳本套件。在MMM高可用方案中，典型的應用是雙主多從架構，通過MySQLreplication技術可以實現(xiàn)兩個服務器互為主從，且在任何時候只有一個節(jié)點可以被寫入，避免了多點寫入的數(shù)據(jù)沖突。同時，當可寫的主節(jié)點故障時，MMM套件可以立刻監(jiān)控到，然后將服務自動切換到另一個主節(jié)點，繼續(xù)提供服務，從而實現(xiàn)MySQL的高可用?？梢造`活選擇VIP方案或者全局目錄數(shù)據(jù)庫方案(更改MasterIP映射)來進行切換。MMM提供了自動和手動兩種方式移除一組服務器中復制延遲較高的服務器的虛擬ip，同時它還可以備份數(shù)據(jù)，實現(xiàn)兩節(jié)點之間的數(shù)據(jù)同步等。由于MMM無法完全的保證數(shù)據(jù)一致性，所以MMM適用于對數(shù)據(jù)的一致性要求不是很高，但是又想最大程度的保證業(yè)務可用性的場景。對于那些對數(shù)據(jù)的一致性要求很高的業(yè)務，非常不建議采用MMM這種高可用架構。7、基于中間件proxy高可用組件的集群方案中間件：阿里Cobar、MyCAT360Atlas淘寶Tddl網(wǎng)易CutusMySQLProxyProxySQL(Percona)KingShardMaxScale(MariaDB)OneProxy切換對應用透明,可擴展性強，方便分片擴展,可以跨機房部署切換,但是需要有一定自研能力，或者選擇有完整的后期技術支持的中間件，以及社區(qū)活躍度較高的，有一定能力，后期可自研或者自己優(yōu)化開發(fā)相關的中間件。以適應自身的業(yè)務需求。二、集群/分布式基于集群或者分布式的HA包括：MysqlGroupReplicationMysqlInnoDBClusterPerconaXtraDBClusterMariaDBGaleraCluster1、MGR關于MGR原理，可參考可以參考阿里的數(shù)據(jù)庫內核月報，關于mgr的文檔。/monthly/2017/08/01/基于傳統(tǒng)異步復制和半同步復制的缺陷——數(shù)據(jù)的一致性問題無法保證，MySQL官方在5.7.17版本正式推出組復制（MySQLGroupReplication，簡稱MGR）。由若干個節(jié)點共同組成一個復制組，一個事務的提交，必須經過組內大多數(shù)節(jié)點（N/21）決議并通過，才能得以提交。如上圖所示，由3個節(jié)點組成一個復制組，Consensus層為一致性協(xié)議層，在事務提交過程中，發(fā)生組間通訊，由2個節(jié)點決議(certify)通過這個事務，事務才能夠最終得以提交并響應。引入組復制，主要是為了解決傳統(tǒng)異步復制和半同步復制可能產生數(shù)據(jù)不一致的問題。組復制依靠分布式一致性協(xié)議(Paxos協(xié)議的變體)，實現(xiàn)了分布式下數(shù)據(jù)的最終一致性，提供了真正的數(shù)據(jù)高可用方案。2、MySQLInnoDBClusterCluster解決方案其實是由MySQL的幾個不同產品和技術組成的，比如MySQLShell,MySQLRouter,GroupReplication.一組MySQL服務器可以配置為一個MySQL集群。在默認的單主節(jié)點模式下，集群服務器具有一個讀寫主節(jié)點和多個只讀輔節(jié)點。輔助服務器是主服務器的副本?？蛻舳藨贸绦蛲ㄟ^MySQLRouter連接到主服務程序。如果主服務連接失敗，則次要的節(jié)點自動提升為主節(jié)點，MySQLRouter請求到新的主節(jié)點。InnoDBCluster不提供NDBCluster支持3、PerconaXtraDBCluster官網(wǎng)地址：/doc/percona-xtradb-cluster/5.7/intro.html節(jié)點在接收sql請求后，對于ddl操作，在commit之前，由WSREPAPI調用galera庫進行集群內廣播，所有其他節(jié)點驗證成功后事務在集群所有節(jié)點進行提交，反之rollback。pxc保證整個集群所有數(shù)據(jù)的強一致性，滿足CAP理論中滿足：Consistency和Availability。PXC提供的特性同步復制，事務要么在所有節(jié)點提交或不提交多主復制，可以在任意節(jié)點進行寫操作在從服務器上并行應用事件，真正意義上的并行復制節(jié)點自動配置數(shù)據(jù)一致性，不再是異步復制限制：只支持INNODB表不允許大事務的產生（否則的話后果很嚴重）寫性能取決于最差的節(jié)點不能解決熱點更新問題樂觀鎖控制對于寫密集型應用需要控制單個節(jié)點的大小，單個節(jié)點數(shù)據(jù)越大，新加節(jié)點如果采用自動添加可能產生很大抖動（添加節(jié)點建議用備份或者備份binlog進行IST(IncrementalStateTransfer)增量同步Ｑ：目前銀行主流的MySQL高可用采用哪種方式，MHA還是MGR？問題描述：目前銀行主流的MySQL高可用采用哪種方式？MHA還是MGR，各自的優(yōu)缺點是什么？各自有哪些坑需要注意？答：銀行主流何種MySQL高可用方案不太了解?？赡苁侵鲝闹虚g件自研套件的模式。由于MGR技術相對較新，目前使用MHA更多。但個人認為，MGR或者基于此的innodbcluster架構（或替代方案）會成為未來主流。MHA：優(yōu)點：成熟穩(wěn)定，自動切換主從，主節(jié)點宕機后盡可能少丟失數(shù)據(jù)（自動抓取未復制的binlog）。缺點：管理節(jié)點單點、可能腦裂、可能有不必要切換、還是有丟數(shù)據(jù)風險、組件多維護相對麻煩MGR：優(yōu)點：基于paxos的高可用架構，支持多主（不建議），強一致缺點：需要innodb引擎（丟業(yè)務有改造代價），應用端沒有自動切換（可以通過中間件解決），技術太新可能有未知bug其他的話還有PXC，但是因為性能問題不太建議。4、安全防范Q：如何做到數(shù)據(jù)庫賬號權限的精細化管理？答：一定是做到對權限的全方位掌控。根據(jù)賬戶的不同類型，以前綴區(qū)分。簡單的分類，分為業(yè)務賬戶和實名賬戶。細分來講，業(yè)務賬號分為網(wǎng)站應用、手機應用、報表應用、服務應用、查詢服務，實名賬戶可以跟蹤到具體的員工。網(wǎng)站應用(web_業(yè)務簡稱)手機應用(mob_業(yè)務簡稱)報表應用(rep_業(yè)務簡稱)服務應用(dae_業(yè)務簡稱)查詢服務(sea_業(yè)務簡稱)實名查詢(dev_姓名拼音)業(yè)務賬號權限最大到SELECT、UPDATE、DELETE和INSERT，查詢服務和實名查詢賬戶只能有查詢權限。每個用戶只有一個密碼，授權時需要知悉此用戶是否存在，如果存在，使用舊密碼授權，如果不存在，生成隨機密碼進行授權。實名權限只能通過堡壘機或者跳板機進行查詢，堡壘機有用戶登錄和執(zhí)行SQL日志。線上IDC數(shù)據(jù)庫只允許線上Web機連接，不允許測試機連接。員工申請權限需要工單申請，授權只能DBA操作。DBA需要做好權限控制，相關業(yè)務負責人可以申請較高權限，但需要郵件抄送上一級領導進行審批。DBA有一套完整的元數(shù)據(jù)庫，里面記錄了所有的用戶相關信息，此數(shù)據(jù)庫重要級別最高，做好安全控制。用戶的密碼需要足夠復雜，而且有一套完整的隨機密碼生成規(guī)則。業(yè)務方通知業(yè)務賬戶存在異常，需要制定快速更改賬戶的流程。員工申請的臨時高權賬號，需要有備案，需要設置密碼過期時間，而且需要制定回收流程。MySQLroot密碼只有DBA擁有，而且不允許將此密碼保存在任何云筆記或者云存儲上，只能保存到本地。另外，定期修改MySQLroot密碼。通過終端進入MySQL，不允許將密碼明文顯示。用戶授權操作建議在Web頁面完成，需要做好安全控制。此項也就是DB運維管理平臺，需要編碼實現(xiàn)。做好數(shù)據(jù)備份，可以在誤操作最快恢復數(shù)據(jù)。如有可能，在新業(yè)務上線MySQL審計方案，可以通過init-connect參數(shù)access_logbinlog實現(xiàn)審計。關于精細化，主要是各個權限分配細致，做到，不重復，其次是權限的定義明確，該給什么權限給什么權限，不存在模糊權限，最后是權限的記錄，做到從權限開始，審批，授權，收回，刪除等一整套的規(guī)章流程，最重要的是一個精細化的思想，做到心中有數(shù)。Q：如何做到數(shù)據(jù)庫賬號權限的精細化管理？答：開審計，監(jiān)控軟件商業(yè)的現(xiàn)在有很完善的，開源的也有免費的插件，沒有最成熟，只有最適合。還想說一下，數(shù)據(jù)庫的安全不止要從數(shù)據(jù)庫方面考慮，還要考慮網(wǎng)絡和系統(tǒng)，網(wǎng)絡和系統(tǒng)如果在入侵的過程中防不住了，數(shù)據(jù)庫層次的防御力也有限，在前邊兩個層次就要做到萬無一失才對，數(shù)據(jù)庫的安全只是針對數(shù)據(jù)，針對一些sql注入等等進行一些安全配置。還要做好備份，主從，異地備，高可用等，其實這些都可以算在數(shù)據(jù)庫的安全里邊，特別是MySQL，作為一個DBA，也許我們做不到萬無一失，但我們要用一萬種方法來防止出問題，能考慮到的，能做到的，我們都要用上。需要確定你當前所想要達到的目標，其次是對業(yè)務的影響。目前常規(guī)是通過數(shù)據(jù)庫防火墻的策略規(guī)則，進行告警處理，主要的還是事后審計的報表分析。5、遷移問題Q：使用MySQL替換Oracle20TB左右的庫，如何設計才能確保性能和高可用？答1：建議分表分庫，建立好相應的索引，使用多節(jié)點主從heartbeat/keepalived/MHA/MMM等等一類的方案來保證高可用。答2：首先需要明確ORACLE20TB!=#MYSQL20T其次

MySQL

替換Oracle

首先需要探討可行性還有就是如果使用MySQL對于這么大的量需要考慮的一點是冷熱數(shù)據(jù)，這不是單出的分庫分表就能解決的，需要根據(jù)事情去探討。答3：首先要知道這是一個OLAP還是一個OLTP。是前者的話，數(shù)據(jù)再大一倍也無妨，畢竟數(shù)據(jù)的抽取方式要變化，甚至手工作業(yè)也能滿足；如果是后者，并且業(yè)務很復雜，那做的工作可要多了，先說數(shù)據(jù)的遷移，數(shù)據(jù)庫中的各種對象（比如函數(shù)和過程的改寫），再說大一點的查詢，分庫分表要考慮進來（分區(qū)不建議考慮，MySQL的分區(qū)表處處受限）。答4：MySQL高可用架構可以參考MHA/PXC/MGR，根據(jù)自己的實際需要進行選擇。數(shù)據(jù)安全性方面考慮增強半同步，數(shù)據(jù)庫版本建議8.0以上。MySQL單實例承擔20TB的數(shù)據(jù)量不是不可以，只不過負擔太重了，你需要考慮的有以下幾個方面：1、單實例TPS/QPS限制2、備份、恢復的影響3、磁盤容量的規(guī)劃4、鎖爭用、單實例連接數(shù)等等如果全部遷移到MySQL，建議：1、先垂直拆分，由多個集群承擔對應的業(yè)務2、水平拆分，確定分片鍵，需要多少分片承擔壓力（單表建議不要超過500w，單實例不要超過500張，單實例數(shù)據(jù)總容量不要超過1T）3、由多個從庫承擔讀壓力（需要考慮延遲，交易類型一律走主庫）有了高可用，有了拆分，那么還需要中間訪問層，目前比較好的開源proxy有：1、簡單讀寫分離的：ProxySQL、DBLE、Cetus等2、具備分庫分表的：DBLE、Cetus、vitness等以上只是簡單的一點看法Q：Db2遷移到MySQL，有什么好的工具與方法？答：Db2遷移到MySQL的工作難點、問題還真不在工具上！最大問題在Db2應用的遷移上，估計Db2應用重構工作量、難度，是你們沒法想象的！也正因如此，想把Oracle這種遷移到國產平臺，難度很大！首先，Db2中的數(shù)據(jù)即使遷移到MySQL中。但是，Db2中復雜的SQL、Xquery、SQLPL存儲過程等如何重寫，業(yè)務程序中依賴Db2的部分如何重構，都是問題！Q：尋求TB級SQLserver和

MySQL數(shù)據(jù)高效遷移工具或方案？問題描述：數(shù)據(jù)量為TB級，分表分庫，大概120個庫左右，后臺開發(fā)用的.net，所以前期數(shù)據(jù)處理什么的都是在sqlserver上進行，之后再遷移到mysql上，所以要經常性的在兩種數(shù)據(jù)庫間遷移數(shù)據(jù)，前面用kettle這類傳統(tǒng)的ETL工具來做數(shù)據(jù)抽取，但是速度慢，品牌多，配置起來也麻煩，想問下各位大佬有沒有什么高效的數(shù)據(jù)遷移工具或者方案沒有？答1：針對特定的規(guī)則邏輯還是自己開發(fā)工具吧，直接文本的導出導入會更快，對于你們經常性的遷移，沒有什么太適合的工具。kettle速度慢，品牌多，配置起來也麻煩

應該是你的使用姿勢不對。答2：樓主明確一下每天的變化的數(shù)據(jù)有多大，如果只是1-10G這樣一個量級，數(shù)據(jù)抽取工具隨便哪一個都能應付過來，不建議超過2個，太多了維護成本過高。6、其他問題Q：MySQL適用的場景是什么？問題描述：MySQL有很多特性，不容易掌