MySQL DBA常用技能集錦

1、MySQL DBA常用技能集錦技術創(chuàng)新，變革未來1.1主從又不一致了老生常談的參數(shù)一致性相關innodb_flush_log_at_trx_commit=1sync_binlog=1復制安全relay_log_info_repository = TABLEmaster_info_repository = TABLE sync_relay_log = 1sync_master_info = 1影響復制安全的參數(shù)故事背景MySQL5.6.24主實例Binlog磁盤滿磁盤清理影響面主從實例不同步，數(shù)據(jù)不一致現(xiàn)象；重做所有從實例原因ERRORCouldnotopen./master-bin.00006

2、4forlogging(error 13).TurningloggingoffforthewholedurationOftheMySQL serverprocess.Toturnitonagain:fixthecause,shutdownthe MySQLserverandrestartit.影響復制安全的參數(shù)分析binlog_error_actionignore_errorabort_server驗證：ABORT_SERVERErrormessage:Binaryloggingnotpossible.Eitherdiskisfullorfilesystemisreadonlywhilerot

3、atingthe binlog.Abortingtheserver影響復制安全的參數(shù)結論5.6.22版本增加了Abort_server選項5.6版本默認值為IGNORE_ERROR5.7版本默認值為ABORT_SERVER該參數(shù)支持動態(tài)修改推薦生產(chǎn)環(huán)境配置ABORT_SERVER選項1.22億行單表的故事一張2億行單表的故事InnoDB:Operatingsystemerrornumber0.InnoDB:Check that your OS andfile systemsupport files ofthissize.故事背景CPU：64c Mem：256G Disk：SSD2億行70G單表

4、上做了alter table加字段操作，報錯如下：mysqlaltertablefriendaddcolumntagvarchar(255)comment好友對應的 tags;InnoDB:Error:Writetofile(merge)failedatoffset3039821824.InnoDB:1048576bytesshouldhavebeenwritten,only471040werewritten.影響面不要跟我談影響面泡面都涼了！一張2億行單表的故事分析問題直接原因：/tmp滿了，重新配置了tmpdir2億3000W條記錄:mysqlaltertablefriendaddcolu

5、mntagvarchar(255) comment好友對應的tags;QueryOK,0rowsaffected(1hour48min36.33sec)Records:0Duplicates:0Warnings:0一張2億行單表的故事問題1：何時產(chǎn)生臨時表？altertabletablecopy-#sql-INPLACE-#sql-ib/tmpdir復雜排序Orderby-SQL_*.Groupby-SQL_*.參考文獻：/doc/refman/5.7/en/temporary-files.html一張2億行單表的故事問題2:altertable添加列屬于哪種操作參考文獻：/doc/refma

6、n/5.6/en/innodb-create-index-overview.html一張2億行單表的故事問題3:是否有其他的DDL方式 mysql：onlineddlpt-osc：perconaonlineschemachangegh-ost：GitHubsonlineschemamigrationforMySQL參考文獻：/doc/refman/5.6/en/innodb-online-ddl.html /doc/percona-toolkit/2.1/pt-online-schema-change.html /github/gh-ost一張2億行單表的故事總結1.tmpdir在生產(chǎn)上線時提

7、前規(guī)劃，建議單獨配置足夠容量 的目錄2.操作ddl前建議根據(jù)altertable的類型，對照官方文檔 確認是inplace還是tablecopy一張2億行單表的故事總結3. DDL操作方式的原則 表數(shù)據(jù)量大小最直接影響選擇ddl方式的因素，建議超過100萬行的表，通過posc或gh-ost操作是否可以接受延遲接受延遲的環(huán)境，表小于100萬，用原生 不接受延遲的環(huán)境，使用posc或gh-ost1.3瘋狂的Slowlog瘋狂的SlowLog瘋狂的SlowLoggrow quickly瘋狂的SlowLog正確的姿勢應該是這樣的2.1偶遇Metadatalock偶遇metadata lockMetad

8、ata lock wait 出現(xiàn)的場景有大事務正在使用表存在有未提交的事物存在有未提交的事務,且是失敗操作解決方案找到未釋放metadatalock的session，并killKillddl語句，等待下一次運維時間變更偶遇metadata lockMetadata lock wait 出現(xiàn)的場景1有大事務正在使用表偶遇metadata lockMetadata lock wait 出現(xiàn)的場景2存在有未提交的事務偶遇metadata lockMetadata lock wait 出現(xiàn)的場景3存在有未提交的事務,且是失敗操作2.2復制延遲，再延遲復制延遲的幾種可能主從服務器的配置或者壓力不一，從實

