MySQL Report

巡检人:小麦苗　QQ:646634621　微信公众号：DB宝　提供OCP、OCM、高可用（rac+dg）、PostgreSQL和MySQL培训　BLOG地址: https://www.dbaup.com 若需要脚本可私聊我

MySQL数据库巡检报告

巡检人：小麦苗([QQ：646634621] [微信公众号：DB宝] [提供OCP、OCM、高可用、MySQL和PostgreSQL最实用的培训])
巡检时间：
版本号：v7.0.0
修改日期：2021-10-18 18:28:00

总体概况	数据库基本信息数据库的总体概况、版本、主机情况、数据库负载情况、数据库属性等	所有数据库及其容量大小当前数据库实例的所有数据库及其容量大小	查看数据库的运行状态	占用空间最大的前10张大表	所有存储引擎列表当前数据库实例的所有存储引擎列表
总体概况	存储引擎和DB的数量关系	查询所有用户	查询MySQL支持的所有字符集	一些重要的参数
锁情况	查询所有线程排除sleep线程	sleep线程TOP20	有多少线程正在使用表	InnoDB存储引擎的运行时信息查询InnoDB存储引擎的运行时信息，包括死锁的详细信息	元数据锁的相关信息
SQL部分	跟踪长时间操作的进度	平均执行时间值大于95%的平均执行时间的语句查看平均执行时间值大于95%的平均执行时间的语句（可近似地认为是平均执行时间超长的语句），默认情况下按照语句平均延迟(执行时间)降序排序	使用了临时表的语句查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序	查看执行了文件排序的语句默认情况下按照语句总延迟时间（执行时间）降序排序	查询SQL的整体消耗百分比
SQL部分	执行次数Top10	使用全表扫描的SQL语句使用全表扫描的SQL语句	没有使用到最优索引的语句查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认情况下按照全表扫描次数与语句总次数百分比和语句总延迟时间(执行时间)降序排序	产生错误或警告的语句查看产生错误或警告的语句，默认情况下，按照错误数量和警告数量降序排序
索引部分	冗余索引	无效索引（从未使用过的索引）	索引区分度区分度，越接近1，表示区分度越高，低于0.1，则说明区分度较差，开发者应该重新评估SQL语句涉及的字段，选择区分度高的多个字段创建索引
主从情况 MySQL Replication（MySQL主从复制）	主从复制涉及到的重要参数	主从库线程主从库线程查询	二进制日志	主库状态监测	备库状态监测
数据库性能	性能参数统计
其它	setup_consumers	自增ID的使用	无主键或唯一键的表

数据库总体概况

● 数据库基本信息
now_date user CURRENT_USER1 CONNECTION_ID db_name Server_version all_db_size_MB all_datafile_size_MB datadir SOCKET log_error autocommit log_bin server_id

2021-10-30 19:25:21 root@123.139.22.10 root@% 13550798 5.7.33-2-log 24.42 184.00 /var/lib/mysql/data/ /tmp/mysql.sock /var/lib/mysql/data/error.log 1 1 4030504773

now_date	user	CURRENT_USER1	CONNECTION_ID	db_name	Server_version	all_db_size_MB	all_datafile_size_MB	datadir	SOCKET	log_error	autocommit	log_bin	server_id
2021-10-30 19:25:21	root@123.139.22.10	root@%	13550798		5.7.33-2-log	24.42	184.00	/var/lib/mysql/data/	/tmp/mysql.sock	/var/lib/mysql/data/error.log	1	1	4030504773

● 版本信息
version_comment MySQL Community Server - (GPL) version_compile_machine x86_64 version_compile_os Linux

● 插件信息
PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_TYPE_VERSION PLUGIN_LIBRARY PLUGIN_LIBRARY_VERSION PLUGIN_AUTHOR PLUGIN_DESCRIPTION PLUGIN_LICENSE LOAD_OPTION

binlog 1.0 ACTIVE STORAGE ENGINE 50733.0 MySQL AB This is a pseudo storage engine to represent the binlog in a transaction GPL FORCE

mysql_native_password 1.1 ACTIVE AUTHENTICATION 1.1 R.J.Silk, Sergei Golubchik Native MySQL authentication GPL FORCE

sha256_password 1.1 ACTIVE AUTHENTICATION 1.1 Oracle SHA256 password authentication GPL FORCE

CSV 1.0 ACTIVE STORAGE ENGINE 50733.0 Brian Aker, MySQL AB CSV storage engine GPL FORCE

MEMORY 1.0 ACTIVE STORAGE ENGINE 50733.0 MySQL AB Hash based, stored in memory, useful for temporary tables GPL FORCE

InnoDB 5.7 ACTIVE STORAGE ENGINE 50733.0 Oracle Corporation Supports transactions, row-level locking, and foreign keys GPL FORCE

INNODB_RSEG 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Huawei InnoDB rollback segment information GPL FORCE

INNODB_TRX 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB transactions GPL FORCE

INNODB_ALTER_TABLE_PROGRESS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Huawei Display ALTER TABLE progress GPL FORCE

INNODB_LOCKS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB conflicting locks GPL FORCE

INNODB_LOCK_WAITS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB which lock is blocking which GPL FORCE

INNODB_CMP 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compression GPL FORCE

INNODB_CMP_RESET 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compression; reset cumulated counts GPL FORCE

INNODB_CMPMEM 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compressed buffer pool GPL FORCE

INNODB_CMPMEM_RESET 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compressed buffer pool; reset cumulated counts GPL FORCE

INNODB_CMP_PER_INDEX 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compression (per index) GPL FORCE

INNODB_CMP_PER_INDEX_RESET 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Statistics for the InnoDB compression (per index); reset cumulated counts GPL FORCE

INNODB_BUFFER_PAGE 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB Buffer Page Information GPL FORCE

INNODB_BUFFER_PAGE_LRU 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB Buffer Page in LRU GPL FORCE

INNODB_BUFFER_POOL_STATS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB Buffer Pool Statistics Information GPL FORCE

INNODB_TEMP_TABLE_INFO 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB Temp Table Stats GPL FORCE

INNODB_METRICS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB Metrics Info GPL FORCE

INNODB_FT_DEFAULT_STOPWORD 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation Default stopword list for InnDB Full Text Search GPL FORCE

INNODB_FT_DELETED 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation INNODB AUXILIARY FTS DELETED TABLE GPL FORCE

INNODB_FT_BEING_DELETED 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation INNODB AUXILIARY FTS BEING DELETED TABLE GPL FORCE

INNODB_FT_CONFIG 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation INNODB AUXILIARY FTS CONFIG TABLE GPL FORCE

INNODB_FT_INDEX_CACHE 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation INNODB AUXILIARY FTS INDEX CACHED GPL FORCE

