一. mysql安装

这是一个大佬发的自动安装mysql8的脚本，收藏下

#!/bin/bash

echo "正在安装MySQL软件......."

useradd mysql
useradd nagios
useradd zabbix

sleep 2

######配置参数######
mysql8_version=mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
mysql8_version_dir=mysql-8.0.18-linux-glibc2.12-x86_64

######同步复制用户######
repl_user=repl
repl_passwd=sysrepl
#######################

######root密码######
root_passwd=123456
#######################

######DBA管理用户######
dba_user=admin
dba_passwd=admin
#######################

######8.0克隆用户######
clone_user=clone_user
clone_passwd=123456
#######################


######修改hosts文件######
cat << EOF >> /etc/hosts

192.168.137.11      mgr1
192.168.137.12      mgr2
192.168.137.13      mgr3

EOF
#######################

######mgr配置######
mysql_port=3306
primary_ip=192.168.137.11
secondary1_ip=192.168.137.12
secondary2_ip=192.168.137.13

primary_port=33061
secondary1_port=33062
secondary2_port=33063

local_ip=192.168.137.11
local_port=33061

###############################################
if [ "$1" = "repl" ]
then
while true
do
    read -t 30 -p "输入你的主库IP:  " master_ip
    read -t 30 -p "输入你的主库端口号:  " master_port
    if [[ -z $master_ip || -z $master_port ]]
    then
        continue
    else
        echo ""
        echo "主库IP是： $master_ip"
        echo "主库端口号是： $master_port"
        break 
    fi
done

/usr/local/mysql/bin/mysql -h127.0.0.1 -u'$dba_user' -p'$dba_passwd' -P"$master_port" --connect-expired-password -e "CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='repl',MASTER_PASSWORD='sysrepl',MASTER_PORT=$master_port,MASTER_AUTO_POSITION = 1,MASTER_CONNECT_RETRY=10; START SLAVE;"

    echo "MySQL主从复制同步已经初始化完毕。"
    exit 0
fi

################################################
if [ "$1" = "mgr" ]
then

while true
do
    read -t 30 -p "是Primary吗？是请输入yes，否输入no:  " is_primary
    if [[ -z $is_primary ]]
    then
        continue
    else
        if [ $is_primary == "yes" ] || [ $is_primary == "no" ]
        then
            break 
        else
             echo "你输入一个错误的字符$is_primary，请重新输入..."
             continue
        fi
    fi
done

if [ $is_primary == "yes" ]
then
    /usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME  'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address =  '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}';SET GLOBAL group_replication_bootstrap_group=ON; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;SET GLOBAL group_replication_bootstrap_group=OFF;"

else
    /usr/local/mysql/bin/mysql -h127.0.0.1 -u"$dba_user" -p"$dba_passwd" -P"$mysql_port" --connect-expired-password -e "INSTALL PLUGIN group_replication SONAME  'group_replication.so'; set persist group_replication_group_name = '3b12b5bd-f0c6-11e9-9778-000c2900afc6';set persist group_replication_local_address =  '${local_ip}:${local_port}'; set persist group_replication_group_seeds = '${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}'; SET GLOBAL group_replication_bootstrap_group=OFF; CHANGE MASTER TO MASTER_USER='$repl_user',MASTER_PASSWORD='$repl_passwd' FOR CHANNEL 'group_replication_recovery';START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;"

fi
    
echo "MySQL Mgr组复制已经初始化完毕。"
exit 0

fi

################################################

ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
    echo "MySQL进程已经启动，无需二次安装。"
    exit 0
fi

if [ ! -d /usr/local/${mysql8_version_dir} ]
then
        yum install xz -y
    tar -Jxvf ${mysql8_version} -C /usr/local/
    ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
    chown -R mysql.mysql /usr/local/mysql/
    chown -R mysql.mysql /usr/local/mysql
else
    ln -s /usr/local/${mysql8_version_dir} /usr/local/mysql
    chown -R mysql.mysql /usr/local/mysql/
    chown -R mysql.mysql /usr/local/mysql
fi 

