• 分享一个实用的MySQL一键巡检脚本

    今日分享一个实用的MySQL一键巡检脚本，脚本内容还不是很完善，后续会继续进行优化。大家可以先在测试环境执行，确认执行没问题后可以在生产环境进行操作，问题的可以私信我。

    MySQL一键巡检脚本的作用主要是帮助数据库管理员快速且系统性地检查和评估MySQL数据库实例的运行状态和健康状况。通过定期执行这样的巡检脚本，可以及时发现潜在问题，优化性能，确保数据库的高可用性和稳定性。以下是MySQL一键巡检脚本的一些主要作用和目标：

    1. 性能监测

    • CPU使用率：检查MySQL实例及其主机的CPU使用情况，识别是否存在过高的CPU占用。

    • 内存使用情况：监测内存使用情况，判断是否存在内存泄露或内存不足。

    • I/O性能：检查磁盘I/O性能，评估读写速度和延迟，确保磁盘性能不会成为瓶颈。

    2. 配置检查

    • 重要参数检查：审查MySQL配置文件中的关键参数（如max_connections、innodb_buffer_pool_size等），根据最佳实践进行调整。

    • 安全设置：检查用户权限、安全配置和日志等，确保数据库的安全性。

    3. 数据库状态

    • 连接数监控：检查当前连接数及最大连接数设置，预防由于连接数过多导致的资源耗尽问题。

    • 线程状态：统计各类线程的状态，识别锁等待、慢查询等问题。

    4. 存储和容量监控

    • 表空间使用：统计数据库和表的大小，监测表空间使用情况，规划存储扩容。

    • 数据文件和日志文件：检查数据文件和日志文件的增长情况，确保磁盘空间充足。

    5. 日志和慢查询分析

    • 错误日志：分析MySQL错误日志，获取近期发生的错误信息。

    • 慢查询日志：捕获并分析慢查询日志，找出执行时间较长的SQL语句，优化查询性能。

    6. 备份和恢复状态

    • 备份状态检查：检查最近的备份时间和备份文件的完整性，确保备份策略有效执行。

    • 恢复测试：定期测试备份文件的恢复情况，验证备份的可用性。

    7. 高可用性状态

    • 主从复制状态：检查主从复制的同步状态，确保数据一致性和复制链路畅通。

    • 自动故障切换：检查高可用配置和故障切换机制的正常运作，确保数据库在故障时能够快速恢复。

      1. #!/bin/bash
      2.  
      3. host="192.168.59.128"
      4.  
      5. port="3306"
      6.  
      7. userName="root"
      8.  
      9. password="123456"
      10.  
      11. dbname="mysql"
      12.  
      13. base='/usr/local/mysql'
      14.  
      15. ##数据文件位置##
      16.  
      17. echo "================= mysql配置信息 ==============================="
      18.  
      19. echo "========= 基本配置信息 ==========="
      20.  
      21. lower_case_table_names="show variables like 'lower_case_table_names';"
      22.  
      23. lower_case_table_names_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${lower_case_table_names}")
      24.  
      25. echo "不区分大小写：" `echo ${lower_case_table_names_val} | cut -d' ' -f4`
      26.  
      27. _port="show variables like 'port';"
      28.  
      29. _port_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${_port}")
      30.  
      31. echo "端口：" `echo ${_port_val} | cut -d' ' -f4`
      32.  
      33. socket="show variables like 'socket';"
      34.  
      35. socket_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${socket}")
      36.  
      37. echo "socket的值：" `echo ${socket_val} | cut -d' ' -f4`
      38.  
      39. skip_name_resolve="show variables like 'skip_name_resolve';"
      40.  
      41. skip_name_resolve_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${skip_name_resolve}")
      42.  
      43. echo "域名解析skip_name_resolve：" `echo ${skip_name_resolve_val} | cut -d' ' -f4`
      44.  
      45. character_set_server="show variables like 'character_set_server';"
      46.  
      47. character_set_server_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${character_set_server}")
      48.  
      49. echo "数据库字符集character_set_server：" `echo ${character_set_server_val} | cut -d' ' -f4`
      50.  
      51. interactive_timeout="show variables like 'interactive_timeout';"
      52.  
      53. interactive_timeout_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${interactive_timeout}")
      54.  
      55. echo "交互式连接超时时间(mysql工具、mysqldump等)interactive_timeout(秒)：" `echo ${interactive_timeout_val} | cut -d' ' -f4`
      56.  
      57. wait_timeout="show variables like 'wait_timeout';"
      58.  
      59. wait_timeout_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${wait_timeout}")
      60.  
      61. echo "非交互式连接超时时间，默认的连接mysql api程序,jdbc连接数据库等wait_timeout(秒)：" `echo ${wait_timeout_val} | cut -d' ' -f4`
      62.  
      63. query_cache_type="show variables like 'query_cache_type';"
      64.  
      65. query_cache_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${query_cache_type}")
      66.  
      67. echo "查询缓存query_cache_type：" `echo ${query_cache_type_val} | cut -d' ' -f4`
      68.  
      69. innodb_version="show variables like 'innodb_version';"
      70.  
      71. innodb_version_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_version}")
      72.  
      73. echo "数据库版本：" `echo ${innodb_version_val} | cut -d' ' -f4`
      74.  
      75. trx_isolation="show variables like 'tx_isolation';"
      76.  
      77. trx_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${trx_isolation}")
      78.  
      79. echo "mysql5.6隔离级别trx_isolation：" `echo ${trx_isolation_val} | cut -d' ' -f4`
      80.  
      81. transaction_isolation="show variables like 'transaction_isolation';"
      82.  
      83. transaction_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${transaction_isolation}")
      84.  
      85. echo "隔离级别transaction_isolation：" `echo ${transaction_isolation_val} | cut -d' ' -f4`
      86.  
      87. datadir="show variables like '%datadir%';"
      88.  
      89. datadir_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${datadir}")
      90.  
      91. echo "mysql 数据文件存放位置：" `echo ${datadir_val} | cut -d' ' -f4`
      92.  
      93. echo "========= 连接数配置信息 ==========="
      94.  
      95. max_connections="show variables like 'max_connections';"
      96.  
      97. max_connections_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_connections}")
      98.  
      99. echo "最大连接数（max_connections）：" `echo ${max_connections_val} | cut -d' ' -f4`
      100.  
      101. Max_used_connections="show status like 'Max_used_connections';"
      102.  
      103. Max_used_connections_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Max_used_connections}")
      104.  
      105. echo "当前连接数（Max_used_connections）：" `echo ${Max_used_connections_val} | cut -d' ' -f4`
      106.  
      107. max_connect_errors="show variables like 'max_connect_errors';"
      108.  
      109. max_connect_errors_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_connect_errors}")
      110.  
      111. echo "最大错误连接数（max_connect_errors）：" `echo ${max_connect_errors_val} | cut -d' ' -f4`
      112.  
      113. echo "========= binlog配置信息 ==========="
      114.  
      115. sync_binlog="show variables like 'sync_binlog';"
      116.  
      117. sync_binlog_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${sync_binlog}")
      118.  
      119. echo "sync_binlog(0|1|n，查看是否采用双1模式)：" `echo ${sync_binlog_val} | cut -d' ' -f4`
      120.  
      121. binlog_format="show variables like 'binlog_format';"
      122.  
      123. binlog_format_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${binlog_format}")
      124.  
      125. echo "binlog格式：" `echo ${binlog_format_val} | cut -d' ' -f4`
      126.  
      127. log_bin="show variables like 'log-bin';"
      128.  
      129. log_bin_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${log-bin}")
      130.  
      131. echo "binlog文件（log-bin）：" `echo ${log_bin_val} | cut -d' ' -f4`
      132.  
      133. expire_logs_days="show variables like 'expire_logs_days';"
      134.  
      135. expire_logs_days_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${expire_logs_days}")
      136.  
      137. echo "binlog文件过期时间：" `echo ${expire_logs_days_val} | cut -d' ' -f4`
      138.  
      139. binlog_cache_size="show variables like 'binlog_cache_size';"
      140.  
      141. binlog_cache_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${binlog_cache_size}")
      142.  
      143. echo "binlog_cache_size：" `echo ${binlog_cache_size_val} | cut -d' ' -f4`
      144.  
      145. max_binlog_cache_size="show variables like 'max_binlog_cache_size';"
      146.  
      147. max_binlog_cache_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_binlog_cache_size}")
      148.  
      149. echo "max_binlog_cache_size：" `echo ${max_binlog_cache_size_val} | cut -d' ' -f4`
      150.  
      151. max_binlog_size="show variables like 'max_binlog_size';"
      152.  
      153. max_binlog_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${max_binlog_size}")
      154.  
      155. echo "binlog文件大小：" `echo ${max_binlog_size_val} | cut -d' ' -f4`
      156.  
      157. master_info_repository="show variables like 'master_info_repository';"
      158.  
      159. master_info_repository_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${master_info_repository}")
      160.  
      161. echo "master_info_repository(table|file,建议用table)：" `echo ${master_info_repository_val} | cut -d' ' -f4`
      162.  
      163. relay_log_info_repository="show variables like 'relay_log_info_repository';"
      164.  
      165. relay_log_info_repository_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${relay_log_info_repository}")
      166.  
      167. echo "relay_log_info_repository(table|file,建议用table)：" `echo ${relay_log_info_repository_val} | cut -d' ' -f4`
      168.  
      169. relay_log_recovery="show variables like 'relay_log_recovery';"
      170.  
      171. relay_log_recovery_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${relay_log_recovery}")
      172.  
      173. echo "relay_log_info_repository(建议开启)：" `echo ${relay_log_recovery_val} | cut -d' ' -f4`
      174.  
      175. echo "========= GTID配置信息 ==========="
      176.  
      177. gtid_mode="show variables like 'gtid_mode';"
      178.  
      179. gtid_mode_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${gtid_mode}")
      180.  
      181. echo "是否开启gtid_mode：" `echo ${gtid_mode_val} | cut -d' ' -f4`
      182.  
      183. enforce_gtid_consistency="show variables like 'enforce_gtid_consistency';"
      184.  
      185. enforce_gtid_consistency_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${enforce_gtid_consistency}")
      186.  
      187. echo "enforce_gtid_consistency是否开启：" `echo ${enforce_gtid_consistency_val} | cut -d' ' -f4`
      188.  
      189. echo "（MySQL官方解释说当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporary table语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行）"
      190.  
      191. log_slave_updates="show variables like 'log_slave_updates';"
      192.  
      193. log_slave_updates_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${log_slave_updates}")
      194.  
      195. echo "级联复制是否开启log_slave_updates：" `echo ${log_slave_updates_val} | cut -d' ' -f4`
      196.  
      197. echo "======== innodb配置信息 ========="
      198.  
      199. innodb_data_home_dir="show variables like 'innodb_data_home_dir';"
      200.  
      201. innodb_data_home_dir_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_data_home_dir}")
      202.  
      203. echo "innodb_data_home_dir：" `echo ${innodb_data_home_dir_val} | cut -d' ' -f4`
      204.  
      205. innodb_buffer_pool_size="show variables like 'innodb_buffer_pool_size';"
      206.  
      207. innodb_buffer_pool_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_buffer_pool_size}")
      208.  
      209. echo "innodb_buffer_pool_size（不超过内存的75%）：" `echo ${innodb_buffer_pool_size_val} | cut -d' ' -f4`
      210.  
      211. innodb_buffer_pool_instances="show variables like 'innodb_buffer_pool_instances';"
      212.  
      213. innodb_buffer_pool_instances_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_buffer_pool_instances}")
      214.  
      215. echo "innodb_buffer_pool_instances(innodb_buffer_pool_size小于8G实例个数建议为1)：" `echo ${innodb_buffer_pool_instances_val} | cut -d' ' -f4`
      216.  
      217. innodb_log_file_size="show variables like 'innodb_log_file_size';"
      218.  
      219. innodb_log_file_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_log_file_size}")
      220.  
      221. echo "redo文件的大小innodb_log_file_size：" `echo ${innodb_log_file_size_val} | cut -d' ' -f4`
      222.  
      223. innodb_log_files_in_group="show variables like 'innodb_log_files_in_group';"
      224.  
      225. innodb_log_files_in_group_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_log_files_in_group}")
      226.  
      227. echo "redo文件的个数innodb_log_files_in_group：" `echo ${innodb_log_files_in_group_val} | cut -d' ' -f4`
      228.  
      229. innodb_flush_log_at_trx_commit="show variables like 'innodb_flush_log_at_trx_commit';"
      230.  
      231. innodb_flush_log_at_trx_commit_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_flush_log_at_trx_commit}")
      232.  
      233. echo "innodb_flush_log_at_trx_commit（0|1|2，跟sync_binlog双1）：" `echo ${innodb_flush_log_at_trx_commit_val} | cut -d' ' -f4`
      234.  
      235. innodb_io_capacity="show variables like 'innodb_io_capacity';"
      236.  
      237. innodb_io_capacity_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_io_capacity}")
      238.  
      239. echo "innodb_io_capacity（机械硬盘200，固态2000，闪存20000）：" `echo ${innodb_io_capacity_val} | cut -d' ' -f4`
      240.  
      241. transaction_isolation="show variables like 'transaction_isolation';"
      242.  
      243. transaction_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${transaction_isolation}")
      244.  
      245. echo "隔离级别transaction_isolation：" `echo ${transaction_isolation_val} | cut -d' ' -f4`
      246.  
      247. trx_isolation="show variables like 'tx_isolation';"
      248.  
      249. trx_isolation_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${trx_isolation}")
      250.  
      251. echo "mysql5.6隔离级别trx_isolation：" `echo ${trx_isolation_val} | cut -d' ' -f4`
      252.  
      253. innodb_max_undo_log_size="show variables like 'innodb_max_undo_log_size';"
      254.  
      255. innodb_max_undo_log_size_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_max_undo_log_size}")
      256.  
      257. echo "undo大小innodb_max_undo_log_size：" `echo ${innodb_max_undo_log_size_val} | cut -d' ' -f4`
      258.  
      259. innodb_undo_tablespaces="show variables like 'innodb_undo_tablespaces';"
      260.  
      261. innodb_undo_tablespaces_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${innodb_max_undo_log_size}")
      262.  
      263. echo "undo个数innodb_undo_tablespaces：" `echo ${innodb_undo_tablespaces_val} | cut -d' ' -f4`
      264.  
      265. echo "========= rep配置信息 ==========="
      266.  
      267. slave_parallel_type="show variables like 'slave-parallel-type';"
      268.  
      269. slave_parallel_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_type}")
      270.  
      271. echo "slave复制模式：" `echo ${slave_parallel_type_val} | cut -d' ' -f4`
      272.  
      273. slave_parallel_workers="show variables like 'slave-parallel-workers';"
      274.  
      275. slave_parallel_workers_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_workers}")
      276.  
      277. echo "slave并发复制：" `echo ${slave_parallel_workers_val} | cut -d' ' -f4`
      278.  
      279. echo "================= 内存配置情况 ==============================="
      280.  
      281. mem_dis_1="show variables like 'innodb_buffer_pool_size';"
      282.  
      283. mem_dis_1_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_1}")
      284.  
      285. mem_dis_1_val_1=`echo ${mem_dis_1_val} | cut -d' ' -f4`
      286.  
      287. mem_dis_1_val_2=`echo | awk "{print $mem_dis_1_val_1/1024/1024}"`
      288.  
      289. echo "InnoDB 数据和索引缓存：" $mem_dis_1_val_1
      290.  
      291. mem_dis_2="show variables like 'innodb_log_buffer_size';"
      292.  
      293. mem_dis_2_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_2}")
      294.  
      295. mem_dis_2_val_1=`echo ${mem_dis_2_val} | cut -d' ' -f4`
      296.  
      297. mem_dis_2_val_2=`echo | awk "{print $mem_dis_2_val_1/1024/1024}"`
      298.  
      299. echo "InnoDB 日志缓冲区：" $mem_dis_2_val_1
      300.  
      301. mem_dis_3="show variables like 'binlog_cache_size';"
      302.  
      303. mem_dis_3_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_3}")
      304.  
      305. mem_dis_3_val_1=`echo ${mem_dis_3_val} | cut -d' ' -f4`
      306.  
      307. mem_dis_3_val_2=`echo | awk "{print $mem_dis_3_val_1/1024/1024}"`
      308.  
      309. echo "二进制日志缓冲区：" $mem_dis_3_val_1
      310.  
      311. mem_dis_4="show variables like 'thread_cache_size';"
      312.  
      313. mem_dis_4_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_4}")
      314.  
      315. echo "连接线程缓存：" `echo $mem_dis_4_val | cut -d' ' -f4`
      316.  
      317. mem_dis_5="show variables like 'query_cache_size';"
      318.  
      319. mem_dis_5_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_5}")
      320.  
      321. echo "查询缓存：" `echo ${mem_dis_5_val} | cut -d' ' -f4`
      322.  
      323. mem_dis_6="show variables like 'table_open_cache';"
      324.  
      325. mem_dis_6_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_6}")
      326.  
      327. echo "表缓存：" `echo ${mem_dis_6_val} | cut -d' ' -f4`
      328.  
      329. mem_dis_7="show variables like 'table_definition_cache';"
      330.  
      331. mem_dis_7_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_7}")
      332.  
      333. echo "表定义缓存：" `echo ${mem_dis_7_val} | cut -d' ' -f4`
      334.  
      335. mem_dis_8="show variables like 'max_connections';"
      336.  
      337. mem_dis_8_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_8}")
      338.  
      339. echo "最大线程数：" `echo ${mem_dis_8_val} | cut -d' ' -f4`
      340.  
      341. mem_dis_9="show variables like 'thread_stack';"
      342.  
      343. mem_dis_9_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_9}")
      344.  
      345. echo "线程栈信息使用内存：" `echo ${mem_dis_9_val} | cut -d' ' -f4`
      346.  
      347. mem_dis_10="show variables like 'sort_buffer_size';"
      348.  
      349. mem_dis_10_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_10}")
      350.  
      351. echo "排序使用内存：" `echo ${mem_dis_10_val} | cut -d' ' -f4`
      352.  
      353. mem_dis_11="show variables like 'join_buffer_size';"
      354.  
      355. mem_dis_11_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_11}")
      356.  
      357. echo "Join操作使用内存：" `echo ${mem_dis_11_val} | cut -d' ' -f4`
      358.  
      359. mem_dis_12="show variables like 'read_buffer_size';"
      360.  
      361. mem_dis_12_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_12}")
      362.  
      363. echo "顺序读取数据缓冲区使用内存：" `echo ${mem_dis_12_val} | cut -d' ' -f4`
      364.  
      365. mem_dis_13="show variables like 'read_rnd_buffer_size';"
      366.  
      367. mem_dis_13_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_13}")
      368.  
      369. echo "随机读取数据缓冲区使用内存：" `echo ${mem_dis_13_val} | cut -d' ' -f4`
      370.  
      371. mem_dis_14="show variables like 'tmp_table_size';"
      372.  
      373. mem_dis_14_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${mem_dis_14}")
      374.  
      375. echo "临时表使用内存：" `echo ${mem_dis_14_val} | cut -d' ' -f4`
      376.  
      377. echo "================= QPS ==============================="
      378.  
      379. Questions1="show global status like 'Questions';"
      380.  
      381. Questions1_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Questions1}")
      382.  
      383. sleep 1
      384.  
      385. Questions2="show global status like 'Questions';"
      386.  
      387. Questions2_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Questions2}")
      388.  
      389. echo "QPS：$((`echo ${Questions2_val} | cut -d' ' -f4`-`echo ${Questions1_val} | cut -d' ' -f4`))"
      390.  
      391. echo "================= TPS ==============================="
      392.  
      393. Com_commit="show  global status like 'Com_commit';"
      394.  
      395. Com_commit_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Com_commit}")
      396.  
      397. Com_rollback="show global status like 'Com_rollback';"
      398.  
      399. Com_rollback_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${Com_rollback}")
      400.  
      401. echo "TPS：" $((`echo ${Com_commit_val} | cut -d' ' -f4` + `echo ${Com_rollback_val} | cut -d' ' -f4`))
      402.  
      403. ##缓存命中率##
      404.  
      405. echo "================= 缓存命中情况 ==============================="
      406.  
      407. cache_hits="show global status like 'QCache_hits';"
      408.  
      409. hits=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_hits}")
      410.  
      411. hits_val=`echo ${hits} | cut -d' ' -f4`
      412.  
      413. echo "缓存命中次数：" ${hits_val}
      414.  
      415. cache_not_hits="show global status like  'Qcache_inserts';"
      416.  
      417. not_hits=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${cache_not_hits}")
      418.  
      419. not_hits_val=`echo ${not_hits} | cut -d' ' -f4`
      420.  
      421. echo "缓存未命中次数：" ${not_hits_val}
      422.  
      423. cache_hits_rate_1=$(($hits_val - $not_hits_val))
      424.  
      425. cache_hits_rate_2=`echo | awk "{print $cache_hits_rate_1/$hits_val * 100}"`
      426.  
      427. echo "缓存命中率：" ${cache_hits_rate_2} "%"
      428.  
      429. echo "================= 主从复制 ============================="
      430.  
      431. slave_parallel_type="show variables like 'slave-parallel-type';"
      432.  
      433. slave_parallel_type_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_type}")
      434.  
      435. echo "slave复制模式：" `echo ${slave_parallel_type_val} | cut -d' ' -f4`
      436.  
      437. slave_parallel_workers="show variables like 'slave-parallel-workers';"
      438.  
      439. slave_parallel_workers_val=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${slave_parallel_workers}")
      440.  
      441. echo "slave并发复制：" `echo ${slave_parallel_workers_val} | cut -d' ' -f4`
      442.  
      443. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show master status\G;"
      444.  
      445. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show slave status\G;"
      446.  
      447. echo "================= 半同步复制 ==============================="
      448.  
      449. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like '%semi%';"
      450.  
      451. echo "================= 慢查询 ==============================="
      452.  
      453. slow_query_log_file=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'slow_query_log_file';"|grep 'slow'|awk '{print $2}')
      454.  
      455. slow_query_log=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'slow_query_log';"|grep 'slow'|awk '{print $2}')
      456.  
      457. long_query_time=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'long_query_time';"|grep 'long_query_time'|awk '{print $2}')
      458.  
      459. log_queries_not_using_indexes=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'log_queries_not_using_indexes';"|grep 'log_queries_not_using_indexes'|awk '{print $2}')
      460.  
      461. if [ ${slow_query_log} == "ON" ];then
      462.  
      463. echo "慢查询状态(slow_query_log)：${slow_query_log} ;long_query_time(s) : ${long_query_time};log_queries_not_using_indexes: ${log_queries_not_using_indexes};慢查询top10，如下："
      464.  
      465. mysqldumpslow -s c -t 10 ${slow_query_log_file};
      466.  
      467. else
      468.  
      469.     echo "慢查询状态(slow_query_log)：${slow_query_log} ，未开启慢查询。"
      470.  
      471. fi
      472.  
      473. ##等待事件##
      474.  
      475. echo "================= 数据库大小 ==============================="
      476.  
      477. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT
      479. table_schema,
      481. sum( data_length )/ 1024 / 1024 / 1024 AS data_length,
      483. sum( index_length )/ 1024 / 1024 / 1024 AS index_length,
      485. sum( data_length + index_length )/ 1024 / 1024 / 1024 AS sum_data_index
      487. FROM
      489. information_schema.TABLES
      491. WHERE
      493. table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys' )
      495. GROUP BY
      497. table_schema;"
      498.  
      499. echo "================= 数据碎片 ==============================="
      500.  
      501. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT
      503. TABLE_SCHEMA,
      505. TABLE_NAME,
      507. ENGINE,
      509. concat( splinter, 'G' ) '碎片(G)'
      511. FROM
      513. (
      515. SELECT
      517. TABLE_SCHEMA,
      519. TABLE_NAME,
      521. ENGINE,
      523. ROUND(( DATA_LENGTH + INDEX_LENGTH - TABLE_ROWS * AVG_ROW_LENGTH )/ 1024 / 1024 / 1024 ) splinter
      525. FROM
      527. information_schema.TABLES
      529. WHERE
      531. TABLE_TYPE = 'BASE TABLE'
      533. ) a
      535. WHERE
      537. splinter > 1
      539. ORDER BY
      541. splinter DESC;"
      542.  
      543. echo "================= 锁查询 ==============================="
      544.  
      545. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "SELECT
      547. r.trx_isolation_level,
      549. r.trx_id waiting_trx_id,
      551. r.trx_mysql_thread_id waiting_trx_thread,
      553. r.trx_state waiting_trx_state,
      555. lr.lock_mode waiting_trx_lock_mode,
      557. lr.lock_type waiting_trx_lock_type,
      559. lr.lock_table waiting_trx_lock_table,
      561. lr.lock_index waiting_trx_lock_index,
      563. r.trx_query waiting_trx_query,
      565. b.trx_id blocking_trx_id,
      567. b.trx_mysql_thread_id blocking_trx_thread,
      569. b.trx_state blocking_trx_state,
      571. lb.lock_mode blocking_trx_lock_mode,
      573. lb.lock_type blocking_trx_lock_type,
      575. lb.lock_table blocking_trx_lock_table,
      577. lb.lock_index blocking_trx_lock_index,
      579. b.trx_query blocking_query
      581. FROM
      583. information_schema.innodb_lock_waits w
      585. INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
      587. INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
      589. INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id = w.blocking_trx_id
      591. INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id = w.requesting_trx_id \G;"
      592.  
      593. echo "================= 等待事件 ==============================="
      594.  
      595. top_event_10="select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where count_star > 0 order by sum_timer_wait desc limit 10;"
      596.  
      597. echo "等待事件 TOP 10："
      598.  
      599. ${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "${top_event_10}"
      600.  
      601. echo "==================最近一周的错误日志 =========================="
      602.  
      603. _time=$(date -d '6 days ago' +%Y-%m-%d)\|$(date -d '5 days ago' +%Y-%m-%d)\|$(date -d '4 days ago' +%Y-%m-%d)\|$(date -d '3 days ago' +%Y-%m-%d)\|$(date -d '2 days ago' +%Y-%m-%d)\|$(date -d '1 days ago' +%Y-%m-%d)\|$(date -d '0 days ago' +%Y-%m-%d)
      604.  
      605. log_error=$(${base}/bin/mysql -h${host} -u${userName} -p${password} ${dbname} -P${port} -e "show variables like 'log_error';"|grep  'log_error'|awk '{print $2}')
      606.  
      607. #grep -i -E 'error' /home/logs/mysql/mysqld.err* | grep -E '2019-03-28|2019-06-14'
      608.  
      609. grep -i -E "error" ${log_error}| grep -E "${_time}"

      该脚本可以放在任意位置，然后赋予X执行权限进行一键巡检，同时也可以设置定时任务，定时进行巡检并输出相关报告。

      ./mysql_check.sh>>/data/check.txt 2>&1

  • 相关阅读:
    SpringMVC 07: WEB-INF下的资源访问 + SpringMVC拦截器
    illustrator一些报错的原因
    mysql面试题20：有哪些合适的分布式主键方案
    国产音频放大器工作原理以及应用领域
    UEFI实战——显示进度条
    快速提高编码生产力——中国用户如何使用Jetbrains内置的AI助手
    学编程的第十八天
    如何用jxTMS开发一个功能（六）
    浅谈DAO智能合约DAPP模式系统开发技术及程序代码分析原理
    Win10/Win11日历提醒与手机日历同步互联
  • 原文地址：https://blog.csdn.net/weixin_45623111/article/details/139316634