数据库监控设计-DBA-专属

作者：李红（老李），中国开源协会（oscna.org）数据库分会成员，热衷于数据库集群架构相关方向，上海某企业Mysql数据库负责人。

本文来源：linux备忘录（iirwt.com）出品，转载请注明来源，谢谢。

【数据库巡检脚本-方案部署设计图】

一、mysql 数据库巡检方案(适用于 Centos And ubuntu 系统)

(1)需要自己准备三台数据库环境 （此操作必须根据自己的服务器情况部署(磁盘信息(磁盘路径)或者数据库安装路径  等等)，别直接拿走使用）
(2)对应数据库 IP 有三台  (192.168.33.50(插入表数据库（巡检数据库）) 192.168.33.51(Mysql 主) 192.168.33.52(MySQL 从))

二、主库准备 shell脚本 以及 文件路径(192.168.33.51)

(1)准备存放的相关文件路径
[root@DBA01 ~]# mkdir -p /mnt/xvda/mysql_back
[root@DBA01 ~]# mkdir -p /mnt/xvda/mysql_scp
[root@DBA01 ~]# mkdir -p /mnt/xvda/mysql_shell
[root@DBA01 ~]# mkdir -p /mnt/xvda/mysql_shell/log
[root@DBA01 ~]# cd /mnt/xvda/mysql_scp/
[root@DBA01 mysql_scp]# touch all_back_test_183.txt
[root@DBA01 mysql_scp]# touch all_back_test_time_183.txt

(2)准备 shell 相关脚本(全备份脚本)测试这里  我把备份放在主库上   不合适，你们要这备份机来做
[root@DBA01 ~]# cd /mnt/xvda/mysql_shell
[root@DBA01 mysql_shell]# which mysqldump
/bin/mysqldump

[root@DBA01 mysql_shell]# vim wholebaseback.sh
#!/bin/bash

echo "start all backup: 全库备份开始" >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
ttime=`date +"%Y-%m-%d_%H:%M:%S"` 
echo $ttime >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
stime=`date +%s`

#echo "start all backup:"
#ttime=`date +"%Y-%m-%d_%H:%M:%S"`
#echo $ttime

/bin/mysqldump -uroot -p"root@1234" -hlocalhost --set-gtid-purged=OFF --single-transaction --triggers --routines --events --master-data=2 -A | gzip > /mnt/xvda/mysql_back/all_${ttime}.sql.gz


echo "complete all backup: 全库备份结束" >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
ettime=`date +"%Y-%m-%d_%H:%M:%S"`
echo $ettime >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt

etime=`date +%s`

s=$(($(( $etime - $stime))%60))
m=$(($(( $etime - $stime))/60%60))
h=$(($(( $etime - $stime))/60/60))

echo "runtime "$h"h:"$m"m:"$s"s"  >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt

# clean the log
aa=`cat /mnt/xvda/mysql_scp/all_back_test_time_183.txt | wc -l`
if [ $aa -gt 2000 ];then
 sed -i "1,10d" /mnt/xvda/mysql_scp/all_back_test_time_183.txt
fi


find /mnt/xvda/mysql_back/  -name "*.sql.gz" -mtime +30 -exec rm {} ;
[root@DBA01 mysql_shell]# 

(3)准备 shell 相关脚本(分库备份脚本)测试这里  我把备份放在主库上   不合适，你们要这备份机来做
[root@DBA01 mysql_shell]# vim branchbaseback.sh
#!/bin/bash

echo "start all backup: 分库备份开始" >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
ttime=`date +"%Y-%m-%d_%H:%M:%S"` 
echo $ttime >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
stime=`date +%s`

#echo "start databases backup::"
#ttime=`date +"%Y-%m-%d_%H:%M:%S"`
#echo $ttime

for dbname in `/bin/mysql -uroot -p"root@1234" -hlocalhost -e "show databases;"|grep -Evi "database|info|perfor|mysql|sys|test"` 
do
echo $dbname
/bin/mysqldump -uroot -p"root@1234" -hlocalhost --set-gtid-purged=OFF -B --single-transaction --master-data=2 --set-gtid-purged=OFF  ${dbname}|gzip > /mnt/xvda/mysql_back/bak_${dbname}_$ttime.sql.gz
	       
done

