【重磅更新】MySQL数据库巡检脚本英文版来啦

AiDBA宝典

发布于 2026-03-26 14:20:53

250

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

字数 2951，阅读大约需 15 分钟

1、巡检脚本简介

该套数据库巡检脚本多数为纯SQL脚本开发，如下所示：

• 目前一共包含**30+**个脚本，若脚本的扩展名为“.sql”则表示该脚本为sql脚本；若脚本的扩展名为“.pl”则表示该脚本为perl脚本；若脚本的扩展名为“.sh”则表示该脚本为shell脚本。
• 对于Oracle的SQL脚本而言，脚本DB_Oracle_HC_lhr_v7.0.0_10g.sql适用于Oracle 10g数据库，脚本DB_Oracle_HC_lhr_v7.0.0_11g.sql适用于Oracle 11g的数据库，脚本DB_Oracle_HC_lhr_v7.0.0_12c.sql适用于Oracle 12c及其以上版本，这3个脚本都是只读版本，这3个脚本只会对数据库做查询操作，不会做DML和DDL操作，这也是很多朋友所期待的功能。另外，由于个别SQL对某些库运行缓慢，故开发了针对Oracle的精简版，若脚本运行缓慢，大家可以考虑使用精简版来巡检！！！
• 脚本DB_OS_HC_lhr_v7.0.0.pl是perl脚本，执行后会对OS的信息进行收集，并且输出到html中。脚本DB_OS_HC_lhr_v7.0.0.sh是shell脚本，执行后会对OS的信息进行收集。
• 脚本DB_MySQL_HC_lhr_v7.0.0.sql是MySQL脚本，执行后会产生MySQL的健康检查html报告，该脚本为只读脚本。该脚本也适用于mariadb数据库。
• 脚本DB_SQLServer_HC_lhr_v7.0.0.sql是SQL Server脚本，执行后会产生SQL Server的健康检查html报告。而历史脚本DB_SQLServer_HC_lhr_v7.0.0_2005.sql和DB_SQLServer_HC_lhr_v7.0.0_2008R2.sql是SQL Server脚本，存在部分DDL和DML操作，执行后会产生SQL Server的健康检查html报告。脚本DB_SQLServer_HC_lhr_v7.0.0_2005.sql最低支持2005版本，而脚本DB_SQLServer_HC_lhr_v7.0.0_2008R2.sql最低支持2008R2版本，而脚本DB_SQLServer_HC_lhr_v7.0.0_2016.sql最低支持2016版本。历史脚本不再更新。
• 脚本DB_PG_HC_lhr_v7.0.0.sql是PG数据库脚本，执行后会产生PostgreSQL数据库的健康检查html报告。
• 脚本DB_GP_HC_lhr_v7.0.0.sql是Greenplum数据库脚本，执行后会产生Greenplum数据库的健康检查html报告。
• 脚本DB_DM_HC_lhr_v7.0.0是达梦数据库脚本，执行后会产生达梦数据库的健康检查html报告。
• 脚本DB_TiDB_HC_lhr_v7.0.0.sql是TiDB数据库脚本，执行后会产生TiDB数据库的健康检查html报告。
• 脚本DB_openGauss_HC_lhr_v7.0.0.sql是openGauss数据库脚本，执行后会产生openGauss数据库的健康检查html报告。
• 脚本DB_DB2_HC_lhr_v7.0.0.sql是DB2数据库脚本，执行后会产生DB2数据库的健康检查html报告。
• 脚本DB_OceanBase_HC_lhr_v7.0.0.sql是DB2数据库脚本，执行后会产生OceanBase数据库的健康检查html报告。
• 脚本DB_kingbase_HC_lhr_v7.0.0.sql是人大金仓OLTP数据库KES脚本，执行后会产生人大金仓数据库KES版的健康检查html报告。
• 脚本DB_kingbase_kadb_HC_lhr_v7.0.0.sql是人大金仓分析型数据库KingbaseAnalyticsDB及KADB数据库脚本，执行后会产生人大金仓KADB数据库的健康检查html报告。
• 脚本DB_GreatDB_HC_lhr_v7.0.0.sql是万里GreatDB数据库脚本，执行后会产生万里GreatDB数据库的健康检查html报告。
• 脚本DB_TBase_HC_lhr_v7.0.0.sql是TDSQL_for_PG（原 TBase）数据库脚本，执行后会产生TDSQL_for_PG（原 TBase）数据库的健康检查html报告。
• 脚本DB_MogDB_HC_lhr_v7.0.0.sql是MogDB数据库脚本，执行后会产生云和恩墨MogDB数据库的健康检查html报告。
• 脚本DB_OushuDB_HC_lhr_v7.0.0.sql是OushuDB数据库脚本，执行后会产生OushuDB数据库的健康检查html报告。
• 脚本DB_PolarDB_for_PG_HC_lhr_v7.0.0.sql是PolarDB_for_PG数据库脚本，执行后会产生PolarDB_for_PG数据库的健康检查html报告。
• 脚本DB_OSCAR_HC_lhr_v7.0.0.sql是神舟通用数据库脚本，执行后会产生神通数据库的健康检查html报告。
• 脚本DB_GBase8a_HC_lhr_v7.0.0.sql是南大通用GBase 8a数据库脚本，执行后会产生南大通用GBase 8a数据库的健康检查html报告。
• 脚本DB_GBase8c_HC_lhr_v7.0.0.sql是南大通用GBase 8c数据库脚本，执行后会产生南大通用GBase 8c数据库的健康检查html报告。
• 脚本DB_StarRocks_HC_lhr_v7.0.0.sql是StarRocks数据库脚本，执行后会产生StarRocks数据库的健康检查html报告。
• 脚本DB_YMatrix_HC_lhr_v7.0.0.sql是matrixdb数据库脚本，执行后会产生YMatrix数据库的健康检查html报告。
• 脚本DB_YashanDB_HC_lhr_v7.0.0.sql是YashanDB数据库脚本，执行后会产生崖山数据库的健康检查html报告。

