mysql自动巡检监控脚本

#! /bin/bash

### DATE: 2023/03/03

# 数据库服务器 性能巡检报告

source /etc/profile

source /root/.bash_profile

#################################

# #

# 全局配置 #

# #

#################################

# 巡检日期

CheckDate=`date +"%Y-%m-%d"`

#mysql 命令前缀

db_sql="mysql --defaults-extra-file=password.txt"

# #

# 巡检内容 #

# #

#################################

# 格式化标题函数

function f_format(){

echo ""

echo "-----------------------------------------------"

echo ""

echo -e "# \e[36m$1\e[0m "

echo ""

}

f_format "01、CPU空闲率、系统负载"

uptime

f_format "02、可用内存"

free -h

f_format "03、磁盘空间使用率"

df -h -x tmpfs

f_format "04、磁盘IO性能"

iostat -c

f_format "05、数据库启动状态"

pid_count=$(ps -ef | grep mysqld|grep -v "grep"|wc -l)

if [[ $pid_count > 0 ]];then

echo "数据库进程：alive"

else

echo "数据库进程：off"

exit 99

fi

echo -n "数据库uptime(秒)："

$db_sql -e "show global status where variable_name in ('Uptime')"|awk 'NR>1{print $2}'

f_format "05、数据库基本信息"

echo "PID文件: $($db_sql -e "show global variables where variable_name in ('pid_file')"|awk 'NR>1{print $2}')"

echo "SOCKET文件: $($db_sql -e "show global variables where variable_name in ('socket')"|awk 'NR>1{print $2}')"

echo "BIN日志文件: $($db_sql -e "show global variables where variable_name in ('log_bin_index')"|awk 'NR>1{print $2}')"

echo "SLOW日志文件: $($db_sql -e "show global variables where variable_name in ('slow_query_log_file')"|awk 'NR>1{print $2}')"

echo "ERROR日志文件: $($db_sql -e "show global variables where variable_name in ('log_error')"|awk 'NR>1{print $2}')"

echo "主机名: $($db_sql -e "show global variables where variable_name in ('hostname')"|awk 'NR>1{print $2}')"

echo "数据库版本: $($db_sql -e "show global variables where variable_name in ('version')"|awk 'NR>1{print $2}')"

echo "SERVEID: $($db_sql -e "show global variables where variable_name in ('server_id')"|awk 'NR>1{print $2}')"

echo "默认字符集: $($db_sql -e "show global variables where variable_name in ('character_set_server')"|awk 'NR>1{print $2}')"

echo "默认存储引擎: $($db_sql -e "show global variables where variable_name in ('default_storage_engine')"|awk 'NR>1{print $2}')"

echo "是否开启自动提交: $($db_sql -e "show global variables where variable_name in ('autocommit')"|awk 'NR>1{print $2}')"

echo "是否开启事件调度器: $($db_sql -e "show global variables where variable_name in ('event_scheduler')"|awk 'NR>1{print $2}')"

echo "是否开启查询缓冲功能: $($db_sql -e "show global variables where variable_name in ('have_query_cache')"|awk 'NR>1{print $2}')"

echo "BIN日志格式: $($db_sql -e "show global variables where variable_name in ('binlog_format')"|awk 'NR>1{print $2}')"

echo "BIN日志保留时长(天): $($db_sql -e "show global variables where variable_name in ('expire_logs_days')"|awk 'NR>1{print $2}')"

echo "慢查询开启状态: $($db_sql -e "show global variables where variable_name in ('slow_query_log')"|awk 'NR>1{print $2}')"

echo "写入慢查询日志时间限制(秒): $($db_sql -e "show global variables where variable_name in ('long_query_time')"|awk 'NR>1{print $2}')"

echo "事务隔离级别: $($db_sql -e "show global variables where variable_name in ('transaction_isolation')"|awk 'NR>1{print $2}')"

echo "线程运行方式: $($db_sql -e "show global variables where variable_name in ('thread_handling')"|awk 'NR>1{print $2}')"

echo "打开文件数限制: $($db_sql -e "show global variables where variable_name in ('open_files_limit')"|awk 'NR>1{print $2}')"

echo "INNODB表创建方式(innodb_file_per_table): $($db_sql -e "show global variables where variable_name in ('innodb_file_per_table')"|awk 'NR>1{print $2}')"

echo "INNODB打开表空间文件数限制: $($db_sql -e "show global variables where variable_name in ('innodb_open_files')"|awk 'NR>1{print $2}')"

echo "表名是否区分大小写: $($db_sql -e "show global variables where variable_name in ('lower_case_table_names')"|awk 'NR>1{print $2}')"

echo "INNODB页大小: $($db_sql -e "show global variables where variable_name in ('innodb_page_size')"|awk 'NR>1{print $2}')"

echo "是否开启双写功能: $($db_sql -e "show global variables where variable_name in ('innodb_doublewrite')"|awk 'NR>1{print $2}')"

echo "是否启用redo专用线程: $($db_sql -e "show global variables where variable_name in ('innodb_log_writer_threads')"|awk 'NR>1{print $2}')"

echo "INNODB读线程数: $($db_sql -e "show global variables where variable_name in ('innodb_read_io_threads')"|awk 'NR>1{print $2}')"

