从mysql到 postgresql_数据从Mysql恢复到Postgres

执行步骤：

1.备份mysql数据库

备份工具目录结构：

[ansible@java2 wz-temp]$ tree mysql_pg/

mysql_pg/

├── conf

│ └── common.sh

├── dump_mysql.sh

└── logs

├── mysql_bak_tables20180514.log

├── mysql_bak_tables20180517.log

├── mysql_bak_tables20180601.log

...

2 directories, 8 files

备份工具dump_mysql.sh：

#!/bin/bash

# 注意：执行该脚本一定要先进入到脚本的当前目录下

source ./conf/common.sh

set -o errexit

set -o nounset

DUMP_CMD="/usr/local/mysql/bin/mysqldump"

DB_CONN="-u rds_read -pxxxx -h 192.168.x.x"

ARGS=" --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date --set-gtid-purged=OFF --skip-tz-utc "

DB_NAME="xxx_db"

DISK_USAGE=3

BackPath="/web/share/backup_tables_pg"

LOG_PREFIX="mysql_bak_tables"

dat="`date +%F`"

# 判断执行返回结果

Execution_result(){

local method_name="$1"

local right_message="$2"

local error_message="$3"

if [[ $? -eq 0 ]]; then

LOG_INFO "${method_name}" "${right_message}"

else

LOG_ERR "${method_name}" "${error_message}"

exit 1

fi

}

# 磁盘空间检测

Check_disk(){

set +o errexit

local func_name="Check_disk"

DiskFree=`df -h | awk -F 'G' 'NR==2 {print $3}'`

c=$(echo " $DiskFree < $DISK_USAGE" | bc) # 小数点的运算

if [[ $c -eq 1 ]]; then

LOG_ERR "${func_name}" "there have no enough disk space."

exit 1

else

LOG_INFO "${func_name}" "Check disk spacess passed."

fi

set -o errexit

}

# 备份表

Backup_tables(){

local func_name="Backup_tables"

Check_disk

if [[ ! -d "${BackPath}" ]]; then

LOG_WARN "${func_name}" "Backup path is not exists, and create it now."

sudo /bin/mkdir -p "${BackPath}"

else

LOG_INFO "${func_name}" "Backup path is available"

fi

# Backup tables

for i in vip_level vip_level_benefit vip_user_level; do

sudo "${DUMP_CMD}" ${DB_CONN} ${ARGS} ${DB_NAME} "${i}" > "${BackPath}"/"${i}"_"${dat}".sql

Execution_result "${func_name}" "Backup table $i succeed." "Backup table $i failed."

done

}

# 对vip_level_benefit表处理成pg能接收的格式

Data_deal(){

local func_name="Data_deal"

if [[ ! -f ${BackPath}/vip_level_benefit_${dat}.sql ]]; then

LOG_ERR "${func_name}" "vip_level_benefit_${dat}.sql is not exists."

exit 1

else

sudo sed -i 's/\\//g' "${BackPath}"/vip_level_benefit_"${dat}".sql

Execution_result "${func_name}" "deal with backup data of vip_level_benefit succeed." "deal with data of vip_level_benefit failed."

fi

}

# 清理备份

Clean_oldback(){

local func_name="Clean_oldback"

sudo find "${BackPath}" -type f -mtime +5 -name "vip_*.sql" -exec rm -rf {} \;

Execution_result "${func_name}" "Clean old tables succeed." "Clean old tables failed."

}

main(){

Backup_tables

Data_deal

Clean_oldback

}

main

日志文件：common.sh , 代码见另一篇文章，Bash脚本的日志公用方法

2.将数据导入到pg数据库中

目录结构:

tree import_pg

import_pg

├── conf

│ └── common.sh

├── import_pg.sh

└── logs

├── import_pg_data20180517.log

├── import_pg_data20180601.log

├── import_pg_data20180612.log

....

2 directories, 7 files

备份恢复到pg中脚本，import_pg.sh

#!/bin/bash

# 注意：执行该脚本一定要先进入到脚本的当前目录下

source ./conf/common.sh

set -o errexit

set -o nounset

BackPath="/web/share/backup_tables_pg"

LOG_PREFIX="import_pg_data"

HOST="10.26.20.75"

USER="p2p_activity_rw"

DBNAME="p2p_activity"

PORT="8888"

NAME_suffix="$(date +%F.sql)"

Result_time="`date '+%Y-%m-%d %H:%M:%S'`"

TABLE_LIST="vip_level_${NAME_suffix} vip_level_benefit_${NAME_suffix} vip_user_level_${NAME_suffix}"

#TABLE_LIST="vip_level_${NAME_suffix} vip_level_benefit_${NAME_suffix} "

Execution_result(){

local method_name="$1"

local right_message="$2"

local error_message="$3"

if [[ $? -eq 0 ]]; then

LOG_INFO "${method_name}" "${right_message}"

else

LOG_ERR "${method_name}" "${error_message}"

exit 1

fi

}

Clean_old_tables(){

local func_name="Clean_old_tables"

sudo /usr/bin/psql -h "${HOST}" -p "${PORT}" -U "${USER}" -d "${DBNAME}" << EOF

truncate table vip_level;

truncate table vip_level_benefit;

truncate table vip_user_level;

EOF

# truncate table vip_user_level;

Execution_result "${func_name}" "truncate tables succeed." "truncate tables failed."

}

Import_tables(){

local func_name="Import_tables"

for i in ${TABLE_LIST} ; do

sudo /usr/bin/psql -h "${HOST}" -p "${PORT}" -U "${USER}" -d "${DBNAME}" < "${BackPath}"/$i > /dev/null

Execution_result "${func_name}" "import $i to p2p_activity db succeed." "import $i to p2p_activity db failed."

done

}

main(){

Clean_old_tables

Import_tables

}

main

备注：

导入到pg数据库的前提条件是需配置服务器本机免密码登陆pg数据库，具体参考简书上写的另一篇文章: 从阿里云上备份pg部分表到内网还原

日志文件：common.sh , 代码见另一篇文章：Bash脚本的日志公用方法