9、例的IO能 力太弱主庫的TPS太高導致備庫延遲備份WaitingforglobalreadlockDDL導致備庫延遲（altertable,create index,optimizetable,repairtable）運行大事務對無主鍵的表進行刪除或者更新運行大事務背景max_binlog_size=512M-rw-r-1mysqlmysql512MMay1110:17mysql-bin.000063-rw-r-1mysqlmysql512MMay1115:59mysql-bin.000064-rw-r-1mysqlmysql1220MMay1222:23mysql-bin.000065缺少主

10、鍵背景Binlog：Row模式，沒有主鍵，表數(shù)據(jù)量3000萬左右主上執(zhí)行DELETEFROM*_snsdb.*_gamedata_service缺少主鍵mysqlbinlog -vv -base64-output=decode-rows mysqld-relay- bin.000065 -start-position=542989459|less缺少主鍵3.0觀測視角，火焰圖背景知識Different types of flame graphs:On-CPUOff-CPUHot/ColdDifferentialMemory背景知識Profiling tools:Linux:Solaris, i

11、llumos, FreeBSD: Mac OS X:Windows:perf, SystemTap, DTraceDTrace and Instruments Xperf.exe參考文獻：Mysqld DTrace Probe Reference /doc/refman/5.7/en/dba-dtrace-mysqld-ref.html用 Systemtap 探 索 MySQL /docs/archives/168場景1：基準測試Mysql5.7測試： 總規(guī)模1億行 數(shù)據(jù)量大小15G 單表只讀 4Gbuffer其他默認場景1：基準測試原生mysql測試，總數(shù)據(jù)量1億， 數(shù)據(jù)量大小15G, 4G

12、buffer，其他默認場景1：基準測試原生mysql測試，總數(shù)據(jù)量1億， 數(shù)據(jù)量大小15G, 8Gbuffer場景1：基準測試Mysql5.7測試： 總規(guī)模1億行 數(shù)據(jù)量大小15G 單表只讀 4G/8G buffer場景2：中間件性能損失測試單實例5.7VS中間件+單實例5.7場景2：中間件性能損失測試MySQLProxy場景2：中間件性能損失測試Mysql_no_sslProxy場景2：中間件性能損失測試單實例5.7VS中間件+單實例5.74.0為神馬參數(shù)也不一致MySQL配置參數(shù)和生效參數(shù)不一致背景客戶端報錯：Cant open file: .testmytable.frm (errno:

13、 24) shell perror 24OS error code 24: Too many open filesmysql的配置文件是這么寫的 open_files_limit =4000max_connections = 500table_open_cache = 1000系統(tǒng) ulimit -n是這樣子設的 1500mysql動態(tài)生效的值是這樣子的 open_files_limit = 1500max_connections = 500table_open_cache = 495MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式根據(jù)配置(三個參數(shù)的配置值或默認值)計算 Reque

14、st_open_files(需要的文件描述符)獲取有效的系統(tǒng)的限制值 effective_open_files根據(jù)effective_open_files調(diào)整request_open_files根據(jù)調(diào)整后的request_open_files，計算實際生效的參數(shù)值(showvariables)MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式1.根據(jù)配置(三個參數(shù)的配置值或默認值)計算 request_open_files(需要的文件描述符)request_open_files=maxlimit_1= max_connections + table_cache_size * 2 + 1

15、0; limit_2= max_connections * 5;limit_3= open_files_limit ? open_files_limit : 5000;/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 15002510250050005000！MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式2. 獲取有效的系統(tǒng)的限制值effective_open_fileseffective_open_files:/mysqlmax_connections = 500t

16、able_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000?NO1500MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式3.根據(jù)effective_open_files 調(diào)整request_open_files/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000requested_open_files=min(effective_open_files，request_ope

17、n_files)結論1:open_files_limit=1500MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式4. 根據(jù)調(diào)整后的request_open_files，計算實際生效的參數(shù)值(show variables 可查看參數(shù)值)max_connections/mysql max_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 1500Min（limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2 ，max_connections）1500-10-800=690500table_cache_sizeMin（max(TABLE_OPEN_CACHE_MIN,(requested_open_files 10 - max_connections)/2)，table_cache_size400（1500-10-500)/2=495結論： max_connections =500 table_open_cache=4951000默認值為400MySQL配置參數(shù)和生效參數(shù)不一致mysql的配置文件是這么寫的 open_f