原创MySQL运维脚本跟一些常用SQL的分享--持续更新

数据库状态收集脚本：

该脚本动态收集实例状态数据，可前端展示，也可收集到后台日志，该脚本目前还处于完善阶段，还有很多状态可以收集，中途忙其他事情去了，后面有时间了继续更新。脚本效果展示：
前端展示

收集到后台日志：

帮助信息：

脚本内容：
vi dbck.sh

#!/bin/bash
db_host=
db_port=
db_user=
db_pwd=
dbexec=“mysql -h ${db_host} -P ${db_port} -u $d{b}_{u}ser-p${db_pwd} information_schema -Ne”
sum1=0
sum2=0
js=0
rm -rf /tmp/m2019_kfdba_status1.txt /tmp/m2019_kfdba_status2.txt

#111
function f_main(){
${dbexec}“select lower(variable_name),variable_value from global_status” 2>/dev/null > /tmp/m2019_kfdba_status1.txt
vquerys1=grep -w 'com_select' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vupdate1=grep -w 'com_insert' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vinsert1=grep -w 'com_update' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vdelete1=grep -w 'com_delete' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vslowqs1=grep -w 'slow_queries' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vinnolockws1=grep -w 'innodb_row_lock_waits' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vrecivebytes1=grep -w 'bytes_received' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vsendbytes1=grep -w 'bytes_sent' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vphysicalread1=grep -w 'innodb_buffer_pool_reads' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vsyncdata1=grep -w 'innodb_data_fsyncs' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vsynclog1=grep -w 'innodb_os_log_fsyncs' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vdisktmps1=grep -w 'created_tmp_disk_tables' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'
vscan1=grep -w 'select_scan' /tmp/m2019_kfdba_status1.txt | awk '{print $2}'

function f_echo() #print head
{
echo “-----datetime------|-----------------------------------------------|-thread-|–lock—|-network(bytes)-|------innodb------|”
echo “-------------------|–qps–tps----len-slow–s10–s60-s300-scan-dtmp|–tr–tc|-wis-cwis|------in-----out|-pread-sdata–slog|”
}

f_echo
while [ 1 ]
do
if [ $js-eq30];then{f}_{e}chojs=0fisleep1letjs=js+1vtime=‘date+len=‘${dbexec}“select count from information_schema.innodb_metrics where name=‘trx_rseg_history_len’” 2>/dev/null${dbexec}"select lower(variable_name),variable_value from global_status" 2>/dev/null > /tmp/m2019_kfdba_status2.txt vquerys2=grep -w ‘com_select’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vupdate2=grep -w ‘com_insert’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vinsert2=grep -w ‘com_update’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vdelete2=grep -w ‘com_delete’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vslowqs2=grep -w ‘slow_queries’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vinnolockws2=grep -w ‘innodb_row_lock_waits’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vrecivebytes2=grep -w ‘bytes_received’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vsendbytes2=grep -w ‘bytes_sent’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vphysicalread2=grep -w ‘innodb_buffer_pool_reads’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vsyncdata2=grep -w ‘innodb_data_fsyncs’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vsynclog2=grep -w ‘innodb_os_log_fsyncs’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vdisktmps2=grep -w ‘created_tmp_disk_tables’ /tmp/m2019_kfdba_status2.txt | awk '{print $2}'vscan2=grep -w ‘select_scan’ /tmp/m2019_kfdba_status2.txt | awk ‘{print $2}’`

cwis=grep -w 'innodb_row_lock_current_waits' /tmp/m2019_kfdba_status2.txt | awk '{print $2}'
tr=grep -w 'threads_running' /tmp/m2019_kfdba_status2.txt | awk '{print $2}'
tc=grep -w 'threads_connected' /tmp/m2019_kfdba_status2.txt | awk '{print $2}'
sall=${dbexec}"select count(*) from information_schema.processlist where command='Query' and time >300 union all select count(*) from information_schema.processlist where command='Query' and time >60 union all select count(*) from information_schema.processlist where command='Query' and time > 10;" 2>/dev/null
s10=echo ${sall} | awk '{print $3}'
s60=echo ${sall} | awk '{print $2}'
s300=echo ${sall} | awk '{print $1}'
let tps=vupdate2+vinsert2+vdelete2-vupdate1-vinsert1-vdelete1
let qps=vquerys2-vquerys1
let slow=vslowqs2-vslowqs1
let wis=vinnolockws2-vinnolockws1
let in=vrecivebytes2-vrecivebytes1
let out=vsendbytes2-vsendbytes1
let pread=vphysicalread2-vphysicalread1
let sdata=vsyncdata2-vsyncdata1
let slog=vsynclog2-vsynclog1
let dtmp=vdisktmps2-vdisktmps1
let scan=vscan2-vscan1
#echo ${vrecivebytes2} ${vsendbytes2} ${vrecivebytes1} ${vsendbytes1}

vquerys1=$vquerys2;vupdate1=${vupdate2};vdelete1= v d