在哪个网站做科目一考试题多用户wordpress

当前位置： 首页 > news >正文

在哪个网站做科目一考试题,多用户wordpress,wordpress瀑布流插件,住房和城乡建设部网站查询今日分享一个实用的MySQL一键巡检脚本#xff0c;脚本内容还不是很完善#xff0c;后续会继续进行优化。大家可以先在测试环境执行#xff0c;确认执行没问题后可以在生产环境进行操作#xff0c;问题的可以私信我。 MySQL一键巡检脚本的作用主要是帮助数据库管理员快速且…今日分享一个实用的MySQL一键巡检脚本脚本内容还不是很完善后续会继续进行优化。大家可以先在测试环境执行确认执行没问题后可以在生产环境进行操作问题的可以私信我。 MySQL一键巡检脚本的作用主要是帮助数据库管理员快速且系统性地检查和评估MySQL数据库实例的运行状态和健康状况。通过定期执行这样的巡检脚本可以及时发现潜在问题优化性能确保数据库的高可用性和稳定性。以下是MySQL一键巡检脚本的一些主要作用和目标

1. 性能监测 CPU使用率检查MySQL实例及其主机的CPU使用情况识别是否存在过高的CPU占用。 内存使用情况监测内存使用情况判断是否存在内存泄露或内存不足。 I/O性能检查磁盘I/O性能评估读写速度和延迟确保磁盘性能不会成为瓶颈。
   
2. 配置检查 重要参数检查审查MySQL配置文件中的关键参数如max_connections、innodb_buffer_pool_size等根据最佳实践进行调整。 安全设置检查用户权限、安全配置和日志等确保数据库的安全性。
   
3. 数据库状态 连接数监控检查当前连接数及最大连接数设置预防由于连接数过多导致的资源耗尽问题。 线程状态统计各类线程的状态识别锁等待、慢查询等问题。
   
4. 存储和容量监控 表空间使用统计数据库和表的大小监测表空间使用情况规划存储扩容。 数据文件和日志文件检查数据文件和日志文件的增长情况确保磁盘空间充足。
   
5. 日志和慢查询分析 错误日志分析MySQL错误日志获取近期发生的错误信息。 慢查询日志捕获并分析慢查询日志找出执行时间较长的SQL语句优化查询性能。
   
6. 备份和恢复状态 备份状态检查检查最近的备份时间和备份文件的完整性确保备份策略有效执行。 恢复测试定期测试备份文件的恢复情况验证备份的可用性。
   
