。

大家可以先在测试环境执行#xff0c;确认执行没问题后可以在生产环境进行操作#xff0c;问题的可以私信我。

MySQL一键巡检脚本的作用主要是帮助数据库管理员快速且…今日分享一个实用的MySQL一键巡检脚本脚本内容还不是很完善后续会继续进行优化。

大家可以先在测试环境执行确认执行没问题后可以在生产环境进行操作问题的可以私信我。

MySQL一键巡检脚本的作用主要是帮助数据库管理员快速且系统性地检查和评估MySQL数据库实例的运行状态和健康状况。

通过定期执行这样的巡检脚本可以及时发现潜在问题优化性能确保数据库的高可用性和稳定性。

以下是MySQL一键巡检脚本的一些主要作用和目标

性能监测

CPU使用率检查MySQL实例及其主机的CPU使用情况识别是否存在过高的CPU占用。

I/O性能检查磁盘I/O性能评估读写速度和延迟确保磁盘性能不会成为瓶颈。

配置检查

重要参数检查审查MySQL配置文件中的关键参数如max_connections、innodb_buffer_pool_size等根据最佳实践进行调整。

数据库状态

连接数监控检查当前连接数及最大连接数设置预防由于连接数过多导致的资源耗尽问题。

存储和容量监控

表空间使用统计数据库和表的大小监测表空间使用情况规划存储扩容。

数据文件和日志文件检查数据文件和日志文件的增长情况确保磁盘空间充足。

日志和慢查询分析

慢查询日志捕获并分析慢查询日志找出执行时间较长的SQL语句优化查询性能。

备份和恢复状态

备份状态检查检查最近的备份时间和备份文件的完整性确保备份策略有效执行。

主从复制状态检查主从复制的同步状态确保数据一致性和复制链路畅通。

自动故障切换检查高可用配置和故障切换机制的正常运作确保数据库在故障时能够快速恢复。

#!/bin/bashhost192.168.59.128port3306userNamerootpassword123456dbnamemysqlbase/usr/local/mysql##数据文件位置##echo

mysql配置信息

