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

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

一、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) “`shell (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 -proot@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 -proot@1234 -hlocalhost -e “show databases;”|grep -Evi “database|info|perfor|mysql|sys|test”` do echo $dbname /bin/mysqldump -uroot -proot@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\n”,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\r”;} “yes/no” {send “yes\r”;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) “`shell (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 -proot@1234 -e “show slave status\G” | grep -iE “running:” | awk {print $1,$2} #/storage/mysql/app/mysql/bin/mysql -uroot -proot@1234 -e “show slave status\G” | grep -iE slave_io_running: | awk {print $1,$2} #/storage/mysql/app/mysql/bin/mysql -uroot -proot@1234 -e “show slave status\G” | 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\r”;} “yes/no” {send “yes\r”;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) “`shell (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 -proot@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 -proot@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 这个库，查看对应的表数据信息，就可以了！ 这样我们每天都是在巡检数据库了！

原文地址：https://blog.csdn.net/u011084922/article/details/107642476?utm_medium=distribute.pc_category.none-task-blog-hot-8.nonecase&depth_1-utm_source=distribute.pc_category.none-task-blog-hot-8.nonecase&request_id=