{"id":2302,"date":"2022-07-04T14:30:31","date_gmt":"2022-07-04T06:30:31","guid":{"rendered":"https:\/\/199604.com\/?p=2302"},"modified":"2022-07-04T14:30:31","modified_gmt":"2022-07-04T06:30:31","slug":"centos7-shell%e7%94%9f%e6%88%90mysql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%b7%a1%e6%a3%80%e6%8a%a5%e5%91%8a","status":"publish","type":"post","link":"https:\/\/199604.com\/2302","title":{"rendered":"Centos7-shell\u751f\u6210MySQL\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a"},"content":{"rendered":"

Centos7-shell\u751f\u6210MySQL\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a<\/strong><\/h1>\n

\u524d\u63d0<\/h2>\n

\u8feb\u4e8e\u7f51\u4e0a\u6709\u4eba\u5356\u6536\u8d39\u7684\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a\u811a\u672c(99\u5757\u94b1)\uff0c\u4f46\u516c\u53f8\u6ca1\u94b1\u4e70\uff0c\u65e2\u7136\u4ed6\u80fd\u5199~\u4e3a\u5565\u6211\u5c31\u65e0\u6cd5\u5199\u5462\uff1f\u5bf9\u4e0d\u5bf9\uff0c\u4e8e\u662f\u4e4e\u561b\u6211\u644a\u724c\u4e86\u6211\u5f00\u6e90\u4e86..\u540e\u7eed\u6709\u5174\u8da3\u6211\u4e5f\u80fd\u7814\u7a76gp\/pg\u7684\u4e5f\u4e0d\u662f\u4ec0\u4e48\u4e8b\u60c5…<\/p>\n

\u6267\u884c\u6548\u679c<\/h2>\n

\u6211\u89c9\u5f97\u6548\u679c\u5df2\u7ecf\u6ee1\u8db3\u4e86\u5427<\/p>\n

\u5177\u4f53\u6548\u679c\u8bf7\u67e5\u770b\uff1ahttps:\/\/qn.199604.com\/typoraImg\/10.80.210.122_20220704.html<\/p>\n

\u540e\u7eed\u66f4\u65b0\u4ee3\u7801\u4ed3\u5e93\uff1ahttps:\/\/gitee.com\/guoliangjun17\/database-patrol-report<\/p>\n

\"image-20220704135941008\"<\/div><\/p>\n

\u6d89\u53ca\u7684sql<\/h2>\n
--> \u6570\u636e\u5e93\u57fa\u672c\u4fe1\u606f\n\nselect now(),user(),current_user(),CONNECTION_ID(),DATABASE(),version(),all_db_size,@@basedir base_dit,@@datadir as data_dir,@@SOCKET as socket_dir,@@log_error as error_dir,@@AUTOCOMMIT as autocommit,@@log_bin as log_bin,@@server_id as  server_id from (SELECT concat(round(sum(DATA_LENGTH\/1024\/1024),2),'MB') as 'all_db_size' from information_schema.TABLES) tmp\n\n\n-->\u7248\u672c\u4fe1\u606f\nshow variables like '%version_comment%';\nshow variables like '%version_compile_machine%';\nshow variables like '%version_compile_os%';\n\n\n-->\u5f53\u524d\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u6240\u6709\u6570\u636e\u5e93\u53ca\u5176\u5bb9\u91cf\u5927\u5c0f\n\nselect SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,\nsum(table_rows) as '\u8bb0\u5f55\u6570',\nsum(truncate(data_length\/1024\/1024, 2)) as '\u6570\u636e\u5bb9\u91cf(MB)',\nsum(truncate(index_length\/1024\/1024, 2)) as '\u7d22\u5f15\u5bb9\u91cf(MB)',\nsum(truncate((data_length+index_length)\/1024\/1024, 2)) as '\u603b\u5927\u5c0f(MB)',\nsum(truncate(max_data_length\/1024\/1024, 2)) as '\u6700\u5927\u503c(MB)'\nfrom information_schema.SCHEMATA ma\nleft join information_schema.tables ta on ma.SCHEMA_NAME = ta.table_schema\ngroup by SCHEMA_NAME\norder by sum(data_length) desc, sum(index_length) desc;\n\n\n-->\u6570\u636e\u5e93\u5bf9\u8c61\nselect * from (\nSELECT table_schema as '\u6570\u636e\u5e93','TABLE' as '\u5bf9\u8c61\u7c7b\u578b', COUNT(*) as '\u5bf9\u8c61\u6570\u91cf' FROM information_schema.TABLES GROUP BY table_schema\nunion \nSELECT table_schema as '\u6570\u636e\u5e93','VIEW' as '\u5bf9\u8c61\u7c7b\u578b', COUNT(*) as '\u5bf9\u8c61\u6570\u91cf' FROM information_schema.VIEWS GROUP BY table_schema\nunion \nSELECT db as '\u6570\u636e\u5e93','PROCEDURE' as '\u5bf9\u8c61\u7c7b\u578b',COUNT(*) as '\u5bf9\u8c61\u6570\u91cf' FROM mysql.proc WHERE  `type` = 'PROCEDURE'  GROUP BY db\nunion\nSELECT db as '\u6570\u636e\u5e93','FUNCTION' as '\u5bf9\u8c61\u7c7b\u578b',COUNT(*) as '\u5bf9\u8c61\u6570\u91cf' FROM mysql.proc WHERE  `type` = 'FUNCTION'  GROUP BY db\n) tmp order by \u6570\u636e\u5e93,\u5bf9\u8c61\u7c7b\u578b\n\n\n-->\u67e5\u770b\u6570\u636e\u5e93\u7684\u8fd0\u884c\u72b6\u6001\nroot@localhost 00:10 [(none)]> status\n--------------\nmysql  Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using  EditLine wrapper\n\nConnection id:          16737\nCurrent database:\nCurrent user:           root@localhost\nSSL:                    Not in use\nCurrent pager:          stdout\nUsing outfile:          ''\nUsing delimiter:        ;\nServer version:         5.7.24-log MySQL Community Server (GPL)\nProtocol version:       10\nConnection:             Localhost via UNIX socket\nServer characterset:    utf8\nDb     characterset:    utf8\nClient characterset:    utf8\nConn.  characterset:    utf8\nUNIX socket:            \/data\/mysql\/mysql.sock\nUptime:                 27 days 10 hours 9 min 0 sec\n\nThreads: 37  Questions: 3437291  Slow queries: 10  Opens: 57716  Flush tables: 1027  Open tables: 1016  Queries per second avg: 1.450\n--------------\n\n\n-->\u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u5f20\u5927\u8868\nselect table_schema,table_name,table_type,engine,create_time,update_time,table_collation,\nsum(table_rows) as '\u8bb0\u5f55\u6570',\nsum(truncate(data_length\/1024\/1024, 2)) as '\u6570\u636e\u5bb9\u91cf(MB)',\nsum(truncate(index_length\/1024\/1024, 2)) as '\u7d22\u5f15\u5bb9\u91cf(MB)',\nsum(truncate((data_length+index_length)\/1024\/1024, 2)) as '\u603b\u5927\u5c0f(MB)',\nsum(truncate(max_data_length\/1024\/1024, 2)) as '\u6700\u5927\u503c(MB)'\nfrom information_schema.tables\nwhere TABLE_SCHEMA not in('information_schema','sys','mysql','performance_schema')\ngroup by table_schema,table_name\norder by sum((data_length+index_length)) desc limit 10;\n\n\n-->\u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u4e2a\u7d22\u5f15\n\nselect ta.table_schema,ta.table_name,st.index_name,sum(truncate(index_length\/1024\/1024, 2)) as 'SizeMB',st.NON_UNIQUE,st.INDEX_TYPE,st.COLUMN_NAME\nfrom information_schema.tables ta\nleft join information_schema.STATISTICS st\non ta.table_schema = st.table_schema and ta.table_name = st.table_name\nwhere ta.TABLE_SCHEMA not in('information_schema','sys','mysql','performance_schema')\ngroup by ta.table_schema,ta.table_name\norder by sum(index_length) desc limit 10;\n\n-->\u6240\u6709\u5b58\u50a8\u5f15\u64ce\u5217\u8868\nSELECT * FROM information_schema.ENGINES order by ENGINE\n\n--\u300b\u67e5\u8be2\u6240\u6709\u7528\u6237\nselect * from mysql.user order by user\n\n--\u300b\u4e00\u4e9b\u91cd\u8981\u7684\u53c2\u6570\nshow variables like '%autocommit%';\nshow variables like '%datadir%';\nshow variables like '%innodb_buffer_pool_size%';\nshow variables like '%innodb_file_per_table%';\nshow variables like '%innodb_flush_log_at_trx_commit%';\nshow variables like '%innodb_io_capacity';\nshow variables like '%innodb_lock_wait_timeout%';\nshow variables like '%log_error';\nshow variables like '%log_output%';\nshow variables like '%log_queries_not_using_indexes%';\nshow variables like '%log_slave_updates%';\nshow variables like '%log_throttle_queries_not_using_indexes%';\nshow variables like '%long_query_time%';\nshow variables like '%lower_case_table_names%';\nshow variables like '%max_connect_errors%';\nshow variables like '%max_connections%';\nshow variables like '%max_user_connections%';\nshow variables like '%pid_file%';\nshow variables like '%query_cache_size%';\nshow variables like '%query_cache_type%';\nshow variables like '%read_only%';\nshow variables like '%server_id%';\nshow variables like '%slow_query_log%';\nshow variables like '%slow_query_log_file%';\nshow variables like '%socket%';\nshow variables like '%sql_mode%';\nshow variables like '%time_zone%';\nshow variables like '%tx_isolation%';\n\n\n--\u300b\u67e5\u770b\u6bcf\u4e2ahost\u7684\u5f53\u524d\u8fde\u63a5\u6570\u548c\u603b\u8fde\u63a5\u6570\nselect * from performance_schema.accounts order by user\n\n-->\u67e5\u8be2\u6267\u884c\u8fc7\u5168\u626b\u63cf\u8bbf\u95ee\u7684\u8868\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8868\u626b\u63cf\u7684\u884c\u6570\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f(\u524d10)\nSELECT object_schema as db,\n  object_name as table_name,\n  count_read AS rows_full_scanned,\n  sys.format_time(sum_timer_wait) AS execu_time\nFROM performance_schema.table_io_waits_summary_by_index_usage\nWHERE index_name IS NULL\nAND count_read > 0\nORDER BY count_read DESC limit 10;\n\n-->\u67e5\u770b\u5e73\u5747\u6267\u884c\u65f6\u95f4\u503c\u5927\u4e8e95%\u7684\u5e73\u5747\u6267\u884c\u65f6\u95f4\u7684\u8bed\u53e5\uff08\u53ef\u8fd1\u4f3c\u5730\u8ba4\u4e3a\u662f\u5e73\u5747\u6267\u884c\u65f6\u95f4\u8d85\u957f\u7684\u8bed\u53e5\uff09\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8bed\u53e5\u5e73\u5747\u5ef6\u8fdf(\u6267\u884c\u65f6\u95f4)\u964d\u5e8f\u6392\u5e8f(\u524d10)\nSELECT sys.format_statement(DIGEST_TEXT) AS query,\n  SCHEMA_NAME as db,\n  IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,\n  COUNT_STAR AS exec_count,\n  SUM_ERRORS AS err_count,\n  SUM_WARNINGS AS warn_count,\n  sys.format_time(SUM_TIMER_WAIT) AS total_latency,\n  sys.format_time(MAX_TIMER_WAIT) AS max_latency,\n  sys.format_time(AVG_TIMER_WAIT) AS avg_latency,\n  SUM_ROWS_SENT AS rows_sent,\n  ROUND(IFNULL(SUM_ROWS_SENT \/ NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,\n  SUM_ROWS_EXAMINED AS rows_examined,\n  ROUND(IFNULL(SUM_ROWS_EXAMINED \/ NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,\n  FIRST_SEEN AS first_seen,\n  LAST_SEEN AS last_seen,\n  DIGEST AS digest\nFROM performance_schema.events_statements_summary_by_digest stmts\nJOIN sys.x$ps_digest_95th_percentile_by_avg_us AS top_percentile\nON ROUND(stmts.avg_timer_wait\/1000000) >= top_percentile.avg_us\nORDER BY AVG_TIMER_WAIT DESC limit 10;\n\n-->\u67e5\u770b\u4ea7\u751f\u9519\u8bef\u6216\u8b66\u544a\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6309\u7167\u9519\u8bef\u6570\u91cf\u548c\u8b66\u544a\u6570\u91cf\u964d\u5e8f\u6392\u5e8f(\u524d10)\nSELECT sys.format_statement(DIGEST_TEXT) AS query,\n  SCHEMA_NAME as db,\n  COUNT_STAR AS exec_count,\n  SUM_ERRORS AS errors,\n  IFNULL(SUM_ERRORS \/ NULLIF(COUNT_STAR, 0), 0) * 100 as error_pct,\n  SUM_WARNINGS AS warnings,\n  IFNULL(SUM_WARNINGS \/ NULLIF(COUNT_STAR, 0), 0) * 100 as warning_pct,\n  FIRST_SEEN as first_seen,\n  LAST_SEEN as last_seen,\n  DIGEST AS digest\nFROM performance_schema.events_statements_summary_by_digest\nWHERE SUM_ERRORS > 0\nOR SUM_WARNINGS > 0\nORDER BY SUM_ERRORS DESC, SUM_WARNINGS DESC limit 10;\n\n-->\u67e5\u770b\u5168\u8868\u626b\u63cf\u6216\u8005\u6ca1\u6709\u4f7f\u7528\u5230\u6700\u4f18\u7d22\u5f15\u7684\u8bed\u53e5\uff08\u524d10\uff09\nSELECT sys.format_statement(DIGEST_TEXT) AS query,\n  SCHEMA_NAME as db,\n  COUNT_STAR AS exec_count,\n  sys.format_time(SUM_TIMER_WAIT) AS total_latency,\n  SUM_NO_INDEX_USED AS no_index_used_count,\n  SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,\n  ROUND(IFNULL(SUM_NO_INDEX_USED \/ NULLIF(COUNT_STAR, 0), 0) * 100) AS no_index_used_pct,\n  SUM_ROWS_SENT AS rows_sent,\n  SUM_ROWS_EXAMINED AS rows_examined,\n  ROUND(SUM_ROWS_SENT\/COUNT_STAR) AS rows_sent_avg,\n  ROUND(SUM_ROWS_EXAMINED\/COUNT_STAR) AS rows_examined_avg,\n  FIRST_SEEN as first_seen,\n  LAST_SEEN as last_seen,\n  DIGEST AS digest\nFROM performance_schema.events_statements_summary_by_digest\nWHERE (SUM_NO_INDEX_USED > 0\nOR SUM_NO_GOOD_INDEX_USED > 0)\nAND DIGEST_TEXT NOT LIKE 'SHOW%'\nORDER BY no_index_used_pct DESC, total_latency DESC limit 10;\n\n\n-->\u67e5\u770b\u6267\u884c\u4e86\u6587\u4ef6\u6392\u5e8f\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8bed\u53e5\u603b\u5ef6\u8fdf\u65f6\u95f4(\u6267\u884c\u65f6\u95f4)\u964d\u5e8f\u6392\u5e8f\uff08\u524d10\uff09\nSELECT sys.format_statement(DIGEST_TEXT) AS query,\n  SCHEMA_NAME db,\n  COUNT_STAR AS exec_count,\n  sys.format_time(SUM_TIMER_WAIT) AS total_latency,\n  SUM_SORT_MERGE_PASSES AS sort_merge_passes,\n  ROUND(IFNULL(SUM_SORT_MERGE_PASSES \/ NULLIF(COUNT_STAR, 0), 0)) AS avg_sort_merges,\n  SUM_SORT_SCAN AS sorts_using_scans,\n  SUM_SORT_RANGE AS sort_using_range,\n  SUM_SORT_ROWS AS rows_sorted,\n  ROUND(IFNULL(SUM_SORT_ROWS \/ NULLIF(COUNT_STAR, 0), 0)) AS avg_rows_sorted,\n  FIRST_SEEN as first_seen,\n  LAST_SEEN as last_seen,\n  DIGEST AS digest\nFROM performance_schema.events_statements_summary_by_digest\nWHERE SUM_SORT_ROWS > 0\nORDER BY SUM_TIMER_WAIT DESC limit 10;\n\n-->\u67e5\u770b\u4f7f\u7528\u4e86\u4e34\u65f6\u8868\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u78c1\u76d8\u4e34\u65f6\u8868\u6570\u91cf\u548c\u5185\u5b58\u4e34\u65f6\u8868\u6570\u91cf\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f\uff08\u524d10\uff09\nSELECT sys.format_statement(DIGEST_TEXT) AS query,\n  SCHEMA_NAME as db,\n  COUNT_STAR AS exec_count,\n  sys.format_time(SUM_TIMER_WAIT) as total_latency,\n  SUM_CREATED_TMP_TABLES AS memory_tmp_tables,\n  SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,\n  ROUND(IFNULL(SUM_CREATED_TMP_TABLES \/ NULLIF(COUNT_STAR, 0), 0)) AS avg_tmp_tables_per_query,\n  ROUND(IFNULL(SUM_CREATED_TMP_DISK_TABLES \/ NULLIF(SUM_CREATED_TMP_TABLES, 0), 0) * 100) AS tmp_tables_to_disk_pct,\n  FIRST_SEEN as first_seen,\n  LAST_SEEN as last_seen,\n  DIGEST AS digest\nFROM performance_schema.events_statements_summary_by_digest\nWHERE SUM_CREATED_TMP_TABLES > 0\nORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC limit 10;\n\n\n--> \u6027\u80fd\u53c2\u6570\u7edf\u8ba1\nshow status like 'Com_delete';\nshow status like 'Com_insert';\nshow status like 'Com_select';\nshow status like 'Connections';\nshow status like 'Created_tmp_disk_tables';\nshow status like 'Created_tmp_files';\nshow status like 'Created_tmp_tables';\nshow status like 'Handler_read_rnd_next';\nshow status like 'Open_files';\nshow status like 'Opened_tables';\nshow status like 'Slow_queries';\nshow status like 'Sort_merge_passes';\nshow status like 'Sort_range';\nshow status like 'Sort_rows';\nshow status like 'Sort_scan';\nshow status like 'Table_locks_immediate';\nshow status like 'Table_locks_waited';\nshow status like 'Uptime';\n<\/code><\/pre>\n
\u6d89\u53ca\u7684shell<\/h2>\n
#!\/bin\/bash\n\nipaddress=`ip a|grep \"global\"| awk NR==1 |awk '{print $2}' |awk -F\/ '{print $1}'`\ntoday=`date +%Y%m%d`\n#today=`date +%Y%m%d%H%M`\nfile_output=${ipaddress}'_'${today}'.html'\n\ntd_str=''\nth_str=''\ndb_username=\"root\"\ndb_passwd=\"123456\"\ndb_ip=\"localhost\"\ndb_port=\"3306\"\nmysql_cmd=\"mysql -u ${db_username} -p${db_passwd} -h${db_ip} -P${db_port} --protocol=tcp --silent\"\n\n# html+css\ncreate_html_css(){\n    echo -e \"<html lang=\\\"zh-CN\\\">\n<head>\n<meta charset=\\\"UTF-8\\\">\n<title>MySQL Report<\/title>\n<style type=\\\"text\/css\\\">\n    body        {font:12px Courier New,Helvetica,sansserif; color:black; background:White;}\n    table {font:12px Courier New,Helvetica,sansserif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}\n    tr,td {padding:10px;margin:10px}\n    th          {font:bold 12px Courier New,Helvetica,sansserif; color:White; background:#0033FF; padding:10px;}\n    h1          {font:bold 32px Courier New,Helvetica,sansserif; color:Black; padding:0px 0px 0px 0px;}\n<\/style>\n<\/head>\n<body>\n<center><font size=\\\"+3\\\" color=\\\"darkgreen\\\"><b>MySQL\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a<\/b><\/font><\/center>\n<hr \/>\n<div style=\\\"font-weight:lighter\\\"><font face=\\\"Courier New,Helvetica,Geneva,sans-serif\\\" color=\\\"#336699\\\">Copyright (c) <a target=\\\"_blank\\\" href=\\\"https:\/\/199604.com\\\">\u8bb0\u5fc6\u89d2\u843d<\/a>. All rights reserved.<\/font><\/div>\n<hr \/>\n<p>\u5de1\u68c0\u65f6\u95f4\uff1a `date +%Y%m%d%H%M`<\/p>\n\"\n}\n\ncreate_html_end(){\n  echo -e \"<\/body><\/html>\"\n}\n\ncreate_h1_head(){\n    echo -e \"<h1>$1<\/h1>\"\n}\n\ncreate_table_start(){\n  echo -e \"<table width=\"68%\" border=\"1\" bordercolor=\"#000000\" cellspacing=\"0px\" style=\"border-collapse:collapse\">\"\n}\n\ncreate_table_end(){\n  echo -e \"<\/table>\"\n}\n\ncreate_td(){\n  td_str=`echo $1 | awk 'BEGIN{FS=\"|\"}''{i=1; while(i<=NF) {print \"<td>\"$i\"<\/td>\";i++}}'`\n}\n\ncreate_th(){\n  th_str=`echo $1|awk 'BEGIN{FS=\"|\"}''{i=1; while(i<=NF) {print \"<th>\"$i\"<\/th>\";i++}}'`\n}\n\ncreate_tr1(){\n  create_td \"$1\"\n  echo -e \"<tr>\n    $td_str\n  <\/tr>\" >> $file_output\n}\ncreate_tr2(){\n  create_th \"$1\"\n  echo -e \"<tr>\n    $th_str\n  <\/tr>\" >> $file_output\n}\ncreate_tr3(){\n  echo -e \"<tr><td>\n  <pre style=\\\"font-family:Courier New; word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap\\\" >\n  `cat $1`\n  <\/pre><\/td><\/tr>\" >> $file_output\n}\n\ncmd_db_base_info_sql(){\n  ${mysql_cmd} -e \"select now(),user(),current_user(),CONNECTION_ID(),DATABASE(),version(),all_db_size,@@basedir base_dit,@@datadir as data_dir,@@SOCKET as socket_dir,@@log_error as error_dir,@@AUTOCOMMIT as autocommit,@@log_bin as log_bin,@@server_id as  server_id from (SELECT concat(round(sum(DATA_LENGTH\/1024\/1024),2),'MB') as 'all_db_size' from information_schema.TABLES) tmp\"\n}\n\ndb_base_info(){\n  create_h1_head \"\u6570\u636e\u5e93\u57fa\u672c\u4fe1\u606f\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"now()   |user() | current_user()    |CONNECTION_ID()    |   DATABASE()  |   version() | all_db_size |   base_dit    |     data_dir  |     socket_dir    |     error_dir |     autocommit    |     log_bin   |     server_id\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  cmd_db_base_info_sql >> \/tmp\/tmp_db_base_info_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_base_info_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_base_info_`date +%Y%m%d%H%M`.txt\n  create_table_end >> ${file_output}\n}\n\ncmd_db_version_info_sql(){\n  ${mysql_cmd} -e \"show variables like '%version_comment%';\"\n  ${mysql_cmd} -e \"show variables like '%version_compile_machine%';\"\n  ${mysql_cmd} -e \"show variables like '%version_compile_os%';\"\n}\ndb_version_info(){\n  create_h1_head \"\u7248\u672c\u4fe1\u606f\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"Variable_name   | Value\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_version_info_sql >> \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_usage_sql(){\n  ${mysql_cmd} -e \"SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, sum(table_rows) AS \\\"\u8bb0\u5f55\u6570\\\" , sum(truncate(data_length \/ 1024 \/ 1024, 2)) AS \\\"\u6570\u636e\u5bb9\u91cf(MB)\\\" , sum(truncate(index_length \/ 1024 \/ 1024, 2)) AS \\\"\u7d22\u5f15\u5bb9\u91cf(MB)\\\" , sum(truncate((data_length + index_length) \/ 1024 \/ 1024, 2)) AS \\\"\u603b\u5927\u5c0f(MB)\\\" , sum(truncate(max_data_length \/ 1024 \/ 1024, 2)) AS \\\"\u6700\u5927\u503c(MB)\\\" FROM information_schema.SCHEMATA ma LEFT JOIN information_schema.tables ta ON ma.SCHEMA_NAME = ta.table_schema GROUP BY SCHEMA_NAME ORDER BY sum(data_length) DESC, sum(index_length) DESC;\"\n}\ndb_usage(){\n  create_h1_head \"\u5f53\u524d\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u6240\u6709\u6570\u636e\u5e93\u53ca\u5176\u5bb9\u91cf\u5927\u5c0f\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"SCHEMA_NAME|DEFAULT_CHARACTER_SET_NAME|DEFAULT_COLLATION_NAME|\u8bb0\u5f55\u6570|\u6570\u636e\u5bb9\u91cf(MB)|\u7d22\u5f15\u5bb9\u91cf(MB)|\u603b\u5927\u5c0f(MB)|\u6700\u5927\u503c(MB)\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_usage_sql >> \/tmp\/tmp_db_usage_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_usage_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_usage_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_object_sql(){\n  ${mysql_cmd} -e \"SELECT * FROM ( SELECT table_schema AS \\\"\u6570\u636e\u5e93\\\", 'TABLE' AS \\\"\u5bf9\u8c61\u7c7b\u578b\\\", COUNT(*) AS \\\"\u5bf9\u8c61\u6570\u91cf\\\" FROM information_schema.TABLES GROUP BY table_schema UNION SELECT table_schema AS \\\"\u6570\u636e\u5e93\\\", 'VIEW' AS \\\"\u5bf9\u8c61\u7c7b\u578b\\\", COUNT(*) AS \\\"\u5bf9\u8c61\u6570\u91cf\\\" FROM information_schema.VIEWS GROUP BY table_schema UNION SELECT db AS \\\"\u6570\u636e\u5e93\\\", 'PROCEDURE' AS \\\"\u5bf9\u8c61\u7c7b\u578b\\\", COUNT(*) AS \\\"\u5bf9\u8c61\u6570\u91cf\\\" FROM mysql.proc WHERE type = 'PROCEDURE' GROUP BY db UNION SELECT db AS \\\"\u6570\u636e\u5e93\\\", 'FUNCTION' AS \\\"\u5bf9\u8c61\u7c7b\u578b\\\", COUNT(*) AS \\\"\u5bf9\u8c61\u6570\u91cf\\\" FROM mysql.proc WHERE type = 'FUNCTION' GROUP BY db ) tmp ORDER BY \\\"\u6570\u636e\u5e93\\\", \\\"\u5bf9\u8c61\u7c7b\u578b\\\"\"\n}\n\ndb_object(){\n  create_h1_head \"\u6570\u636e\u5e93\u5bf9\u8c61\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"\u6570\u636e\u5e93|\u5bf9\u8c61\u7c7b\u578b|\u5bf9\u8c61\u6570\u91cf\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_object_sql >> \/tmp\/tmp_db_object_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_object_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_object_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ndb_status(){\n  create_h1_head \"\u67e5\u770b\u6570\u636e\u5e93\u7684\u8fd0\u884c\u72b6\u6001\" >> ${file_output}\n  create_table_start >> ${file_output}\n  ${mysql_cmd} -e \"status\" > \/tmp\/tmp_db_status_`date +%Y%m%d%H%M`.txt\n  create_tr3 \/tmp\/tmp_db_status_`date +%Y%m%d%H%M`.txt\n  create_table_end >> ${file_output}\n}\n\ncmd_db_top_ten_big_tables_sql(){\n  ${mysql_cmd} -e \"SELECT table_schema, table_name, table_type, engine, create_time , update_time, table_collation, sum(table_rows) AS \\\"\u8bb0\u5f55\u6570\\\" , sum(truncate(data_length \/ 1024 \/ 1024, 2)) AS \\\"\u6570\u636e\u5bb9\u91cf(MB)\\\" , sum(truncate(index_length \/ 1024 \/ 1024, 2)) AS \\\"\u7d22\u5f15\u5bb9\u91cf(MB)\\\" , sum(truncate((data_length + index_length) \/ 1024 \/ 1024, 2)) AS \\\"\u603b\u5927\u5c0f(MB)\\\" , sum(truncate(max_data_length \/ 1024 \/ 1024, 2)) AS \\\"\u6700\u5927\u503c(MB)\\\" FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('information_schema', 'sys', 'mysql', 'performance_schema') GROUP BY table_schema, table_name ORDER BY sum(data_length + index_length) DESC LIMIT 10;\"\n}\n\ndb_top_ten_big_tables(){\n  create_h1_head \"\u6570\u636e\u5e93\u5bf9\u8c61\u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u5f20\u5927\u8868\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"table_schema|table_name|table_type|engine|create_time|update_time|table_collation|\u8bb0\u5f55\u6570|\u6570\u636e\u5bb9\u91cf(MB)|\u7d22\u5f15\u5bb9\u91cf(MB)|\u603b\u5927\u5c0f(MB)|\u6700\u5927\u503c(MB)\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_top_ten_big_tables_sql >> \/tmp\/tmp_db_top_ten_big_tables_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_top_ten_big_tables_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_top_ten_big_tables_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_top_ten_index_sql(){\n  ${mysql_cmd} -e \"SELECT ta.table_schema, ta.table_name, st.index_name , sum(truncate(index_length \/ 1024 \/ 1024, 2)) AS \\\"SizeMB\\\" , st.NON_UNIQUE, st.INDEX_TYPE, st.COLUMN_NAME FROM information_schema.tables ta LEFT JOIN information_schema.STATISTICS st ON ta.table_schema = st.table_schema AND ta.table_name = st.table_name WHERE ta.TABLE_SCHEMA NOT IN ('information_schema', 'sys', 'mysql', 'performance_schema') GROUP BY ta.table_schema, ta.table_name ORDER BY sum(index_length) DESC LIMIT 10;\"\n}\n\ndb_top_ten_index(){\n  create_h1_head \"\u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u4e2a\u7d22\u5f15\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"table_schema|table_name|index_name|SizeMB|NON_UNIQUE|INDEX_TYPE|COLUMN_NAME\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_top_ten_index_sql >> \/tmp\/tmp_db_top_ten_index_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_top_ten_index_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_top_ten_index_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_all_user_sql(){\n  ${mysql_cmd} -e \"select * from mysql.user order by user;\"\n}\n\ndb_all_user(){\n  create_h1_head \"\u67e5\u8be2\u6240\u6709\u7528\u6237\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"Host|User|Select_priv|Insert_priv|Update_priv|Delete_priv|Create_priv|Drop_priv|Reload_priv|Shutdown_priv|Process_priv|File_priv|Grant_priv|References_priv|Index_priv|Alter_priv|Show_db_priv|Super_priv|Create_tmp_table_priv|Lock_tables_priv|Execute_priv|Repl_slave_priv|Repl_client_priv|Create_view_priv|Show_view_priv|Create_routine_priv|Alter_routine_priv|Create_user_priv|Event_priv|Trigger_priv|Create_tablespace_priv|ssl_type|ssl_cipher|x509_issuer|x509_subject|max_questions|max_updates|max_connections|max_user_connections|plugin|authentication_string|password_expired|password_last_changed|password_lifetime|account_locked\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_all_user_sql >> \/tmp\/tmp_db_all_user_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_all_user_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_all_user_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_some_important_para_sql(){\n  ${mysql_cmd} -e \"show variables like '%autocommit%';\"\n  ${mysql_cmd} -e \"show variables like '%datadir%';\"\n  ${mysql_cmd} -e \"show variables like '%innodb_buffer_pool_size%';\"\n  ${mysql_cmd} -e \"show variables like '%innodb_file_per_table%';\"\n  ${mysql_cmd} -e \"show variables like '%innodb_flush_log_at_trx_commit%';\"\n  ${mysql_cmd} -e \"show variables like '%innodb_io_capacity';\"\n  ${mysql_cmd} -e \"show variables like '%innodb_lock_wait_timeout%';\"\n  ${mysql_cmd} -e \"show variables like '%log_error';\"\n  ${mysql_cmd} -e \"show variables like '%log_output%';\"\n  ${mysql_cmd} -e \"show variables like '%log_queries_not_using_indexes%';\"\n  ${mysql_cmd} -e \"show variables like '%log_slave_updates%';\"\n  ${mysql_cmd} -e \"show variables like '%log_throttle_queries_not_using_indexes%';\"\n  ${mysql_cmd} -e \"show variables like '%long_query_time%';\"\n  ${mysql_cmd} -e \"show variables like '%lower_case_table_names%';\"\n  ${mysql_cmd} -e \"show variables like '%max_connect_errors%';\"\n  ${mysql_cmd} -e \"show variables like '%max_connections%';\"\n  ${mysql_cmd} -e \"show variables like '%max_user_connections%';\"\n  ${mysql_cmd} -e \"show variables like '%pid_file%';\"\n  ${mysql_cmd} -e \"show variables like '%query_cache_size%';\"\n  ${mysql_cmd} -e \"show variables like '%query_cache_type%';\"\n  ${mysql_cmd} -e \"show variables like '%read_only%';\"\n  ${mysql_cmd} -e \"show variables like '%server_id%';\"\n  ${mysql_cmd} -e \"show variables like '%slow_query_log%';\"\n  ${mysql_cmd} -e \"show variables like '%slow_query_log_file%';\"\n  ${mysql_cmd} -e \"show variables like '%socket%';\"\n  ${mysql_cmd} -e \"show variables like '%sql_mode%';\"\n  ${mysql_cmd} -e \"show variables like '%time_zone%';\"\n  ${mysql_cmd} -e \"show variables like '%tx_isolation%';\"\n}\n\ndb_some_important_para(){\n  create_h1_head \"\u91cd\u8981\u7684\u53c2\u6570\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"Variable_name   | Value\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_some_important_para_sql >> \/tmp\/tmp_db_some_important_para_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_some_important_para_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_some_important_para_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_current_and_total_connections_num(){\n  ${mysql_cmd} -e \"select * from performance_schema.accounts order by user;\"\n}\n\ndb_current_and_total_connections_num(){\n  create_h1_head \"\u67e5\u770b\u6bcf\u4e2ahost\u7684\u5f53\u524d\u8fde\u63a5\u6570\u548c\u603b\u8fde\u63a5\u6570\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"USER|HOST|CURRENT_CONNECTIONS|TOTAL_CONNECTIONS\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_current_and_total_connections_num >> \/tmp\/tmp_db_current_and_total_connections_num_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_current_and_total_connections_num_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_current_and_total_connections_num_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_query_tables_have_full_scan_sql(){\n  ${mysql_cmd} -e \"SELECT object_schema AS db, object_name AS table_name, count_read AS rows_full_scanned, sys.format_time(sum_timer_wait) AS execu_time FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NULL AND count_read > 0 ORDER BY count_read DESC LIMIT 10;\"\n}\n\ndb_query_tables_have_full_scan(){\n  create_h1_head \"\u67e5\u8be2\u6267\u884c\u8fc7\u5168\u626b\u63cf\u8bbf\u95ee\u7684\u8868\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8868\u626b\u63cf\u7684\u884c\u6570\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f(\u524d10)\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"db|table_name|rows_full_scanned|execu_time\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_query_tables_have_full_scan_sql >> \/tmp\/tmp_db_query_tables_have_full_scan_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_query_tables_have_full_scan_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_query_tables_have_full_scan_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_execution_time_values_greater_than_95_sql(){\n  ${mysql_cmd} -e \"SELECT sys.format_statement(DIGEST_TEXT) AS query, SCHEMA_NAME AS db , IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan , COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, sys.format_time(SUM_TIMER_WAIT) AS total_latency , sys.format_time(MAX_TIMER_WAIT) AS max_latency, sys.format_time(AVG_TIMER_WAIT) AS avg_latency , SUM_ROWS_SENT AS rows_sent , ROUND(IFNULL(SUM_ROWS_SENT \/ NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg , SUM_ROWS_EXAMINED AS rows_examined , ROUND(IFNULL(SUM_ROWS_EXAMINED \/ NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg , FIRST_SEEN AS first_seen, LAST_SEEN AS last_seen, DIGEST AS digest FROM performance_schema.events_statements_summary_by_digest stmts JOIN sys.x\\$ps_digest_95th_percentile_by_avg_us top_percentile ON ROUND(stmts.avg_timer_wait \/ 1000000) >= top_percentile.avg_us ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;\"\n}\n\ndb_execution_time_values_greater_than_95(){\n  create_h1_head \"\u67e5\u8be2\u6267\u884c\u8fc7\u5168\u626b\u63cf\u8bbf\u95ee\u7684\u8868\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8868\u626b\u63cf\u7684\u884c\u6570\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f(\u524d10)\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"query|db|full_scan|exec_count|err_count|warn_count|total_latency|max_latency|avg_latency|rows_sent|rows_sent_avg|rows_examined|rows_examined_avg|first_seen|last_seen|digest\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_execution_time_values_greater_than_95_sql >> \/tmp\/tmp_db_execution_time_values_greater_than_95_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_execution_time_values_greater_than_95_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_execution_time_values_greater_than_95_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_statements_that_generate_errors_or_warnings_sql(){\n  ${mysql_cmd} -e \"SELECT sys.format_statement(DIGEST_TEXT) AS query, SCHEMA_NAME AS db, COUNT_STAR AS exec_count, SUM_ERRORS AS errors , IFNULL(SUM_ERRORS \/ NULLIF(COUNT_STAR, 0), 0) * 100 AS error_pct , SUM_WARNINGS AS warnings , IFNULL(SUM_WARNINGS \/ NULLIF(COUNT_STAR, 0), 0) * 100 AS warning_pct , FIRST_SEEN AS first_seen, LAST_SEEN AS last_seen, DIGEST AS digest FROM performance_schema.events_statements_summary_by_digest WHERE SUM_ERRORS > 0 OR SUM_WARNINGS > 0 ORDER BY SUM_ERRORS DESC, SUM_WARNINGS DESC LIMIT 10;\"\n}\n\ndb_statements_that_generate_errors_or_warnings(){\n  create_h1_head \"\u67e5\u770b\u4ea7\u751f\u9519\u8bef\u6216\u8b66\u544a\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6309\u7167\u9519\u8bef\u6570\u91cf\u548c\u8b66\u544a\u6570\u91cf\u964d\u5e8f\u6392\u5e8f(\u524d10)\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"query|db|exec_count|errors|error_pct|warnings|warning_pct|first_seen|last_seen|digest\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_statements_that_generate_errors_or_warnings_sql >> \/tmp\/tmp_db_statements_that_generate_errors_or_warnings_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_statements_that_generate_errors_or_warnings_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_statements_that_generate_errors_or_warnings_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncmd_db_performance_parameter_statistics_sql(){\n  ${mysql_cmd} -e \"show status like 'Com_delete';\"\n  ${mysql_cmd} -e \"show status like 'Com_insert';\"\n  ${mysql_cmd} -e \"show status like 'Com_select';\"\n  ${mysql_cmd} -e \"show status like 'Connections';\"\n  ${mysql_cmd} -e \"show status like 'Created_tmp_disk_tables';\"\n  ${mysql_cmd} -e \"show status like 'Created_tmp_files';\"\n  ${mysql_cmd} -e \"show status like 'Created_tmp_tables';\"\n  ${mysql_cmd} -e \"show status like 'Handler_read_rnd_next';\"\n  ${mysql_cmd} -e \"show status like 'Open_files';\"\n  ${mysql_cmd} -e \"show status like 'Opened_tables';\"\n  ${mysql_cmd} -e \"show status like 'Slow_queries';\"\n  ${mysql_cmd} -e \"show status like 'Sort_merge_passes';\"\n  ${mysql_cmd} -e \"show status like 'Sort_range';\"\n  ${mysql_cmd} -e \"show status like 'Sort_rows';\"\n  ${mysql_cmd} -e \"show status like 'Sort_scan';\"\n  ${mysql_cmd} -e \"show status like 'Table_locks_immediate';\"\n  ${mysql_cmd} -e \"show status like 'Table_locks_waited';\"\n  ${mysql_cmd} -e \"show status like 'Uptime';\"\n}\n\ndb_performance_parameter_statistics(){\n  create_h1_head \"\u6027\u80fd\u53c2\u6570\u7edf\u8ba1\" >> ${file_output}\n  create_table_start >> ${file_output}\n  echo \"Variable_name   | Value\" > \/tmp\/tmp_inspecting_mysql_table.txt\n  while read line\n  do\n    create_tr2 \"$line\"\n  done < \/tmp\/tmp_inspecting_mysql_table.txt\n\n  cmd_db_performance_parameter_statistics_sql >> \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n  sed -i 's\/\\t\/|\/g' \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n\n  while read line\n  do\n    create_tr1 \"$line\"\n  done < \/tmp\/tmp_db_version_info_`date +%Y%m%d%H%M`.txt\n\n  create_table_end >> ${file_output}\n}\n\ncreate_html_file(){\n  rm -rf ${file_output}\n  touch ${file_output}\n  create_html_css >> ${file_output}\n\n  # \u6570\u636e\u5e93\u57fa\u672c\u4fe1\u606f start\n  db_base_info\n\n  # \u7248\u672c\u4fe1\u606f start\n  db_version_info\n\n  # \u91cd\u8981\u7684\u53c2\u6570\n  db_some_important_para\n\n  # \u6027\u80fd\u53c2\u6570\u7edf\u8ba1\n  db_performance_parameter_statistics\n\n  # \u67e5\u770b\u6570\u636e\u5e93\u7684\u8fd0\u884c\u72b6\u6001\n  db_status\n\n  # \u5f53\u524d\u6570\u636e\u5e93\u5b9e\u4f8b\u7684\u6240\u6709\u6570\u636e\u5e93\u53ca\u5176\u5bb9\u91cf\u5927\u5c0f\n  db_usage\n\n  # \u67e5\u8be2\u6240\u6709\u7528\u6237\n  db_all_user\n\n  # \u67e5\u770b\u6bcf\u4e2ahost\u7684\u5f53\u524d\u8fde\u63a5\u6570\u548c\u603b\u8fde\u63a5\u6570\n  db_current_and_total_connections_num\n\n  # \u6570\u636e\u5e93\u5bf9\u8c61\n  db_object\n\n  # \u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u5f20\u5927\u8868\n  db_top_ten_big_tables\n\n  # \u5360\u7528\u7a7a\u95f4\u6700\u5927\u7684\u524d10\u4e2a\u7d22\u5f15\n  db_top_ten_index\n\n  # \u67e5\u8be2\u6267\u884c\u8fc7\u5168\u626b\u63cf\u8bbf\u95ee\u7684\u8868\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8868\u626b\u63cf\u7684\u884c\u6570\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f(\u524d10)\n  db_query_tables_have_full_scan\n\n  # \u67e5\u770b\u5e73\u5747\u6267\u884c\u65f6\u95f4\u503c\u5927\u4e8e95%\u7684\u5e73\u5747\u6267\u884c\u65f6\u95f4\u7684\u8bed\u53e5\uff08\u53ef\u8fd1\u4f3c\u5730\u8ba4\u4e3a\u662f\u5e73\u5747\u6267\u884c\u65f6\u95f4\u8d85\u957f\u7684\u8bed\u53e5\uff09\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8bed\u53e5\u5e73\u5747\u5ef6\u8fdf(\u6267\u884c\u65f6\u95f4)\u964d\u5e8f\u6392\u5e8f(\u524d10)\n  db_execution_time_values_greater_than_95\n\n  # \u67e5\u770b\u4ea7\u751f\u9519\u8bef\u6216\u8b66\u544a\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6309\u7167\u9519\u8bef\u6570\u91cf\u548c\u8b66\u544a\u6570\u91cf\u964d\u5e8f\u6392\u5e8f(\u524d10)\n  db_statements_that_generate_errors_or_warnings\n\n  # \u67e5\u770b\u5168\u8868\u626b\u63cf\u6216\u8005\u6ca1\u6709\u4f7f\u7528\u5230\u6700\u4f18\u7d22\u5f15\u7684\u8bed\u53e5\uff08\u524d10\uff09\n\n  # \u67e5\u770b\u6267\u884c\u4e86\u6587\u4ef6\u6392\u5e8f\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u8bed\u53e5\u603b\u5ef6\u8fdf\u65f6\u95f4(\u6267\u884c\u65f6\u95f4)\u964d\u5e8f\u6392\u5e8f\uff08\u524d10\uff09\n\n  # \u67e5\u770b\u4f7f\u7528\u4e86\u4e34\u65f6\u8868\u7684\u8bed\u53e5\uff0c\u9ed8\u8ba4\u60c5\u51b5\u4e0b\u6309\u7167\u78c1\u76d8\u4e34\u65f6\u8868\u6570\u91cf\u548c\u5185\u5b58\u4e34\u65f6\u8868\u6570\u91cf\u8fdb\u884c\u964d\u5e8f\u6392\u5e8f\uff08\u524d10\uff09\n\n  create_html_end >> ${file_output}\n\n  rm -f \/tmp\/tmp_db_*.txt\n}\n\nRUID=`id|awk -F\\( '{print $1}'|awk -F\\= '{print $2}'`\nif [ ${RUID} != \"0\" ];then\n    echo\"This script must be executed as root\"\n    exit 1\nfi\n\ncreate_html_file\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Centos7-shell\u751f\u6210MySQL\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a \u524d\u63d0 \u8feb\u4e8e\u7f51\u4e0a\u6709\u4eba\u5356\u6536\u8d39\u7684\u6570\u636e\u5e93\u5de1\u68c0\u62a5\u544a\u811a\u672c(99\u5757\u94b1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[260],"tags":[239,22,191],"class_list":["post-2302","post","type-post","status-publish","format-standard","hentry","category-linux","tag-centos7","tag-mysql","tag-shell"],"_links":{"self":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/2302","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/comments?post=2302"}],"version-history":[{"count":1,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/2302\/revisions"}],"predecessor-version":[{"id":2303,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/2302\/revisions\/2303"}],"wp:attachment":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/media?parent=2302"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/categories?post=2302"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/tags?post=2302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}