echo "complete all backup: 分库备份结束" >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
ettime=`date +"%Y-%m-%d_%H:%M:%S"`
echo $ettime >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt
etime=`date +%s`

s=$(($(( $etime - $stime))%60))
m=$(($(( $etime - $stime))/60%60))
h=$(($(( $etime - $stime))/60/60))

echo "runtime "$h"h:"$m"m:"$s"s"  >> /mnt/xvda/mysql_scp/all_back_test_time_183.txt

# clean the log
aa=`cat /mnt/xvda/mysql_scp/all_back_test_time_183.txt | wc -l`
if [ $aa -gt 2000 ];then
 sed -i "1,10d" /mnt/xvda/mysql_scp/all_back_test_time_183.txt
fi

find /mnt/xvda/mysql_back/ -name "*.sql.gz" -mtime +30 -exec rm {} ;
[root@DBA01 mysql_shell]# 

(4)准备 shell 相关脚本(磁盘信息，CPU，内存，数据库状态，数据库备份大小)测试这里  我把备份放在主库上   不合适，你们要这备份机来做
[root@DBA01 mysql_shell]# vim disk.sh 
#/bin/bash

base_dir="/mnt/xvda/mysql_scp"
base_data="/mnt/xvda/mysql_back"
echo "start databases backup::"
ttime=`date +"%Y-%m-%d_%H:%M:%S"`
ttime1=`date "+%Y-%m-%d"`
echo $ttime

disk_check(){
    cat >> $base_dir/all_back_test_183.txt << EOF
`date +%Y-%m-%d` 数据备份服务器 192.168.33.51
/ `df -Th |awk "/root/{print $(NF-1)}"`
/mnt `df -Th |awk "/mnt/{print $(NF-1)}"`
EOF
}
disk_check

cpu_check(){
cat >> $base_dir/all_back_test_183.txt << EOF
`date +%Y-%m-%d` 数据备份服务器_CPU 192.168.33.51
`cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c | awk "{print $4,$1}"`
EOF
}
cpu_check

MemTotal_check(){
cat >> $base_dir/all_back_test_183.txt << EOF
`date +%Y-%m-%d` 数据备份服务器_内核 192.168.33.51
`cat /proc/meminfo | grep "^MemTotal:" /proc/meminfo | awk "{ mem=($2)/(1024)/(1024); printf "MemTotal: %0.0fGB
",mem}"`
EOF
}
MemTotal_check

Mysql_check(){
	cat >> $base_dir/all_back_test_183.txt << EOF
`date +%Y-%m-%d` 数据库服务器状态_MySQL 192.168.33.51
`netstat -nl | awk "NR>2{if ($4 ~ /.*:3306/) {print "mysqld OK";exit 0}}"`
EOF
}
Mysql_check

size_check(){
	cat >> $base_dir/all_back_test_183.txt << EOF 
`date +%Y-%m-%d` 数据库备份大小 192.168.33.51
`ls $base_data/all_*${ttime1}* | xargs du -sh | sed "s#${base_data}/##g" | awk "{print $2,$1}"`
`ls $base_data/bak_*${ttime1}* | xargs du -sh | sed "s#${base_data}/##g" | awk "{print $2,$1}"`
EOF
}
size_check
[root@DBA01 mysql_shell]# 

(5)准备 shell 相关脚本(拷贝)请自行安装 expect 这个插件这里我就不教怎么安装了
[root@DBA01 mysql_shell]# vim scp.sh
#!/usr/bin/expect
set timeout -1
spawn bash -c "scp -r /mnt/xvda/mysql_scp/all_back_* root@192.168.33.50:/opt/source_data_lh/"
expect {
    "password" {send "root";}
    "yes/no" {send "yes";exp_continue}
}
expect eof
exit
[root@DBA01 mysql_shell]# 

(6)准备 shell 相关脚本(清除文件)
[root@DBA01 mysql_shell]# vim delete.sh
#!/bin/bash
dir=/mnt/xvda/mysql_scp           #//需要清空的目录名称
files=`ls ${dir}`
for file in $files
do
if [ -e ${dir}/${file} ];then
rm -f ${dir}/${file}
fi
done
[root@DBA01 mysql_shell]# 