INNODB_FT_INDEX_TABLE 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation INNODB AUXILIARY FTS INDEX TABLE GPL FORCE

INNODB_SYS_TABLES 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_TABLES GPL FORCE

INNODB_SYS_TABLESTATS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_TABLESTATS GPL FORCE

INNODB_SYS_INDEXES 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_INDEXES GPL FORCE

INNODB_SYS_COLUMNS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_COLUMNS GPL FORCE

INNODB_SYS_INSTANT_COLUMNS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Huawei InnoDB instantly added columns GPL FORCE

INNODB_SYS_FIELDS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_FIELDS GPL FORCE

INNODB_SYS_FOREIGN 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_FOREIGN GPL FORCE

INNODB_SYS_FOREIGN_COLS 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_FOREIGN_COLS GPL FORCE

INNODB_SYS_TABLESPACES 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_TABLESPACES GPL FORCE

INNODB_SYS_DATAFILES 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_DATAFILES GPL FORCE

INNODB_SYS_VIRTUAL 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Oracle Corporation InnoDB SYS_VIRTUAL GPL FORCE

METADATA_LOCK_INFO 5.7 ACTIVE INFORMATION SCHEMA 50733.0 Huawei DBS Metadata locking viewer GPL FORCE

MyISAM 1.0 ACTIVE STORAGE ENGINE 50733.0 MySQL AB MyISAM storage engine GPL FORCE

MRG_MYISAM 1.0 ACTIVE STORAGE ENGINE 50733.0 MySQL AB Collection of identical MyISAM tables GPL FORCE

PERFORMANCE_SCHEMA 0.1 ACTIVE STORAGE ENGINE 50733.0 Marc Alff, Oracle Performance Schema GPL FORCE

ARCHIVE 3.0 ACTIVE STORAGE ENGINE 50733.0 Brian Aker, MySQL AB Archive storage engine GPL ON

BLACKHOLE 1.0 ACTIVE STORAGE ENGINE 50733.0 MySQL AB /dev/null storage engine (anything you write to it disappears) GPL ON

FEDERATED 1.0 DISABLED STORAGE ENGINE 50733.0 Patrick Galbraith and Brian Aker, MySQL AB Federated MySQL storage engine GPL OFF

partition 1.0 ACTIVE STORAGE ENGINE 50733.0 Mikael Ronstrom, MySQL AB Partition Storage Engine Helper GPL ON

ngram 0.1 ACTIVE FTPARSER 1.1 Oracle Corp Ngram Full-Text Parser GPL ON

threadpool 0.1 ACTIVE DAEMON 50733.0 Huawei Technologies Co. Ltd Thread pool from Percona 5.7 and enhanced GPL ON

THREAD_GROUP_STATUS 0.1 ACTIVE INFORMATION SCHEMA 50733.0 Huawei Technologies Co. Ltd I_S table providing a view about threadpool group information GPL ON

validate_password 1.1 ACTIVE VALIDATE PASSWORD 1.0 validate_password.so 1.7 Oracle Corporation check password strength GPL FORCE_PLUS_PERMANENT

rpl_semi_sync_master 1.0 ACTIVE REPLICATION 4.0 semisync_master.so 1.7 He Zhenxing Semi-synchronous replication master GPL ON

rpl_semi_sync_slave 1.0 ACTIVE REPLICATION 4.0 semisync_slave.so 1.7 He Zhenxing Semi-synchronous replication slave GPL ON

