1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
| #!/bin/bash
host="127.0.0.1"
port="3306"
userName="root"
password="123456"
dbname="mysql"
base='/usr/bin/mysql'
mysql_query() {
query=$1
$base -h$host -u$userName -p$password $dbname -P$port -e "$query" 2>/dev/null | awk 'NR==2 {print $2}'
}
echo "========= 基本配置信息 ==========="
lower_case_table_names_val=$(mysql_query "show variables like 'lower_case_table_names';")
echo "不区分大小写：$lower_case_table_names_val"
_port_val=$(mysql_query "show variables like 'port';")
echo "端口：$_port_val"
socket_val=$(mysql_query "show variables like 'socket';")
echo "socket的值：$socket_val"
skip_name_resolve_val=$(mysql_query "show variables like 'skip_name_resolve';")
echo "域名解析skip_name_resolve：$skip_name_resolve_val"
character_set_server_val=$(mysql_query "show variables like 'character_set_server';")
echo "数据库字符集character_set_server：$character_set_server_val"
interactive_timeout_val=$(mysql_query "show variables like 'interactive_timeout';")
echo "交互式连接超时时间：$interactive_timeout_val 秒"
wait_timeout_val=$(mysql_query "show variables like 'wait_timeout';")
echo "非交互式连接超时时间：$wait_timeout_val 秒"
query_cache_type_val=$(mysql_query "show variables like 'query_cache_type';")
echo "查询缓存query_cache_type：$query_cache_type_val"
innodb_version_val=$(mysql_query "show variables like 'innodb_version';")
echo "数据库版本：$innodb_version_val"
trx_isolation_val=$(mysql_query "show variables like 'tx_isolation';")
echo "隔离级别trx_isolation：$trx_isolation_val"
datadir_val=$(mysql_query "show variables like '%datadir%';")
echo "mysql 数据文件存放位置：$datadir_val"
echo "========= 连接数配置信息 ==========="
max_connections_val=$(mysql_query "show variables like 'max_connections';")
echo "最大连接数：$max_connections_val"
Max_used_connections_val=$(mysql_query "show status like 'Max_used_connections';")
echo "当前连接数：$Max_used_connections_val"
max_connect_errors_val=$(mysql_query "show variables like 'max_connect_errors';")
echo "最大错误连接数：$max_connect_errors_val"
echo "========= binlog配置信息 ==========="
sync_binlog_val=$(mysql_query "show variables like 'sync_binlog';")
echo "sync_binlog：$sync_binlog_val"
binlog_format_val=$(mysql_query "show variables like 'binlog_format';")
echo "binlog格式：$binlog_format_val"
log_bin_val=$(mysql_query "show variables like 'log-bin';")
echo "binlog文件：$log_bin_val"
expire_logs_days_val=$(mysql_query "show variables like 'expire_logs_days';")
echo "binlog文件过期时间：$expire_logs_days_val"
echo "========= GTID配置信息 ==========="
gtid_mode_val=$(mysql_query "show variables like 'gtid_mode';")
echo "是否开启gtid_mode：$gtid_mode_val"
enforce_gtid_consistency_val=$(mysql_query "show variables like 'enforce_gtid_consistency';")
echo "enforce_gtid_consistency是否开启：$enforce_gtid_consistency_val"
log_slave_updates_val=$(mysql_query "show variables like 'log_slave_updates';")
echo "级联复制是否开启log_slave_updates：$log_slave_updates_val"
echo "======== InnoDB配置信息 ========="
innodb_buffer_pool_size_val=$(mysql_query "show variables like 'innodb_buffer_pool_size';")
echo "innodb_buffer_pool_size：$innodb_buffer_pool_size_val"
innodb_log_file_size_val=$(mysql_query "show variables like 'innodb_log_file_size';")
echo "innodb_log_file_size：$innodb_log_file_size_val"
innodb_flush_log_at_trx_commit_val=$(mysql_query "show variables like 'innodb_flush_log_at_trx_commit';")
echo "innodb_flush_log_at_trx_commit：$innodb_flush_log_at_trx_commit_val"
innodb_io_capacity_val=$(mysql_query "show variables like 'innodb_io_capacity';")
echo "innodb_io_capacity：$innodb_io_capacity_val"
echo "================= 监控指标 ==============================="
innodb_buffer_pool_size_mb=$(($innodb_buffer_pool_size_val / 1024 / 1024))
echo "InnoDB 数据和索引缓存：$innodb_buffer_pool_size_mb MB"
query_cache_hits=$(mysql_query "show status like 'Qcache_hits';")
echo "查询缓存命中次数：$query_cache_hits"
query_cache_inserts=$(mysql_query "show status like 'Qcache_inserts';")
echo "查询缓存插入次数：$query_cache_inserts"
threads_connected=$(mysql_query "show status like 'Threads_connected';")
echo "当前连接线程数：$threads_connected"
threads_running=$(mysql_query "show status like 'Threads_running';")
echo "当前运行线程数：$threads_running"
threads_created=$(mysql_query "show status like 'Threads_created';")
echo "创建的线程数：$threads_created"
threads_cached=$(mysql_query "show status like 'Threads_cached';")
echo "缓存的线程数：$threads_cached"
slow_queries=$(mysql_query "show status like 'Slow_queries';")
echo "慢查询次数：$slow_queries"
innodb_rows_read=$(mysql_query "show status like 'Innodb_rows_read';")
echo "InnoDB 读取的行数：$innodb_rows_read"
innodb_rows_inserted=$(mysql_query "show status like 'Innodb_rows_inserted';")
echo "InnoDB 插入的行数：$innodb_rows_inserted"
innodb_rows_updated=$(mysql_query "show status like 'Innodb_rows_updated';")
echo "InnoDB 更新的行数：$innodb_rows_updated"
innodb_rows_deleted=$(mysql_query "show status like 'Innodb_rows_deleted';")
echo "InnoDB 删除的行数：$innodb_rows_deleted"
|