2
mysql 巡检脚本
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`
of 8
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论