while true
do
    read -t 30 -p "输入你的数据库名:  " dbname
    read -t 30 -p "输入你的数据库端口号:  " dbport
    read -t 30 -p "输入MySQL serverId:  " serverId
    read -t 30 -p "输入innodb_buffer_pool_size大小，单位G:  " innodb_bp_size
    if [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]]
    then
        continue
    else
        echo "数据库名字是： $dbname"
        echo "数据库端口是： $dbport"
        echo "MySQL serverId： $serverId"
        echo "BP大小是： $innodb_bp_size GB"
        break 
    fi
done

sed "s/test/$dbname/g;s/3306/$dbport/;s/413306/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/" my_test.cnf > /etc/my_$dbname.cnf

DATA_DIR=/data/mysql/$dbname
[ ! -d $DATA_DIR ] && mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/


if [ `ls -A $DATA_DIR/data/ | wc -w` -eq 0 ]
then
    cd /usr/local/mysql
    echo ""
    echo "初始化MySQL数据目录......"
    echo ""
    bin/mysqld --defaults-file=/etc/my_$dbname.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/$dbname/data
    sleep 2
    bin/mysqld_safe --defaults-file=/etc/my_$dbname.cnf --user=mysql &
fi

while true
do
     netstat -ntlp | grep $dbport
     if [ $? -eq 1 ]
     then
        echo "MySQL启动中，稍等......"
        sleep 5
        continue
     else
        break
     fi
done

ps aux | grep 'mysql' | grep -v 'grep' | grep -v 'bash'
if [ $? -eq 0 ]
then
        echo "MySQL安装完毕。"
else
    echo "MySQL安装失败。"
fi

###更改root账号随机密码
random_passwd=`grep 'temporary password' $DATA_DIR/log/error.log | awk -F 'root@localhost: ' '{print $2}'`
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p"$random_passwd" --connect-expired-password -e "set sql_log_bin=0;alter user root@'localhost' identified by '$root_passwd';" 

echo "root账号随机密码更改完毕。"

###创建同步账号和管理员账号
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;create user '$repl_user'@'%' IDENTIFIED BY '$repl_passwd'; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '$repl_user'@'%'; create user '$dba_user'@'%' IDENTIFIED BY '$dba_passwd'; GRANT ALL on *.* to '$dba_user'@'%' WITH GRANT OPTION;"

sed -i -r "s/(PATH=)/\1\/usr\/local\/mysql\/bin:/" /root/.bash_profile
source /root/.bash_profile

echo "MySQL账号初始化完毕。"

###安装clone插件
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock --connect-expired-password -p"$root_passwd" -e "set sql_log_bin=0;INSTALL PLUGIN CLONE SONAME 'mysql_clone.so'; CREATE USER '$clone_user'@'%' IDENTIFIED BY '$clone_passwd';GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO '$clone_user'@'%';"

echo ""
echo "clone克隆插件安装完毕。"

二. MySQL 备份

2.1 逻辑备份

逻辑备份所有库

#!/bin/bash
# -------------------------------------------------------------------------------
# FileName:    mysql_logical_backup.sh 
# Describe:    Used for database backup
# Version:    1.0
# Author:      只是甲
# Date:        2021/04/22


# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="abc123"
mysql_host="localhost"
mysql_port="3306"
backup_dir=/backup/mysqlbackup

dt=`date +'%Y%m%d_%H%M'`
echo "Backup Begin Date:" $(date +"%Y-%m-%d %H:%M:%S")

# 备份全部数据库
/u01/my3306/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -R -E --all-databases --single-transaction > $backup_dir/mysql_backup_$dt.sql

# 清理超过7天的备份
find $backup_dir -mtime +7 -type f -name '*.sql' -exec rm -rf {} \;
echo "Backup Succeed Date:" $(date +"%Y-%m-%d %H:%M:%S")

2.2 物理备份

通过xtrabackup全备mysql
参考: https://blog.csdn.net/db_murphy/article/details/96428613

#!/bin/bash
#######################################################
# $Name: mysql_physical_fullback.sh
# $Version: v1.0
# $Create Date: 2019-07-16
# $Description: MySQL full_backup all-databases
#######################################################
 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=/usr/local/mysql/bin:$PATH:$HOME/bin
export PATH
 
###################   Declare environment variables  #########
record_log=/mysqldata/backup/
log_name=physical_fullback_record.log
 
