暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / shell案例系列2-MySQL相关的shell案例

shell案例系列2-MySQL相关的shell案例

原创 只是甲 2022-04-19
307

Table of Contents

一. 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 (没有那个文件或目录)
shell
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论