需求

mysql在导入数据的时候, 想看下进度, 但是mysql命令行是没得相关进度的, 只能去数据库看下insert到哪张表了, 即使看了 也不知道还要多久…

mysql < xxx.sql

分析

/proc/PID/io 文件的rchar有记录 该进程读取了多少数据.

详情请看官网: https://www.kernel.org/doc/html/latest/filesystems/proc.html

那我们使用rchar的值除以 文件总大小 就是 导入进度了.

查看 /proc/PID/fd 就可以看到导入文件名字了. 然后使用 stat -c '%s' /root/t20230612_db2.sql 查看其大小

演示

在知道了上述信息后, 就可以使用shell脚本来查看进度了

sh MysqlImportRate.sh `pidof mysql`

就动态的显示进度了.

Time Remaining: 1 seconds 表示剩余时间

Rate: 32 MB/s 表示当前速度

注意: 如果存在多个导入进程, 或者多个mysql进程, 就需要指定具体的mysql进程的PID

sh MysqlImportRate.sh PID

以后就不用傻傻的等数据导入了. 至少我们有个预估剩余时间. 可以去吃个饭了再来 -_-

附脚本

墨天轮: https://www.modb.pro/doc/125462
github: https://github.com/ddcw/ddcw/blob/master/shells/MysqlImportRate.sh
或者如下:

#!/usr/bin/env bash
#write by ddcw @https://github.com/ddcw
#跟进mysql导入进程打印其进度
# $1 = `pidof mysqld`
export LANG="en_US.UTF-8"

jindutiao(){
	percentage=$1
	msg=$2
	filled_length=$[ ${percentage} / 2 ]
	bar=$(printf "%-50s" "$(printf '#%.0s' $(seq 1 $filled_length))")
	echo -ne "\r[$bar] $percentage% ${msg}"
}

MYSQL_PID=$1
FILENAME=""
#检查mysql进程号是否存在
if [ "$1" == "" ];then
	MYSQL_PID=`pidof mysql | awk '{print $1}'`
	if [ "${MYSQL_PID}" == "" ];then
		echo -e "no mysql pid\n sh $0 mysql_pid"
		exit 3
	fi
elif [ ! -f "/proc/$1/io" ];then
	echo "${PID} is not exists. Please check by yourself"
	exit 1
fi
FILENAME=`ls -l /proc/${MYSQL_PID}/fd/0 | awk '{print $NF}'`

#检查文件是否存在
if [ ! -f ${FILENAME} ];then
	echo "${FILENAME} is not exists. Please check by yourself!"
	exit 2
fi

SLEEP_INTER=1
LAST_SIZE=1
TOTAL_SIZE=`stat -c '%s' ${FILENAME}`
IS_RUN=0
while [ 1 -eq 1 ];do
	CURRENT_SIZE=`awk '{ if ($1=="rchar:") print $2}' /proc/${MYSQL_PID}/io 2>/dev/null`
	if [ "${CURRENT_SIZE}" == "" ];then
		#不存在了. 也就是导完了
		if [ ${IS_RUN} -eq 1 ];then
			jindutiao "100"
		fi
		break 1
	elif [ ${CURRENT_SIZE} -lt ${TOTAL_SIZE} ];then
		#打印进度
		REST_SIZE=$[ ${TOTAL_SIZE} - ${CURRENT_SIZE} ]
		RATE=$[ (${CURRENT_SIZE} - ${LAST_SIZE}) / ${SLEEP_INTER} ]
		if [ ${RATE} -gt 0 ];then #如果速度大于0, 就计算剩余的时间
			REST_TIME=$[ ${REST_SIZE} / ${RATE} ]
			#时间格式化
			if [ $[ ${REST_TIME} / 60 / 60 / 24 ] -ge 1 ];then
				REST_TIME=$[ ${REST_TIME} / 60 / 60 / 24 ]" days"
			elif [ $[ ${REST_TIME} / 60 / 60 ] -ge 1 ];then
				REST_TIME=$[ ${REST_TIME} / 60 / 60 ]" hours"
			elif [ $[ ${REST_TIME} / 60 ] -ge 1 ];then
				REST_TIME=$[ ${REST_TIME} / 60 ]" minutes"
			else
				REST_TIME="${REST_TIME} seconds"
			fi

			#速度格式化
			if [ $[ ${RATE} / 1024 / 1024 / 1024 ] -ge 1 ];then
				RATEH=$[ ${RATE} / 1024 / 1024 / 1024 ]" GB/s"
			elif [ $[ ${RATE} / 1024 / 1024 ] -ge 1 ];then
				RATEH=$[ ${RATE} / 1024 / 1024 ]" MB/s"
			elif [ $[ ${RATE} / 1024 ] -ge 1 ];then
				RATEH=$[ ${RATE} / 1024 ]" KB/s"
			else
				RATEH="{RATE} B/s"
			fi
		else
			REST_TIME=""
			RATEH=${RATE}
		fi
		jindutiao "$[ ${CURRENT_SIZE} * 100 / ${TOTAL_SIZE} ]" "Time Remaining: ${REST_TIME} Rate: ${RATEH}"
	elif [ ${CURRENT_SIZE} -ge ${TOTAL_SIZE} ];then
		jindutiao "100"
		break
	fi
	LAST_SIZE=${CURRENT_SIZE}
	IS_RUN=1
	sleep ${SLEEP_INTER}
done
echo ""