2、巡检脚本特点

1、可以巡检Oracle、MySQL（包括Mariadb）、SQL Server、PostgreSQL、Greenplum、OceanBase、TiDB、openGauss、DB2、达梦、人大金仓（KES+KADB）、万里GreatDB、TDSQL_for_PG（原 TBase）、MogDB、OushuDB、PolarDB_for_PG、神通、南大通用GBase 8a+8c、StarRocks、YMatrix、YashanDB等21种数据库，也可以巡检Linux操作系统（后续会免费逐步增加MongoDB、Redis等数据库） 2、脚本为绿色版、免安装、纯SQL文本 3、跨平台，只要有sqlplus（Oracle）、mysql（MySQL、Mariadb、TiDB、OceanBase、万里GreatDB、南大通用GBase 8a、StarRocks）、sqlcmd（SSMS、Navicat皆可）、psql（PG、Greenplum、openGauss、人大金仓KES+KADB、TBase、MogDB、OushuDB、PolarDB_for_PG、南大通用GBase 8c、YMatrix）、gsql（openGauss、GaussDB、MogDB、南大通用GBase 8c）、disql（国产达梦）、db2（DB2数据库）、obclient（OceanBase）、ksql(人大金仓KES)、isql(神舟通用)、gccli（南大通用GBase 8a）、yasql（崖山数据库）环境即可运行脚本 4、脚本内容可视化，可以看到脚本内容，因此可供学习数据库使用 5、兼容Oracle 10g、11g、12c、18c、19c、21c、23ai等高版本Oracle数据库 6、对Oracle 10g、11g、12c、18c、19c、21c、23ai等版本分别提供了只读版（只对数据库查询，不做DDL和DML操作） 7、MySQL最低支持5.5版本，小于5.5版本未进行测试 8、SQL Server最低支持2005版本 9、增删监控项非常方便，只需要提供相关SQL即可 10、一次购买，所有脚本终身免费升级 11、检查内容非常全面 12、针对每种数据库，只有1个SQL脚本，不存在嵌套调用脚本等其它问题 13、最终生成html文件格式的健康检查结果 14、Oracle数据库对巡检结果进行过滤，列出了数据库有问题的内容 15、对OS的信息提供了收集（单独脚本） 16、对Oracle、MySQL、SQL Server、PG、GreenPlum提供英文版