(7)准备执行计划任务(添加)
[root@DBA01 mysql_shell]# crontab -l
00 02 * * * /bin/bash /mnt/xvda/mysql_shell/delete.sh >> /mnt/xvda/mysql_shell/log/delete.log
00 03 * * * /bin/bash /mnt/xvda/mysql_shell/wholebaseback.sh >> /mnt/xvda/mysql_shell/log/wholebaseback.log
40 03 * * * /bin/bash /mnt/xvda/mysql_shell/branchbaseback.sh >> /mnt/xvda/mysql_shell/log/branchbaseback.log
00 04 * * * /bin/bash /mnt/xvda/mysql_shell/disk.sh >> /mnt/xvda/mysql_shell/log/disk.log
00 05 * * * /usr/bin/expect /mnt/xvda/mysql_shell/scp.sh >> /mnt/xvda/mysql_shell/log/scp.log

三、从库准备 shell脚本 以及 文件路径(192.168.33.52)

(1)准备存放的相关文件路径
[root@DBA02 ~]# mkdir -p /storage/mysql_shell
[root@DBA02 ~]# mkdir -p /storage/mysql_shell/log
[root@DBA02 ~]# mkdir -p /software/mysql_scp
[root@DBA02 ~]# cd /software/mysql_scp/
[root@DBA02 ~]# touch disk_back_test_208.txt
[root@DBA02 ~]# touch sql_running_208.txt

(2)准备 shell 相关脚本(磁盘信息，CPU，内存)
[root@DBA02 ~]# cd /storage/mysql_shell
[root@DBA02 mysql_shell]# vim disk.sh
#/bin/bash

base_dir="/software/mysql_scp"
echo "start databases backup::"
ttime=`date +"%Y-%m-%d_%H:%M:%S"`
ttime1=`date "+%Y-%m-%d"`
echo $ttime

disk_check(){
	cat >> $base_dir/disk_back_test_208.txt <> $base_dir/disk_back_test_208.txt <> $base_dir/disk_back_test_208.txt <> $base_dir/sql_running_208.txt <2{if ($4 ~ /.*:3306/) {print "mysqld OK";exit 0}}"`
EOF
}
Mysql_check