PLUGIN_NAME	PLUGIN_VERSION	PLUGIN_STATUS	PLUGIN_TYPE	PLUGIN_TYPE_VERSION	PLUGIN_LIBRARY	PLUGIN_LIBRARY_VERSION	PLUGIN_AUTHOR	PLUGIN_DESCRIPTION	PLUGIN_LICENSE	LOAD_OPTION
binlog	1.0	ACTIVE	STORAGE ENGINE	50733.0			MySQL AB	This is a pseudo storage engine to represent the binlog in a transaction	GPL	FORCE
mysql_native_password	1.1	ACTIVE	AUTHENTICATION	1.1			R.J.Silk, Sergei Golubchik	Native MySQL authentication	GPL	FORCE
sha256_password	1.1	ACTIVE	AUTHENTICATION	1.1			Oracle	SHA256 password authentication	GPL	FORCE
CSV	1.0	ACTIVE	STORAGE ENGINE	50733.0			Brian Aker, MySQL AB	CSV storage engine	GPL	FORCE
MEMORY	1.0	ACTIVE	STORAGE ENGINE	50733.0			MySQL AB	Hash based, stored in memory, useful for temporary tables	GPL	FORCE
InnoDB	5.7	ACTIVE	STORAGE ENGINE	50733.0			Oracle Corporation	Supports transactions, row-level locking, and foreign keys	GPL	FORCE
INNODB_RSEG	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Huawei	InnoDB rollback segment information	GPL	FORCE
INNODB_TRX	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB transactions	GPL	FORCE
INNODB_ALTER_TABLE_PROGRESS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Huawei	Display ALTER TABLE progress	GPL	FORCE
INNODB_LOCKS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB conflicting locks	GPL	FORCE
INNODB_LOCK_WAITS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB which lock is blocking which	GPL	FORCE
INNODB_CMP	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compression	GPL	FORCE
INNODB_CMP_RESET	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compression; reset cumulated counts	GPL	FORCE
INNODB_CMPMEM	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compressed buffer pool	GPL	FORCE
INNODB_CMPMEM_RESET	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compressed buffer pool; reset cumulated counts	GPL	FORCE
INNODB_CMP_PER_INDEX	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compression (per index)	GPL	FORCE
INNODB_CMP_PER_INDEX_RESET	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Statistics for the InnoDB compression (per index); reset cumulated counts	GPL	FORCE
INNODB_BUFFER_PAGE	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB Buffer Page Information	GPL	FORCE
INNODB_BUFFER_PAGE_LRU	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB Buffer Page in LRU	GPL	FORCE
INNODB_BUFFER_POOL_STATS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB Buffer Pool Statistics Information	GPL	FORCE
INNODB_TEMP_TABLE_INFO	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB Temp Table Stats	GPL	FORCE
INNODB_METRICS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB Metrics Info	GPL	FORCE
INNODB_FT_DEFAULT_STOPWORD	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	Default stopword list for InnDB Full Text Search	GPL	FORCE
INNODB_FT_DELETED	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	INNODB AUXILIARY FTS DELETED TABLE	GPL	FORCE
INNODB_FT_BEING_DELETED	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	INNODB AUXILIARY FTS BEING DELETED TABLE	GPL	FORCE
INNODB_FT_CONFIG	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	INNODB AUXILIARY FTS CONFIG TABLE	GPL	FORCE
INNODB_FT_INDEX_CACHE	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	INNODB AUXILIARY FTS INDEX CACHED	GPL	FORCE
INNODB_FT_INDEX_TABLE	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	INNODB AUXILIARY FTS INDEX TABLE	GPL	FORCE
INNODB_SYS_TABLES	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_TABLES	GPL	FORCE
INNODB_SYS_TABLESTATS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_TABLESTATS	GPL	FORCE
INNODB_SYS_INDEXES	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_INDEXES	GPL	FORCE
INNODB_SYS_COLUMNS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_COLUMNS	GPL	FORCE
INNODB_SYS_INSTANT_COLUMNS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Huawei	InnoDB instantly added columns	GPL	FORCE
INNODB_SYS_FIELDS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_FIELDS	GPL	FORCE
INNODB_SYS_FOREIGN	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_FOREIGN	GPL	FORCE
INNODB_SYS_FOREIGN_COLS	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_FOREIGN_COLS	GPL	FORCE
INNODB_SYS_TABLESPACES	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_TABLESPACES	GPL	FORCE
INNODB_SYS_DATAFILES	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_DATAFILES	GPL	FORCE
INNODB_SYS_VIRTUAL	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Oracle Corporation	InnoDB SYS_VIRTUAL	GPL	FORCE
METADATA_LOCK_INFO	5.7	ACTIVE	INFORMATION SCHEMA	50733.0			Huawei DBS	Metadata locking viewer	GPL	FORCE
MyISAM	1.0	ACTIVE	STORAGE ENGINE	50733.0			MySQL AB	MyISAM storage engine	GPL	FORCE
MRG_MYISAM	1.0	ACTIVE	STORAGE ENGINE	50733.0			MySQL AB	Collection of identical MyISAM tables	GPL	FORCE
PERFORMANCE_SCHEMA	0.1	ACTIVE	STORAGE ENGINE	50733.0			Marc Alff, Oracle	Performance Schema	GPL	FORCE
ARCHIVE	3.0	ACTIVE	STORAGE ENGINE	50733.0			Brian Aker, MySQL AB	Archive storage engine	GPL	ON
BLACKHOLE	1.0	ACTIVE	STORAGE ENGINE	50733.0			MySQL AB	/dev/null storage engine (anything you write to it disappears)	GPL	ON
FEDERATED	1.0	DISABLED	STORAGE ENGINE	50733.0			Patrick Galbraith and Brian Aker, MySQL AB	Federated MySQL storage engine	GPL	OFF
partition	1.0	ACTIVE	STORAGE ENGINE	50733.0			Mikael Ronstrom, MySQL AB	Partition Storage Engine Helper	GPL	ON
ngram	0.1	ACTIVE	FTPARSER	1.1			Oracle Corp	Ngram Full-Text Parser	GPL	ON
threadpool	0.1	ACTIVE	DAEMON	50733.0			Huawei Technologies Co. Ltd	Thread pool from Percona 5.7 and enhanced	GPL	ON
THREAD_GROUP_STATUS	0.1	ACTIVE	INFORMATION SCHEMA	50733.0			Huawei Technologies Co. Ltd	I_S table providing a view about threadpool group information	GPL	ON
validate_password	1.1	ACTIVE	VALIDATE PASSWORD	1.0	validate_password.so	1.7	Oracle Corporation	check password strength	GPL	FORCE_PLUS_PERMANENT
rpl_semi_sync_master	1.0	ACTIVE	REPLICATION	4.0	semisync_master.so	1.7	He Zhenxing	Semi-synchronous replication master	GPL	ON
rpl_semi_sync_slave	1.0	ACTIVE	REPLICATION	4.0	semisync_slave.so	1.7	He Zhenxing	Semi-synchronous replication slave	GPL	ON

● 当前数据库实例的所有数据库及其容量大小
SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME table_rows data_size_mb index_size_mb all_size_mb max_size_mb free_size_mb disk_size_mb

dj00037 utf8 utf8_general_ci 135976 19.26 2.56 21.82 0.00 6.00 35.00

mysql utf8 utf8_general_ci 10073 2.59 0.21 2.80 1250640789503.99 4.00 9.00

information_schema utf8 utf8_general_ci 0.18 0.00 0.18 911.88 96.00

sys utf8 utf8_general_ci 6 0.01 0.00 0.01 0.00 0.00 0.00

performance_schema utf8 utf8_general_ci 11993 0.00 0.00 0.00 0.00 0.00

SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	table_rows	data_size_mb	index_size_mb	all_size_mb	max_size_mb	free_size_mb	disk_size_mb
dj00037	utf8	utf8_general_ci	135976	19.26	2.56	21.82	0.00	6.00	35.00
mysql	utf8	utf8_general_ci	10073	2.59	0.21	2.80	1250640789503.99	4.00	9.00
information_schema	utf8	utf8_general_ci		0.18	0.00	0.18	911.88	96.00
sys	utf8	utf8_general_ci	6	0.01	0.00	0.01	0.00	0.00	0.00
performance_schema	utf8	utf8_general_ci	11993	0.00	0.00	0.00	0.00	0.00

● 数据库对象
db_name ob_type sums

dj00037 TABLE 33

mysql TABLE 35

performance_schema TABLE 87

sys TABLE 1

dj00037 TRIGGERS 20

mysql PROCEDURE 8

sys PROCEDURE 26

dj00037 FUNCTION 3

sys FUNCTION 22

dj00037 VIEWS 4

sys VIEWS 100

db_name	ob_type	sums
dj00037	TABLE	33
mysql	TABLE	35
performance_schema	TABLE	87
sys	TABLE	1
dj00037	TRIGGERS	20
mysql	PROCEDURE	8
sys	PROCEDURE	26
dj00037	FUNCTION	3
sys	FUNCTION	22
dj00037	VIEWS	4
sys	VIEWS	100

● 查看数据库的运行状态
-------------- mysql Ver 14.14 Distrib 5.7.30, for Win64 (x86_64) Connection id: 13550798 Current database: Current user: root@123.139.22.10 SSL: Not in use Using delimiter: ; Server version: 5.7.33-2-log MySQL Community Server - (GPL) Protocol version: 10 Connection: 119.3.25.58 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 32 days 2 hours 7 min 42 sec Threads: 2 Questions: 21410977 Slow queries: 107 Opens: 1443 Flush tables: 1 Open tables: 1079 Queries per second avg: 7.722 --------------

● 占用空间最大的前10张大表
db_name table_name TABLE_TYPE ENGINE CREATE_TIME UPDATE_TIME TABLE_COLLATION table_rows tb_size_mb index_size_mb all_size_mb free_size_mb disk_size_mb