3、MySQL（Mariadb）数据库运行方式

只要有mysql客户端即可，使用MariaDB的客户端也可以，如下：

mysql -uroot -plhr -P3306 -h 192.168.8.8  -s -f <  DB_MySQL_HC_lhr_v7.0.0.sql  > lhr_mysql_check.html

该脚本也适用于mariadb数据库。

注意：

由于版本问题，如下的报错请忽略，不影响最终的结果：

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.8.8-P3415 -s -f < D:\DB_MySQL_HC_lhr_v7.0.0.sql> d:\tmp.html
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1193 (HY000) at line 159: Unknownsystem variable 'show_compatibility_56'
ERROR 1146 (42S02) at line 205: Table'performance_schema.global_variables' doesn't exist
ERROR 1193 (HY000) at line 370: Unknown system variable 'innodb_page_size'
ERROR 1146 (42S02) at line 509: Table 'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 546: Table'performance_schema.hosts' doesn't exist
ERROR 1054 (42S22) at line 634: Unknown column 'a.type' in 'where clause'
ERROR 1054 (42S22) at line 662: Unknown column 'a.type' in 'where clause'
ERROR 1054 (42S22) at line 732: Unknown column 'trx_is_read_only' in 'field list'
ERROR 1146 (42S02) at line 811: Table 'performance_schema.metadata_locks' doesn't exist
ERROR 1146 (42S02) at line 872: Table'performance_schema.metadata_locks' doesn't exist
ERROR 1146 (42S02) at line 905: Table 'sys.processlist' doesn't exist
ERROR 1146 (42S02) at line 919: Table'sys.schema_table_lock_waits' doesn't exist
ERROR 1146 (42S02) at line 940: Table 'performance_schema.global_status' doesn't exist
ERROR 1146 (42S02) at line 979: Table'performance_schema.events_stages_current' doesn't exist
ERROR 1146 (42S02) at line 995: Table 'performance_schema.events_statements_summary_by_digest' doesn't exist
ERROR 1146 (42S02) at line 1027: Table'sys.session' doesn't exist
ERROR 1146 (42S02) at line 1040: Table 'sys.session' doesn't exist
ERROR 1146 (42S02) at line 1053: Table'performance_schema.events_statements_summary_by_digest' doesn't exist
ERROR 1146 (42S02) at line 1080: Table 'sys.statement_analysis' doesn't exist
ERROR 1146 (42S02) at line 1096: Table'performance_schema.events_statements_summary_by_digest' doesn't exist
ERROR 1146 (42S02) at line 1149: Table 'sys.statement_analysis' doesn't exist
ERROR 1146 (42S02) at line 1165: Table'performance_schema.table_io_waits_summary_by_index_usage' doesn't exist
ERROR 1146 (42S02) at line 1187: Table 'performance_schema.events_statements_summary_by_digest' doesn't exist
ERROR 1146 (42S02) at line 1222: Table'performance_schema.events_statements_summary_by_digest' doesn't exist
ERROR 1146 (42S02) at line 1276: Table 'sys.schema_unused_indexes' doesn't exist
ERROR 1146 (42S02) at line 1295: Table'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 1349: Table 'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 1386: Table'performance_schema.global_status' doesn't exist
ERROR 1054 (42S22) at line 1411: Unknown column 'a.PROCESSLIST_COMMAND' in 'where clause'
ERROR 1146 (42S02) at line 1470: Table 'performance_schema.replication_group_members' doesn't exist
ERROR 1146 (42S02) at line 1514: Table'performance_schema.replication_connection_configuration' doesn't exist
ERROR 1146 (42S02) at line 1539: Table 'performance_schema.clone_status' doesn't exist
ERROR 1146 (42S02) at line 1550: Table'performance_schema.clone_progress' doesn't exist
ERROR 1146 (42S02) at line 1619: Table 'performance_schema.global_status' doesn't exist

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.8.8-P3416 -s -f < D:\DB_MySQL_HC_lhr_v7.0.0.sql> d:\tmp.html
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1193 (HY000) at line 159: Unknownsystem variable 'show_compatibility_56'
ERROR 1146 (42S02) at line 205: Table'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 509: Table 'performance_schema.global_variables' doesn't exist
ERROR 1054 (42S22) at line 634: Unknowncolumn'HISTORY'in'field list'
ERROR 1054 (42S22) at line 662: Unknowncolumn'HISTORY'in'field list'
ERROR 1146 (42S02) at line 811: Table'performance_schema.metadata_locks' doesn't exist
ERROR 1146 (42S02) at line 872: Table 'performance_schema.metadata_locks' doesn't exist
ERROR 1146 (42S02) at line 905: Table'sys.processlist' doesn't exist
ERROR 1146 (42S02) at line 919: Table 'sys.schema_table_lock_waits' doesn't exist
ERROR 1146 (42S02) at line 940: Table'performance_schema.global_status' doesn't exist
ERROR 1054 (42S22) at line 979: Unknown column 'WORK_COMPLETED' in 'field list'
ERROR 1146 (42S02) at line 995: Table 'sys.x$ps_digest_95th_percentile_by_avg_us' doesn't exist
ERROR 1146 (42S02) at line 1027: Table'sys.session' doesn't exist
ERROR 1146 (42S02) at line 1040: Table 'sys.session' doesn't exist
ERROR 1305 (42000) at line 1053: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1080: Table'sys.statement_analysis' doesn't exist
ERROR 1305 (42000) at line 1096: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1149: Table 'sys.statement_analysis' doesn't exist
ERROR 1305 (42000) at line 1165: FUNCTION sys.format_time does not exist
ERROR 1305 (42000) at line 1187: FUNCTION sys.format_statement does not exist
ERROR 1305 (42000) at line 1222: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1276: Table'sys.schema_unused_indexes' doesn't exist
ERROR 1146 (42S02) at line 1349: Table 'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 1386: Table'performance_schema.global_status' doesn't exist
ERROR 1054 (42S22) at line 1411: Unknown column 'HISTORY' in 'field list'
ERROR 1146 (42S02) at line 1470: Table 'performance_schema.replication_group_members' doesn't exist
ERROR 1146 (42S02) at line 1514: Table'performance_schema.replication_connection_configuration' doesn't exist
ERROR 1146 (42S02) at line 1539: Table 'performance_schema.clone_status' doesn't exist
ERROR 1146 (42S02) at line 1550: Table'performance_schema.clone_progress' doesn't exist
ERROR 1146 (42S02) at line 1619: Table 'performance_schema.global_status' doesn't exist

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.8.8-P3418 -s -f < D:\DB_MySQL_HC_lhr_v7.0.0.sql> d:\tmp.html
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1193 (HY000) at line 159: Unknownsystem variable 'show_compatibility_56'
ERROR 1109 (42S02) at line 214: Unknowntable'GLOBAL_VARIABLES'in information_schema
ERROR 1109 (42S02) at line 518: Unknowntable'GLOBAL_VARIABLES'in information_schema
ERROR 1109 (42S02) at line 699: Unknowntable'INNODB_LOCKS'in information_schema
ERROR 1109 (42S02) at line 719: Unknowntable'INNODB_LOCK_WAITS'in information_schema
ERROR 1109 (42S02) at line 747: Unknowntable'INNODB_LOCK_WAITS'in information_schema
ERROR 1109 (42S02) at line 949: Unknowntable'GLOBAL_STATUS'in information_schema
ERROR 1109 (42S02) at line 1358: Unknowntable'GLOBAL_VARIABLES'in information_schema
ERROR 1109 (42S02) at line 1395: Unknowntable'GLOBAL_STATUS'in information_schema
ERROR 1146 (42S02) at line 1539: Table'performance_schema.clone_status' doesn't exist
ERROR 1146 (42S02) at line 1550: Table 'performance_schema.clone_progress' doesn't exist
ERROR 1109 (42S02) at line 1628: Unknowntable'GLOBAL_STATUS'in information_schema

