mysql分庫.分表備份

標籤：mysql   table   databases   

1.mysql的分庫備份

myuser=root

mypass=yzg1314520

mycmd="mysql -u$myuser -p$mypass "

mydump="mysqldump -u$myuser -p$mypass"

for database in  mysql test

do

        $mydump $database   > $database_$(date +%F).sql

       tar -zcvf $database_$(date +%F).tar.gz  $database_$(date +%F).sql

        rm -rf $database_$(date +%F).sql

done

2.mysql的分庫分表備份

myuser=root

mypass=yzg1314520

mycmd="mysql -u$myuser -p$mypass "

mydump="mysqldump -u$myuser -p$mypass"

for database in  `$mycmd -e "show databases" | sed ‘1,3d‘`

do

        mkdir -p /home/scripts/mysqlbak

        for table in `$mycmd -e "show tables from $database;" |sed  ‘1d‘`

        do

        $mydump $database $table  > ${database}_${table}_$(date +%F).sql

         tar -zcvf ${database}_${table}_$(date +%F).tar.gz  ${database}_${table}_$(date +%F)

.sql

        mv ./${database}_${table}_$(date +%F).tar.gz  /home/scripts/mysqlbak

        rm -rf /home/scripts/${database}_${table}_$(date +%F).sql

        rm -rf  $(find /home/scripts/mysqlbak -name "*.tar.gz" -mtime +5 -type f)

        done

done

mysql分庫.分表備份