#/bin/sh
dodir=/home/mysql/ebplugin/bin
shname=`echo $0|awk -F'/' '{print $NF}'`
dS=`date +%Y%m%d%H%M%S`
mysqlpath=0 #mysql目录，如果不配置就设置为0，如果目录比较特殊，请手动修改配置
user=root #如果脚本里写入用户名和密码，则后面的命令默认以该用户名和密码执行。如果不像填写，请写入0，则默认使用root，且每次需要输入密码
passwd=""
init()
{
if [ X$mysqlpath = X0 ] || [ X$mysqlpath = X ]
then
which mysqld > $dodir/init.tmp.$dS
if [ $? != 0 ]
then
echo "获取mysql目录失败"
exit 1
fi
mysqlpath=`tail $dodir/init.tmp.$dS|awk -F'/' '{print $(NF-2)}'`
fi
}
startmysql()
{
#启动数据库
nohup mysqld_safe --defaults-file=/home/mysql/${mysqlpath}/my.cnf --user=mysql &
}
stopmysql()
{
#停数据库
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysqladmin -uroot -p shutdown
else
mysqladmin -u${user} -p"${passwd}" shutdown
fi
}
startmgr()
{
#启动组复制
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e"start group_replication;"
else
mysql -u${user} sys -p"${passwd}" -e"start group_replication;"
fi
}
stopmgr()
{
#停止组复制
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e"stop group_replication;"
else
mysql -u${user} -p"${passwd}" sys -e"stop group_replication;"
fi
}
show_mgr()
{
#查看组复制状态
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e"select * from performance_schema.replication_group_members;"
else
mysql -u${user} -p"${passwd}" sys -e"select * from performance_schema.replication_group_members;"
fi
}
show_seq()
{
#查看顺扫语句统计
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e "select * from statements_with_full_table_scans where db not in ('mysql','sys','information_schema','performance_schema')\G"
else
mysql -u${user} -p"${passwd}" sys -e "select * from statements_with_full_table_scans where db not in ('mysql','sys','information_schema','performance_schema')\G"
fi
}
show_status()
{
#查看mysql状态
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e 'show global status' > $dodir/global_status.lst.$dS
else
mysql -u${user} -p"${passwd}" sys -e 'show global status' > $dodir/global_status.lst.$dS
fi
cat $dodir/global_status.lst.$dS |awk '
function trim(str) { sub(/^[ \t]*/,"",str); sub(/[ \t]*$/,"",str); return str;}
{t[trim($1)]=trim($2);}
END {
print "Profile"
print "Disk: dskreads bufreads %cached ";
pr=t["Innodb_buffer_pool_reads"]
br=t["Innodb_buffer_pool_read_requests"]-t["Innodb_buffer_pool_reads"]
printf "%7s %-10d %-10d %.2f \n\n","", pr,br,br/(pr+br)*100;
rread=t["Innodb_rows_read"]
rinsert=t["Innodb_rows_inserted"];
rupdate=t["Innodb_rows_updated"];
rdelete=t["Innodb_rows_deleted"];
commits=t["Handler_commit"];
rollbk=t["Handler_rollback"];
print "Trans: Innodb_rows_read Innodb_rows_inserted Innodb_rows_updated Innodb_rows_deleted commits rollbk"
printf "%7s %-17d %-21d %-19d %-21d %-10d %-10d \n\n","", rread,rinsert,rupdate,rdelete,commits,rollbk;
sfj=t["select_full_join"];
src=t["select_range_check"];
sscan=t["select_scan"];
sq=t["Slow_queries"];
print "Stmts: select_full_join select_range_check select_scan Slow_queries "
printf "%7s %-17d %-21d %-19d %-21d \n\n","", sfj,src,sscan,sq;
sort_mp=t["Sort_merge_passes"];
sort_range=t["Sort_range"];
sort_rows=t["Sort_rows"];
sort_scan=t["Sort_scan"];
print " Sort_merge_passes Sort_range Sort_rows Sort_scan "
printf "%7s %-17d %-21d %-19d %-21d \n\n","", sort_mp,sort_range,sort_rows,sort_scan;
tmp_disk_tables=t["Created_tmp_disk_tables"];
tmp_files=t["Created_tmp_files"];
tmp_tables=t["Created_tmp_tables"];
print " Created_tmp_disk_tables Created_tmp_files Created_tmp_tables"
printf "%7s %-40d %-19d %-21d \n\n","", tmp_disk_tables,tmp_files,tmp_tables;
lock_current_waits=t["Innodb_row_lock_current_waits"];
lock_time=t["Innodb_row_lock_time"];
lock_time_max=t["Innodb_row_lock_time_max"];
lock_waits=t["Innodb_row_lock_waits"];
print "Lock: Innodb_row_lock_current_waits Innodb_row_lock_time Innodb_row_lock_time_max Innodb_row_lock_waits"
printf "%7s %-40d %-19d %-33d %-21d \n\n","", lock_current_waits,lock_time,lock_time_max,lock_waits;
Table_locks_immediate=t["Table_locks_immediate"];
Table_locks_waited=t["Table_locks_waited"];
print " Table_locks_immediate Table_locks_waited"
printf "%7s %-40d %-19d \n\n","", Table_locks_immediate,Table_locks_waited;
connections=t["Connections"];
Connection_errors=t["Connection_errors_accept"]+t["Connection_errors_internal"]+t["Connection_errors_max_connections"]+t["Connection_errors_peer_address"]+t["Connection_errors_select"]+t["Connection_errors_tcpwrap"];
Max_used_connections=t["Max_used_connections"];
Max_used_connections_time=t["Max_used_connections_time"];
Threads_connected=t["Threads_connected"];
print "Conn: Total_Connections Connection_errors Max_used_connections Max_used_connections_time Threads_connected"
printf "%7s %-17d %-21d %-19d %-33d %-20d \n\n","", connections,Connection_errors,Max_used_connections,Max_used_connections_time,Threads_connected;
Uptime=t["Uptime"];
print "Uptime: seconds of the mysql server running"
printf "%7s %-40d \n\n","", Uptime;
}
'
}
show_binlog()
{
#查看binlog
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e'show binary logs'
else
mysql -u${user} -p"${passwd}" sys -e'show binary logs'
fi
}
show_top20tab()
{
#查看行数前20的表
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p information_schema -e "select table_schema,table_name,table_rows,engine from tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by table_rows desc limit 0,19;"
else
mysql -u${user} -p"${passwd}" information_schema -e "select table_schema,table_name,table_rows,engine from tables where table_schema not in ('sys','mysql','information_schema','performance_schema') order by table_rows desc limit 0,19;"
fi
}
show_connect()
{
#查看所有连接
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e "show processlist;"
else
mysql -u${user} -p"${passwd}" sys -e "show processlist;"
fi
}
show_var()
{
#查看变量，可以加参数
if [ X$1 != X ]
then
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e "show variables like $1;"
else
mysql -u${user} -p"${passwd}" sys -e "show variables like \'%$1%\';"
fi
else
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e "show variables;"
else
mysql -u${user} -p"${passwd}" sys -e "show variables;"
fi
fi
}
show_con_summury_by_host()
{
#查看主机的连接信息
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e 'select * from host_summary\G'
else
mysql -u${user} -p"${passwd}" sys -e 'select * from host_summary\G'
fi
}
clone()
{
#克隆恢复，只能用于已安装了克隆插件，且配置了clone_valid_donor_list的数据库之间，
#参数1：用于恢复的正常库；参数2：用于克隆的账户名称。参数3：克隆账户的密码
clonehost=$1
cloneuser=$2
clonepasswd=$3
if [ X$clonehost = X ] || [ X$cloneuser = X ] || [ X$clonepasswd = X ]
then
echo "参数不正确。"
echo "参数1：用于恢复的正常库；参数2：用于克隆的账户名称。参数3：克隆账户的密码"
fi
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysql -uroot -p sys -e "CLONE INSTANCE FROM $cloneuser@'$clonehost':3306 IDENTIFIED BY '$clonepasswd';"
else
mysql -u${user} -p"${passwd}" sys -e "CLONE INSTANCE FROM $cloneuser@'$clonehost':3306 IDENTIFIED BY '$clonepasswd';"
fi
}
unload_schema()
{
#导出表结构
#参数1：指定的库名；参数2：指定的表名
if [ X$1 = X ] || [ X$2 = X ]
then
echo "参数不正确。"
echo "参数1：指定的库名；参数2：指定的表名"
fi
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysqldump -uroot -p $1 $2 -d --routines --events --single-transaction 1>$1.$2.sql 2>$dodir/unload_schema.log
if [ $? = 0 ]
then
echo "导出表结构成功，请见 $1.$2.sql "
else
echo "导出表结构失败，请查看错误日志 $dodir/unload_schema.log "
fi
else
mysqldump -u${user} -p"${passwd}" $1 $2 -d --routines --events --single-transaction 1>$1.$2.sql 2>$dodir/unload_schema.log
if [ $? = 0 ]
then
echo "导出表结构成功，请见 $1.$2.sql "
else
echo "导出表结构失败，请查看错误日志 $dodir/unload_schema.log "
fi
fi
}
unload_data()
{
#导出表数据
#参数1：指定的库名；参数2：指定的表名
if [ X$1 = X ] || [ X$2 = X ]
then
echo "参数不正确。"
echo "参数1：指定的库名；参数2：指定的表名"
fi
if [ X$user = X0 ] || [ X$passwd = X0 ]
then
mysqldump -uroot -p $1 $2 -t --routines --events --single-transaction 1>$1.$2.unl 2>$dodir/unload_data.log
if [ $? = 0 ]
then
echo "导出表数据成功，请见 $1.$2.sql "
else
echo "导出表数据失败，请查看错误日志 $dodir/unload_data.log "
fi
else
mysqldump -u${user} -p"${passwd}" $1 $2 -t --routines --events --single-transaction 1>$1.$2.unl 2>$dodir/unload_data.log
if [ $? = 0 ]
then
echo "导出表数据成功，请见 $1.$2.sql "
else
echo "导出表数据失败，请查看错误日志 $dodir/unload_data.log "
fi
fi
}
help()
{
echo "配置说明："
echo "使用时将脚本放在/home/mysql/ebplugin/bin下"
echo "可以修改参数如下："
echo "mysqlpath 如果mysql存在的是默认的那种目录下，比如/home/mysq/mysql-8.0.18-linux-glibc2.12-x86_64 或者/home/mysql/mysql，则不需要修改，如果是其他更多层的目录，或者目录中有反斜杠 / ，则需要手动指定"
if [ $shname != ebsql.sh ]
then
echo "系列脚本说明如下："
else
echo "支持参数如下："
fi
echo "startmysql 启动mysql数据库"
echo "stopmysql 停止mysql数据库"
echo "startmgr 启动组复制"
echo "stopmgr 停止组复制"
echo "show_mgr 查看组复制状态"
echo "show_seq 显示顺扫语句"
echo "show_status 显示数据库基本状态"
echo "show_binlog 查看binlog"
echo "show_top20tab 显示行数前20的表，考虑到性能问题，所以不一定准确"
echo "show_connect 显示所有连接"
echo "show_var 显示所有变量，可以再加一个参数作为模糊匹配的字样"
echo "show_con_summury_by_host 显示所有主机的连接信息"
echo "clone 使用克隆恢复本机，详细用法见下方"
echo "unload_schema 导出表结构"
echo "unload_data 导出表数据"
echo ""
echo "clone详解："
if [ X$shname = Xclone.sh ]
then
echo "参数1：用于恢复的正常库；参数2：用于克隆的账户名称。参数3：克隆账户的密码"
echo "举例：sh ./$shname mysql1 clone '1qaz@WSX'"
else
echo "参数2：用于恢复的正常库；参数3：用于克隆的账户名称。参数4：克隆账户的密码"
echo "举例：sh ./$shname clone mysql1 clone '1qaz@WSX'"
fi
echo ""
echo "unload_schema,unload_data详解："
if [ X$shname = Xunload_schema.sh ] || [ X$shname = Xunload_data.sh ]
then
echo "参数1：指定的库名；参数2：指定的表名"
else
echo "参数2：指定的库名；参数3：指定的表名"
fi
echo "执行后，将导出的结构/数据倒入到 库名.表名.sql/库名.表名.unl里"
}
qingli()
{
if [ -f $dodir/init.tmp.$dS ]
then
rm $dodir/init.tmp.$dS
fi
if [ -f $dodir/global_status.lst.$dS ]
then
rm $dodir/global_status.lst.$dS
fi
}
init
if [ X$1 = Xstartmysql ] || [ X$shname = Xstartmysql.sh ]
then
startmysql
elif [ X$1 = Xstopmysql ] || [ X$shname = Xstopmysql.sh ]
then
stopmysql
elif [ X$1 = Xstartmgr ] || [ X$shname = Xstartmgr.sh ]
then
startmgr
elif [ X$1 = Xstopmgr ] || [ X$shname = Xstopmgr.sh ]
then
stopmgr
elif [ X$1 = Xshow_mgr ] || [ X$shname = Xshow_mgr.sh ]
then
show_mgr
elif [ X$1 = Xshow_seq ] || [ X$shname = Xshow_seq.sh ]
then
show_seq
elif [ X$1 = Xshow_status ] || [ X$shname = Xshow_status.sh ]
then
show_status
elif [ X$1 = Xshow_binlog ] || [ X$shname = Xshow_binlog.sh ]
then
show_binlog
elif [ X$1 = Xshow_top20tab ] || [ X$shname = Xshow_top20tab.sh ]
then
show_top20tab
elif [ X$1 = Xshow_connect ] || [ X$shname = Xshow_connect.sh ]
then
show_connect
elif [ X$1 = Xshow_var ] || [ X$shname = Xshow_var.sh ]
then
if [ X$shname = Xshow_var.sh ]
then
show_var "\"%$1%\""
else
show_var "\"%$2%\""
fi
elif [ X$1 = Xshow_con_summury_by_host ] || [ X$shname = Xshow_con_summury_by_host.sh ]
then
show_con_summury_by_host
elif [ X$1 = Xclone ] || [ X$shname = Xclone.sh ]
then
if [ X$shname = Xclone.sh ]
then
if [ $# != 3 ]
then
echo "参数个数不对，参数1：用于恢复的正常库；参数2：用于克隆的账户名称。参数3：克隆账户的密码"
else
clone $1 $2 $3
fi
else
if [ $# != 4 ]
then
echo "参数个数不对，参数2：用于恢复的正常库；参数3：用于克隆的账户名称。参数4：克隆账户的密码"
else
clone $2 $3 $4
fi
fi
elif [ X$1 = Xunload_schema ] || [ X$shname = Xunload_schema.sh ]
then
if [ X$shname = Xunload_schema.sh ]
then
if [ $# != 2 ]
then
echo "参数个数不对，参数1：指定的库名；参数2：指定的表名"
else
unload_schema $1 $2
fi
else
if [ $# != 3 ]
then
echo "参数个数不对，参数2：指定的库名；参数3：指定的表名"
else
unload_schema $2 $3
fi
fi
elif [ X$1 = Xunload_data ] || [ X$shname = Xunload_data.sh ]
then
if [ X$shname = Xunload_data.sh ]
then
if [ $# != 2 ]
then
echo "参数个数不对，参数1：指定的库名；参数2：指定的表名"
else
unload_data $1 $2
fi
else
if [ $# != 3 ]
then
echo "参数个数不对，参数2：指定的库名；参数3：指定的表名"
else
unload_data $2 $3
fi
fi
elif [ X$1 = Xmakefiles ]
then
cp ebsql.sh startmysql.sh
cp ebsql.sh stopmysql.sh
cp ebsql.sh startmgr.sh
cp ebsql.sh stopmgr.sh
cp ebsql.sh show_mgr.sh
cp ebsql.sh show_seq.sh
cp ebsql.sh show_status.sh
cp ebsql.sh show_binlog.sh
cp ebsql.sh show_top20tab.sh
cp ebsql.sh show_connect.sh
cp ebsql.sh show_var.sh
cp ebsql.sh show_con_summury_by_host.sh
cp ebsql.sh clone.sh
cp ebsql.sh unload_schema.sh
cp ebsql.sh unload_data.sh
cp ebsql.sh help.sh
else
help
fi
qingli