Mysql_run(){
        cat >> $base_dir/sql_running_208.txt <> $base_dir/sql_running_208.txt
#/storage/mysql/app/mysql/bin/mysql -uroot -p"root@1234" -e "show slave statusG" | grep -iE "running:" | awk "{print $1,$2}"
#/storage/mysql/app/mysql/bin/mysql -uroot -p"root@1234" -e "show slave statusG" | grep -iE slave_io_running: | awk "{print $1,$2}"
#/storage/mysql/app/mysql/bin/mysql -uroot -p"root@1234" -e "show slave statusG" | grep -iE slave_sql_running: | awk "{print $1,$2}"
[root@DBA02 mysql_shell]#

(4)准备 shell 相关脚本(拷贝)请自行安装 expect 这个插件这里我就不教怎么安装了
[root@DBA02 mysql_shell]# vim scp.sh
#!/usr/bin/expect
set timeout -1
spawn bash -c "scp -r /software/mysql_scp/* root@192.168.33.50:/opt/source_data_lh/"
expect {
    "password" {send "root";}
    "yes/no" {send "yes";exp_continue}
}
expect eof
exit
[root@DBA01 mysql_shell]# 

(5)准备 shell 相关脚本(清除文件)
[root@DBA02 mysql_shell]# vim delete.sh
#!/bin/bash
dir=/software/mysql_scp           #//需要清空的目录名称
files=`ls ${dir}`
for file in $files
do
if [ -e ${dir}/${file} ];then
rm -f ${dir}/${file}
fi
done
[root@DBA01 mysql_shell]# 

(6)准备执行计划任务(添加)
[root@DBA02 mysql_shell]# crontab -l
00 02 * * * /bin/bash /storage/mysql_shell/delete.sh >> /storage/mysql_shell/log/delete.log
00 04 * * * /bin/bash /storage/mysql_shell/disk.sh >> /storage/mysql_shell/log/disk.log
30 04 * * * /bin/bash /storage/mysql_shell/mysql_running.sh >> /storage/mysql_shell/log/mysql_running.log
30 05 * * * /usr/bin/expect /storage/mysql_shell/scp.sh >> /storage/mysql_shell/log/scp.log

四、巡检数据库操作(192.168.33.50)

(1)在数据库(192.168.33.50)建立对应的库和表。作为 shell 脚本获取的数据插入到数据库表(主从环境，需要两张表 inspection_yjk 为主表  inspection_yjk_208 为从表)
[root@DBA ~]# mysql -uroot -p"*******"
mysql> create database ABC;
Query OK, 1 row affected (0.00 sec)

mysql> use ABC;
mysql> CREATE TABLE `inspection_yjk` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT "逻辑id",
    ->   `date` varchar(200) NOT NULL DEFAULT "" COMMENT "日期",
    ->   `beifenji_gen` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机根容量大小",
    ->   `beifenji_mnt` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机/mnt容量大小",
    ->   `beifenji_cpu` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机/CPU容量大小",
    ->   `beifenji_MemTotal` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机/MemTotal容量大小",
    ->   `Run_MySQL` varchar(255) NOT NULL DEFAULT "" COMMENT "服务器，mysql运行情况",
    ->   `yjk_all_size` varchar(255) NOT NULL DEFAULT "" COMMENT "易监控项目，全库备份大小",
    ->   `yjk_all_runtime` varchar(255) NOT NULL DEFAULT "" COMMENT "易监控项目，全库备份时间",
    ->   `yjk_jk` varchar(255) NOT NULL DEFAULT "" COMMENT "易监控项目，jk库备份大小",
    ->   `yjk_jkproduct` varchar(255) NOT NULL DEFAULT "" COMMENT "易监控项目，jkproduct库备份大小",
    ->   `yjk_fenku_runtime` varchar(255) NOT NULL DEFAULT "" COMMENT "易监控项目，分库备份时间",
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT="易监控项目数据库巡检表";
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `inspection_yjk_208` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT "逻辑id",
    ->   `date` varchar(200) NOT NULL DEFAULT "" COMMENT "日期",
    ->   `gen` varchar(255) NOT NULL DEFAULT "" COMMENT "208服务器,根容量情况",
    ->   `storage` varchar(255) NOT NULL DEFAULT "" COMMENT "208服务器,(/storage容量情况)",
    ->   `beifenji_cpu` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机/CPU容量大小",
    ->   `beifenji_MemTotal` varchar(255) NOT NULL DEFAULT "" COMMENT "备份机/MemTotal容量大小",
    ->   `Run_MySQL` varchar(255) NOT NULL DEFAULT "" COMMENT "208服务器,mysql运行情况",
    ->   `Slave_IO_Running` varchar(255) NOT NULL DEFAULT "" COMMENT "208服务器,(Mysql 主从状态)",
    ->   `Slave_SQL_Running` varchar(255) NOT NULL DEFAULT "" COMMENT "208服务器,(Mysql 主从状态)",
    ->   `entryname` varchar(255) NOT NULL DEFAULT "" COMMENT "项目名称",
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT="易监控数据库巡检表";
Query OK, 0 rows affected (0.01 sec)

mysql> quit;
Bye
[root@DBA ~]#

(2)创建对应的文件目录(192.168.33.51/192.168.33.52 传过来的文件存放地址)这一步骤执行完成之后  在(192.168.33.51/192.168.33.52) 执行除了 delete.sh 脚本外都执行
[root@DBA ~]# mkdir -p /opt/source_data_lh

(3)准备 shell 相关脚本(MySQL 插入文件脚本——1)
[root@DBA ~]# which mysql
/mysql/app/mysql/bin/mysql

[root@DBA ~]# vim /opt/auto_inspection_yjk_208.sh
#!/bin/bash

# define the datetime

ff=$(echo `date +"%Y-%m-%d"`)

# define the variable of 208
date=`awk "NR==1{print $1}" /opt/source_data_lh/disk_back_test_208.txt`
gen=`awk "NR==2{print $2}" /opt/source_data_lh/disk_back_test_208.txt`
storage=`awk "NR==3{print $2}" /opt/source_data_lh/disk_back_test_208.txt`
beifenji_cpu=`awk "NR==5{print $2}" /opt/source_data_lh/disk_back_test_208.txt`
beifenji_memtotal=`awk "NR==7{print $2}" /opt/source_data_lh/disk_back_test_208.txt`
Run_MySQL=`awk "NR==2{print $2}" /opt/source_data_lh/sql_running_208.txt`
Slave_IO_Running=`awk "NR==4{print $2}" /opt/source_data_lh/sql_running_208.txt`
Slave_SQL_Running=`awk "NR==5{print $2}" /opt/source_data_lh/sql_running_208.txt`
entryname=`awk "NR==6{print $1}" /opt/source_data_lh/sql_running_208.txt`

/mysql/app/mysql/bin/mysql  -uroot -p"root@1234" -e "INSERT INTO  ABC.inspection_yjk_208(date,gen,storage,beifenji_cpu,beifenji_memtotal,Run_MySQL,Slave_IO_Running,Slave_SQL_Running,entryname) VALUES ("$date","$gen","$storage","$beifenji_cpu","$beifenji_memtotal","$Run_MySQL","$Slave_IO_Running","$Slave_SQL_Running","$entryname");" 1>/opt/auto_inspection_yjk.log 2>&1
[root@DBA ~]# 

(4)准备 shell 相关脚本(MySQL 插入文件脚本——2)
[root@DBA ~]# vim /opt/auto_inspection_yjk.sh
#!/bin/bash

# define the datetime

ff=$(echo `date +"%Y-%m-%d"`)

# define the variable of 196
date=`awk "NR==1{print $1}" /opt/source_data_lh/all_back_test_183.txt`
yjk_beifenji_gen=`awk "NR==2{print $2}" /opt/source_data_lh/all_back_test_183.txt`
yjk_beifenji_mnt=`awk "NR==3{print $2}" /opt/source_data_lh/all_back_test_183.txt`
beifenji_cpu=`awk "NR==5{print $2}" /opt/source_data_lh/all_back_test_183.txt`
beifenji_memtotal=`awk "NR==7{print $2}" /opt/source_data_lh/all_back_test_183.txt`
Run_mysql=`awk "NR==9{print $2}" /opt/source_data_lh/all_back_test_183.txt`
yjk_all_size=`awk "NR==11{print $2}" /opt/source_data_lh/all_back_test_183.txt`
yjk_all_runtime=`awk "NR==5{print $2}" /opt/source_data_lh/all_back_test_time_183.txt`
yjk_jk_size=`awk "NR==12{print $2}" /opt/source_data_lh/all_back_test_183.txt`
yjk_jkproduct_size=`awk "NR==13{print $2}" /opt/source_data_lh/all_back_test_183.txt`
yjk_fenku_runtime=`awk "NR==10{print $2}" /opt/source_data_lh/all_back_test_time_183.txt`

/mysql/app/mysql/bin/mysql -uroot -p"root@1234" -e "INSERT INTO ABC.inspection_yjk(date,beifenji_gen,beifenji_mnt,beifenji_cpu,beifenji_memtotal,Run_mysql,yjk_all_size,yjk_all_runtime,yjk_jk,yjk_jkproduct,yjk_fenku_runtime)VALUES("$date","$yjk_beifenji_gen","$yjk_beifenji_mnt","$beifenji_cpu","$beifenji_memtotal","$Run_mysql","$yjk_all_size","$yjk_all_runtime","$yjk_jk_size","$yjk_jkproduct_size","$yjk_fenku_runtime");" 1>/opt/auto_inspection_yjk.log 2>&1
[root@DBA ~]# 

(5)准备 shell 相关脚本(脚本管理脚本)
[root@DBA ~]# vim /opt/auto_inspection_2.sh
/bin/bash /opt/auto_inspection_yjk.sh
/bin/bash /opt/auto_inspection_yjk_208.sh
[root@DBA ~]# 

(6)准备执行计划任务(添加)
[root@DBA ~]# crontab -l
30 07 * * * /bin/bash /opt/auto_inspection_2.sh 1>/dev/null 2>&1

(7)总结：
这些脚本循环使用就可以检查数据库是否都在运行，以及运行的情况，当然脚本可根据需求合理安排！

数据库要插入数据，必须在 192.168.33.51/192.168.33.52 上执行除了 delete.sh 脚本以外的脚本都执行

保证 192.168.33.50  /opt/source_data_lh/  目录下有如下四个文件
all_back_test_183.txt
all_back_test_time_183.txt
disk_back_test_208.txt
sql_running_208.txt

每天只需要到 192.168.33.50 用 Navicat MySQL 工具连接   进入 ABC 这个库，查看对应的表数据信息，就可以了！

这样我们每天都是在巡检数据库了！

联系人

姓名：李红
电话：13817757494
QQ号：1322734677
微信号：13817757494