dj00037 pb_member_activity BASE TABLE InnoDB 2021-10-29 21:49:12 2021-10-29 12:44:42 utf8_general_ci 126960 10.51 2.51 13.03 2.00 20.00

dj00037 pb_activity BASE TABLE InnoDB 2021-10-01 15:56:11 2021-10-28 15:34:28 utf8_general_ci 1478 7.43 0.00 7.43 4.00 15.00

mysql help_topic BASE TABLE InnoDB 2021-09-28 17:17:41 utf8_general_ci 621 1.51 0.07 1.59 4.00 9.00

dj00037 pb_member_points BASE TABLE InnoDB 2021-10-01 15:56:13 2021-10-28 14:56:22 utf8_general_ci 3459 0.32 0.00 0.32 0.00

mysql proc BASE TABLE MyISAM 2021-09-28 17:17:42 2021-10-01 15:56:15 utf8_general_ci 59 0.29 0.00 0.29 0.00

mysql time_zone_transition BASE TABLE InnoDB 2021-09-28 17:17:41 utf8_general_ci 7498 0.28 0.00 0.28 0.00

dj00037 pb_member BASE TABLE InnoDB 2021-10-26 22:44:32 2021-10-30 15:43:27 utf8_general_ci 659 0.18 0.00 0.18 0.00

dj00037 pb_user BASE TABLE InnoDB 2021-10-01 15:56:19 2021-10-30 15:43:27 utf8_general_ci 721 0.12 0.04 0.17 0.00 0.00

mysql help_keyword BASE TABLE InnoDB 2021-09-28 17:17:41 utf8_general_ci 798 0.09 0.07 0.17 0.00

mysql help_relation BASE TABLE InnoDB 2021-09-28 17:17:41 utf8_general_ci 469 0.07 0.00 0.07 0.00 0.00

db_name	table_name	TABLE_TYPE	ENGINE	CREATE_TIME	UPDATE_TIME	TABLE_COLLATION	table_rows	tb_size_mb	index_size_mb	all_size_mb	free_size_mb	disk_size_mb
dj00037	pb_member_activity	BASE TABLE	InnoDB	2021-10-29 21:49:12	2021-10-29 12:44:42	utf8_general_ci	126960	10.51	2.51	13.03	2.00	20.00
dj00037	pb_activity	BASE TABLE	InnoDB	2021-10-01 15:56:11	2021-10-28 15:34:28	utf8_general_ci	1478	7.43	0.00	7.43	4.00	15.00
mysql	help_topic	BASE TABLE	InnoDB	2021-09-28 17:17:41		utf8_general_ci	621	1.51	0.07	1.59	4.00	9.00
dj00037	pb_member_points	BASE TABLE	InnoDB	2021-10-01 15:56:13	2021-10-28 14:56:22	utf8_general_ci	3459	0.32	0.00	0.32	0.00
mysql	proc	BASE TABLE	MyISAM	2021-09-28 17:17:42	2021-10-01 15:56:15	utf8_general_ci	59	0.29	0.00	0.29	0.00
mysql	time_zone_transition	BASE TABLE	InnoDB	2021-09-28 17:17:41		utf8_general_ci	7498	0.28	0.00	0.28	0.00
dj00037	pb_member	BASE TABLE	InnoDB	2021-10-26 22:44:32	2021-10-30 15:43:27	utf8_general_ci	659	0.18	0.00	0.18	0.00
dj00037	pb_user	BASE TABLE	InnoDB	2021-10-01 15:56:19	2021-10-30 15:43:27	utf8_general_ci	721	0.12	0.04	0.17	0.00	0.00
mysql	help_keyword	BASE TABLE	InnoDB	2021-09-28 17:17:41		utf8_general_ci	798	0.09	0.07	0.17	0.00
mysql	help_relation	BASE TABLE	InnoDB	2021-09-28 17:17:41		utf8_general_ci	469	0.07	0.00	0.07	0.00	0.00

● 占用空间最大的前10个索引
database_name table_name index_name SizeMB NON_UNIQUE INDEX_TYPE COLUMN_NAME

dj00037 pb_member_activity index_activity_id 2.52 1 BTREE activity_id

dj00037 pb_user unq_user_username 0.05 0 BTREE username

database_name	table_name	index_name	SizeMB	NON_UNIQUE	INDEX_TYPE	COLUMN_NAME
dj00037	pb_member_activity	index_activity_id	2.52	1	BTREE	activity_id
dj00037	pb_user	unq_user_username	0.05	0	BTREE	username

● 所有存储引擎列表
ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS

PERFORMANCE_SCHEMA YES Performance Schema NO NO NO

MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO

ARCHIVE YES Archive storage engine NO NO NO

InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES YES YES

BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO

MyISAM YES MyISAM storage engine NO NO NO

MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO

FEDERATED NO Federated MySQL storage engine

CSV YES CSV storage engine NO NO NO

ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine
CSV	YES	CSV storage engine	NO	NO	NO

● 存储引擎和DB的数量关系
ENGINE counts

104

CSV 2

InnoDB 69

MEMORY 57

MyISAM 10

PERFORMANCE_SCHEMA 87

ENGINE	counts
	104
CSV	2
InnoDB	69
MEMORY	57
MyISAM	10
PERFORMANCE_SCHEMA	87

TABLE_SCHEMA ENGINE counts

dj00037 4

dj00037 InnoDB 33

information_schema InnoDB 12

information_schema MEMORY 57

mysql CSV 2

mysql InnoDB 23

mysql MyISAM 10

performance_schema PERFORMANCE_SCHEMA 87

sys 100

sys InnoDB 1

TABLE_SCHEMA	ENGINE	counts
dj00037		4
dj00037	InnoDB	33
information_schema	InnoDB	12
information_schema	MEMORY	57
mysql	CSV	2
mysql	InnoDB	23
mysql	MyISAM	10
performance_schema	PERFORMANCE_SCHEMA	87
sys		100
sys	InnoDB	1

● InnoDB 系统表空间
FILE_ID FILE_NAME FILE_TYPE TABLESPACE_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME LOGFILE_GROUP_NAME LOGFILE_GROUP_NUMBER ENGINE FULLTEXT_KEYS DELETED_ROWS UPDATE_COUNT FREE_EXTENTS TOTAL_EXTENTS EXTENT_SIZE INITIAL_SIZE MAXIMUM_SIZE AUTOEXTEND_SIZE CREATION_TIME LAST_UPDATE_TIME LAST_ACCESS_TIME RECOVER_TIME TRANSACTION_COUNTER VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM STATUS EXTRA

0 ./ibdata1 TABLESPACE innodb_system InnoDB 1 128 1048576 134217728 67108864 121634816 NORMAL

