#!/bin/bash

dump_date=$(date +%Y%m%d-%H);

dump_dir="/backup/DB_BACKUP";
DB_user="userid";
DB_pass="userpwd";

DB_optimize_switch=0;

for database in `/usr/local/mysql/bin/mysqlshow -u ${DB_user} -p${DB_pass} | awk -F" " '{ print $2 }' | grep -v "^$" |grep -v "Databases"`
do
    echo "*------------------------------------------------------ *";
    echo "* ${database} START";
    echo "*------------------------------------------------------ *";

    if [ ! -d "${dump_dir}/${dump_date}/${database}" ]
    then
         mkdir -p ${dump_dir}/${dump_date}/${database};
    fi

    for table in `mysql -u ${DB_user} -p${DB_pass} -e"show tables" ${database} | grep -v "Tables_in_${database}" | grep -v "^$"`
    do
       if [ ${DB_optimize_switch} = "1" ]
       then
           mysql -u ${DB_user} -p${DB_pass} -e"optimize table ${table}" ${database}
       fi

       mysqldump -u ${DB_user} -p${DB_pass} --quick --single-transaction "${database}" ${table} > ${dump_dir}/${dump_date}/${database}/${table}.sql
       echo "mysql ${DB_user} -p${DB_pass} $db < $table.sql" >> $dump_dir/$dump_date/$database/restore.sh
       echo $table;
    done

    sleep 1;
done

echo "* ----------------------- - 7 DAY DELETE-------------------- *";
Old_Date=`/bin/date -d "10 day ago" +"%Y%m%d-%H"`;
rm -rf ${dump_dir}/${Old_Date};

#tar cvfz /backups/${dump_date}.tar ${dump_dir}/${dump_date};

echo "*----------------------------------------------------------- *";
echo "* BACKUP PATH : ${dump_dir}/${dump_date} ";
echo "*----------------------------------------------------------- *";
ls -asl ${dump_dir}/${dump_date};
exit 0;