MySQL运维之两招小技大智

1、MySQL 内存检查脚本
这个脚本是一个 MySQL 内存使用分析工具，主要用于计算和展示 MySQL 服务器的内存使用情况，帮助 DBA 或运维人员评估数据库的内存配置是否合理。以下是其核心功能和使用场景的详细分析：

#!/bin/sh
mysql -uroot -pxxx -h 1xx.1x.13.1xx -P 3305 -e “show variables; show status” | awk ’
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR[“max_connections”]
MAX_USED_CONN = VAR[“Max_used_connections”]
BASE_MEM=VAR[“key_buffer_size”] + VAR[“query_cache_size”] + VAR[“innodb_buffer_pool_size”] + VAR[“innodb_additional_mem_pool_size”] + VAR[“innodb_log_buffer_size”]
MEM_PER_CONN=VAR[“read_buffer_size”] + VAR[“read_rnd_buffer_size”] + VAR[“sort_buffer_size”] + VAR[“join_buffer_size”] + VAR[“binlog_cache_size”] + VAR[“thread_stack”] + VAR[“tmp_table_size”] + VAR[“net_buffer_length”]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONNMAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONNMAX_CONN
printf “±—————————————–±——————-+\\n”
printf “| %40s | %15.3f MB |\\n”, “key_buffer_size”, VAR[“key_buffer_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “query_cache_size”, VAR[“query_cache_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “innodb_buffer_pool_size”, VAR[“innodb_buffer_pool_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “innodb_additional_mem_pool_size”, VAR[“innodb_additional_mem_pool_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “innodb_log_buffer_size”, VAR[“innodb_log_buffer_size”]/1048576
printf “±—————————————–±——————-+\\n”
printf “| %40s | %15.3f MB |\\n”, “BASE MEMORY”, BASE_MEM/1048576
printf “±—————————————–±——————-+\\n”
printf “| %40s | %15.3f MB |\\n”, “sort_buffer_size”, VAR[“sort_buffer_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “read_buffer_size”, VAR[“read_buffer_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “read_rnd_buffer_size”, VAR[“read_rnd_buffer_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “join_buffer_size”, VAR[“join_buffer_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “thread_stack”, VAR[“thread_stack”]/1048576
printf “| %40s | %15.3f MB |\\n”, “binlog_cache_size”, VAR[“binlog_cache_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “tmp_table_size”, VAR[“tmp_table_size”]/1048576
printf “| %40s | %15.3f MB |\\n”, “net_buffer_length”, VAR[“net_buffer_length”]/1048576
printf “±—————————————–±——————-+\\n”
printf “| %40s | %15.3f MB |\\n”, “MEMORY PER CONNECTION”, MEM_PER_CONN/1048576
printf “±—————————————–±——————-+\\n”
printf “| %40s | %18d |\\n”, “Max_used_connections”, MAX_USED_CONN
printf “| %40s | %18d |\\n”, “max_connections”, MAX_CONN
printf “±—————————————–±——————-+\\n”
printf “| %40s | %15.3f MB |\\n”, “TOTAL (MIN)”, MEM_TOTAL_MIN/1048576
printf “| %40s | %15.3f MB |\\n”, “TOTAL (MAX)”, MEM_TOTAL_MAX/1048576
printf “±—————————————–±——————-+\\n”
}’

效果：

2、binlog分析脚本
使用场景：需要分析某时间段内，某些库、表的DML类型、影响行数。
比如分析2027年7月27日–2027年7月28日 order_db的所有dml汇总统计：

ls mysql-bin.00259[7-9] mysql-bin.00260[0-5]
mysql-bin.002597 mysql-bin.002598 mysql-bin.002599 mysql-bin.002600 mysql-bin.002601 mysql-bin.002602 mysql-bin.002603 mysql-bin.002604 mysql-bin.002605

mysqlbinlog –no-defaults -vv –base64-output=decode mysql-bin.00259[7-9] mysql-bin.00260[0-5] | egrep -v ‘@’| egrep -i ‘order_db’ |awk ‘/###/ {if($0~/UPDATE|INSERT|DELETE/)count[$2""$NF]++}END{for(i in count) print i,“\\t”,count[i]}’ | column -t | sort -k3nr

INSERT order_db.hangfire_job_history 36847
INSERT order_db.user_login_record 16
UPDATE order_db.user 16
UPDATE order_db.device 5