mysql备份监控脚本

 建一系列目录用于监控和备份

su - mysql

cd /mysql_backup

mkdir backup.1 backup.2 backup.3 monitor_log scripts

定时备份

#!/bin/sh
db_user="user"
db_passwd="password"
db_host="localhost"
backup_dir="/mysql_backup/master_backup"
time="$(date +"%Y-%m-%d-%H-%M")"
# mysql, mysqldump and some other bin's path
MYSQL="/usr/local/mysql/bin/mysql"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
MKDIR="/bin/mkdir"
RM="/bin/rm"
MV="/bin/mv"
GZIP="/bin/gzip"
# check the directory for store backup is writeable
test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0
# the directory for story the newest backup
test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"
# get all databases
#all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
#for db in $all_db
#do
#$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd --skip-lock-tables --single-transaction $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"
#done

$MYSQLDUMP -u$db_user -p$db_passwd --all-databases --opt --add-drop-database --add-drop-table --events --triggers --routines --default-character-set=utf8 --master-data=2 --single-transaction --complete-insert --quote-names --log-error=$backup_dir/mysqldump$time.log | $GZIP -9 > "$backup_dir/backup.0/$time.all_mysql.gz"

 

循环删除

#!/bin/sh
backup_dir="/mysql_backup/master_backup"
time="$(date +"%Y-%m-%d-%H-%M")"
MKDIR="/bin/mkdir"
RM="/bin/rm"
MV="/bin/mv"

test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"
# delete the oldest backup
test -d "$backup_dir/backup.3/" && $RM -rf "$backup_dir/backup.3"
# rotate backup directory
for int in 2 1 0
do
if(test -d "$backup_dir"/backup."$int")
then
next_int=`expr $int + 1`
$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"
fi
done
exit 0;

备份binlog

#!/bin/bash
cd /mysqldata/master_data
find /mysqldata/master_data/ -name "mysql-bin*" -ctime 0 -exec cp {} /mysql_backup/master_backup/backup.1/ \;

 

监控

#!/bin/bash

cd /mysqldata/mysql_data

find /mysqldata/mysql_data/ -name "mysql-bin*" -ctime 0 -exec cp {} /mysql_backup/backup.1/ \;

-bash-4.1$

-bash-4.1$ cat /mysql_backup/scripts/mysql_processlist.sh

#!/bin/bash

 

pgm=`basename $0`

dir=`dirname $0`

 

YY=`date +%Y`

MM=`date +%m`

DD=`date +%d`

HH=`date +%H`

MI=`date +%M`

SS=`date +%S`

day=`date +%a`

 

today=`date +%Y%m%d_%H`

 

datafile="/mysql_backup/monitor_log/$today.txt"

 

 

echo `date` >> $datafile

echo "+------+-------+-------+-----+---------+------+-------+------------------------------+----------+" >> $datafile

echo "| Id   | User  | Host  | db  | Command | Time | State | Info                         | Progress |" >> $datafile

echo "+------+-------+-------+-----+---------+------+-------+------------------------------+----------+" >> $datafile

/usr/local/mysql/bin/mysql -uroot -plisx123 -e "show processlist ;" >> $datafile

 

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile

/usr/local/mysql/bin/mysql -uroot -plisx123 -e "status ;" >> $datafile

 

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile

echo "                                                                         " >> $datafile