linux执行mysql脚本

待我称王封你为后i 2023-09-25 09:29 68阅读 0赞

### 如何检查mysql从数据库是否正常运行，脚本 ###

一般在从库执行showslavestatus，看behind值是否为0来判断。

更准确的一些的方法是在主库做一个表，每秒insert一个时间戳，在从库读取，来看时间差是否超过1秒。

### 监控mysql的几个脚本 ###

如果您不太熟悉MySQL监控，开始可以看一看流行的监控软件。例如，下面列出了MySQL Cacti PluginInnodb Buffer Pool Activity•Pages Created•Pages Written•Pages ReadInnodb Buffer Pool Pages•Pool Size•Database Pages•Free Pages•Modified PagesInoodb File I/O•File Reads•Files Writes•Log Writes•File FsyncsInnodb Pending I/O•Aio Log Ios•Aio Sync ios•Buffer Pool Flushes•Chkp Writes•Ibuf Aio Reads•Log Flushes•Log Writes•Normal Aio Reads•Normal Aio WritesInnodb Insert Buffer•InsertsInnodb Log•Log Buffer Size•Log Bytes Written•Log Bytes Flushed•Unflushed LogInnodb Row Operations•Rows Read•Rows Deleted•Rows Updated•Rows InsertedInnodb Semaphores•Spin Rounds•Spin Waits•OS WaitsInnodb Transactions•Innodb Transactions•Current Transactions•History List•Read ViewsMySQL Binary/Relay Logs•Binlog Cache use•Binlog Cache Disk Use•Binary Log Space•Relay Log SpaceMySQL Command Counters•Questions•REPLACE•DELETE MULTI•INSERT SELECT•UPDATE MULTI•REPLACE SELECTMySQL Connections•Max Connections•Max Used Connections•Aborted Clients•Aborted Connects•Threads Connected•ConnectionsMySQL Files and Tables•Table Cache•Open Tables•Open Files•Opened TablesMySQL Network Traffic•Bytes Received•Bytes SentMySQL Processlist•State Closing Tables•State Copying to Tmp Table•State End•State Freeing Items•State Init•State Locked•State Login•State Preparing•State Reading From Net•State Sending Data•State Sorting Result•State Statistics•State Updating•State Writing to Net•State None•State OtherMySQL Query Cache•Queries In Cache•Inserts•Not Cached•Lowmem PrunesMySQL Query Cache Memory•Query Cache Size•Free Memory•Total Blocks•Free BlocksMySQL Replication•Slave Running•Slave Stopped•Slave Lag•Slave Open Temp Tables•Slave Retried TransactionsMySQL Select Types•Select Full Join•Select Full Range Join•Select Range•Select Range Check•Select ScanMySQL Sorts•Sort Rows•Sort Range•Sort Merge Passes•Sort ScanMySQL Table Locks•Table Locks Immediate•Table Locks Waited•Slow QueriesMySQL Temporary Objects•Created Tmp Tables•Created Tmp Disk Tables•Created Tmp FilesMySQL Threads•Thread Cache Size•Threads Created

### 如何检查mysql从数据库是否正常运行，脚本 ###

一般在从库执行show slave status，看behind值是否为0来判断。

更准确的一些的方法是在主库做一个表，每秒insert一个时间戳，在从库读取，来看时间差是否超过1秒。还是先导出sql共用的格式 然后倒入吧

### 监控mysql的几个脚本 ###

如果您不太熟悉MySQL监控，开始可以看一看流行的监控软件。例如，7a686964616fe58685e5aeb931333335336536下面列出了MySQL Cacti Plugin

Innodb Buffer Pool Activity

•Pages Created

•Pages Written

•Pages Read

Innodb Buffer Pool Pages

•Pool Size

•Database Pages

•Free Pages

•Modified Pages

Inoodb File I/O

•File Reads

•Files Writes

•Log Writes

•File Fsyncs

Innodb Pending I/O

•Aio Log Ios

•Aio Sync ios

•Buffer Pool Flushes

•Chkp Writes

•Ibuf Aio Reads

•Log Flushes

•Log Writes

•Normal Aio Reads

•Normal Aio Writes

Innodb Insert Buffer

•Inserts

•Merged

•Merges

Innodb Log

•Log Buffer Size

•Log Bytes Written

•Log Bytes Flushed

•Unflushed Log

Innodb Row Operations

•Rows Read

•Rows Deleted

•Rows Updated

•Rows Inserted

Innodb Semaphores

•Spin Rounds

•Spin Waits

•OS Waits

Innodb Transactions

•Innodb Transactions

•Current Transactions

•History List

•Read Views

MySQL Binary/Relay Logs

•Binlog Cache use

•Binlog Cache Disk Use

•Binary Log Space

•Relay Log Space

MySQL Command Counters

•Questions

•SELECT

•DELETE

•INSERT

•UPDATE

•REPLACE

•LOAD

•DELETE MULTI

•INSERT SELECT

•UPDATE MULTI

•REPLACE SELECT

MySQL Connections

•Max Connections

•Max Used Connections

•Aborted Clients

•Aborted Connects

•Threads Connected

•Connections

MySQL Files and Tables

•Table Cache

•Open Tables

•Open Files

•Opened Tables

MySQL Network Traffic

•Bytes Received

•Bytes Sent

MySQL Processlist

•State Closing Tables

•State Copying to Tmp Table

•State End

•State Freeing Items

•State Init

•State Locked

•State Login

•State Preparing

•State Reading From Net