C:\Users\lhrxxt> mysql -uroot -plhr -h192.168.8.8-P3419 -s -f < DB_MySQL_HC_lhr_v7.0.0.sql> mariadb.html
ERROR 1193 (HY000) at line 159: Unknownsystem variable 'show_compatibility_56'
ERROR 1146 (42S02) at line 205: Table'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 509: Table 'performance_schema.global_variables' doesn't exist
ERROR 1054 (42S22) at line 732: Unknowncolumn'trx_adaptive_hash_latched'in'field list'
ERROR 1146 (42S02) at line 905: Table'sys.processlist' doesn't exist
ERROR 1146 (42S02) at line 919: Table 'sys.schema_table_lock_waits' doesn't exist
ERROR 1146 (42S02) at line 995: Table'sys.x$ps_digest_95th_percentile_by_avg_us' doesn't exist
ERROR 1146 (42S02) at line 1027: Table 'sys.session' doesn't exist
ERROR 1146 (42S02) at line 1040: Table'sys.session' doesn't exist
ERROR 1305 (42000) at line 1053: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1080: Table 'sys.statement_analysis' doesn't exist
ERROR 1305 (42000) at line 1096: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1149: Table'sys.statement_analysis' doesn't exist
ERROR 1305 (42000) at line 1165: FUNCTION sys.format_time does not exist
ERROR 1305 (42000) at line 1187: FUNCTION sys.format_statement does not exist
ERROR 1305 (42000) at line 1222: FUNCTION sys.format_statement does not exist
ERROR 1146 (42S02) at line 1276: Table 'sys.schema_unused_indexes' doesn't exist
ERROR 1146 (42S02) at line 1349: Table'performance_schema.global_variables' doesn't exist
ERROR 1146 (42S02) at line 1470: Table 'performance_schema.replication_group_members' doesn't exist
ERROR 1146 (42S02) at line 1514: Table'performance_schema.replication_connection_status' doesn't exist
ERROR 1146 (42S02) at line 1539: Table 'performance_schema.clone_status' doesn't exist
ERROR 1146 (42S02) at line 1550: Table'performance_schema.clone_progress' doesn't exist

