数据库监控设计DBA专属

database

作者:李红(老李),中国开源协会(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

 

以上是 数据库监控设计DBA专属 的全部内容, 来源链接: utcz.com/z/535106.html

回到顶部