•State Sending Data

•State Sorting Result

•State Statistics

•State Updating

•State Writing to Net

•State None

•State Other

MySQL Query Cache

•Queries In Cache

•Hits

•Inserts

•Not Cached

•Lowmem Prunes

MySQL Query Cache Memory

•Query Cache Size

•Free Memory

•Total Blocks

•Free Blocks

MySQL Replication

•Slave Running

•Slave Stopped

•Slave Lag

•Slave Open Temp Tables

•Slave Retried Transactions

MySQL Select Types

•Select Full Join

•Select Full Range Join

•Select Range

•Select Range Check

•Select Scan

MySQL Sorts

•Sort Rows

•Sort Range

•Sort Merge Passes

•Sort Scan

MySQL Table Locks

•Table Locks Immediate

•Table Locks Waited

•Slow Queries

MySQL Temporary Objects

•Created Tmp Tables

•Created Tmp Disk Tables

•Created Tmp Files

MySQL Threads

•Thread Cache Size

•Threads Created几个常用的linux监控脚本 下面是几个主机监控的脚本，大家可以根据自己的情况再进行修改，希望能给大家一点帮助。 1、查看主机网卡流量 \#!/bin/bash \#network \#mike.xu while : ; do time='date +%m"-"%d" "%k":"%m' day='date +%m"-"%d' rx\_before='ifconfig eth0|sed -n "8"p|awk '\{print $2\}'|cut -c7-' tx\_before='ifconfig eth0|sed -n "8"p|awk '\{print $6\}'|cut -c7-' sleep 2 rx\_after='ifconfig eth0|sed -n "8"p|awk '\{print $2\}'|cut -c7-' tx\_after='ifconfig eth0|sed -n "8"p|awk '\{print $6\}'|cut -c7-' rx\_result=$\[(rx\_after-rx\_before)/256\] tx\_result=$\[(tx\_after-tx\_before)/256\] echo "$time now\_in\_speed: "$rx\_result"kbps now\_out\_speed: "$tx\_result"kbps" sleep 2 done 2、系统状况监控 \#!/bin/sh \#systemstat.sh \#mike.xu ip=192.168.1.227 top -n 2| grep "cpu" >>./temp/cpu.txt free -m | grep "mem" >> ./temp/mem.txt df -k | grep "sda1" >> ./temp/drive\_sda1.txt \#df -k | grep sda2 >> ./temp/drive\_sda2.txt df -k | grep "/mnt/storage\_0" >> ./temp/mnt\_storage\_0.txt df -k | grep "/mnt/storage\_pic" >> ./temp/mnt\_storage\_pic.txt time=\`date +%m"."%d" "%k":"%m\` connect=\`netstat -na | grep "219.238.148.30:80" | wc -l\` echo "$time $connect" >> ./temp/connect\_count.txt 3、监控主机的磁盘空间,当使用空间超过90％就通过发mail来发警告 \#!/bin/bash \#monitor available disk space space='df | sed -n '/ \\ / $ / p' | gawk '\{print $5\}' | sed 's/%//' if \[ $space -ge 90 \] then fty89@163.com fi 4、 监控cpu和内存的使用情况 \#!/bin/bash \#script to capture system statistics outfile=/home/xu/capstats.csv date='date +%m/%d/%y' time='date +%k:%m:%s' timeout='uptime' vmout='vmstat 1 2' users='echo $timeout | gawk '\{print $4\}' ' load='echo $timeout | gawk '\{print $9\}' | sed "s/,//' ' free='echo $vmout | sed -n '/\[0-9\]/p' | sed -n '2p' | gawk '\{print $4\} ' ' idle='echo $vmout | sed -n '/\[0-9\]/p' | sed -n '2p' |gawk '\{print $15\}' ' echo "$date,$time,$users,$load,$free,$idle" >> $outfile 5、全方位监控主机 \#!/bin/bash \# check\_xu.sh \# 0 \* \* \* \* /home/check\_xu.sh dat="\`date +%y%m%d\`" hour="\`date +%h\`" dir="/home/oslog/host\_$\{dat\}/$\{hour\}" delay=60 count=60 \# whether the responsible directory exist if ! test -d $\{dir\} then /bin/mkdir -p $\{dir\} fi \# general check export term=linux /usr/bin/top -b -d $\{delay\} -n $\{count\} > $\{dir\}/top\_$\{dat\}.log 2>&1 & \# cpu check /usr/bin/sar -u $\{delay\} $\{count\} > $\{dir\}/cpu\_$\{dat\}.log 2>&1 & \#/usr/bin/mpstat -p 0 $\{delay\} $\{count\} > $\{dir\}/cpu\_0\_$\{dat\}.log 2>&1 & \#/usr/bin/mpstat -p 1 $\{delay\} $\{count\} > $\{dir\}/cpu\_1\_$\{dat\}.log 2>&1 & \# memory check /usr/bin/vmstat $\{delay\} $\{count\} > $\{dir\}/vmstat\_$\{dat\}.log 2>&1 & \# i/o check /usr/bin/iostat $\{delay\} $\{count\} > $\{dir\}/iostat\_$\{dat\}.log 2>&1 & \# network check /usr/bin/sar -n dev $\{delay\} $\{count\} > $\{dir\}/net\_$\{dat\}.log 2>&1 & \#/usr/bin/sar -n edev $\{delay\} $\{count\} > $\{dir\}/net\_edev\_$\{dat\}.log 2>&1 & 放在crontab里每小时自动执行：