当前位置：Gxlcms > mysql > mysql常用监控脚本整理_MySQL

mysql常用监控脚本整理_MySQL

时间：2021-07-01 10:21:17 帮助过：16人阅读

bitsCN.com

mysql常用监控脚本整理

  1. 01#!/bin/sh02#filename：mysql_monitor.sh03#site：www.jbxue.com04#检测mysql server是否正常提供服务05mysqladmin -u sky -ppwd -h localhost ping0607#获取mysql当前的几个状态值08mysqladmin -u sky -ppwd -h localhost status0910#获取数据库当前的连接信息11mysqladmin -u sky -ppwd -h localhost processlist1213#获取当前数据库的连接数14mysql -u root -p123456 -BNe "select host,count(host) from processlist group by host;" information_schema1516#显示mysql的uptime17mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'1819#查看数据库的大小20mysql -u root -p123456-e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'2122#查看某个表的列信息23mysql -u <user> --password=<password> -e "SHOW COLUMNS FROM " <database> | awk '{print $1}' | tr "/n" "," | sed 's/,$//g'2425#执行mysql脚本26mysql -u user-name -p password < script.sql2728#mysql dump数据导出29mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields-enclosed-by=/" --fields-terminated-by=,3031#mysql数据导入32mysqlimport --user=name --password=pwd test --fields-enclosed-by=/" --fields-terminated-by=, /tmp/test_outfile.txt33LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';343536#mysql进程监控37ps -ef | grep "mysqld_safe" | grep -v "grep"38ps -ef | grep "mysqld" | grep -v "mysqld_safe"| grep -v "grep"3940#查看当前数据库的状态41mysql -u root -p123456 -e 'show status'4243#mysqlcheck 工具程序可以检查(check),修 复( repair),分 析( analyze)和优化(optimize)MySQL Server 中的表44mysqlcheck -u root -p123456 --all-databases4546#mysql qps查询  QPS = Questions(or Queries) / Seconds47mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Questions"'48mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Queries"'49#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%50mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Key%"'5152#mysql Innodb Buffer 命中率  innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%53mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_buffer_pool_read%"'5455#mysql Query Cache 命中率 Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%56mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Qcache%"'5758#mysql Table Cache 状态量59mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Open%"'6061#mysql Thread Cache 命中率  Thread_cache_hits = (1 - Threads_created / Connections) * 100%  正常来说,Thread Cache 命中率要在 90% 以上才算比较合理。62mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Thread%"'63# www.jbxue.com6465#mysql 锁定状态:锁定状态包括表锁和行锁两种,我们可以通过系统状态变量获得锁定总次数,锁定造成其他线程等待的次数,以及锁定等待时间信息66mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "%lock%"'6768#mysql 复制延时量 在slave节点执行69mysql -u root -p123456 -e 'SHOW SLAVE STATUS'7071#mysql Tmp table 状况 Tmp Table 的状况主要是用于监控 MySQL 使用临时表的量是否过多,是否有临时表过大而不得不从内存中换出到磁盘文件上72mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Created_tmp%"'7374#mysql Binlog Cache 使用状况:Binlog Cache 用于存放还未写入磁盘的 Binlog 信 息 。75mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Binlog_cache%"'7677#mysql nnodb_log_waits 量:Innodb_log_waits 状态变量直接反应出 Innodb Log Buffer 空间不足造成等待的次数78mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_log_waits'<p> </p><p></p><p> </p>
  2. bitsCN.com                    
  3.                   
  4.  
  5.                     <div class="">
  6.                         <ul class="m-news-opt fix">
  7.                             <li class="opt-item">
  8.                                 <a href="/mysql-299678.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">mysql查找结果中自动加序号列_MySQL</p></a>
  9.                             </li>
  10.                             <li class="opt-item ta-r">
  11.                                  <a href="/mysql-299680.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">mysql无法启动恢复解决_MySQL</p></a>
  12.                             </li>
  13.                         </ul>
  14.                     </div>
  15.                     
  16.                     
  17.                     
  18.                     
  19.                 
  20.               
  21.                     
  22.                 
  23.                   
  24.                     <div class="g-title fix">
  25.                         <h2 class="title-txt">人气教程排行</h2>
  26.                     </div>
  27.                     <div class="m-rank u-dashed mb40">
  28.                         <ul>
  29.  <li class="rank-item">
  30.                                 <a href="/mysql-462732.html" title="对数据库模式进行规范化处理，是在数据库设计的什么阶段？" class="item-name ellipsis" target="_blank">
  31.                                     <span class="g-art-count fr">355次</span>
  32.                                     <span class="g-sort-num top">1</span>
  33.                                    对数据库模式进行规范化处理，是在数据库设计的什么阶段？                                </a>
  34.                             </li>
  35.   
  36.   
  37.   <li class="rank-item">
  38.                                 <a href="/mysql-380346.html" title="mysql如何删除多个表格数据库数据" class="item-name ellipsis" target="_blank">
  39.                                     <span class="g-art-count fr">355次</span>
  40.                                     <span class="g-sort-num second">2</span>
  41.                                    mysql如何删除多个表格数据库数据                                </a>
  42.                             </li>
  43.   
  44.   
  45.   <li class="rank-item">
  46.                                 <a href="/mysql-345368.html" title="Oracle购买价格和服务费计算方式" class="item-name ellipsis" target="_blank">
  47.                                     <span class="g-art-count fr">355次</span>
  48.                                     <span class="g-sort-num third">3</span>
  49.                                    Oracle购买价格和服务费计算方式                                </a>
  50.                             </li>
  51.   
  52.   
  53. <li class="rank-item">
  54.                                 <a href="/mysql-339781.html" title="MYSQL查看和新增表分区" class="item-name ellipsis" target="_blank">
  55.                                     <span class="g-art-count fr">355次</span>
  56.                                     <span class="g-sort-num ">4</span>
  57.                                    MYSQL查看和新增表分区                                </a>
  58.                             </li>
  59.   
  60.   
  61. <li class="rank-item">
  62.                                 <a href="/mysql-345027.html" title="TRACE32调试技巧" class="item-name ellipsis" target="_blank">
  63.                                     <span class="g-art-count fr">354次</span>
  64.                                     <span class="g-sort-num ">5</span>
  65.                                    TRACE32调试技巧                                </a>
  66.                             </li>
  67.   
  68.   
  69. <li class="rank-item">
  70.                                 <a href="/mysql-320236.html" title="Oracle数据库教程：ORA-01031:权限不足" class="item-name ellipsis" target="_blank">
  71.                                     <span class="g-art-count fr">353次</span>
  72.                                     <span class="g-sort-num ">6</span>
  73.                                    Oracle数据库教程：ORA-01031:权限不足                                </a>
  74.                             </li>
  75.   
  76.   
  77. <li class="rank-item">
  78.                                 <a href="/mysql-319270.html" title="Oracle物化视图失效的几种情况及测试" class="item-name ellipsis" target="_blank">
  79.                                     <span class="g-art-count fr">353次</span>
  80.                                     <span class="g-sort-num ">7</span>
  81.                                    Oracle物化视图失效的几种情况及测试                                </a>
  82.                             </li>
  83.   
  84.   
  85. <li class="rank-item">
  86.                                 <a href="/mysql-306127.html" title="mysql-sql语句查询多个字段不等于零怎么写？" class="item-name ellipsis" target="_blank">
  87.                                     <span class="g-art-count fr">352次</span>
  88.                                     <span class="g-sort-num ">8</span>
  89.                                    mysql-sql语句查询多个字段不等于零怎么写？                                </a>
  90.                             </li>
  91.   
  92.   
  93. <li class="rank-item">
  94.                                 <a href="/mysql-306897.html" title="如何提高mysql大批量数据更新（update）的效率？" class="item-name ellipsis" target="_blank">
  95.                                     <span class="g-art-count fr">351次</span>
  96.                                     <span class="g-sort-num ">9</span>
  97.                                    如何提高mysql大批量数据更新（update）的效率？                                </a>
  98.                             </li>
  99.   
  100.   
  101. <li class="rank-item">
  102.                                 <a href="/mysql-464754.html" title="mysql如何从ibd文件恢复数据" class="item-name ellipsis" target="_blank">
  103.                                     <span class="g-art-count fr">350次</span>
  104.                                     <span class="g-sort-num ">10</span>
  105.                                    mysql如何从ibd文件恢复数据                                </a>
  106.                             </li>
  107.   
  108.   
  109. <li class="rank-item">
  110.                                 <a href="/mysql-345209.html" title="ORACLEROLLUP和CUBE函数" class="item-name ellipsis" target="_blank">
  111.                                     <span class="g-art-count fr">350次</span>
  112.                                     <span class="g-sort-num ">11</span>
  113.                                    ORACLEROLLUP和CUBE函数                                </a>
  114.                             </li>
  115.   
  116.   
  117. <li class="rank-item">
  118.                                 <a href="/mysql-344534.html" title="IBMDB2赋权[SQL0551N]" class="item-name ellipsis" target="_blank">
  119.                                     <span class="g-art-count fr">350次</span>
  120.                                     <span class="g-sort-num ">12</span>
  121.                                    IBMDB2赋权[SQL0551N]                                </a>
  122.                             </li>
  123.   
  124.   
  125. <li class="rank-item">
  126.                                 <a href="/mysql-288264.html" title="mysql不等于符号写法" class="item-name ellipsis" target="_blank">
  127.                                     <span class="g-art-count fr">350次</span>
  128.                                     <span class="g-sort-num ">13</span>
  129.                                    mysql不等于符号写法                                </a>
  130.                             </li>
  131.   
  132.   
  133. <li class="rank-item">
  134.                                 <a href="/mysql-303705.html" title="redhat8mysql安装具体过程_MySQL" class="item-name ellipsis" target="_blank">
  135.                                     <span class="g-art-count fr">349次</span>
  136.                                     <span class="g-sort-num ">14</span>
  137.                                    redhat8mysql安装具体过程_MySQL                                </a>
  138.                             </li>
  139.   
  140.   
  141. <li class="rank-item">
  142.                                 <a href="/mysql-290023.html" title="SQLServer出现Error:1326错误（管理器无法连接远程数据库）问题解决方案" class="item-name ellipsis" target="_blank">
  143.                                     <span class="g-art-count fr">349次</span>
  144.                                     <span class="g-sort-num ">15</span>
  145.                                    SQLServer出现Error:1326错误（管理器无法连接远程数据库）问题解决方案                                </a>
  146.                             </li>
  147.   
  148.   
  149. <li class="rank-item">
  150.                                 <a href="/mysql-367580.html" title="centos下配置mysql数据库自动备份" class="item-name ellipsis" target="_blank">
  151.                                     <span class="g-art-count fr">348次</span>
  152.                                     <span class="g-sort-num ">16</span>
  153.                                    centos下配置mysql数据库自动备份                                </a>
  154.                             </li>
  155.   
  156.   
  157. <li class="rank-item">
  158.                                 <a href="/mysql-289513.html" title="MySQL中使用SQL语句对字段进行重命名" class="item-name ellipsis" target="_blank">
  159.                                     <span class="g-art-count fr">348次</span>
  160.                                     <span class="g-sort-num ">17</span>
  161.                                    MySQL中使用SQL语句对字段进行重命名                                </a>
  162.                             </li>
  163.   
  164.   
  165. <li class="rank-item">
  166.                                 <a href="/mysql-337773.html" title="数据库中的表以行和列来组织数据，每一行称为每一列称为" class="item-name ellipsis" target="_blank">
  167.                                     <span class="g-art-count fr">347次</span>
  168.                                     <span class="g-sort-num ">18</span>
  169.                                    数据库中的表以行和列来组织数据，每一行称为每一列称为                                </a>
  170.                             </li>
  171.   
  172.   
  173. <li class="rank-item">
  174.                                 <a href="/mysql-306268.html" title="windowsservice-mysql安装出错，远程调用失败" class="item-name ellipsis" target="_blank">
  175.                                     <span class="g-art-count fr">347次</span>
  176.                                     <span class="g-sort-num ">19</span>
  177.                                    windowsservice-mysql安装出错，远程调用失败                                </a>
  178.                             </li>
  179.   
  180.   
  181. <li class="rank-item">
  182.                                 <a href="/mysql-316621.html" title="SQL连接查询语法及使用" class="item-name ellipsis" target="_blank">
  183.                                     <span class="g-art-count fr">346次</span>
  184.                                     <span class="g-sort-num ">20</span>
  185.                                    SQL连接查询语法及使用                                </a>
  186.                             </li>
  187.                         </ul>
  188.                     </div>
  189.                 
  190.             
  191.             <!-- / 教程内容页 -->
  192.         
  193.     
  194.   
  195. <!-- 页尾 -->
  196. <div class="footer">
  197.    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！</div>
  198. <!-- / 页尾 -->
  199.  
  200. <div style="display:none">
  201. <div class="login-box" id="login-dialog">
  202. <div class="login-top"><a class="current" rel="nofollow" id="login1" onclick="setTab('login',1,2);">登录</a></div>
  203. <div class="login-form" id="nav-signin">
  204.  <!-- <div class="login-ico"><a rel="nofollow" class="qq" id="qqlogin" target="_blank" href="/user-center-qqlogin.html"> QQ </a></div>  -->
  205. <div class="login-box-form" id="con_login_1">
  206. <form id="loginform" action="/user-center-login.html" method="post" onsubmit="return false;"></form>
  207. <p class="int-text">
  208. <input class="email" id="username" name="username" type="text" value="用户名或Email" onfocus="if(this.value=='用户名或Email'){this.value='';}" onblur="if(this.value==''){this.value='用户名或Email';};"></p>
  209. <p class="int-text">
  210. <input class="password1" type="password" id="password" name="password" value="******" onblur="if(this.value=='') this.value='******';" onfocus="if(this.value=='******') this.value='';">
  211. </p>
  212. <p class="int-info">
  213.                 <label class="ui-label"> </label>
  214.                 <label for="agreement" class="ui-label-checkbox">
  215.                 <input type="checkbox" value="" name="cookietime" id="cookietime" checked="checked">
  216.                 <input type="hidden" name="notforward" id="notforward" value="1">
  217.                 <input type="hidden" name="dosubmit" id="dosubmit" value="1">记住我的登录 </label>                           
  218.        <a rel="nofollow" class="aright" href="/user-center-forgetpwd.html" target="_blank"> 忘记密码？ </a></p>
  219.   <p class="int-btn"><a rel="nofollow" id="loginbt" class="loginbtn"><span>登录</span></a></p> 
  220.   
  221. </div>
  222. <form id="regform" action="/user-center-reg.html" method="post"></form>
  223. <div class="login-reg" style="display: none;" id="con_login_2">
  224. <input type="hidden" name="t" id="t">
  225.   <p class="int-text">
  226.     <input id="email" name="email" type="text" value="Email" onfocus="if(this.value=='Email'){this.value='';}" onblur="if(this.value==''){this.value='Email';};"></p>
  227.     <p class="int-text">
  228.     <input id="uname" name="username" type="text" value="用户名或昵称" onfocus="if(this.value=='用户名或昵称'){this.value='';}" onblur="if(this.value==''){this.value='用户名或昵称';};"></p>
  229.   <p class="int-text">
  230.   <input type="password" id="pwd" name="password" value="******" onblur="if(this.value=='') this.value='******';" onfocus="if(this.value=='******') this.value='';"> </p>
  231.   <p class="int-text1"><span class="inputbox">
  232.     <input id="validate" name="validate" type="text" value="验证码" onfocus="if(this.value=='验证码'){this.value='';}" onblur="if(this.value==''){this.value='验证码';};">
  233.     </span><span class="yzm-img"><img src="/user-checkcode-index" alt="看不清楚换一张" id="indexlogin"></span></p>
  234.   <p class="int-info">
  235.     <label>
  236.       <input value="" name="agreement" id="agreement" checked="checked" type="checkbox">
  237.       我已阅读<a rel="nofollow" href="/user-center-agreement.html">用户协议</a>及<a rel="nofollow" href="/user-center-agreement.html">版权声明</a></label>
  238.   </p>
  239.   <p class="int-btn"><input type="hidden" name="dosubmit">
  240. <a rel="nofollow" class="loginbtn" id="register"><span>注册</span></a></p>
  241. </div>
  242.  
  243. </div>
  244. </div>
  245. </div>
  246.  
  247. <div data-type="4" data-plugin="aroundbox" data-plugin-aroundbox-x="left" data-plugin-aroundbox-y="bottom" data-plugin-aroundbox-iconsize="60x60" data-plugin-aroundbox-fixed="1" data-plugin-aroundbox-offsetx="10"></div>
  248.    
  249. <script src="https://hm.baidu.com/hm.js?6dc1c3c5281cf70f49bc0bc860ec24f2"></script><script>
  250. var _hmt = _hmt || [];
  251. (function() {
  252.   var hm = document.createElement("script");
  253.   hm.src = "https://hm.baidu.com/hm.js?6dc1c3c5281cf70f49bc0bc860ec24f2";
  254.   var s = document.getElementsByTagName("script")[0]; 
  255.   s.parentNode.insertBefore(hm, s);
  256. })();
  257. </script>
  258.  <script type="text/javascript" src="/layui/layui.js"></script>
  259.     <script>
  260.     layui.use('code', function() {
  261.         layui.code({
  262.             elem: 'pre', //默认值为.layui-code
  263.             about: false,
  264.             skin: 'notepad',
  265.             title: 'php怎么实现数据库验证跳转代码块',
  266.             encode: true //是否转义html标签。默认不开启
  267.         });
  268.     });
  269.     </script>
  270. </database><table></table></password></user>