29 ./ibtmp1 TEMPORARY innodb_temporary InnoDB 4 12 1048576 12582912 67108864 8388608 NORMAL

● 查询所有用户
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

localhost mysql.session N N N N N N N N N N N N N N N Y N N N N N N N N N N N N N 0 0 0 0 mysql_native_password

localhost mysql.sys N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password

localhost rdsAdmin Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 100000 100000 100000 100000 mysql_native_password

% root Y Y Y Y Y Y Y N Y N Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y N 0 0 0 0 mysql_native_password

192.168.% rdsRepl N N N N N N N N N N N N N N N N N N N Y N N N N N N N N N 100000 100000 100000 100000 mysql_native_password

192.168.% rdsMetric N N N N N N N N N N N N N N N N N N N N Y N N N N N N N N 100000 100000 100000 100000 mysql_native_password

localhost rdsBackup N Y Y Y Y Y Y N Y N Y Y Y Y N Y Y Y Y Y Y Y N Y Y Y Y Y N 100000 100000 100000 100000 mysql_native_password

10.11.17.114 dj00037m N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 mysql_native_password

● 查询MySQL支持的所有字符集
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN

big5 big5_chinese_ci Big5 Traditional Chinese 2

dec8 dec8_swedish_ci DEC West European 1

cp850 cp850_general_ci DOS West European 1

hp8 hp8_english_ci HP West European 1

koi8r koi8r_general_ci KOI8-R Relcom Russian 1

latin1 latin1_swedish_ci cp1252 West European 1

latin2 latin2_general_ci ISO 8859-2 Central European 1

swe7 swe7_swedish_ci 7bit Swedish 1

ascii ascii_general_ci US ASCII 1

ujis ujis_japanese_ci EUC-JP Japanese 3

sjis sjis_japanese_ci Shift-JIS Japanese 2

hebrew hebrew_general_ci ISO 8859-8 Hebrew 1

tis620 tis620_thai_ci TIS620 Thai 1

euckr euckr_korean_ci EUC-KR Korean 2

koi8u koi8u_general_ci KOI8-U Ukrainian 1

gb2312 gb2312_chinese_ci GB2312 Simplified Chinese 2

greek greek_general_ci ISO 8859-7 Greek 1

cp1250 cp1250_general_ci Windows Central European 1

gbk gbk_chinese_ci GBK Simplified Chinese 2

latin5 latin5_turkish_ci ISO 8859-9 Turkish 1

armscii8 armscii8_general_ci ARMSCII-8 Armenian 1

utf8 utf8_general_ci UTF-8 Unicode 3

ucs2 ucs2_general_ci UCS-2 Unicode 2

cp866 cp866_general_ci DOS Russian 1

keybcs2 keybcs2_general_ci DOS Kamenicky Czech-Slovak 1

macce macce_general_ci Mac Central European 1

macroman macroman_general_ci Mac West European 1

cp852 cp852_general_ci DOS Central European 1

latin7 latin7_general_ci ISO 8859-13 Baltic 1

utf8mb4 utf8mb4_general_ci UTF-8 Unicode 4

cp1251 cp1251_general_ci Windows Cyrillic 1

utf16 utf16_general_ci UTF-16 Unicode 4

utf16le utf16le_general_ci UTF-16LE Unicode 4

cp1256 cp1256_general_ci Windows Arabic 1

cp1257 cp1257_general_ci Windows Baltic 1

utf32 utf32_general_ci UTF-32 Unicode 4

binary binary Binary pseudo charset 1

geostd8 geostd8_general_ci GEOSTD8 Georgian 1

cp932 cp932_japanese_ci SJIS for Windows Japanese 2

eucjpms eucjpms_japanese_ci UJIS for Windows Japanese 3

gb18030 gb18030_chinese_ci China National Standard GB18030 4

CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
big5	big5_chinese_ci	Big5 Traditional Chinese	2
dec8	dec8_swedish_ci	DEC West European	1
cp850	cp850_general_ci	DOS West European	1
hp8	hp8_english_ci	HP West European	1
koi8r	koi8r_general_ci	KOI8-R Relcom Russian	1
latin1	latin1_swedish_ci	cp1252 West European	1
latin2	latin2_general_ci	ISO 8859-2 Central European	1
swe7	swe7_swedish_ci	7bit Swedish	1
ascii	ascii_general_ci	US ASCII	1
ujis	ujis_japanese_ci	EUC-JP Japanese	3
sjis	sjis_japanese_ci	Shift-JIS Japanese	2
hebrew	hebrew_general_ci	ISO 8859-8 Hebrew	1
tis620	tis620_thai_ci	TIS620 Thai	1
euckr	euckr_korean_ci	EUC-KR Korean	2
koi8u	koi8u_general_ci	KOI8-U Ukrainian	1
gb2312	gb2312_chinese_ci	GB2312 Simplified Chinese	2
greek	greek_general_ci	ISO 8859-7 Greek	1
cp1250	cp1250_general_ci	Windows Central European	1
gbk	gbk_chinese_ci	GBK Simplified Chinese	2
latin5	latin5_turkish_ci	ISO 8859-9 Turkish	1
armscii8	armscii8_general_ci	ARMSCII-8 Armenian	1
utf8	utf8_general_ci	UTF-8 Unicode	3
ucs2	ucs2_general_ci	UCS-2 Unicode	2
cp866	cp866_general_ci	DOS Russian	1
keybcs2	keybcs2_general_ci	DOS Kamenicky Czech-Slovak	1
macce	macce_general_ci	Mac Central European	1
macroman	macroman_general_ci	Mac West European	1
cp852	cp852_general_ci	DOS Central European	1
latin7	latin7_general_ci	ISO 8859-13 Baltic	1
utf8mb4	utf8mb4_general_ci	UTF-8 Unicode	4
cp1251	cp1251_general_ci	Windows Cyrillic	1
utf16	utf16_general_ci	UTF-16 Unicode	4
utf16le	utf16le_general_ci	UTF-16LE Unicode	4
cp1256	cp1256_general_ci	Windows Arabic	1
cp1257	cp1257_general_ci	Windows Baltic	1
utf32	utf32_general_ci	UTF-32 Unicode	4
binary	binary	Binary pseudo charset	1
geostd8	geostd8_general_ci	GEOSTD8 Georgian	1
cp932	cp932_japanese_ci	SJIS for Windows Japanese	2
eucjpms	eucjpms_japanese_ci	UJIS for Windows Japanese	3
gb18030	gb18030_chinese_ci	China National Standard GB18030	4