echo "INNODB写线程数: $($db_sql -e "show global variables where variable_name in ('innodb_write_io_threads')"|awk 'NR>1{print $2}')"

echo "INNODB是否启用本地AIO: $($db_sql -e "show global variables where variable_name in ('innodb_use_native_aio')"|awk 'NR>1{print $2}')"

f_format "06、数据库监听状态"

db_port=$($db_sql -e "show global variables where variable_name in ('port')"|awk 'NR>1{print $2}')

echo "数据库监听端口：$db_port"

echo "数据库端口连接状态："

netstat -ant|grep -w "3306"|awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'

f_format "07、数据库大小"

DataDir=$($db_sql -e "show global variables where variable_name in ('innodb_data_home_dir')"|awk 'NR>1{print $2}')

echo "数据目录:$DataDir"

du -sh $DataDir

f_format "08、数据库连接信息"

echo -n "数据库设置最大连接数："

$db_sql -e "show global variables where variable_name in ('max_connections')"|awk 'NR>1{print $2}'

echo -n "数据库连接超时时间(秒)："

$db_sql -e "show global variables where variable_name in ('connect_timeout')"|awk 'NR>1{print $2}'

echo -n "数据库峰值连接数："

$db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Max_used_connections'"|awk 'NR>1{print $2}'

echo -n "数据库当前打开连接数："

$db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Threads_connected'"|awk 'NR>1{print $2}'

echo -n "拒绝的连接数（内部错误）："

$db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Connection_errors_internal'"|awk 'NR>1{print $2}'

echo -n "拒绝的连接数（超出最大连接数）："

$db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Connection_errors_max_connections'"|awk 'NR>1{print $2}'

f_format "09、数据库运行状态信息"

Uptime=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Uptime'"|awk 'NR>1{print $2}')

Questions=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Questions'"|awk 'NR>1{print $2}')

Com_commit=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Com_commit'"|awk 'NR>1{print $2}')

Com_rollback=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Com_rollback'"|awk 'NR>1{print $2}')

Connected=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Connections'"|awk 'NR>1{print $2}')

Created_th=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Threads_created'"|awk 'NR>1{print $2}')

Total_buff=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_pages_total'"|awk 'NR>1{print $2}')

Free_buff=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_pages_free'"|awk 'NR>1{print $2}')

mem_read=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_read_requests'"|awk 'NR>1{print $2}')

disk_read=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_reads'"|awk 'NR>1{print $2}')

other_read=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_buffer_pool_read_ahead'"|awk 'NR>1{print $2}')

disk_tmp=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Created_tmp_disk_tables'"|awk 'NR>1{print $2}')

total_tmp=$($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Created_tmp_tables'"|awk 'NR>1{print $2}')

echo "平均每秒查询量(QPS):$(( $Questions / $Uptime))"

echo "平均每秒事务量(TPS):$((( $Com_commit + $Com_rollback ) /$Uptime))"

echo "发送数据量(Bytes): $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Bytes_sent'"|awk 'NR>1{print $2}')"

echo "接收数据量(Bytes): $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Bytes_received'"|awk 'NR>1{print $2}')"

echo "慢查询数: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Slow_queries'"|awk 'NR>1{print $2}')"

echo "打开文件数: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Open_files'"|awk 'NR>1{print $2}')"

echo -n "线程缓存命中率(target 100%): "

echo $Total_buff $Created_th|awk '{printf("%4.2f%\n", (1-$2/$1)*100)}'

echo -n "INNODB缓存空闲率: "

echo $Total_buff $Free_buff|awk '{printf("%4.2f%\n", ($2/$1)*100)}'

echo -n "INNODB缓存命中率: "

echo $mem_read $disk_read $other_read|awk '{printf("%.2f%\n", $1/($1+$2+$3)*100)}'

echo -n "临时表磁盘创建率: "

echo $disk_tmp $total_tmp|awk '{printf("%.2f%\n", ($1/$2)*100)}'

echo "等待LOG刷出次数: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_log_waits'"|awk 'NR>1{print $2}')"

echo "行锁等待次数: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_waits'"|awk 'NR>1{print $2}')"

echo "行锁等待平均时长: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_time_avg'"|awk 'NR>1{print $2}')"

echo "行锁等待最长时间: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_time_max'"|awk 'NR>1{print $2}')"

echo "正在等待行锁的数量: $($db_sql -e "SHOW GLOBAL STATUS WHERE variable_name='Innodb_row_lock_current_waits'"|awk 'NR>1{print $2}')"

f_format "10、数据库用户信息"

$db_sql -e "SELECT user , host, account_locked, password_expired FROM mysql.user"

f_format "11、数据库主从同步状态"

echo "MASTER状态:"

$db_sql -e "SHOW master status \G"

echo ""

echo "SLAVE状态:"

$db_sql -e "SHOW slave status \G"

f_format "12、事务和锁信息"

SQL="SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM sys.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r

ON r.trx_id = w.waiting_trx_id;"

$db_sql -e "$SQL"

echo ""

f_format "12、INNODB整体运行状态"

echo ""

$db_sql -e "show engine innodb status \G"

f_format "THE END ! "