C:\Users\lhr>mysql -uroot -plhr -h192.168.8.8 -P3420 -s -f < DB_MySQL_HC_lhr_v7.0.0.sql  > lhr_mysql_check.html
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1193 (HY000) at line 158: Unknown system variable 'show_compatibility_56'
ERROR 1109 (42S02) at line 213: Unknown table 'GLOBAL_VARIABLES' in information_schema
ERROR 1109 (42S02) at line 521: Unknown table 'GLOBAL_VARIABLES' in information_schema
ERROR 1109 (42S02) at line 701: Unknown table 'INNODB_LOCKS' in information_schema
ERROR 1109 (42S02) at line 721: Unknown table 'INNODB_LOCK_WAITS' in information_schema
ERROR 1109 (42S02) at line 749: Unknown table 'INNODB_LOCK_WAITS' in information_schema
ERROR 1109 (42S02) at line 951: Unknown table 'GLOBAL_STATUS' in information_schema
ERROR 1109 (42S02) at line 1362: Unknown table 'GLOBAL_VARIABLES' in information_schema
ERROR 1109 (42S02) at line 1399: Unknown table 'GLOBAL_STATUS' in information_schema
ERROR 1064 (42000) at line 1464: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave hosts' at line 1
ERROR 1064 (42000) at line 1504: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master status' at line 1
ERROR 1064 (42000) at line 1518: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave status' at line 1
ERROR 1146 (42S02) at line 1553: Table 'performance_schema.clone_status' doesn't exist
ERROR 1146 (42S02) at line 1564: Table'performance_schema.clone_progress' doesn't exist
ERROR 1109 (42S02) at line 1642: Unknown table 'GLOBAL_STATUS' in information_schema