lower_case_table_names;lower_case_table_names_val$(${base}/bin/mysql

-h${host}

port;_port_val$(${base}/bin/mysql

-h${host}

socket;socket_val$(${base}/bin/mysql

-h${host}

skip_name_resolve;skip_name_resolve_val$(${base}/bin/mysql

-h${host}

character_set_server;character_set_server_val$(${base}/bin/mysql

-h${host}

interactive_timeout;interactive_timeout_val$(${base}/bin/mysql

-h${host}

交互式连接超时时间(mysql工具、mysqldump等)interactive_timeout(秒)

echo

wait_timeout;wait_timeout_val$(${base}/bin/mysql

-h${host}

api程序,jdbc连接数据库等wait_timeout(秒)

echo

query_cache_type;query_cache_type_val$(${base}/bin/mysql

-h${host}

innodb_version;innodb_version_val$(${base}/bin/mysql

-h${host}

tx_isolation;trx_isolation_val$(${base}/bin/mysql

-h${host}

transaction_isolation;transaction_isolation_val$(${base}/bin/mysql

-h${host}

%datadir%;datadir_val$(${base}/bin/mysql

-h${host}

max_connections;max_connections_val$(${base}/bin/mysql

-h${host}

Max_used_connections;Max_used_connections_val$(${base}/bin/mysql

-h${host}

max_connect_errors;max_connect_errors_val$(${base}/bin/mysql

-h${host}

sync_binlog;sync_binlog_val$(${base}/bin/mysql

-h${host}

binlog_format;binlog_format_val$(${base}/bin/mysql

-h${host}

log-bin;log_bin_val$(${base}/bin/mysql

-h${host}

expire_logs_days;expire_logs_days_val$(${base}/bin/mysql

-h${host}

binlog_cache_size;binlog_cache_size_val$(${base}/bin/mysql

-h${host}

max_binlog_cache_size;max_binlog_cache_size_val$(${base}/bin/mysql

-h${host}

max_binlog_size;max_binlog_size_val$(${base}/bin/mysql

-h${host}

master_info_repository;master_info_repository_val$(${base}/bin/mysql

-h${host}

master_info_repository(table|file,建议用table)

echo

-f4relay_log_info_repositoryshow

variables

relay_log_info_repository;relay_log_info_repository_val$(${base}/bin/mysql

-h${host}

${relay_log_info_repository})echo

relay_log_info_repository(table|file,建议用table)

echo

${relay_log_info_repository_val}

cut

relay_log_recovery;relay_log_recovery_val$(${base}/bin/mysql

-h${host}

relay_log_info_repository(建议开启)

echo

gtid_mode;gtid_mode_val$(${base}/bin/mysql

-h${host}

-f4enforce_gtid_consistencyshow

variables

enforce_gtid_consistency;enforce_gtid_consistency_val$(${base}/bin/mysql

-h${host}

${enforce_gtid_consistency})echo

echo

${enforce_gtid_consistency_val}

cut

MySQL官方解释说当启用enforce_gtid_consistency功能的时候MySQL只允许能够保障事务安全并且能够被日志记录的SQL语句被执行像create

table

table语句以及同时更新事务表和非事务表的SQL语句或事务都不允许执行log_slave_updatesshow

variables

log_slave_updates;log_slave_updates_val$(${base}/bin/mysql

-h${host}

innodb_data_home_dir;innodb_data_home_dir_val$(${base}/bin/mysql

-h${host}

innodb_buffer_pool_size;innodb_buffer_pool_size_val$(${base}/bin/mysql

-h${host}

${innodb_buffer_pool_size})echo

innodb_buffer_pool_size不超过内存的75%

echo

-f4innodb_buffer_pool_instancesshow

variables

innodb_buffer_pool_instances;innodb_buffer_pool_instances_val$(${base}/bin/mysql

-h${host}

${innodb_buffer_pool_instances})echo

innodb_buffer_pool_instances(innodb_buffer_pool_size小于8G实例个数建议为1)

echo

${innodb_buffer_pool_instances_val}

cut

innodb_log_file_size;innodb_log_file_size_val$(${base}/bin/mysql

-h${host}

-f4innodb_log_files_in_groupshow

variables

innodb_log_files_in_group;innodb_log_files_in_group_val$(${base}/bin/mysql

-h${host}

${innodb_log_files_in_group})echo

redo文件的个数innodb_log_files_in_group

echo

${innodb_log_files_in_group_val}

cut

-f4innodb_flush_log_at_trx_commitshow

variables

innodb_flush_log_at_trx_commit;innodb_flush_log_at_trx_commit_val$(${base}/bin/mysql

-h${host}

${innodb_flush_log_at_trx_commit})echo

innodb_flush_log_at_trx_commit0|1|2跟sync_binlog双1

echo

${innodb_flush_log_at_trx_commit_val}

cut

innodb_io_capacity;innodb_io_capacity_val$(${base}/bin/mysql

-h${host}

innodb_io_capacity机械硬盘200固态2000闪存20000

echo

transaction_isolation;transaction_isolation_val$(${base}/bin/mysql

-h${host}

tx_isolation;trx_isolation_val$(${base}/bin/mysql

-h${host}

-f4innodb_max_undo_log_sizeshow

variables

innodb_max_undo_log_size;innodb_max_undo_log_size_val$(${base}/bin/mysql

-h${host}

${innodb_max_undo_log_size})echo

echo

${innodb_max_undo_log_size_val}

cut

innodb_undo_tablespaces;innodb_undo_tablespaces_val$(${base}/bin/mysql

-h${host}

${innodb_max_undo_log_size})echo

echo

slave-parallel-type;slave_parallel_type_val$(${base}/bin/mysql

-h${host}

slave-parallel-workers;slave_parallel_workers_val$(${base}/bin/mysql

-h${host}

innodb_buffer_pool_size;mem_dis_1_val$(${base}/bin/mysql

-h${host}

${mem_dis_1})mem_dis_1_val_1echo

${mem_dis_1_val}

$mem_dis_1_val_1/1024/1024}echo

InnoDB

innodb_log_buffer_size;mem_dis_2_val$(${base}/bin/mysql

-h${host}

${mem_dis_2})mem_dis_2_val_1echo

${mem_dis_2_val}

$mem_dis_2_val_1/1024/1024}echo

InnoDB

binlog_cache_size;mem_dis_3_val$(${base}/bin/mysql

-h${host}

${mem_dis_3})mem_dis_3_val_1echo

${mem_dis_3_val}

$mem_dis_3_val_1/1024/1024}echo

二进制日志缓冲区

thread_cache_size;mem_dis_4_val$(${base}/bin/mysql

-h${host}

query_cache_size;mem_dis_5_val$(${base}/bin/mysql

-h${host}

table_open_cache;mem_dis_6_val$(${base}/bin/mysql

-h${host}

table_definition_cache;mem_dis_7_val$(${base}/bin/mysql

-h${host}

max_connections;mem_dis_8_val$(${base}/bin/mysql

-h${host}

thread_stack;mem_dis_9_val$(${base}/bin/mysql

-h${host}

sort_buffer_size;mem_dis_10_val$(${base}/bin/mysql

-h${host}

join_buffer_size;mem_dis_11_val$(${base}/bin/mysql

-h${host}

read_buffer_size;mem_dis_12_val$(${base}/bin/mysql

-h${host}

read_rnd_buffer_size;mem_dis_13_val$(${base}/bin/mysql

-h${host}

tmp_table_size;mem_dis_14_val$(${base}/bin/mysql

-h${host}

Questions;Questions1_val$(${base}/bin/mysql

-h${host}

Questions;Questions2_val$(${base}/bin/mysql

-h${host}

Com_commit;Com_commit_val$(${base}/bin/mysql

-h${host}

Com_rollback;Com_rollback_val$(${base}/bin/mysql

-h${host}

QCache_hits;hits$(${base}/bin/mysql

-h${host}

Qcache_inserts;not_hits$(${base}/bin/mysql

-h${host}

${cache_not_hits})not_hits_valecho

${not_hits}

${not_hits_val}cache_hits_rate_1$(($hits_val

$not_hits_val))cache_hits_rate_2echo

awk

slave-parallel-type;slave_parallel_type_val$(${base}/bin/mysql

-h${host}

slave-parallel-workers;slave_parallel_workers_val$(${base}/bin/mysql

-h${host}

slow_query_log_file$(${base}/bin/mysql

-h${host}

$2})slow_query_log$(${base}/bin/mysql

-h${host}

$2})long_query_time$(${base}/bin/mysql

-h${host}

$2})log_queries_not_using_indexes$(${base}/bin/mysql

-h${host}

log_queries_not_using_indexes;|grep

log_queries_not_using_indexes|awk

{print

慢查询状态(slow_query_log)${slow_query_log}

;long_query_time(s)

${long_query_time};log_queries_not_using_indexes:

${log_queries_not_using_indexes};慢查询top10如下mysqldumpslow

${slow_query_log_file};elseecho

慢查询状态(slow_query_log)${slow_query_log}

数据库大小

sum_data_indexFROMinformation_schema.TABLESWHEREtable_schema

NOT

SELECTTABLE_SCHEMA,TABLE_NAME,ENGINE,concat(

splinter,

碎片(G)FROM(SELECTTABLE_SCHEMA,TABLE_NAME,ENGINE,ROUND((

DATA_LENGTH

splinterFROMinformation_schema.TABLESWHERETABLE_TYPE

BASE

SELECTr.trx_isolation_level,r.trx_id

waiting_trx_id,r.trx_mysql_thread_id

waiting_trx_lock_mode,lr.lock_type

waiting_trx_lock_type,lr.lock_table

waiting_trx_lock_table,lr.lock_index

waiting_trx_lock_index,r.trx_query

blocking_trx_id,b.trx_mysql_thread_id

blocking_trx_thread,b.trx_state

blocking_trx_state,lb.lock_mode

blocking_trx_lock_mode,lb.lock_type

blocking_trx_lock_type,lb.lock_table

blocking_trx_lock_table,lb.lock_index

blocking_trx_lock_index,b.trx_query

blocking_queryFROMinformation_schema.innodb_lock_waits

wINNER

information_schema.innodb_locks

lb.lock_trx_id

information_schema.innodb_locks

lr.lock_trx_id

performance_schema.events_waits_summary_global_by_event_name

where

%Y-%m-%d)log_error$(${base}/bin/mysql

-h${host}

该脚本可以放在任意位置然后赋予X执行权限进行一键巡检同时也可以设置定时任务定时进行巡检并输出相关报告。

./mysql_check.sh/data/check.txt