#!/bin/sh  
  
db_host=192.168.1.55  
db_port=3306  
db_username=ruobbo
db_password="123456"
db_name=ruobbo_test

backup_dir="/home/backup"  
today=`date "+%Y%m%d%H%M%S"`
exclude_tables=(t_attendance_record t_operation_log)

for n in `mysql -u$db_username -p$db_password -h$db_host -e 'show databases;' 2>/dev/null|grep -Ev '_schema|mysql'|sed '1d'`;
do
    mkdir -p $backup_dir/$n
 
    for t in `mysql -u$db_username -p$db_password -h$db_host $n -e "show tables;" 2>/dev/null|sed '1d'`;  
    do  
        
        if [[ "${exclude_tables[@]}" =~ $t ]]; then
            continue
        fi
        
        backup_file="${t}_${today}.sql.gz" 
     
        mysqldump -u$db_username -p$db_password -h$db_host $db_name --quick --skip-lock-tables --single-transaction $t | gzip > $backup_dir/$n/$backup_file;
    done
done

find $backup_dir -mtime +7 -name "*sql.gz" -exec rm {} \;