mysql主从集群定时备份脚本
#!/bin/bash
dpath="/mysql_backup"
mydays="7"
username="root"
mysql_pwd="redhat"
xmysql="/application/mysql/bin/mysql -u$username -p${mysql_pwd}"
xmysqldump="/application/mysql/bin/mysqldump -u$username -p${mysql_pwd}"
today=`date +%Y%m%d`
old_backup_dir=`date -d "-${mydays} day" +%Y%m%d`
if [ -d $old_backup_dir ];then
rm -rf $old_backup_dir
fi
if [ ! -d $today ]; then
mkdir -p $dpath/$today
cp /etc/my.cnf $dpath/$today
fi
$xmysql -e "show databases;"|grep -vE "(Database|_schema)" > $dpath/db_list
$xmysql -e "flush tables with read lock;"
while read dbname
do
if [ ! -d $dpath/$today/$dbname ];then
mkdir -p $dpath/$today/$dbname
fi
#######################
#echo $dbname
$xmysql -e "use $dbname; show tables;"|grep -vE "(Tables_in|general_log|slow_log)" > $dpath/table_list
while read tablename
do
#echo "echo $tablename"
$xmysqldump $dbname $tablename -R --events --triggers > $dpath/$today/$dbname/$tablename.sql
done < $dpath/table_list
#######################
done < $dpath/db_list
rm -rf $dpath/db_list
rm -rf $dpath/table_list
ls -al /application/mysql/data/mysql-bin* | grep -v mysql-bin.index | tail -1 | awk '{print $5,$9}'| awk -F'/' '{print $1,$5}' > /mysql_backup/$today/position.txt
$xmysql -e "unlock tables;"