● 一些重要的参数
autocommit ON datadir /var/lib/mysql/data/ innodb_buffer_pool_size 4294967296 innodb_file_per_table ON innodb_flush_log_at_trx_commit 1 innodb_io_capacity 12000 innodb_lock_wait_timeout 50 log_error /var/lib/mysql/data/error.log log_output FILE log_queries_not_using_indexes OFF log_slave_updates ON log_throttle_queries_not_using_indexes 0 long_query_time 1.000000 lower_case_table_names 1 max_connect_errors 100 max_connections 2500 max_user_connections 0 pid_file /tmp/rds_mysql.pid query_cache_size 2097152 query_cache_type OFF read_only OFF server_id 4030504773 slow_query_log ON slow_query_log_file /var/lib/mysql/data/slow.log socket /tmp/mysql.sock sql_mode time_zone SYSTEM tx_isolation REPEATABLE-READ

● 查看当前连接到数据库的用户和Host
USER HOST

root 123.139.22.10:20866

root 123.139.22.10:20645

rdsRepl 192.168.6.139:42124

USER	HOST
root	123.139.22.10:20866
root	123.139.22.10:20645
rdsRepl	192.168.6.139:42124

● 查看每个host的当前连接数和总连接数
HOST CURRENT_CONNECTIONS TOTAL_CONNECTIONS

● 按照登录用户+登录服务器查看登录信息
login_user login_ip login_count

rdsRepl 192.168.6.139 1

root 123.139.22.10 2

login_user	login_ip	login_count
rdsRepl	192.168.6.139	1
root	123.139.22.10	2

● 按照登录用户+数据库+登录服务器查看登录信息

锁情况

● 查询所有线程（排除sleep线程）
ID USER HOST DB COMMAND TIME STATE INFO

2 rdsRepl 192.168.6.139:42124 Binlog Dump GTID 2772503 Master has sent all binlog to slave; waiting for more updates

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
2	rdsRepl	192.168.6.139:42124		Binlog Dump GTID	2772503	Master has sent all binlog to slave; waiting for more updates

THREAD_ID NAME TYPE PROCESSLIST_ID PROCESSLIST_USER PROCESSLIST_HOST PROCESSLIST_DB PROCESSLIST_COMMAND PROCESSLIST_TIME PROCESSLIST_STATE PROCESSLIST_INFO PARENT_THREAD_ID ROLE INSTRUMENTED HISTORY CONNECTION_TYPE THREAD_OS_ID

● sleep线程TOP20
ID USER HOST DB COMMAND TIME STATE INFO

13551342 root 123.139.22.10:20866 Sleep 38

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
13551342	root	123.139.22.10:20866		Sleep	38

● 有多少线程正在使用表

● 查询InnoDB存储引擎的运行时信息，包括死锁的详细信息
InnoDB \n=====================================\n2021-10-30 19:26:42 0x7fa5ed92d700 INNODB MONITOR OUTPUT\n=====================================\nPer second averages calculated from the last 47 seconds\n-----------------\nBACKGROUND THREAD\n-----------------\nsrv_master_thread loops: 94829 srv_active, 0 srv_shutdown, 2677318 srv_idle\nsrv_master_thread log flush and writes: 2772147\n----------\nSEMAPHORES\n----------\nOS WAIT ARRAY INFO: reservation count 16582\nOS WAIT ARRAY INFO: signal count 16197\nRW-shared spins 0, rounds 32895, OS waits 16307\nRW-excl spins 0, rounds 1767, OS waits 26\nRW-sx spins 81, rounds 1227, OS waits 19\nSpin rounds per wait: 32895.00 RW-shared, 1767.00 RW-excl, 15.15 RW-sx\n------------\nTRANSACTIONS\n------------\nTrx id counter 33136\nPurge done for trx's n:o < 33136 undo n:o < 0 state: running but idle\nHistory list length 0\nLIST OF TRANSACTIONS FOR EACH SESSION:\n---TRANSACTION 421830635564688, not started\n0 lock struct(s), heap size 1136, 0 row lock(s)\n---TRANSACTION 421830635568880, not started\n0 lock struct(s), heap size 1136, 0 row lock(s)\n--------\nFILE I/O\n--------\nI/O thread 0 state: waiting for completed aio requests (insert buffer thread)\nI/O thread 1 state: waiting for completed aio requests (log thread)\nI/O thread 2 state: waiting for completed aio requests (read thread)\nI/O thread 3 state: waiting for completed aio requests (read thread)\nI/O thread 4 state: waiting for completed aio requests (read thread)\nI/O thread 5 state: waiting for completed aio requests (read thread)\nI/O thread 6 state: waiting for completed aio requests (write thread)\nI/O thread 7 state: waiting for completed aio requests (write thread)\nI/O thread 8 state: waiting for completed aio requests (write thread)\nI/O thread 9 state: waiting for completed aio requests (write thread)\nPending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,\n ibuf aio reads:, log i/o's:, sync i/o's:\nPending flushes (fsync) log: 0; buffer pool: 0\n389 OS file reads, 428636 OS file writes, 103437 OS fsyncs\n0.00 reads/s, 0 avg bytes/read, 0.47 writes/s, 0.00 fsyncs/s\n-------------------------------------\nINSERT BUFFER AND ADAPTIVE HASH INDEX\n-------------------------------------\nIbuf: size 1, free list len 0, seg size 2, 0 merges\nmerged operations:\n insert 0, delete mark 0, delete 0\ndiscarded operations:\n insert 0, delete mark 0, delete 0\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\nHash table size 1106407, node heap has 0 buffer(s)\n0.00 hash searches/s, 0.00 non-hash searches/s\n---\nLOG\n---\nLog sequence number 41310947\nLog flushed up to 41310947\nPages flushed up to 41310947\nLast checkpoint at 41310938\n0 pending log flushes, 0 pending chkp writes\n55454 log i/o's done, 0.00 log i/o's/second\n-------------\nLOG MIGRATION\n-------------\nThe Number of switch log successfully in Innodb level is 0\nThe Number of switch log fail in Innodb level is 0\nThe Longest time for switch log is 0 ms \nThe Current time for swtich log is 0 ms \nThe active of log file path is in base log group path set by param innodb_log_group_home_dir\nThe Status of log migration \nInvalid status?\nThe count of waiting double write finish in Current log migration is 0\n----------------------\nBUFFER POOL AND MEMORY\n----------------------\nTotal large memory allocated 4294967296\nDictionary memory allocated 347261\nBuffer pool size 256128\nFree buffers 254221\nDatabase pages 1907\nOld database pages 663\nModified db pages 0\nPending reads 0\nPending writes: LRU 0, flush list 0, single page 0\nPages made young 0, not young 0\n0.00 youngs/s, 0.00 non-youngs/s\nPages read 347, created 1560, written 355431\n0.00 reads/s, 0.00 creates/s, 0.00 writes/s\nBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000\nPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\nLRU len: 1907, unzip_LRU len: 0\nI/O sum[0]:cur[6], unzip sum[0]:cur[0]\n----------------------\nINDIVIDUAL BUFFER POOL INFO\n----------------------\n---BUFFER POOL 0\nBuffer pool size 128064\nFree buffers 127178\nDatabase pages 886\nOld database pages 307\nModified db pages 0\nPending reads 0\nPending writes: LRU 0, flush list 0, single page 0\nPages made young 0, not young 0\n0.00 youngs/s, 0.00 non-youngs/s\nPages read 162, created 724, written 64525\n0.00 reads/s, 0.00 creates/s, 0.00 writes/s\nNo buffer pool page gets since the last printout\nPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\nLRU len: 886, unzip_LRU len: 0\nI/O sum[0]:cur[3], unzip sum[0]:cur[0]\n---BUFFER POOL 1\nBuffer pool size 128064\nFree buffers 127043\nDatabase pages 1021\nOld database pages 356\nModified db pages 0\nPending reads 0\nPending writes: LRU 0, flush list 0, single page 0\nPages made young 0, not young 0\n0.00 youngs/s, 0.00 non-youngs/s\nPages read 185, created 836, written 290906\n0.00 reads/s, 0.00 creates/s, 0.00 writes/s\nBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000\nPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s\nLRU len: 1021, unzip_LRU len: 0\nI/O sum[0]:cur[3], unzip sum[0]:cur[0]\n--------------\nROW OPERATIONS\n--------------\n0 queries inside InnoDB, 0 queries in queue\n0 read views open inside InnoDB\nProcess ID=10890, Main thread ID=140350591063808, state: sleeping\nNumber of rows inserted 1296426, updated 10289, deleted 2233, read 215415260\n0.55 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.60 reads/s\n----------------------------\nEND OF INNODB MONITOR OUTPUT\n============================\n