backup_dir=/mysqldata/backup/back_images
metadata_dir=/mysqldata/backup/back_metadata
 
echo "--------------------Full Backup Starting------------------"  >> $record_log/$log_name
date >> $record_log/$log_name
 
mysqlbackup --user=root --password=XXXXXX --socket=/mysqldata/tmp/mysql.sock --host=localhost \
--backup-image=$backup_dir/physical_fullback_`date '+%m-%d-%Y'`.mbi \
--backup-dir=$metadata_dir/fullback_info_`date '+%m-%d-%Y'` backup-to-image
 
date >> $record_log/$log_name
echo "--------------------Full Backup Ended------------------"  >> $record_log/$log_name
 
##########  delete the physical_images and metadata_infor from 7 days ago  #############
 
images_dir=/mysqldata/backup/back_images
find $images_dir -type f -name "physical_fullback_*.mbi" -mtime +7 -exec rm -rf {} \;
 
metadata_dir=/mysqldata/backup/back_metadata
find $metadata_dir -type d -name "fullback_info_*" -mtime +7 -exec rm -rf {} \;

2.3 从MySQL逻辑备份中找到单表的备份

有时候我们需要从mysql的逻辑全备中，找到单表的备份

sed -n -e '/CREATE TABLE.*`my_table`/,/UNLOCK TABLES/p' mydump.sql >/tmp/my_table.sql

有点慢,9GB左右文件 5分钟左右，结果文件500M左右

需要特别注意，如果多个库都有表名，可能会有多个表的备份

grep 'CREATE TABLE' mydump.sql 

三. MySQL 监控

3.1 监控qps

参考: https://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/

mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'

测试记录:

[root@hp8 tmp]# mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 ext |\
> awk -F"|" \
> "BEGIN{ count=0; }"\
> '{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
>     print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
>     print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
> }\
> else if ($2 ~ /Queries/){queries=$3;}\
> else if ($2 ~ /Com_select /){com_select=$3;}\
> else if ($2 ~ /Com_insert /){com_insert=$3;}\
> else if ($2 ~ /Com_update /){com_update=$3;}\
> else if ($2 ~ /Com_delete /){com_delete=$3;}\
> else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
> else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
> else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
> else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
> else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
> else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
> else if ($2 ~ /Uptime / && count >= 2){\
>   printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
>   printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
>   printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
>   printf("|%10d %11d\n",innodb_lor,innodb_phr);\
> }}'
Enter password: 
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical
 10:37:35 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:36 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:38 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:38 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:39 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:40 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:42 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:42 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:43 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:44 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:46 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:46 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:47 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:49 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:50 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:37:50 |        1|     0      0      0      0|     0        0       0       0|         0           0
^C
[root@hp8 tmp]# 

3.2 监控连接数

参考: http://f.dataguru.cn/thread-353575-1-1.html

function usrinfo(){
    mysqladmin -uroot -pabc123 processlist 2>/dev/null | sed '1,3d' | sed '$d' | grep -v "system user" | awk -F"|" '{printf("%s %s %s\n",match($4,":")?substr($4,0,match($4,":")-1):$4, $3, index($5,"  ")?"mysql":$5)}' | sort | uniq -c | sort -n -k 1 -r | awk '{ printf("%s    %s      %s     %s       %s\n",strftime("%Y-%m-%d %H:%M:%S",systime()),$1,$2,$3,$4) }'

}

function headerinfo(){
    echo "      DateTime       Con(s)      Host       User       Database     " 
    echo "+++++++++++++++++++ |++++++| +++++++++++ | +++++++ | ++++++++++++++|"         
}


# ***********************************************
# Main 

INTERVAL=2

while [ 1 ]
do
    headerinfo
    usrinfo
    #echo "+++++++++++++++++++ |++++++| +++++++++++ | +++++++ | ++++++++++++++|"     
        sleep $INTERVAL
done

测试记录:

[root@hp7 tmp]# sh 1.sh 
      DateTime       Con(s)      Host       User       Database     
+++++++++++++++++++ |++++++| +++++++++++ | +++++++ | ++++++++++++++|
2022-03-24 16:59:30    1      localhost     root       mysql
      DateTime       Con(s)      Host       User       Database     
