数据备份脚本

         有些运维场景需要备份某些大表，select   into操作会比较慢，这里提供可以考虑使用mysqldump,服务本机操作，执行效率也高,脚本如下

#!/bin/bash

dbhost=127.0.0.1
dbname=test_db
dbport=3306
dbuser="root"
dbpwd="root"

backpath="/var/lib/mysql-files"
# 根据需要备份的表填写
tbnames=("test_a" "test_a")

echo "back data start  ......"
rm -f ${backpath}/*.sql
for tbname in "${tbnames[@]}"; do
    bk_tbname=${tbname}_bak
    bk_tb_file=${backpath}/${bk_tbname}.sql

    echo "mysqldump  -u${dbuser} -p${dbpwd} -t ${dbname}  ${tbname} > ${bk_tb_file}"
    mysqldump  -u${dbuser} -p${dbpwd} -t ${dbname}  --skip-comments --no-tablespaces   ${tbname} > ${bk_tb_file}
    echo "${tbname}备份至${ba_tb_file}完成"
    sed -i  "s/${tbname}/${bk_tbname}/g"  ${bk_tb_file}
    echo "${bk_tb_file}表名替换完成"

    drop_sql=" DROP TABLE IF EXISTS ${bk_tbname} ;"
    echo  "删除备份表表结构 ${dorp_sql}"
    mysql -u${dbuser}  -p${dbpwd}  ${dbname}  -e  "${drop_sql} ;"

    create_sql=" CREATE TABLE ${bk_tbname}  LIKE ${tbname} ;"
    echo  "创建备份表表结构 ${create_sql}"
    mysql -u${dbuser}  -p${dbpwd}  ${dbname}  -e  "${create_sql}"

    echo "开始导入${bk_tbname}数据"
    mysql -u${dbuser}  -p${dbpwd}  ${dbname}  < "${bk_tb_file}"
    echo "结束导入${bk_tbname}数据"
done

res=${bakpath}/resfile.txt
rm -f  $res
touch  $res

for tbname in "${tbnames[@]}"; do
   bk_tbname=${tbname}_bak
   query_tb_sql=" SELECT  COUNT(1)  '总数'  FROM  ${tbname};"
   tb_count=$( mysql -u${dbuser}  -p${dbpwd} ${dbname}  -e  "${query_tb_sql}" )
   query_bk_sql=" SELECT  COUNT(1) '总数'  FROM  ${bk_tbname}"
   bk_count=$( mysql -u${dbuser}  -p${dbpwd}  ${dbname}  -e  "${query_bk_sql}" )
   msg= ${tbname}${tb_count}"条",${bk_tbname}${bk_count}"条"
   echo  msg  >>  $res  
done

echo  "back data end  ......"