{"id":1486,"date":"2020-06-05T09:02:06","date_gmt":"2020-06-05T01:02:06","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1486"},"modified":"2020-06-05T09:02:06","modified_gmt":"2020-06-05T01:02:06","slug":"mysql_monito-%e5%bc%80%e6%ba%90%e7%9a%84mysql%e7%9b%91%e6%8e%a7%e5%b7%a5%e5%85%b7","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1486","title":{"rendered":"MySQL_Monito \u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7\u5de5\u5177"},"content":{"rendered":"<h1>MySQL_Monito \u7b80\u4ecb<\/h1>\n<p>\u5c31\u76ee\u524d\u5e38\u7528\u7684\u5f00\u6e90\u76d1\u63a7\u5de5\u5177\u6709nagios\u3001zabbix\u3001grafana\uff0c\u4f46\u8fd9\u4e9b\u662f\u9762\u5411\u4e13\u4e1aDBA\u4f7f\u7528\u7684\uff0c\u800c\u5bf9\u4e8e\u6d4b\u8bd5\u4eba\u5458\u6765\u8bf4\uff0c\u6ca1\u6709\u4e13\u4e1a\u7684MySQL\u7406\u8bba\u77e5\u8bc6\uff0c\u5e76\u4e14\u4e0a\u8ff0\u76d1\u63a7\u5de5\u5177\u5747\u4e3a\u7eaf\u82f1\u6587\u754c\u9762\uff0c\u4ea4\u4e92\u4e0d\u76f4\u89c2\uff0c\u90a3\u4e48\u591a\u7684\u76d1\u63a7\u6307\u6807\uff0c\u4f60\u77e5\u9053\u6709\u54ea\u4e9b\u6570\u636e\u9700\u8981\u76d1\u63a7\u90a3\u4e9b\u6570\u636e\u51fa\u73b0\u5f02\u5e38\uff1f<\/p>\n<ul>\n<li>MySQL_Monito \u5f00\u6e90\u7684mysql\u76d1\u63a7\u5de5\u5177\u5177\u6709\u4e00\u773c\u5c31\u80fd\u770b\u61c2\u7684\u6307\u6807\u9879\uff0c\u7eaf\u4e2d\u6587\u9875\u9762\uff0c\u6e05\u723d\u76f4\u89c2\uff0c\u7b80\u7ea6\u800c\u4e0d\u7b80\u5355\uff0c\u51fa\u4e86\u95ee\u9898\u62a5\u8b66\u4fe1\u606f\u76f4\u63a5\u7b2c\u4e00\u65f6\u95f4\u63a8\u9001\u7ed9\u76d1\u63a7\u8005\uff0c\u6548\u7387\u4f1a\u5927\u5927\u63d0\u5347\u3002\n<ul>\n<li>MySQL\u72b6\u6001\u76d1\u63a7\n<div class=\\\"image-package\\\">\n<div class=\\\"image-container\\\">\n<div class=\\\"image-view\\\" data-width=\\\"1080\\\" data-height=\\\"334\\\"><img class=\\\"\\\" src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-6ca3d933b9d44aab?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/1080\/format\/webp\\\" data-original-src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-6ca3d933b9d44aab\\\" data-original-width=\\\"1080\\\" data-original-height=\\\"334\\\" data-original-format=\\\"image\/jpeg\\\" data-original-filesize=\\\"29012\\\" data-image-index=\\\"1\\\" \/><\/div>\n<\/div>\n<div class=\\\"image-caption\\\" style=\\\"text-align: center;\\\">dba+\u5f00\u6e90\u5de5\u5177\uff1a\u9762\u5411\u5f00\u53d1\u7684MySQL\u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7<\/div>\n<\/div>\n<\/li>\n<li>\u70b9\u51fb\u6d3b\u52a8\u8fde\u63a5\u6570\uff0c\u53ef\u4ee5\u67e5\u770b\u5177\u4f53\u7684\u8fde\u63a5\u6570\u7edf\u8ba1\u4fe1\u606f\n<div class=\\\"image-package\\\">\n<div class=\\\"image-container\\\">\n<div class=\\\"image-view\\\" data-width=\\\"640\\\" data-height=\\\"305\\\"><img class=\\\"\\\" src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-117c76b8f29d91f9?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/640\/format\/webp\\\" data-original-src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-117c76b8f29d91f9\\\" data-original-width=\\\"640\\\" data-original-height=\\\"305\\\" data-original-format=\\\"image\/jpeg\\\" data-original-filesize=\\\"14733\\\" data-image-index=\\\"2\\\" \/><\/div>\n<\/div>\n<div class=\\\"image-caption\\\" style=\\\"text-align: center;\\\">dba+\u5f00\u6e90\u5de5\u5177\uff1a\u9762\u5411\u5f00\u53d1\u7684MySQL\u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7<\/div>\n<\/div>\n<\/li>\n<li>\u70b9\u51fb\u56fe\u8868\uff0c\u53ef\u4ee5\u67e5\u770b\u5386\u53f2\u66f2\u7ebf\u56fe\n<div class=\\\"image-package\\\">\n<div class=\\\"image-container\\\">\n<div class=\\\"image-view\\\" data-width=\\\"640\\\" data-height=\\\"306\\\"><img class=\\\"\\\" src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-6d31709824cc6ac7?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/640\/format\/webp\\\" data-original-src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-6d31709824cc6ac7\\\" data-original-width=\\\"640\\\" data-original-height=\\\"306\\\" data-original-format=\\\"image\/jpeg\\\" data-original-filesize=\\\"16169\\\" data-image-index=\\\"3\\\" \/><\/div>\n<\/div>\n<div class=\\\"image-caption\\\">dba+\u5f00\u6e90\u5de5\u5177\uff1a\u9762\u5411\u5f00\u53d1\u7684MySQL\u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7<\/div>\n<\/div>\n<\/li>\n<li>\u4e3b\u4ece\u72b6\u6001\u76d1\u63a7\n<div class=\\\"image-package\\\">\n<div class=\\\"image-container\\\">\n<div class=\\\"image-container-fill\\\"><\/div>\n<div class=\\\"image-view\\\" data-width=\\\"632\\\" data-height=\\\"237\\\"><img class=\\\"\\\" src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-41386ce27ee1ec65?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/632\/format\/webp\\\" data-original-src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-41386ce27ee1ec65\\\" data-original-width=\\\"632\\\" data-original-height=\\\"237\\\" data-original-format=\\\"image\/jpeg\\\" data-original-filesize=\\\"15692\\\" data-image-index=\\\"4\\\" \/><\/div>\n<\/div>\n<div class=\\\"image-caption\\\" style=\\\"text-align: center;\\\">dba+\u5f00\u6e90\u5de5\u5177\uff1a\u9762\u5411\u5f00\u53d1\u7684MySQL\u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7<\/div>\n<\/div>\n<\/li>\n<li>\u5fae\u4fe1\u62a5\u8b66\n<div class=\\\"image-package\\\">\n<div class=\\\"image-container\\\">\n<div class=\\\"image-container-fill\\\"><\/div>\n<div class=\\\"image-view\\\" data-width=\\\"550\\\" data-height=\\\"640\\\"><img class=\\\"\\\" src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-c8013d123060822e?imageMogr2\/auto-orient\/strip|imageView2\/2\/w\/550\/format\/webp\\\" data-original-src=\\\"\/\/upload-images.jianshu.io\/upload_images\/16753854-c8013d123060822e\\\" data-original-width=\\\"550\\\" data-original-height=\\\"640\\\" data-original-format=\\\"image\/jpeg\\\" data-original-filesize=\\\"40809\\\" data-image-index=\\\"5\\\" \/><\/div>\n<\/div>\n<div class=\\\"image-caption\\\" style=\\\"text-align: center;\\\">dba+\u5f00\u6e90\u5de5\u5177\uff1a\u9762\u5411\u5f00\u53d1\u7684MySQL\u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7<\/div>\n<\/div>\n<div><\/div>\n<\/li>\n<\/ul>\n<\/li>\n<li>\u9879\u76ee\u5730\u5740\uff1ahttps:\/\/github.com\/hcymysql\/mysql_monitor<\/li>\n<li>\u73af\u5883\u642d\u5efa\u8bf4\u660e<\/li>\n<\/ul>\n<div>\n<pre># yum install httpd mysql php-mysqlnd -y\n# yum install python-simplejson -y\n# service httpd start\n<\/pre>\n<\/div>\n<ul>\n<li>https:\/\/github.com\/hcymysql\/mysql_monitor\/archive\/master.zip\u5b89\u88c5\u5305\u89e3\u538b\u7f29\u5230\/var\/www\/html\/\u76ee\u5f55\u4e0b\u3002<\/li>\n<\/ul>\n<div>\n<pre>wget https:\/\/github.com\/hcymysql\/mysql_monitor\/archive\/master.zip\ncd \/var\/www\/html\/mysql_monitor\/\nchmod 755 .\/mail\/sendEmail\nchmod 755 .\/weixin\/wechat.py\n<\/pre>\n<\/div>\n<blockquote><p>\u6ce8\uff1a\u90ae\u4ef6\u548c\u5fae\u4fe1\u62a5\u8b66\u8c03\u7528\u7684\u7b2c\u4e09\u65b9\u5de5\u5177\uff0c\u6240\u4ee5\u8fd9\u91cc\u8981\u8d4b\u4e88\u53ef\u6267\u884c\u6743\u9650755<\/p><\/blockquote>\n<ul>\n<li>MySQL Monitor\u76d1\u63a7\u5de5\u5177\u642d\u5efa<\/li>\n<\/ul>\n<p>\u5bfc\u5165MySQL Monitor\u76d1\u63a7\u5de5\u5177\u8868\u7ed3\u6784\uff08sql_db\u5e93\uff09<\/p>\n<div>\n<pre># cd \/var\/www\/html\/mysql_monitor\/\n# mysql -uroot -p123456 &lt; mysql_monitor_schema.sql\n<\/pre>\n<\/div>\n<ul>\n<li>\u5f55\u5165\u88ab\u76d1\u63a7\u4e3b\u673a\u7684\u4fe1\u606f<\/li>\n<\/ul>\n<div>\n<pre>mysql&gt;insert into\n`mysql_status_info`(`id`,`ip`,`dbname`,`user`,`pwd`,`port`,`monitor`,`send_mail`,`send_mail_to_list`,`send_weixin`,`send_weixin_to_list`,`alarm_threads_running`,`threshold_alarm_threads_running`,`alarm_repl_status`,`threshold_warning_repl_delay`)\nvalues\n(1,\\'127.0.0.1\\',\\'sql_db\\',\\'admin\\',\\'hechunyang\\',3306,1,1,\\'chunyang_he@139.com,chunyang_he@126.com\\',1,\\'hechunyang\\',NULL,NULL,NULL,NULL);\n<\/pre>\n<\/div>\n<blockquote><p>\u6ce8\uff1a\u4ee5\u4e0b\u5b57\u6bb5\u53ef\u4ee5\u6309\u7167\u9700\u6c42\u53d8\u66f4\uff1a<br \/>\nip\u5b57\u6bb5\u542b\u4e49\uff1a\u8f93\u5165\u88ab\u76d1\u63a7MySQL\u7684IP\u5730\u5740<br \/>\ndbname\u5b57\u6bb5\u542b\u4e49\uff1a\u8f93\u5165\u88ab\u76d1\u63a7MySQL\u7684\u6570\u636e\u5e93\u540d<br \/>\nuser\u5b57\u6bb5\u542b\u4e49\uff1a\u8f93\u5165\u88ab\u76d1\u63a7MySQL\u7684\u7528\u6237\u540d\uff08\u6700\u597d\u7ed9ALL\u7ba1\u7406\u5458\u6743\u9650\uff09<br \/>\npwd\u5b57\u6bb5\u542b\u4e49\uff1a\u8f93\u5165\u88ab\u76d1\u63a7MySQL\u7684\u5bc6\u7801<br \/>\nport\u5b57\u6bb5\u542b\u4e49\uff1a\u8f93\u5165\u88ab\u76d1\u63a7MySQL\u7684\u7aef\u53e3\u53f7<br \/>\nmonitor\u5b57\u6bb5\u542b\u4e49\uff1a0\u4e3a\u5173\u95ed\u76d1\u63a7\uff08\u4e5f\u4e0d\u91c7\u96c6\u6570\u636e\uff0c\u76f4\u63a5\u8df3\u8fc7\uff09;1\u4e3a\u5f00\u542f\u76d1\u63a7\uff08\u91c7\u96c6\u6570\u636e\uff09<br \/>\nsend_mail\u5b57\u6bb5\u542b\u4e49\uff1a0\u4e3a\u5173\u95ed\u90ae\u4ef6\u62a5\u8b66;1\u4e3a\u5f00\u542f\u90ae\u4ef6\u62a5\u8b66<br \/>\nsend_mail_to_list\u5b57\u6bb5\u542b\u4e49\uff1a\u90ae\u4ef6\u4eba\u5217\u8868<br \/>\nsend_weixin\u5b57\u6bb5\u542b\u4e49\uff1a0\u4e3a\u5173\u95ed\u5fae\u4fe1\u62a5\u8b66;1\u4e3a\u5f00\u542f\u5fae\u4fe1\u62a5\u8b66<br \/>\nsend_weixin_to_list\u5b57\u6bb5\u542b\u4e49\uff1a\u5fae\u4fe1\u516c\u4f17\u53f7<br \/>\nthreshold_alarm_threads_running\u5b57\u6bb5\u542b\u4e49\uff1a\u8bbe\u7f6e\u8fde\u63a5\u6570\u9600\u503c\uff08\u5355\u4f4d\u4e2a\uff09<br \/>\nthreshold_warning_repl_delay\u5b57\u6bb5\u542b\u4e49\uff1a\u8bbe\u7f6e\u4e3b\u4ece\u590d\u5236\u5ef6\u8fdf\u9600\u503c\uff08\u5355\u4f4d\u79d2\uff09<\/p><\/blockquote>\n<ul>\n<li>\u4fee\u6539conn.php\u914d\u7f6e\u6587\u4ef6<\/li>\n<\/ul>\n<div>\n<pre># vim \/var\/www\/html\/mysql_monitor\/conn.php\n#\u8fdb\u5165\u914d\u7f6e\u6587\u4ef6\u4fee\u6539\u914d\u7f6e\n$con = mysqli_connect(\\\"127.0.0.1\\\",\\\"admin\\\",\\\"hechunyang\\\",\\\"sql_db\\\",\\\"3306\\\") or die(\\\"\u6570\u636e\u5e93\u94fe\u63a5\u9519\u8bef\\\".mysql_error());\n\u6539\u6210\u4f60\u7684MySQL Monitor\u76d1\u63a7\u5de5\u5177\u8868\u7ed3\u6784\uff08sql_db\u5e93\uff09\u8fde\u63a5\u4fe1\u606f\u3002\n<\/pre>\n<\/div>\n<ul>\n<li>\u4fee\u6539\u90ae\u4ef6\u62a5\u8b66\u4fe1\u606f<\/li>\n<\/ul>\n<div>\n<pre># cd \/var\/www\/html\/mysql_monitor\/mail\/\n# vim mail.php\nsystem(\\\".\/mail\/sendEmail -f chunyang_he@139.com -t \\'{$this-&gt;send_mail_to_list}\\' -ssmtp.139.com:25 -u \\'{$this-&gt;alarm_subject}\\' -o message-charset=utf8 -o message-content-type=html -m \\'\u62a5\u8b66\u4fe1\u606f\uff1a&lt;br&gt;&lt;font\ncolor=\\'#FF0000\\'&gt;{$this-&gt;alarm_info}&lt;\/font&gt;\\' -xu chunyang_he@139.com -xp\\'123456\\' -o tls=no\\\");\n\u6539\u6210\u4f60\u7684\u53d1\u4ef6\u4eba\u5730\u5740\u3001\u8d26\u53f7\u5bc6\u7801\uff0c\u91cc\u9762\u7684\u53d8\u91cf\u4e0d\u7528\u4fee\u6539\u3002\n<\/pre>\n<\/div>\n<ul>\n<li>\u4fee\u6539\u5fae\u4fe1\u62a5\u8b66\u4fe1\u606f<\/li>\n<\/ul>\n<div>\n<pre># cd \/var\/www\/html\/mysql_monitor\/weixin\/\n# vim wechat.py\n<\/pre>\n<\/div>\n<ul>\n<li>\u5b9a\u65f6\u4efb\u52a1\u6bcf\u5206\u949f\u6293\u53d6\u4e00\u6b21<\/li>\n<\/ul>\n<div>\n<pre># crontab -l\n*\/1 * * * * cd \/var\/www\/html\/mysql_monitor\/; \/usr\/bin\/php\/var\/www\/html\/mysql_monitor\/check_mysql_repl.php &gt; \/dev\/null 2 &gt;&amp;1\n*\/1 * * * * cd \/var\/www\/html\/mysql_monitor\/; \/usr\/bin\/php\/var\/www\/html\/mysql_monitor\/check_mysql_status.php &gt; \/dev\/null 2 &gt;&amp;1\ncheck_mysql_status.php\uff08\u7528\u6765\u91c7\u96c6\u88ab\u76d1\u63a7\u7aefMySQL\u72b6\u6001\u4fe1\u606f\u548c\u89e6\u53d1\u62a5\u8b66\uff09\ncheck_mysql_repl.php\uff08\u7528\u6765\u91c7\u96c6\u88ab\u76d1\u63a7\u7aefMySQL\u4e3b\u4ece\u590d\u5236\u4fe1\u606f\u548c\u89e6\u53d1\u62a5\u8b66\uff09\n<\/pre>\n<\/div>\n<ul>\n<li>\u66f4\u6539\u9875\u9762\u81ea\u52a8\u5237\u65b0\u9891\u7387<\/li>\n<\/ul>\n<div>\n<pre>vim mysql_status_monitor.php\nvim mysql_repl_monitor.php\n&lt;meta http-equiv=\\\"refresh\\\" content=\\\"600\\\" \/&gt; &lt;!-- \u9875\u9762\u5237\u65b0\u65f6\u95f4600\u79d2 --&gt;\n\u9ed8\u8ba4\u9875\u9762\u6bcf600\u79d2\u81ea\u52a8\u5237\u65b0\u4e00\u6b21\u3002\n<\/pre>\n<\/div>\n<ul>\n<li>UI\u9875\u9762\u8bbf\u95ee\u8def\u5f84\uff1a<br \/>\nhttp:\/\/yourIP\/mysql_monitor\/mysql_status_monitor.php<br \/>\nhttp:\/\/yourIP\/mysql_monitor\/mysql_repl_monitor.php<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MySQL_Monito \u7b80\u4ecb \u5c31\u76ee\u524d\u5e38\u7528\u7684\u5f00\u6e90\u76d1\u63a7\u5de5\u5177\u6709nagios\u3001zabbix\u3001grafana\uff0c\u4f46\u8fd9\u4e9b\u662f &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=1486\"> <span class=\"screen-reader-text\">MySQL_Monito \u56fe\u5f62\u53ef\u89c6\u5316\u76d1\u63a7\u5de5\u5177<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,122],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"MySQL_Monito \u7b80\u4ecb \u5c31\u76ee\u524d\u5e38\u7528\u7684\u5f00\u6e90\u76d1\u63a7\u5de5\u5177\u6709nagios\u3001zabbix\u3001grafana\uff0c\u4f46\u8fd9\u4e9b\u662f&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1486"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1486"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1486\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1486"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}