● 查看当前状态产生的InnoDB锁，仅在有锁等待时有结果输出
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data

● 查看当前状态产生的InnoDB锁等待，仅在有锁等待时有结果输出
requesting_trx_id requested_lock_id blocking_trx_id blocking_lock_id

● 当前Innodb内核中的当前活跃（active）事务
trx_id trx_state trx_started trx_requested_lock_id trx_wait_started trx_weight trx_mysql_thread_id trx_query trx_operation_state trx_tables_in_use trx_tables_locked trx_lock_structs trx_lock_memory_bytes trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks trx_last_foreign_key_error trx_adaptive_hash_latched trx_adaptive_hash_timeout trx_is_read_only trx_autocommit_non_locking

● 锁详情
trx_isolation_level waiting_trx_id waiting_trx_thread waiting_trx_state waiting_trx_lock_mode waiting_trx_lock_type waiting_trx_lock_table waiting_trx_lock_index waiting_trx_query blocking_trx_id blocking_trx_thread blocking_trx_state blocking_trx_lock_mode blocking_trx_lock_type blocking_trx_lock_table blocking_trx_lock_index blocking_query

● 元数据锁的相关信息

locked_schema locked_table locked_type waiting_processlist_id waiting_age waiting_query waiting_state blocking_processlist_id blocking_age blocking_query sql_kill_blocking_connection

thd_id conn_id user db command state time current_statement statement_latency progress lock_latency rows_examined rows_sent rows_affected tmp_tables tmp_disk_tables full_scan last_statement last_statement_latency current_memory last_wait last_wait_latency source trx_latency trx_state trx_autocommit pid program_name lock_summary

object_schema object_name waiting_thread_id waiting_pid waiting_account waiting_lock_type waiting_lock_duration waiting_query waiting_query_secs waiting_query_rows_affected waiting_query_rows_examined blocking_thread_id blocking_pid blocking_account blocking_lock_type blocking_lock_duration sql_kill_blocking_query sql_kill_blocking_connection

● 查看服务器的状态
Com_lock_tables 0 Com_lock_tables_for_backup 0 Com_lock_binlog_for_backup 0 Com_unlock_binlog 0 Com_unlock_tables 0 Handler_external_lock 84 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Key_blocks_not_flushed 0 Key_blocks_unused 13386 Key_blocks_used 10 Locked_connects 0 Performance_schema_locker_lost 0 Performance_schema_metadata_lock_lost 0 Performance_schema_rwlock_classes_lost 0 Performance_schema_rwlock_instances_lost 0 Performance_schema_table_lock_stat_lost 0 Qcache_free_blocks 4 Qcache_total_blocks 4 Table_locks_immediate 5536803 Table_locks_waited 0

[回到目录]

SQL部分

● 跟踪长时间操作的进度
THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT WORK_COMPLETED WORK_ESTIMATED NESTING_EVENT_ID NESTING_EVENT_TYPE

● 查看平均执行时间值大于95%的平均执行时间的语句（可近似地认为是平均执行时间超长的语句），默认情况下按照语句平均延迟(执行时间)降序排序
thd_id conn_id user db command state time current_statement statement_latency progress lock_latency rows_examined rows_sent rows_affected tmp_tables tmp_disk_tables full_scan last_statement last_statement_latency current_memory last_wait last_wait_latency source trx_latency trx_state trx_autocommit pid program_name

● 查看已经执行完的语句相关统计信息
thd_id conn_id user db command state time current_statement statement_latency progress lock_latency rows_examined rows_sent rows_affected tmp_tables tmp_disk_tables full_scan last_statement last_statement_latency current_memory last_wait last_wait_latency source trx_latency trx_state trx_autocommit pid program_name

● 查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序
query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest

● 有临时表的前10条SQL语句
query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen

● 查看执行了文件排序的语句，默认情况下按照语句总延迟时间（执行时间）降序排序

● 查询SQL的整体消耗百分比

● 执行次数Top10
query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen

● 使用全表扫描的SQL语句
object_schema object_name rows_full_scanned latency

● 查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认情况下按照全表扫描次数与语句总次数百分比和语句总延迟时间(执行时间)降序排序

● 查看产生错误或警告的语句，默认情况下，按照错误数量和警告数量降序排序
query db exec_count errors error_pct warnings warning_pct first_seen last_seen digest

[回到目录]

索引部分

● 冗余索引

● 无效索引（从未使用过的索引）
object_schema object_name index_name

● 每张表的索引区分度（前100条）

区分度越接近1，表示区分度越高；低于0.1，则说明区分度较差，开发者应该重新评估SQL语句涉及的字段，选择区分度高的多个字段创建索引
ASdb AStable ASindex_name AScols ASdefferRows ASROWS sel_persent

dj00037 pb_member_activity index_activity_id activity_id 956 126960 0.01

ASdb	AStable	ASindex_name	AScols	ASdefferRows	ASROWS	sel_persent
dj00037	pb_member_activity	index_activity_id	activity_id	956	126960	0.01

