使用shell脚本从一个数据库按条件导出部份表数据然后按条件清除表数据，并把导出的数据导入到新库的表里

作用：A库作为源库，B库目的库。A库中的某些表只保留当天最新的数据，历史数据被转移到B库的表里。

从A库的有关表里先备份数据(个别表有条件)，然后清空这些表的数据(个别表有条件)，再然后把备份的数据导入到B库的表里，最后删除备份的数据

事先从A库中获取到这些表的表结构，在B库中导入这些表结构。因为A库备份的只有表数据，没有表结构

后续问题：A库表里若有字段变动，需同步在B库里操作

表名文件：tables_names.txt，其内容为：（每行表示一个表名）

b2c_pass_free_payment_messagesend_log
b2c_pass_free_payment_query_log
b2c_pass_free_payment_sign_log
b2c_pass_free_payment_terminate_log

脚本内容:

1. b.sh

#!/bin/bash

# 作用:导出某个库中指定表的表结构,所有表结构导入到一个sql文件中，方便导入到新库的表里
# 再导出每个表的数据，作为备份

# 源数据库信息

source_host="xxx"
source_user="xxx"
source_password="xxx"
source_port="3306"
source_db_name="xxx"

# 全局变量
time=`date +%Y%m%d%H%M%S`
back_path="/back_mysql/icbc_jdd_icbc-db"
data_path="${back_path}/data"
log="icbc_jdd.log"

mkdir -p ${data_path}/${time}

export text_path=${back_path}/tables_names.txt
for table_name in $(cat $text_path)
do  
    # -d 表结构
    /back_mysql/mysqldump -h${source_host} -u${source_user} -P${source_port} -p${source_password} --set-gtid-purged=OFF -d ${source_db_name} ${table_name} >> ${data_path}/${time}/all.sql
    # -t 表数据
    /back_mysql/mysqldump -h${source_host} -u${source_user} -P${source_port} -p${source_password} --skip-extended-insert --set-gtid-purged=OFF -t ${source_db_name} ${table_name} > ${data_path}/${time}/${table_name}.sql
    [[ $? == 0 ]] && echo -e "\033[32m $time ${table_name} backup success \033[0m" >> ${data_path}/${time}/${log} ||  echo -e "\033[31m $time ${table_name} backup failed \033[0m" >> ${data_path}/${time}/${log}
done

2.a.sh

#!/bin/bash

# 源数据库信息
source_host="xxx"
source_user="xxx"
source_password="xxx"
source_port="3306"
source_db_name="xxx"

# 目的数据库信息
destination_host="xxx"
destination_user="xxx"
destination_password="xxx"
destination_port="3306"
destination_db_name="xxx"

# 全局变量
time=`date +%Y%m%d%H%M%S`
back_path="/back_mysql/icbc_jdd_icbc-db"
data_path="${back_path}/data"
log="/back_mysql/icbc_jdd_icbc-db/result.log"
tar_time=`date '+%Y-%m-%d 00:00:00' --date='-3 month'`

mkdir -p ${data_path}/${time}

export text_path=${back_path}/tables_names.txt
for table_name in $(cat $text_path)
do  
    echo '1.1 备份数据表:'${table_name}
    if [[ ${table_name} = "icbc_user_account_flow" ]] || [[ ${table_name} = "icbc_aggregate_payment_notify_log" ]] || [[ ${table_name} = "icbc_aggregate_payment_log" ]]; then
        table_where="create_time < '${tar_time}'"
        /back_mysql/mysqldump -h${source_host} -u${source_user} -P${source_port} -p${source_password} --skip-extended-insert --set-gtid-purged=OFF -t ${source_db_name} ${table_name} --where="create_time < '${tar_time}'" > ${data_path}/${time}/${table_name}.sql
    else
        /back_mysql/mysqldump -h${source_host} -u${source_user} -P${source_port} -p${source_password} --skip-extended-insert --set-gtid-purged=OFF -t ${source_db_name} ${table_name} > ${data_path}/${time}/${table_name}.sql
    fi
    
    #/back_mysql/mysqldump -h${source_host} -u${source_user} -P${source_port} -p${source_password} --skip-extended-insert --set-gtid-purged=OFF -t ${source_db_name} ${table_name} > ${data_path}/${time}/${table_name}.sql
    [[ $? == 0 ]] && echo -e "${time} ${table_name} backup success" >> ${log} || echo -e "${time} ${table_name} backup failed" >> ${log}
    
    echo "1.2 清空数据表:"${table_name}
    if [[ ${table_name} = "icbc_user_account_flow" ]] || [[ ${table_name} = "icbc_aggregate_payment_notify_log" ]] || [[ ${table_name} = "icbc_aggregate_payment_log" ]]; then
        truncate_sql="delete from ${table_name} where create_time < '${tar_time}'"
    else
        truncate_sql="truncate table ${table_name}"
    fi
    /usr/bin/mysql -h${source_host} -u${source_user} -P${source_port} -p${source_password} ${source_db_name} -e "${truncate_sql}"
    [[ $? == 0 ]] && echo -e "${time} ${table_name} truncate success" >> ${log} || echo -e "${time} ${table_name} truncate failed" >> ${log}
    
    echo "1.3 导入数据表:"${table_name}
    /usr/bin/mysql -h${destination_host} -u${destination_user} -P${destination_port} -p${destination_password} ${destination_db_name} < ${data_path}/${time}/${table_name}.sql
    [[ $? == 0 ]] && echo -e "${time} ${table_name} source success" >> ${log} || echo -e "${time} ${table_name} source failed" >> ${log}
done

echo "1.4 删除数据表:"${table_name}
cd ${data_path}
tar -zcf ${time}.tar.gz ${time} --remove-files
find ${data_path} -name "*.tar.gz" -mtime +15 -type f -exec rm -rf {} \;
# 日志中怎么输出删除的压缩包，还没想好咋写，下方注释表示的是当前备份的压缩包，但是并不是实际删除的
#[[ $? == 0 ]] && echo -e "\033[32m ${data_path}/${time}.tar.gz delete  success \033[0m" >> ${log} ||  echo -e "\033[31m ${data_path}/${time}.tar.gz delete failed \033[0m" >> ${log}

配置定时任务

#每天早上6点执行
0 6 * * * /usr/bin/bash /root/a.sh > /dev/null 2>&1