+++++++++++++++++++ |++++++| +++++++++++ | +++++++ | ++++++++++++++|
2022-03-24 16:59:32    1      localhost     root       mysql
      DateTime       Con(s)      Host       User       Database     
+++++++++++++++++++ |++++++| +++++++++++ | +++++++ | ++++++++++++++|
2022-03-24 16:59:34    1      localhost     root       mysql

四. 锁相关

4.1 查询阻塞

参考:http://f.dataguru.cn/thread-367134-1-1.html

mysql_lock.sql

SELECT
    NOW()                                               AS "采集时间", 
    b.trx_mysql_thread_id                               AS "阻塞源",
    SUBSTRING(p.HOST, 1, INSTR(p.HOST, ':') - 1)            AS "源主机",
    SUBSTRING(p.HOST, INSTR(p.HOST, ':') + 1)               AS "源端口",
    -- p.USER                                               AS "连接用户", 
    -- p.DB                                             AS "数据库",     
    IF(p.COMMAND='Sleep', p.TIME, 0)                        AS "空闲(秒)",
    r.trx_mysql_thread_id                               AS "被阻塞",
    TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP)  AS "阻塞时长(秒)",
    l.lock_table                                        AS "锁表",     
    b.trx_query                                         AS "阻塞SQL",   
    r.trx_query                                         AS "请求SQL"
FROM information_schema.INNODB_LOCK_WAITS w 
     INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id 
     INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id 
     INNER JOIN information_schema.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id 
     LEFT  JOIN information_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_id 
ORDER BY b.trx_mysql_thread_id, r.trx_wait_started ; 


SELECT  NOW()                                           AS "采集时间",
     tb.trx_mysql_thread_id                             AS "阻塞源",
     tb.trx_state                                       AS "源状态",
     TIMESTAMPDIFF(SECOND,tb.trx_started,CURRENT_TIMESTAMP)         AS "事务时长(秒)",
     CONCAT(lb.lock_type, ":" ,lb.lock_mode)                AS "锁信息",
     CONCAT(lb.lock_index, "@",lb.lock_table )              AS "锁对象",
     tr.trx_mysql_thread_id                             AS "请求者",
     tr.trx_state                                       AS "请求状态",
     TIMESTAMPDIFF(SECOND,tr.trx_wait_started,CURRENT_TIMESTAMP)    AS "阻塞时长(秒)",
     CONCAT(lr.lock_type, ":" ,lr.lock_mode)                AS "锁信息",
     CONCAT(lr.lock_index, "@",lr.lock_table )              AS "锁对象",
     tb.trx_query                                       AS "阻塞SQL",
     tr.trx_query                                       AS "请求SQL"
FROM information_schema.innodb_lock_waits t
     INNER JOIN information_schema.innodb_trx tb ON tb.trx_id=t.blocking_trx_id
     INNER JOIN information_schema.innodb_trx tr ON tr.trx_id=t.requesting_trx_id
     INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id=tb.trx_id AND t.blocking_lock_id=lb.lock_id
     INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id=tr.trx_id AND t.requested_lock_id=lr.lock_id    
ORDER BY tb.trx_mysql_thread_id, tr.trx_started;  

blocking_detect.sh

function usage(){
    cat <<-EOF
        Usage:
            `basename $0` ip:port 
    EOF
}

function load_default_settings(){
    MYSQL_HOST=127.0.0.1
    MYSQL_HOST=192.168.137.130
    MYSQL_PORT=3306
    MYSQL_USER=root
    MYSQL_PASSWD="zhudonhua"
    blocking_threshold=1
}

