mysql 巡检脚本 - 墨天轮文档

mysql 巡检脚本

祈

1732

8页

104次

2021-11-01

5墨值下载

#!/bin/bash

host="127.0.0.1" #数据库 IP

port="3306" #数据库端口

userName="username" #用户名

password="password" #密码

dbname="mysql" #数据库名称

dbset="--default-character-set=utf8 -A" # 字符集

base='/usr/' #mysql 软件安装路径

##数据文件位置##

echo "================= mysql 配置信息 ==============================="

echo "========= 基本配置信息 ==========="

lower_case_table_names="show 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`

_port="show 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' ' -f4`

socket="show 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' ' -f4`

skip_name_resolve="show 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' '

-f4`

character_set_server="show 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' ' -f4`

interactive_timeout="show 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' ' -f4`

wait_timeout="show 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' ' -f4`

query_cache_type="show 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' ' -f4`

innodb_version="show 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' ' -f4`

trx_isolation="show 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' '

-f4`

transaction_isolation="show 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' ' -f4`

datadir="show 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' ' -f4`

echo "========= 连接数配置信息 ==========="

max_connections="show 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' '

-f4`

Max_used_connections="show 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' ' -f4`

max_connect_errors="show 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' ' -f4`

echo "========= binlog 配置信息 ==========="

sync_binlog="show 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'

' -f4`

binlog_format="show 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' ' -f4`

log_bin="show 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' ' -f4`

expire_logs_days="show 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' ' -f4`

binlog_cache_size="show 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' ' -f4`

max_binlog_cache_size="show 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' '

-f4`

max_binlog_size="show 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' ' -f4`

master_info_repository="show 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' ' -f4`

relay_log_info_repository="show 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' ' -f4`

relay_log_recovery="show 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' ' -f4`

echo "========= GTID 配置信息 ==========="

gtid_mode="show 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' ' -f4`

enforce_gtid_consistency="show 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' ' -f4`

echo "（MySQL 官方解释说当启用 enforce_gtid_consistency 功能的时候，MySQL 只允许能够保障事

务安全，并且能够被日志记录的 SQL 语句被执行，像 create table … select 和 create temporary

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

log_slave_updates="show 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' ' -f4`

echo "======== innodb 配置信息 ========="

innodb_data_home_dir="show 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' ' -f4`

innodb_buffer_pool_size="show 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' ' -f4`

innodb_buffer_pool_instances="show 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' ' -f4`

innodb_log_file_size="show 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' ' -f4`

innodb_log_files_in_group="show 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' ' -f4`

innodb_flush_log_at_trx_commit="show 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}")

of 8

5墨值下载

相关文档

评论