[回到目录]

主从情况

● 主从复制涉及到的重要参数
auto_increment_increment 1 auto_increment_offset 1 binlog_format ROW expire_logs_days 1 log_bin ON log_bin_basename /var/lib/mysql/data/mysql-bin log_bin_index /var/lib/mysql/data/mysql-bin.index log_slave_updates ON max_allowed_packet 1073741824 max_binlog_size 536870912 read_only OFF server_id 4030504773 server_uuid eebd540e-203c-11ec-9c1c-fa163e9b9b3a slave_exec_mode STRICT slave_max_allowed_packet 1073741824 slave_skip_errors OFF sql_slave_skip_counter 0 sync_binlog 1

● 主从库线程

● 二进制日志
mysql-bin.001942 5449 mysql-bin.001943 244 mysql-bin.001944 244 mysql-bin.001945 244 mysql-bin.001946 244 mysql-bin.001947 197

● 主库端查看所有从库
2778178655 3306 4030504773 ed670a41-203c-11ec-832e-fa163e5474a2

● MGR详情

● 主库状态监测
mysql-bin.001947 197 eebd540e-203c-11ec-9c1c-fa163e9b9b3a:1-11323

● 从库状态监测（需要在从库执行才有数据）
192.168.55.58 rdsRepl 3306 60 4 rds_mysql-relay-bin.000001 4 No No 0 0 0 1441 None 0 No NULL No 0 0 0 mysql.slave_master_info 0 NULL 86400 eebd540e-203c-11ec-9c1c-fa163e9b9b3a:1-11323 1
CHANNEL_NAME HOST PORT USER CONNECTION_RETRY_COUNT CONNECTION_RETRY_INTERVAL SOURCE_UUID THREAD_ID SERVICE_STATE COUNT_RECEIVED_HEARTBEATS LAST_HEARTBEAT_TIMESTAMP LAST_ERROR_NUMBER LAST_ERROR_MESSAGE LAST_ERROR_TIMESTAMP

● 克隆进度和状态

[回到目录]

数据库性能

● 性能参数统计
Com_delete 4981 Com_insert 7538 Com_select 3775138 Connections 13552272 Created_tmp_disk_tables 179776 Created_tmp_files 331 Created_tmp_tables 5862673 Handler_read_rnd_next 7055503235 Open_files 60 Opened_tables 1742 Slow_queries 107 Sort_merge_passes 8 Sort_range 0 Sort_rows 6420185 Sort_scan 93005 Table_locks_immediate 5537034 Table_locks_waited 0 Uptime 2772646

[回到目录]

其它

● setup_consumers
NAME ENABLED

● 自增ID的使用情况（前20条）
table_schema table_name engine Auto_increment

dj00037 pb_member_points InnoDB 3748

dj00037 pb_user_log InnoDB 2102

dj00037 pb_activity InnoDB 1174

dj00037 pb_user InnoDB 728

dj00037 pb_member_relation InnoDB 666

dj00037 pb_member InnoDB 666

dj00037 pb_learning InnoDB 471

dj00037 pb_function InnoDB 371

dj00037 pb_dictionary InnoDB 305

dj00037 pb_commissioner InnoDB 208

dj00037 pb_dues InnoDB 203

dj00037 pb_organization InnoDB 61

dj00037 pb_province InnoDB 35

dj00037 oauth_user InnoDB 24

dj00037 pb_update_log InnoDB 16

dj00037 pb_role InnoDB 14

dj00037 pb_honor InnoDB 6

dj00037 pb_appraisal_config InnoDB 6

dj00037 pb_activity_comment InnoDB 5

dj00037 pb_ad InnoDB 4

table_schema	table_name	engine	Auto_increment
dj00037	pb_member_points	InnoDB	3748
dj00037	pb_user_log	InnoDB	2102
dj00037	pb_activity	InnoDB	1174
dj00037	pb_user	InnoDB	728
dj00037	pb_member_relation	InnoDB	666
dj00037	pb_member	InnoDB	666
dj00037	pb_learning	InnoDB	471
dj00037	pb_function	InnoDB	371
dj00037	pb_dictionary	InnoDB	305
dj00037	pb_commissioner	InnoDB	208
dj00037	pb_dues	InnoDB	203
dj00037	pb_organization	InnoDB	61
dj00037	pb_province	InnoDB	35
dj00037	oauth_user	InnoDB	24
dj00037	pb_update_log	InnoDB	16
dj00037	pb_role	InnoDB	14
dj00037	pb_honor	InnoDB	6
dj00037	pb_appraisal_config	InnoDB	6
dj00037	pb_activity_comment	InnoDB	5
dj00037	pb_ad	InnoDB	4

● 无主键或唯一键的表（前100条）
table_schema table_name

[回到目录]

FILE_ID	FILE_NAME	FILE_TYPE	TABLESPACE_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	LOGFILE_GROUP_NAME	LOGFILE_GROUP_NUMBER	ENGINE	FULLTEXT_KEYS	DELETED_ROWS	UPDATE_COUNT	FREE_EXTENTS	TOTAL_EXTENTS	EXTENT_SIZE	INITIAL_SIZE	MAXIMUM_SIZE	AUTOEXTEND_SIZE	CREATION_TIME	LAST_UPDATE_TIME	LAST_ACCESS_TIME	RECOVER_TIME	TRANSACTION_COUNTER	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	STATUS	EXTRA
0	./ibdata1	TABLESPACE	innodb_system						InnoDB				1	128	1048576	134217728		67108864													121634816					NORMAL
29	./ibtmp1	TEMPORARY	innodb_temporary						InnoDB				4	12	1048576	12582912		67108864													8388608					NORMAL

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	max_questions	max_updates	max_connections	max_user_connections	plugin
localhost	mysql.session	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	0	0	0	0	mysql_native_password
localhost	mysql.sys	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	0	0	0	0	mysql_native_password
localhost	rdsAdmin	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	100000	100000	100000	100000	mysql_native_password
%	root	Y	Y	Y	Y	Y	Y	Y	N	Y	N	Y	Y	Y	Y	Y	N	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	N	0	0	0	0	mysql_native_password
192.168.%	rdsRepl	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	100000	100000	100000	100000	mysql_native_password
192.168.%	rdsMetric	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	100000	100000	100000	100000	mysql_native_password
localhost	rdsBackup	N	Y	Y	Y	Y	Y	Y	N	Y	N	Y	Y	Y	Y	N	Y	Y	Y	Y	Y	Y	Y	N	Y	Y	Y	Y	Y	N	100000	100000	100000	100000	mysql_native_password
10.11.17.114	dj00037m	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	0	0	0	0	mysql_native_password