function parse_options(){
    [ $# -lt 1 ] && {
        action "[*ERROR*] You must specify one instance at least." /bin/false
        usage
        exit 1
    }

    ip1=`echo $1 | cut -d ":" -f 1`
    port1=`echo $1 | cut -d ":" -f 2`
    FORMAT_ERROR=0
    REGEX_IP="^(2[0-4][0-9]|25[0-5]|1[0-9][0-9]|[1-9]?[0-9])(\.(2[0-4][0-9]|25[0-5]|1[0-9][0-9]|[1-9]?[0-9])){3}$"
    [ `echo $ip1 | grep -E $REGEX_IP | wc -l` -eq 0 ] && FORMAT_ERROR=1
    [ `echo $port1 | grep -E '[^0-9]+' | wc -l` -eq 1 ] && FORMAT_ERROR=1
    [ "$FORMAT_ERROR" = "1" ] && {
        action "[*ERROR*] IP:Port should be numeric format." /bin/false
        exit 1
    }

    MYSQL_HOST=$ip1
    MYSQL_PORT=$port1
}

function blocking(){
    log=/opt/mysql/blocking_`date +"%F_%H-%M-%S"`.log
    tmpsql=/tmp/mysql_block_$RANDOM.sql 

    cat <<-EOF > ${tmpsql}
        SELECT  NOW()                                                       AS "采集时间",
            tb.trx_mysql_thread_id                                          AS "阻塞源",
            tb.trx_state                                                    AS "源状态",
            TIMESTAMPDIFF(SECOND,tb.trx_started,CURRENT_TIMESTAMP)          AS "事务时长(秒)",
            CONCAT(lb.lock_type, ":" ,lb.lock_mode)                         AS "锁信息",
            CONCAT(lb.lock_index, "@",lb.lock_table )                       AS "锁对象",
            tr.trx_mysql_thread_id                                          AS "请求者",
            tr.trx_state                                                    AS "请求状态",
            TIMESTAMPDIFF(SECOND,tr.trx_wait_started,CURRENT_TIMESTAMP)     AS "阻塞时长(秒)",
            CONCAT(lr.lock_type, ":" ,lr.lock_mode)                         AS "锁信息",
            CONCAT(lr.lock_index, "@",lr.lock_table )                       AS "锁对象",
            tb.trx_query                                                    AS "阻塞SQL",
            tr.trx_query                                                    AS "请求SQL"
        FROM information_schema.innodb_lock_waits t
            INNER JOIN information_schema.innodb_trx tb ON tb.trx_id=t.blocking_trx_id
            INNER JOIN information_schema.innodb_trx tr ON tr.trx_id=t.requesting_trx_id
            INNER JOIN information_schema.innodb_locks lb ON lb.lock_trx_id=tb.trx_id AND t.blocking_lock_id=lb.lock_id
            INNER JOIN information_schema.innodb_locks lr ON lr.lock_trx_id=tr.trx_id AND t.requested_lock_id=lr.lock_id    
        ORDER BY tb.trx_mysql_thread_id, tr.trx_started; 
    EOF

    mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST -P$MYSQL_PORT -e"source ${tmpsql}" > ${log} 2>/dev/null   
    # cat ${log}  # used for debug
    [ `cat ${log} | wc -l` -ge ${blocking_threshold} ] && {
        # kill spid
        # cat ${log} | sed '1d' | awk '{print $3}' | uniq | awk '{printf("kill %s;\n",$1)}'
        cat ${log} | sed '1d' | awk '{print $3}' | uniq | awk '{printf("kill %s;\n",$1)}' | mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST -P$MYSQL_PORT 
    } || {
        :
        [ -r $log ] && rm -rf $log
    }

    [ -r $tmpsql ] && rm -rf $tmpsql
} 

# **************************************
# Main
#
source /etc/init.d/functions
load_default_settings
parse_options $*
blocking

五.其它

5.1 查找正在使用的my.cnf

有时候我们不知道mysql服务器使用的是那个配置文件，可以使用如下方法:

代码:

strace -f mysql -h 127.0.0.1 --port=3306 |& grep my.cnf

测试记录:

[root@hp8 ~]# strace -f mysql -h 127.0.0.1 --port=3306 |& grep my.cnf
stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=1102, ...}) = 0
open("/etc/my.cnf", O_RDONLY)           = 3
stat("/etc/mysql/my.cnf", 0x7ffdee5829b0) = -1 ENOENT (没有那个文件或目录)
stat("/usr/etc/my.cnf", 0x7ffdee5829b0) = -1 ENOENT (没有那个文件或目录)
stat("/root/.my.cnf", 0x7ffdee5829b0)   = -1 ENOENT (没有那个文件或目录)