7. 高可用性状态 主从复制状态检查主从复制的同步状态确保数据一致性和复制链路畅通。 自动故障切换检查高可用配置和故障切换机制的正常运作确保数据库在故障时能够快速恢复。 #!/bin/bashhost192.168.59.128port3306userNamerootpassword123456dbnamemysqlbase/usr/local/mysql##数据文件位置##echo mysql配置信息 echo 基本配置信息 lower_case_table_namesshow variables like lower_case_table_names;lower_case_table_names_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){lower_case_table_names})echo 不区分大小写 echo \({lower_case_table_names_val} | cut -d -f4_portshow variables like port;_port_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({_port})echo 端口 echo \){_port_val} | cut -d -f4socketshow variables like socket;socket_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){socket})echo socket的值 echo \({socket_val} | cut -d -f4skip_name_resolveshow variables like skip_name_resolve;skip_name_resolve_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({skip_name_resolve})echo 域名解析skip_name_resolve echo \){skip_name_resolve_val} | cut -d -f4character_set_servershow variables like character_set_server;character_set_server_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){character_set_server})echo 数据库字符集character_set_server echo \({character_set_server_val} | cut -d -f4interactive_timeoutshow variables like interactive_timeout;interactive_timeout_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({interactive_timeout})echo 交互式连接超时时间(mysql工具、mysqldump等)interactive_timeout(秒) echo \){interactive_timeout_val} | cut -d -f4wait_timeoutshow variables like wait_timeout;wait_timeout_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){wait_timeout})echo 非交互式连接超时时间默认的连接mysql api程序,jdbc连接数据库等wait_timeout(秒) echo \({wait_timeout_val} | cut -d -f4query_cache_typeshow variables like query_cache_type;query_cache_type_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({query_cache_type})echo 查询缓存query_cache_type echo \){query_cache_type_val} | cut -d -f4innodb_versionshow variables like innodb_version;innodb_version_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_version})echo 数据库版本 echo \({innodb_version_val} | cut -d -f4trx_isolationshow variables like tx_isolation;trx_isolation_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({trx_isolation})echo mysql5.6隔离级别trx_isolation echo \){trx_isolation_val} | cut -d -f4transaction_isolationshow variables like transaction_isolation;transaction_isolation_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){transaction_isolation})echo 隔离级别transaction_isolation echo \({transaction_isolation_val} | cut -d -f4datadirshow variables like %datadir%;datadir_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({datadir})echo mysql 数据文件存放位置 echo \){datadir_val} | cut -d -f4echo 连接数配置信息 max_connectionsshow variables like max_connections;max_connections_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){max_connections})echo 最大连接数max_connections echo \({max_connections_val} | cut -d -f4Max_used_connectionsshow status like Max_used_connections;Max_used_connections_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({Max_used_connections})echo 当前连接数Max_used_connections echo \){Max_used_connections_val} | cut -d -f4max_connect_errorsshow variables like max_connect_errors;max_connect_errors_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){max_connect_errors})echo 最大错误连接数max_connect_errors echo \({max_connect_errors_val} | cut -d -f4echo binlog配置信息 sync_binlogshow variables like sync_binlog;sync_binlog_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({sync_binlog})echo sync_binlog(0|1|n查看是否采用双1模式) echo \){sync_binlog_val} | cut -d -f4binlog_formatshow variables like binlog_format;binlog_format_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){binlog_format})echo binlog格式 echo \({binlog_format_val} | cut -d -f4log_binshow variables like log-bin;log_bin_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({log-bin})echo binlog文件log-bin echo \){log_bin_val} | cut -d -f4expire_logs_daysshow variables like expire_logs_days;expire_logs_days_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){expire_logs_days})echo binlog文件过期时间 echo \({expire_logs_days_val} | cut -d -f4binlog_cache_sizeshow variables like binlog_cache_size;binlog_cache_size_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({binlog_cache_size})echo binlog_cache_size echo \){binlog_cache_size_val} | cut -d -f4max_binlog_cache_sizeshow variables like max_binlog_cache_size;max_binlog_cache_size_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){max_binlog_cache_size})echo max_binlog_cache_size echo \({max_binlog_cache_size_val} | cut -d -f4max_binlog_sizeshow variables like max_binlog_size;max_binlog_size_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({max_binlog_size})echo binlog文件大小 echo \){max_binlog_size_val} | cut -d -f4master_info_repositoryshow variables like master_info_repository;master_info_repository_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){master_info_repository})echo master_info_repository(table|file,建议用table) echo \({master_info_repository_val} | cut -d -f4relay_log_info_repositoryshow variables like relay_log_info_repository;relay_log_info_repository_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({relay_log_info_repository})echo relay_log_info_repository(table|file,建议用table) echo \){relay_log_info_repository_val} | cut -d -f4relay_log_recoveryshow variables like relay_log_recovery;relay_log_recovery_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){relay_log_recovery})echo relay_log_info_repository(建议开启) echo \({relay_log_recovery_val} | cut -d -f4echo GTID配置信息 gtid_modeshow variables like gtid_mode;gtid_mode_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({gtid_mode})echo 是否开启gtid_mode echo \){gtid_mode_val} | cut -d -f4enforce_gtid_consistencyshow variables like enforce_gtid_consistency;enforce_gtid_consistency_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){enforce_gtid_consistency})echo enforce_gtid_consistency是否开启 echo \({enforce_gtid_consistency_val} | cut -d -f4echo MySQL官方解释说当启用enforce_gtid_consistency功能的时候MySQL只允许能够保障事务安全并且能够被日志记录的SQL语句被执行像create table … select 和 create temporary table语句以及同时更新事务表和非事务表的SQL语句或事务都不允许执行log_slave_updatesshow variables like log_slave_updates;log_slave_updates_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({log_slave_updates})echo 级联复制是否开启log_slave_updates echo \){log_slave_updates_val} | cut -d -f4echo innodb配置信息 innodb_data_home_dirshow variables like innodb_data_home_dir;innodb_data_home_dir_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_data_home_dir})echo innodb_data_home_dir echo \({innodb_data_home_dir_val} | cut -d -f4innodb_buffer_pool_sizeshow variables like innodb_buffer_pool_size;innodb_buffer_pool_size_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({innodb_buffer_pool_size})echo innodb_buffer_pool_size不超过内存的75% echo \){innodb_buffer_pool_size_val} | cut -d -f4innodb_buffer_pool_instancesshow variables like innodb_buffer_pool_instances;innodb_buffer_pool_instances_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_buffer_pool_instances})echo innodb_buffer_pool_instances(innodb_buffer_pool_size小于8G实例个数建议为1) echo \({innodb_buffer_pool_instances_val} | cut -d -f4innodb_log_file_sizeshow variables like innodb_log_file_size;innodb_log_file_size_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({innodb_log_file_size})echo redo文件的大小innodb_log_file_size echo \){innodb_log_file_size_val} | cut -d -f4innodb_log_files_in_groupshow variables like innodb_log_files_in_group;innodb_log_files_in_group_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_log_files_in_group})echo redo文件的个数innodb_log_files_in_group echo \({innodb_log_files_in_group_val} | cut -d -f4innodb_flush_log_at_trx_commitshow variables like innodb_flush_log_at_trx_commit;innodb_flush_log_at_trx_commit_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({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 -d -f4innodb_io_capacityshow variables like innodb_io_capacity;innodb_io_capacity_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_io_capacity})echo innodb_io_capacity机械硬盘200固态2000闪存20000 echo \({innodb_io_capacity_val} | cut -d -f4transaction_isolationshow variables like transaction_isolation;transaction_isolation_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({transaction_isolation})echo 隔离级别transaction_isolation echo \){transaction_isolation_val} | cut -d -f4trx_isolationshow variables like tx_isolation;trx_isolation_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){trx_isolation})echo mysql5.6隔离级别trx_isolation echo \({trx_isolation_val} | cut -d -f4innodb_max_undo_log_sizeshow variables like innodb_max_undo_log_size;innodb_max_undo_log_size_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({innodb_max_undo_log_size})echo undo大小innodb_max_undo_log_size echo \){innodb_max_undo_log_size_val} | cut -d -f4innodb_undo_tablespacesshow variables like innodb_undo_tablespaces;innodb_undo_tablespaces_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){innodb_max_undo_log_size})echo undo个数innodb_undo_tablespaces echo \({innodb_undo_tablespaces_val} | cut -d -f4echo rep配置信息 slave_parallel_typeshow variables like slave-parallel-type;slave_parallel_type_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({slave_parallel_type})echo slave复制模式 echo \){slave_parallel_type_val} | cut -d -f4slave_parallel_workersshow variables like slave-parallel-workers;slave_parallel_workers_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){slave_parallel_workers})echo slave并发复制 echo \({slave_parallel_workers_val} | cut -d -f4echo 内存配置情况 mem_dis_1show variables like innodb_buffer_pool_size;mem_dis_1_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_1})mem_dis_1_val_1echo \){mem_dis_1_val} | cut -d -f4mem_dis_1_val_2echo | awk {print \(mem_dis_1_val_1/1024/1024}echo InnoDB 数据和索引缓存 \)mem_dis_1_val_1mem_dis_2show variables like innodb_log_buffer_size;mem_dis_2_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_2})mem_dis_2_val_1echo \({mem_dis_2_val} | cut -d -f4mem_dis_2_val_2echo | awk {print \)mem_dis_2_val_1/1024/1024}echo InnoDB 日志缓冲区 \(mem_dis_2_val_1mem_dis_3show variables like binlog_cache_size;mem_dis_3_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_3})mem_dis_3_val_1echo \){mem_dis_3_val} | cut -d -f4mem_dis_3_val_2echo | awk {print \(mem_dis_3_val_1/1024/1024}echo 二进制日志缓冲区 \)mem_dis_3_val_1mem_dis_4show variables like thread_cache_size;mem_dis_4_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_4})echo 连接线程缓存 echo \(mem_dis_4_val | cut -d -f4mem_dis_5show variables like query_cache_size;mem_dis_5_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_5})echo 查询缓存 echo \){mem_dis_5_val} | cut -d -f4mem_dis_6show variables like table_open_cache;mem_dis_6_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_6})echo 表缓存 echo \({mem_dis_6_val} | cut -d -f4mem_dis_7show variables like table_definition_cache;mem_dis_7_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_7})echo 表定义缓存 echo \){mem_dis_7_val} | cut -d -f4mem_dis_8show variables like max_connections;mem_dis_8_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_8})echo 最大线程数 echo \({mem_dis_8_val} | cut -d -f4mem_dis_9show variables like thread_stack;mem_dis_9_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_9})echo 线程栈信息使用内存 echo \){mem_dis_9_val} | cut -d -f4mem_dis_10show variables like sort_buffer_size;mem_dis_10_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_10})echo 排序使用内存 echo \({mem_dis_10_val} | cut -d -f4mem_dis_11show variables like join_buffer_size;mem_dis_11_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_11})echo Join操作使用内存 echo \){mem_dis_11_val} | cut -d -f4mem_dis_12show variables like read_buffer_size;mem_dis_12_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_12})echo 顺序读取数据缓冲区使用内存 echo \({mem_dis_12_val} | cut -d -f4mem_dis_13show variables like read_rnd_buffer_size;mem_dis_13_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({mem_dis_13})echo 随机读取数据缓冲区使用内存 echo \){mem_dis_13_val} | cut -d -f4mem_dis_14show variables like tmp_table_size;mem_dis_14_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){mem_dis_14})echo 临时表使用内存 echo \({mem_dis_14_val} | cut -d -f4echo QPS Questions1show global status like Questions;Questions1_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({Questions1})sleep 1Questions2show global status like Questions;Questions2_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({Questions2})echo QPS\)((echo \({Questions2_val} | cut -d -f4-echo \){Questions1_val} | cut -d -f4))echo TPS Com_commitshow global status like Com_commit;Com_commit_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){Com_commit})Com_rollbackshow global status like Com_rollback;Com_rollback_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){Com_rollback})echo TPS \(((echo \){Com_commit_val} | cut -d -f4 echo \({Com_rollback_val} | cut -d -f4))##缓存命中率##echo 缓存命中情况 cache_hitsshow global status like QCache_hits;hits\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({cache_hits})hits_valecho \){hits} | cut -d -f4echo 缓存命中次数 \({hits_val}cache_not_hitsshow global status like Qcache_inserts;not_hits\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({cache_not_hits})not_hits_valecho \){not_hits} | cut -d -f4echo 缓存未命中次数 \({not_hits_val}cache_hits_rate_1\)((\(hits_val - \)not_hits_val))cache_hits_rate_2echo | awk {print \(cache_hits_rate_1/\)hits_val * 100}echo 缓存命中率 \({cache_hits_rate_2} %echo 主从复制 slave_parallel_typeshow variables like slave-parallel-type;slave_parallel_type_val\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e \({slave_parallel_type})echo slave复制模式 echo \){slave_parallel_type_val} | cut -d -f4slave_parallel_workersshow variables like slave-parallel-workers;slave_parallel_workers_val\((\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){slave_parallel_workers})echo slave并发复制 echo \({slave_parallel_workers_val} | cut -d -f4\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e show master status\G;\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e show slave status\G;echo 半同步复制 \){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e show variables like %semi%;echo 慢查询 slow_query_log_file\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e show variables like slow_query_log_file;|grep slow|awk {print \(2})slow_query_log\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e show variables like slow_query_log;|grep slow|awk {print \(2})long_query_time\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e show variables like long_query_time;|grep long_query_time|awk {print \(2})log_queries_not_using_indexes\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e show variables like log_queries_not_using_indexes;|grep log_queries_not_using_indexes|awk {print \(2})if [ \){slow_query_log} ON ];thenecho 慢查询状态(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 -s c -t 10 \){slow_query_log_file};elseecho 慢查询状态(slow_query_log)\({slow_query_log} 未开启慢查询。fi##等待事件##echo 数据库大小 \){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e SELECTtable_schema,sum( data_length )/ 1024 / 1024 / 1024 AS data_length,sum( index_length )/ 1024 / 1024 / 1024 AS index_length,sum( data_length index_length )/ 1024 / 1024 / 1024 AS sum_data_indexFROMinformation_schema.TABLESWHEREtable_schema NOT IN ( mysql, information_schema, performance_schema, sys )GROUP BYtable_schema;echo 数据碎片 \){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e SELECTTABLE_SCHEMA,TABLE_NAME,ENGINE,concat( splinter, G ) 碎片(G)FROM(SELECTTABLE_SCHEMA,TABLE_NAME,ENGINE,ROUND(( DATA_LENGTH INDEX_LENGTH - TABLE_ROWS * AVG_ROW_LENGTH )/ 1024 / 1024 / 1024 ) splinterFROMinformation_schema.TABLESWHERETABLE_TYPE BASE TABLE) aWHEREsplinter 1ORDER BYsplinter DESC;echo 锁查询 \){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e SELECTr.trx_isolation_level,r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread,r.trx_state waiting_trx_state,lr.lock_mode 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 waiting_trx_query,b.trx_id 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 JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_trx_idINNER JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_trx_idINNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id w.blocking_trx_idINNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id w.requesting_trx_id \G;echo 等待事件 top_event_10select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where count_star 0 order by sum_timer_wait desc limit 10;echo 等待事件 TOP 10\){base}/bin/mysql -h\({host} -u\){userName} -p\({password} \){dbname} -P\({port} -e \){top_event_10}echo 最近一周的错误日志 _time\((date -d 6 days ago %Y-%m-%d)\|\)(date -d 5 days ago %Y-%m-%d)|\((date -d 4 days ago %Y-%m-%d)\|\)(date -d 3 days ago %Y-%m-%d)|\((date -d 2 days ago %Y-%m-%d)\|\)(date -d 1 days ago %Y-%m-%d)|\((date -d 0 days ago %Y-%m-%d)log_error\)(\({base}/bin/mysql -h\){host} -u\({userName} -p\){password} \({dbname} -P\){port} -e show variables like log_error;|grep log_error|awk {print \(2})#grep -i -E error /home/logs/mysql/mysqld.err* | grep -E 2019-03-28|2019-06-14grep -i -E error \){log_error}| grep -E ${_time} 该脚本可以放在任意位置然后赋予X执行权限进行一键巡检同时也可以设置定时任务定时进行巡检并输出相关报告。 ./mysql_check.sh/data/check.txt 21