.jpg)
mysql越來越被更多企業(yè)接受，隨著企業(yè)發(fā)展，mysql存儲數(shù)據(jù)日益膨脹，mysql的性能分析、監(jiān)控預(yù)警、容量擴(kuò)展議題越來越多。本篇文章我們就為大家做了一個關(guān)于幾個mysql監(jiān)控腳本命令的總結(jié)。
[java]?
#/bin/sh?
?
#檢測mysql?server是否正常提供服務(wù)?
mysqladmin?-u?sky?-ppwd?-h?localhost?ping?
?
#獲取mysql當(dāng)前的幾個狀態(tài)值?
mysqladmin?-u?sky?-ppwd?-h?localhost?status?
?
#獲取數(shù)據(jù)庫當(dāng)前的連接信息?
mysqladmin?-u?sky?-ppwd?-h?localhost?processlist?
?
?
#mysql?dump數(shù)據(jù)導(dǎo)出?
mysqldump?-uroot?-T/tmp/mysqldump?test?test_outfile?–fields-enclosed-by=”?–fields-terminated-by=,?
?
#mysql數(shù)據(jù)導(dǎo)入?
mysqlimport?–user=name?–password=pwd?test?–fields-enclosed-by=”?–fields-terminated-by=,?/tmp/test_outfile.txt?
LOAD?DATA?INFILE?‘/tmp/test_outfile.txt’?INTO?TABLE?test_outfile?FIELDS?TERMINATED?BY?‘”‘?ENCLOSED?BY?‘,’;?
?
#mysql進(jìn)程監(jiān)控?
ps?-ef?|?grep?“mysqld_safe”?|?grep?-v?“grep”?
ps?-ef?|?grep?“mysqld”?|?grep?-v?“mysqld_safe”|?grep?-v?“grep”?
?
?
#查看當(dāng)前數(shù)據(jù)庫的狀態(tài)?
mysql?-u?root?-p123456?-e?‘show?status’?
?
?
#mysqlcheck?工具程序可以檢查(check),修?復(fù)(?repair),分?析(?analyze)和優(yōu)化(optimize)MySQL?Server?中的表?
mysqlcheck?-u?root?-p123456?–all-databases?
?
#mysql?qps查詢?QPS?=?Questions(or?Queries)?/?Seconds?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Questions”‘?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Queries”‘?
?
#mysql?Key?Buffer?命中率?key_buffer_read_hits?=?(1?–?Key_reads?/?Key_read_requests)?*?100%?key_buffer_write_hits=?(1?–?Key_writes?/?Key_write_requests)?*?100%?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Key%”‘?
?
#mysql?Innodb?Buffer?命中率?innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)?*?100%?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Innodb_buffer_pool_read%”‘?
?
#mysql?Query?Cache?命中率?Query_cache_hits=?(Qcache_hits?/?(Qcache_hits?+?Qcache_inserts))?*?100%?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Qcache%”‘?
?
#mysql?Table?Cache?狀態(tài)量?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Open%”‘?
?
#mysql?Thread?Cache?命中率?Thread_cache_hits?=?(1?–?Threads_created?/?Connections)?*?100%?正常來說,Thread?Cache?命中率要在?90%?以上才算比較合理。?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Thread%”‘?
?
#mysql?鎖定狀態(tài):鎖定狀態(tài)包括表鎖和行鎖兩種,我們可以通過系統(tǒng)狀態(tài)變量獲得鎖定總次數(shù),鎖定造成其他線程等待的次數(shù),以及鎖定等待時(shí)間信息?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“%lock%”‘?
?
#mysql?復(fù)制延時(shí)量?在slave節(jié)點(diǎn)執(zhí)行?
mysql?-u?root?-p123456?-e?‘SHOW?SLAVE?STATUS’?
?
#mysql?Tmp?table?狀況?Tmp?Table?的狀況主要是用于監(jiān)控?MySQL?使用臨時(shí)表的量是否過多,是否有臨時(shí)表過大而不得不從內(nèi)存中換出到磁盤文件上?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Created_tmp%”‘?
?
#mysql?Binlog?Cache?使用狀況:Binlog?Cache?用于存放還未寫入磁盤的?Binlog?信?息?。?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Binlog_cache%”‘?
?
#mysql?nnodb_log_waits?量:Innodb_log_waits?狀態(tài)變量直接反應(yīng)出?Innodb?Log?Buffer?空間不足造成等待的次數(shù)?
mysql?-u?root?-p123456?-e?‘SHOW?/*!50000?GLOBAL?*/?STATUS?LIKE?“Innodb_log_waits’?
以上內(nèi)容就是幾個mysql監(jiān)控腳本命令的總結(jié)，希望對大家有幫助。
相關(guān)推薦：
mysql實(shí)現(xiàn)自動監(jiān)控同步的腳本
.png)
