mysql管理シナリオ
20560 ワード
作者のホームページ:http://www.ruzuojun.com/
#!/bin/bash
#script: my
#version: 1.0
#purpose: this script can manage you mysql server Faster
#author: ruzuojun
#script update url: www.ruzuojun.com
#bug report email: [email protected]
#create date: 2013-10-12
#update_date: 2014-04-18
#Important statement: The script can be free to use, but the author does not take responsibility for the script, any economic loss or safety issues.Thanks
#this config must be change before usage
###########################################################################
host="127.0.0.1" #mysql server
port="3306" #mysql port
username="root" #mysql username
password="root" #mysql password
basedir="" # if not define. default /usr/local/mysql
datadir="" # if not define. default $basedir/data
#check privileges,This script must be run as root.
############################################################################
if [ $EUID -ne 0 ]; then
echo "[ERROR]: Please run this script with root privileges."
exit 1
fi
#check system,This script must be run on CentOS/RHEL only.
############################################################################
if [ ! -f /etc/redhat-release ]; then
echo "[ERROR]: Sorry, this script is for CentOS/RHEL only."
exit 1
fi
# The following variables are only set for letting mysql.server find things.
#############################################################################
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
# export PATH
#############################################################################
PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH
#parameter define
#############################################################################
login_param="-h$host -P$port -u$username -p$password"
mode=$1
value=$2
#Command
#############################################################################
case "$mode" in
'dba')
#Login mysql server; Command: #my dba
cd $basedir
if test -x $bindir/mysql
then
$bindir/mysql $login_param
else
echo "Couldn't find MySQL server ($bindir/mysql)"
fi
;;
'listen')
#Print the mysql listen port; Command: #my listen
listen=`netstat -nutpl |grep LISTEN |grep mysql`
if [ "$listen" == "0" ];then
echo "This server is not listen mysql. please check mysql server status."
else
netstat -nutpl |grep LISTEN |grep mysql
fi
;;
'ping')
#Check if mysqld is alive; Command: #my ping
for((;;))
do
if [ -f "$bindir/mysqladmin" ] ;then
$bindir/mysqladmin $login_param ping
echo "************************"
else
echo "The mysqladmin script is not find in $bindir."
exit
fi
sleep 1
done
;;
'status-inc')
#Check mysql status change value; Command: #my status-inc [parameter]; Example: my status-inc Buffer
if [ -f "$bindir/mysqladmin" ] ;then
$bindir/mysqladmin $login_param extended-status -r -i 3 |grep "$value"
else
echo "The mysqladmin script is not find in $bindir."
exit
fi
;;
'active')
#Print mysql processlist once ; Command: #my active
echo "**********************************************************************************************************************************"
num_active=`$bindir/mysql $login_param -e 'show full processlist;'|grep -v Sleep|grep -v Command |wc -l`
num_sleep=`$bindir/mysql $login_param -e 'show full processlist;'|grep Sleep|grep -v Command |wc -l`
echo "Active sessions:$num_active Sleep sessions:$num_sleep"
echo "**********************************************************************************************************************************"
$bindir/mysql $login_param -e "show full processlist;"|grep -v Sleep |sed 's/\
//g' |sed 's/\\t//g'
echo "**********************************************************************************************************************************"
;;
'top')
#Print mysql processlist ever and again; Commdnd: #my top
for((;;))
do
clear
echo "**********************************************************************************************************************************"
num_active=`$bindir/mysql $login_param -e 'show full processlist;'|grep -v Sleep|grep -v Command |wc -l`
num_sleep=`$bindir/mysql $login_param -e 'show full processlist;'|grep Sleep|grep -v Command |wc -l`
echo "Active sessions:$num_active Sleep sessions:$num_sleep"
echo "**********************************************************************************************************************************"
$bindir/mysql $login_param -e "show full processlist;"|grep -v Sleep |sed 's/\
//g' |sed 's/\\t//g'
echo "**********************************************************************************************************************************"
sleep 2
done
;;
'kill')
#Kill session by session id ; Command: #my kill [pid parameter]; Example: my kill 20000
if [[ "$value" =~ "^[0-9]+$" ]] ;then
$bindir/mysql $login_param -e "kill $value"
echo "killed mysql processes id is $value"
else
echo "The value is wrong, must be a number"
fi
;;
'killall')
#Kill all session if time > N seconds; Command: #my killall [N second parameter]; Example: my killall 60
if [[ "$value" =~ "^[0-9]+$" ]] ;then
$bindir/mysql $login_param -e 'show processlist'|grep -v -i -E "system users|replication|processlist|Sleep|Command|Connect|master|Slave|information_schema"|awk '{if($6>'$value')print $1}' |while read line; do $bindir/mysql $login_param -e "kill query $line" ; echo "mysql processes id $line been killed"; done
else
echo "The value is wrong, must be a number"
fi
;;
'query')
#Print query avg number before two seconds; Command: #my query
for((;;))
do
query=`$bindir/mysql $login_param -e "show global status like 'Queries';"|grep Queries|awk '{if($2>0) print $2}'`
sleep 2
query2=`$bindir/mysql $login_param -e "show global status like 'Queries';"|grep Queries|awk '{if($2>0) print $2}'`
let query_avg=($query2-$query)/2
time=`date +"%H:%M:%S"`
echo "[$time] query avg number: $query_avg"
echo "**********************************************************"
sleep 1
done
;;
'dml')
#Print current dml number per seconds from com status; Command: #my dml
for((;;))
do
Com_select=`$bindir/mysql $login_param -e "show global status like 'Com_select';"|grep Com_select|awk '{if($2>0) print $2}'`
Com_insert=`$bindir/mysql $login_param -e "show global status like 'Com_insert';"|grep Com_insert|awk '{if($2>0) print $2}'`
Com_update=`$bindir/mysql $login_param -e "show global status like 'Com_update';"|grep Com_update|awk '{if($2>0) print $2}'`
Com_delete=`$bindir/mysql $login_param -e "show global status like 'Com_delete';"|grep Com_delete|awk '{if($2>0) print $2}'`
Com_commit=`$bindir/mysql $login_param -e "show global status like 'Com_commit';"|grep Com_commit|awk '{if($2>0) print $2}'`
Com_rollback=`$bindir/mysql $login_param -e "show global status like 'Com_rollback';"|grep Com_rollback|awk '{if($2>0) print $2}'`
sleep 1
Com_select_2=`$bindir/mysql $login_param -e "show global status like 'Com_select';"|grep Com_select|awk '{if($2>0) print $2}'`
Com_insert_2=`$bindir/mysql $login_param -e "show global status like 'Com_insert';"|grep Com_insert|awk '{if($2>0) print $2}'`
Com_update_2=`$bindir/mysql $login_param -e "show global status like 'Com_update';"|grep Com_update|awk '{if($2>0) print $2}'`
Com_delete_2=`$bindir/mysql $login_param -e "show global status like 'Com_delete';"|grep Com_delete|awk '{if($2>0) print $2}'`
Com_commit_2=`$bindir/mysql $login_param -e "show global status like 'Com_commit';"|grep Com_commit|awk '{if($2>0) print $2}'`
Com_rollback_2=`$bindir/mysql $login_param -e "show global status like 'Com_rollback';"|grep Com_rollback|awk '{if($2>0) print $2}'`
let select=$Com_select_2-$Com_select
let insert=$Com_insert_2-$Com_insert
let update=$Com_update_2-$Com_update
let delete=$Com_delete_2-$Com_delete
let commit=$Com_commit_2-$Com_commit
let rollback=$Com_rollback_2-$Com_rollback
time=`date +"%H:%M:%S"`
echo "[$time] select:$select insert:$insert update:$update delete:$delete commit:$commit rollback:$rollback"
echo "*******************************************************************************************"
sleep 1
done
;;
'rtxlock')
#Print innodb rtx locks info; Command: #my rtxlock
$bindir/mysql $login_param -e "select * from information_schema.INNODB_TRX a, information_schema.INNODB_LOCKS b, information_schema.INNODB_LOCK_WAITS c, information_schema.INNODB_TRX d where a.trx_query like '%T_PRICING_RESULT%' and a.trx_id=b.lock_trx_id and a.trx_id=c.requesting_trx_id and c.blocking_trx_id=d.trx_id\G"
;;
'errorlog')
#Print last 50 line error log info; Command: #my errorlog
errorlog_file=`$bindir/mysql $login_param -e "show variables like 'log_error';"|grep log_error |awk -F ' ' '{print $2}'`
echo "**********************************************************************************************************************************"
tail -n 100 $errorlog_file
echo "**********************************************************************************************************************************"
echo "MySQL Error log write at: $errorlog_file"
echo "**********************************************************************************************************************************"
;;
'tailerror')
#Print error log info real time; Command: #my tailerror
errorlog_file=`$bindir/mysql $login_param -e "show variables like 'log_error';"|grep log_error |awk -F ' ' '{print $2}'`
echo "**********************************************************************************************************************************"
tail -f -n 50 $errorlog_file
;;
'mycnf')
#Print my.cnf info; Command: #my mycnf [parameter] ;you can find my.cnf file by #mysql --verbose --help |grep -A 1 'Default options'
if [ -f "/etc/my.cnf" ]; then
more /etc/my.cnf |grep "$value"
elif [ -f "/etc/mysql/my.cnf" ]; then
more /etc/mysql/my.cnf |grep "$value"
elif [ -f "$basedir/etc/my.cnf" ]; then
more $basedir/etc/my.cnf |grep "$value"
elif [ -f "~/.my.cnf" ]; then
more ~/.my.cnf |grep "$value"
else
echo "Error:not find my.cnf in /etc/my.cnf /etc/mysql/my.cnf $basedir/etc/my.cnf ~/.my.cnf"
fi
;;
'variables')
#Print mysql variables info; Command: #my variables [parameter]
$bindir/mysql $login_param -e "show variables like '%$value%';"
;;
'status')
#Print mysql status info; Command: #my status [parameter] ; Example: #my status locks
$bindir/mysql $login_param -e "show status like '%$value%';"
;;
'innodb')
#Print innodb status info; Command: #my innodb
$bindir/mysql $login_param -e "show engine innodb status;" |more
;;
'mutex')
#Print mutex status info; Command: #my mutex
$bindir/mysql $login_param -e "show engine innodb mutex;" |more
;;
'exec')
#Execute SQL statement; Command:#my exec [SQL parameter] ; Example: #my exec "select * from test.user"
if [ "$value" == "" ];then
echo "Error:please give a SQL statement."
else
$bindir/mysql $login_param -e "$value;"
fi
;;
'user')
#Print mysql user list info; Command: #my user
$bindir/mysql $login_param -e "select user,host,password from mysql.user;"
;;
'grants')
#Print mysql user grant info; Command: #my grants [parameter] ; Example: #my grants root@localhost
$bindir/mysql $login_param -e "show grants for $value"
;;
'history')
#Print mysql history file info; Command: #my history [parameter] ; Example: #my history update
cat /root/.mysql_history |grep "$value"
;;
'doc')
#Print mysql refman doc; Command: #my doc [parameter] ; Example: #my doc 'create table'
$bindir/mysql $login_param -e "help $value"
;;
'version')
#Print mysql server version; Command: #my version ; Example: #my version
$bindir/mysql $login_param -e "select version() as version"
;;
'slave')
#Print mysql slave info; Command: #my slave
c=`$bindir/mysql $login_param -e "show slave status;" |wc -l`
if [ "$c" == "0" ];then
echo "Note:This mysql server is not a slave."
else
$bindir/mysql $login_param -e "show slave status\G"
fi
;;
'delay')
#Print mysql slave delay to master time ever and again; Command: #my delay
for((;;))
do
c=`$bindir/mysql $login_param -e "show slave status;" |wc -l`
if [ "$c" == "0" ];then
echo "Note:This mysql server is not a slave."
else
$bindir/mysql $login_param -e "show slave status\G" |grep Seconds_Behind_Master
echo "*************************************************"
fi
sleep 1
done
;;
'admin')
#Execute mysqladmin Command; Command: #my admin [Command parameter]; Example: #my admin stop-slave
if [ -f "$bindir/mysqladmin" ] ;then
$bindir/mysqladmin $login_param $value
else
echo "The mysqladmin script is not find in $bindir."
exit
fi
;;
'bigtable')
#Print mysql large table info; Command: #my bigtable ; Example: #my bigtable
echo "big table check start, please wait......"
$bindir/mysql $login_param -e "SELECT table_schema as 'DB',table_name as 'TABLE',CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), '') 'TOTAL(M)' , table_comment as COMMENT FROM information_schema.TABLES ORDER BY data_length + index_length DESC limit 20;"
;;
'source')
#Print mysql connect source info; Command: #my source ; Example: #my source
$bindir/mysql $login_param -e "select SUBSTRING_INDEX(host,':',1) as connect_server, user connect_user,db connect_db, count(SUBSTRING_INDEX(host,':',1)) as connect_count from information_schema.processlist where db is not null and db!='information_schema' and db !='performance_schema' group by connect_server order by connect_count desc limit 20;"
;;
'nettop')
#Print netstat top ip connect source info; Command: #my nettop ; Example: #my nettop
echo "================== Top 20 netstat IP Source =========================="
/bin/netstat -nat|grep ESTABLISHED|awk '{print $5}'|awk -F: '{print $1}'|sort|uniq -c|sort -n -r |head -n 20
echo "======================================================================"
;;
'--help')
#for help
echo "my help info:"
echo "support-site:www.ruzuojun.com bug-report:[email protected]"
echo "======================================================================="
echo "--help Print my tools help info; Command: #my --help"
echo "dba Login mysql server; Command: #my dba"
echo "doc Print mysql refman doc onine; Command: #my doc [parameter] ; Example: #my doc 'create table'"
echo "ping Check if mysqld is alive; Command: #my ping"
echo "version Pirnt mysql server version; Command: #my version"
echo "listen Print the mysql listen port; Command: #my listen"
echo "active Print mysql processlist once ; Command: #my active"
echo "top Print mysql processlist ever and again; Commdnd: #my top"
echo "kill kill session by session id ; Command: #my kill [pid parameter]; Example: my kill 20000"
echo "killall kill all session if time > N seconds; Command: #my killall [N second parameter]; Example: my killall 60"
echo "query Print query avg number before two seconds; Command: #my query"
echo "dml Print current dml number per seconds from com status; Command: #my dml"
echo "rtxlock Print innodb rtx locks info; Command: #my rtxlock"
echo "errorlog Print last 50 line error log info; Command: #my errorlog"
echo "tailerror Print last error log info real time; Command: #my tailerror"
echo "mycnf Print my.cnf info; Command: #my mycnf [parameter] ;you can find my.cnf file by #mysql --verbose --help |grep -A 1 'Default options'"
echo "variables Print mysql variables info; Command: #my variables [parameter]"
echo "status Print mysql status info; Command: #my status [parameter] ; Example: #my status locks"
echo "status-inc Check mysql status change value; Command: #my status-inc [parameter]; Example: my status-inc Buffer"
echo "innodb Print innodb status info; Command: #my innodb"
echo "mutex Print innodb mutex status info; Command: #my mutex"
echo "user Print mysql user list info; Command: #my user"
echo "grants Print mysql user grant info; Command: #my grants [parameter] ; Example: #my grants root@localhost"
echo "history Print mysql history file info; Command: #my history [parameter] ; Example: #my history update"
echo "slave Print mysql slave info; Command: #my slave"
echo "delay Print mysql slave delay to master time ever and again; Command: #my delay"
echo "exec Execute SQL statement; Command:#my exec [SQL parameter] ; Example: #my exec 'select * from test.user' "
echo "admin Execute mysqladmin Command; Command: #my admin [Command parameter]; Example: #my admin stop-slave"
echo "bigtable Print mysql large space table top 20; Command: #my bigtable; Example: #my bigtable"
echo "source Print mysql connect source info; Command: #my source ; Example: #my source"
echo "nettop Print netstat top ip connect source info; Command: #my nettop ; Example: #my nettop"
exit
;;
*)
echo "Please input '#my --help' to read the help info."
;;
esac
exit 0