mysql主从定时备份脚本_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;"

原文：http://www.cnblogs.com/bolinzhihua/p/5650724.html