centos环境通过记录mysql的processlist过程信息对性能问题进行定位

在日常的mysql数据库运维中，经常会碰到一些性能问题，比如锁表、慢查询等，可以通过定时获取mysql数据库的 processlist 的信息作为数据库的执行过程收集，为性能优化做参考

大概的思路是：
创建 processlist 相关的表，然后通过脚本定时去mysql中获取processlist信息插入到表中，对这些过程信息分析解决mysql数据库的性能问题

1.监控端创建数据库和收集信息的表结构

# 创建库

CREATE DATABASE monitors_eus DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建表

CREATE TABLE `all_mysql_processlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
  `USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
  `HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
  `DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
  `COMMAND` varchar(16) NOT NULL DEFAULT '' COMMENT '状态',
  `TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext COMMENT '执行SQL',
  `TIME_MS` bigint(21) NOT NULL DEFAULT '0',
  `ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_server_ip` (`server_ip`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表';


CREATE TABLE `all_mysql_processlist_v5` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
  `USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
  `HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
  `DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
  `COMMAND` varchar(16) NOT NULL DEFAULT '' COMMENT '状态',
  `TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext COMMENT '执行SQL',
  `TIME_MS` bigint(21) NOT NULL DEFAULT '0',
  `ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ROWS_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_server_ip` (`server_ip`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表 MySQL5.X版本';

CREATE TABLE `all_mysql_processlist_yt` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `server_ip` varchar(64) NOT NULL DEFAULT '' COMMENT '服务器IP',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `pid` varchar(32) NOT NULL DEFAULT '' COMMENT 'processID',
  `USER` varchar(32) NOT NULL DEFAULT '' COMMENT '连接的用户',
  `HOST` varchar(64) NOT NULL DEFAULT '' COMMENT '连接的主机',
  `DB` varchar(64) DEFAULT NULL COMMENT '连接的数据库',
  `COMMAND` varchar(128) NOT NULL DEFAULT '' COMMENT '状态',
  `TIME` int(7) NOT NULL DEFAULT '0' COMMENT '运行时间',
  `STATE` varchar(128) DEFAULT NULL,
  `INFO` longtext COMMENT '执行SQL',
  `ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_server_ip` (`server_ip`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_time` (`TIME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='所有process统计收集表 MySQL8.0版本';

2.在需要被收集信息的数据库中添加相关的账号

用户：sql_monitor_user
权限：process

3.编写收集mysql数据库信息的脚本

# vim 192.168.1.16_db1.sh

#!/bin/bash

# 收集会话
# DATE=`date +%m%d_[%T]`

#定义被监控目标端数据库时使用的用户名和密码
dbuser='sql_monitor_user'
dbpasswd='pass'
port="3306"
server_ip="192.168.1.16" # 被监控数据库的IP
#server_ip=$1

data_time=`date +%m%d_[%T]`

#定义load数据到服务端时使用的用户名和密码，收集服务端的账号信息
#GRANT PROCESS, FILE ON *.* TO `monitor_manager_user`@`localhost`                              
#GRANT SELECT, INSERT, UPDATE, DELETE ON `monitors`.* TO `monitor_manager_user`@`localhost`    
#GRANT SELECT, INSERT, UPDATE, DELETE ON `monitors_eus`.* TO `monitor_manager_user`@`localhost`
local_dbuser='monitor_manager_user'
local_dbpasswd='pass'
local_port="3306"
local_host="localhost"

agent_exe_sql="SELECT CONCAT('${server_ip}','|',sysdate(),'|',id, '|', USER, '|', HOST, '|', DB, '|', COMMAND, '|', TIME, '|', STATE,'|', IFNULL(INFO, ''), '|', TIME_MS, '|', ROWS_SENT, '|', ROWS_EXAMINED ) as monitor_result FROM information_schema. PROCESSLIST t  where COMMAND <> 'Sleep'  AND t.state <> ''  AND t.info <> '' ORDER BY time DESC;"

#收集目标机器所有会话
all_mysql_processlist_stat(){
#/usr/local/mysql/bin/mysql -u${dbuser} -p${dbpasswd} -e "${exe_sql1}"
#  if [  $? -ne 0 ]; then
    echo "begin ...."
    result=$(/usr/local/mysql/bin/mysql -u${dbuser} -h${server_ip} -P${port} -p${dbpasswd} -e "${agent_exe_sql}")
           if [  $? -eq 0 ]; then
            echo "$data_time -- $result" >>/tmp/$(date +%a)_$server_ip.txt
            echo "$result" |grep -v 'monitor_result'> /tmp/$server_ip.txt
            sed -i '/NULL/d' /tmp/$server_ip.txt
            echo "sql result dump ok"
           fi
#  fi
} 


# 通过shell进行导入   --不使用
insert_server_data(){
#需要读取文本的路径
file="/tmp/$server_ip.txt"

# 判断文件是否为空  或者 文本包含 “NULL”
grep -q "NULL" $file
if [ ! -s ${file} ] || [ "$?" -eq "0" ]; then
        echo "待导入的文件为空"
        exit;
else
        echo "文件ok"
fi

#临时设置默认分隔符为|
IFS="|"
OLF_IFS=$IFS

cat $file | while read server_ip create_time pid USER HOST DB COMMAND TIME STATE INFO TIME_MS ROWS_SENT ROWS_EXAMINED
do

#处理INFO自动内容

#INFO字段处理很麻烦，需要处理单引号和双引号的情况,所以使用load数据的方式入库
server_exe_sql="INSERT INTO monitors_eus.all_mysql_processlist_insert_tmp(server_ip,create_time,pid,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED) select '${server_ip}','${create_time}','${pid}','${USER}','${HOST}','DB','${COMMAND}','${TIME}','${STATE}','${INFO}','${TIME_MS}','${ROWS_SENT}','${ROWS_EXAMINED}' from dual"

/usr/local/mysql/bin/mysql -u${local_dbuser} -h${local_host} -P${local_port} -p${local_dbpasswd} monitors_eus <<EOF
$server_exe_sql;
EOF
 
if [  $? -eq 0 ]; then
 echo "insert ok"
fi

done
}

#通过load的方式进行导入
load_data_server(){

#需要读取文本的路径
file="/tmp/$server_ip.txt"

#判断文件是否为空  或者 文本包含 “NULL”
grep -q "NULL" $file
if [ ! -s ${file} ] || [ "$?" -eq "0" ]; then
        echo "${server_ip} 待导入的文件为空"
        exit;
else
        echo "${server_ip} 文件 ok"
fi

load_exe_sql="load data infile  '$file' into table monitors_eus.all_mysql_processlist fields terminated by '|' lines terminated by '\n'(server_ip,create_time,pid,USER,HOST,DB,COMMAND,TIME,STATE,INFO,TIME_MS,ROWS_SENT,ROWS_EXAMINED) ;"

/usr/local/mysql/bin/mysql -u${local_dbuser} -h${local_host} -P${local_port} -p${local_dbpasswd} -e "$load_exe_sql"
        if [  $? -eq 0 ]; then
            echo "load ok"
            fi

}

#load_data_server
#####################  MAIN  ###############################
usage () {
        echo ""
        echo "  Please Input args infomation!"
        echo ""
        echo "  USAGE: `basename $0` [all_processlist|lock_processlist]"
        echo ""
}
    

if [ $# != 1 ]
then
        usage >&2
        exit 1
fi
OPT=$1
case $OPT in

all_processlist)
        echo "all_mysql_processlist_stat `basename $0` "
        all_mysql_processlist_stat && load_data_server
        ;;

*)
    echo "  USAGE: `basename $0` [all_processlist|lock_processlist]"
    exit 1
esac

4.创建自动化计划任务1分钟执行一次性能收集，在被监控端的服务器中创建

#eus_cart_db2
*/1      *       *       *       *      /bin/bash /usr/local/worksh/db_tools/192.168.1.16_db1.sh all_processlist > /dev/null 2>&1

# 收集到的信息
# more /tmp/Sun_192.168.1.16.txt

0301_[00:00:01] -- monitor_result
NULL
0301_[00:01:01] -- monitor_result
NULL
192.168.1.16|2020-03-01 16:06:01|27416829|chinasoft_v2_write|172.17.0.40:38838|chinasoft_online|Execute|0|query end|INSERT INTO task_0 (user_id,task_id,org_file_name,operate_type,start_time,status,password,multi_id,create_time)VALUE('0','aa','105141_5d78613d4fdc2.pdf','pdf_to_pptx','2020-03-01 16:06:01','3','','aa
5a62e4','2020-03-01 16:06:01')|0|0|0
192.168.1.16|2020-03-01 16:06:01|27416830|chinasoft_v2_write|172.17.0.40:38842|chinasoft_online|Execute|0|query end|UPDATE task_0 SET end_time='2020-03-01 16:06:01',download_url='files/chinasoft/bb.docx',output_file_name='2018考试上午真题.docx',status='0' WHERE user_id=0 AND task_id='bb'|0|0|1
NULL
0301_[00:07:01] -- monitor_result

# 收集到数据库中的信息
#

[monitors_eus]> select * from all_mysql_processlist_yt limit 2\G
*************************** 1. row ***************************
           id: 1
    server_ip: chinasoft.rds.amazonaws.com
  create_time: 2020-09-17 12:38:16
          pid: 13462643
         USER: chinasoft_cloud_write
         HOST: 1.1.1.1:57326
           DB: chinasoft
      COMMAND: Execute
         TIME: 0
        STATE: delayed commit ok initiated
         INFO: update `max_analysis` set `disconnect` = ? where `date` = ?
    ROWS_SENT: 0
ROWS_EXAMINED: 0
*************************** 2. row ***************************
           id: 2
    server_ip: chinasoft.rds.amazonaws.com
  create_time: 2020-09-17 12:39:51
          pid: 13463423
         USER: chinasoft_cloud_write
         HOST: 172.31.20.187:59689
           DB: edrawusers
      COMMAND: Execute
         TIME: 0
        STATE: Sending data
         INFO: select `user_info`.`user_id`, `user_info`.`user_pwd`, `nick_name`, `user_info`.`user_name`, `email`, `mobile`, `user_storage`.`max_storage`, `user_storage`.`used_storage`, `lang`, `profile`, `birthday`, `country`, `region`, `sex`, `activation`, `buy_deadtime`, `reg_time`, `avatar`, `avatar_url`, `migrate`, `weibo_login`.`weibo_id` as `weibo`, `qq_login`.`open_id` as `qq`, `weixin_login`.`open_id` as `weixin` from `user_info` left join `weibo_login` on `user_info`.`user_id` = `weibo_login`.`user_id` left join `qq_login` on `user_info`.`user_id` = `qq_login`.`user_id` left join `weixin_login` on `user_info`.`user_id` = `weixin_login`.`user_id` left join `user_storage` on `user_info`.`user_id` = `user_storage`.`user_id` where `user_info`.`user_id` = '135' limit 1
    ROWS_SENT: 0
ROWS_EXAMINED: 0
2 rows in set (0.00 sec)