文章

跨境转移数据

发表于 更新于
作者 echoCourage 9 分钟阅读 阅读次数:

国内

主程序 export_seo_task

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

#!/bin/bash

ROOTDIR="/data/rsync_seo_task"
DATADIR=$ROOTDIR/data
LOGSDIR=$ROOTDIR/logs
TMPDIR=$ROOTDIR/tmp

[ ! -d ${LOGSDIR} ] && mkdir -p $LOGSDIR
[ ! -d ${LOGSDIR} ] && mkdir -p $TMPDIR

# 初始化日志文件
DATETIME=$( date "+%Y%m%d" )
LOGFILE="${LOGSDIR}/export_seo_task.log.${DATETIME}"
ERRFILE="${LOGSDIR}/export_seo_task.err.${DATETIME}"
[ ! -f $LOGFILE ] && touch $LOGFILE
[ ! -f $ERRFILE ] && touch $ERRFILE
[ -f ${LOGFILE} ] && exec 1>> ${LOGFILE}
[ -f ${ERRFILE} ] && exec 2>> ${ERRFILE}

REDIS_IP=r-******.redis.rds.aliyuncs.com
REDIS_PORT=6379
REDIS_DB=19
REDIS_PASSWD='******'
CMD_REDIS="/bin/redis-cli -h $REDIS_IP -p $REDIS_PORT -a $REDIS_PASSWD -n $REDIS_DB "

REDIS_KEY="data_transfer"

SSH_CONN="ssh -ttq -o ConnectTimeout=10"

function mysql_run()
{
    export MYSQL_PWD="******"
    local dbhost=rm-******.mysql.rds.aliyuncs.com
    local dbport=3306
    local dbuser=root
    local dbname=cms

    local dbrun="/usr/bin/mysql -A -N -h $dbhost  -P $dbport -u $dbuser -D $dbname"

    $dbrun -e "${1}"
    return $?
}

function mysql_dump()
{
    export MYSQL_PWD="******"
    local dbhost=rm-******.mysql.rds.aliyuncs.com
    local dbport=3306
    local dbuser=root
    local dbname=cms

    local dbdump="/usr/bin/mysqldump -u $dbuser --host=${dbhost}"

    $dbdump "${1}"
    return $?
}

# 日志函数
function logger()
{
    echo $(date "+%Y%m%d%H%M%S"):$*
}
# 加锁函数
function lock_export_seo_task()
{
    local database_name=$1
    local localfile="${LOGSDIR}/export_seo_task_${database_name}.lock"
    if [ -f $localfile ]; then
        return 1
    fi
    touch $localfile
    if [ $? -ne 0 ]; then
        return 1
    fi
    return 0
}

function unlock_export_seo_task()
{
    local database_name=$1
    local localfile="${LOGSDIR}/export_seo_task_${database_name}.lock"
    [ -f "${localfile}" ] && rm -f "${localfile}"
    if [ $? -ne 0 ]; then
        return 1
    fi
    return 0
}

function main()
{
    # 判断当前是否有库在跑
    if [[ -f ${LOGSDIR}/*.lock ]] && [[ ! -f ${LOGSDIR}/*failed.lock ]]; then
        echo "lock is exist, quit!!!"
        return 0
    fi

    # 删除3天前备份文件
    three_ago_date=$( date -d "-3 day" "+%Y%m%d" )
    file_name=$( ls ${DATADIR}/${three_ago_date}* 2>> /dev/null )
    if [[ -n "${file_name}" ]]; then
        logger "check:three_ago_date:${file_name}:exist!"
        for file in ${file_name}
        do
            rm -rf ${file_name}
            if [ $? -ne 0 ];then
                logger "delete:${file_name}:failed!"
            fi
            logger "delete:${file_name}:success!"
        done
    fi
    # 数据库连接测试
    mysql_run "select 1;" >> /dev/null
    if [ $? -ne 0 ]; then
        logger "db:test:failed!"
        unlock_export_seo_task
        return 1
    fi
    logger "db:test:success!"

    # 判断 键值 是否为空
    local ret_cnt=$( $CMD_REDIS scard ${REDIS_KEY} )
    if [ $ret_cnt -eq 0 ]; then
        logger "main:scard:${REDIS_KEY}:empty"
        return 0
    fi

    # 开始操作 mysqldump 备份数据
    source  $ROOTDIR/seo_task_mysqldump.sh ${1} &


}

logger "start"
#main ${1}
main
logger "end"

副程序 seo_task_mysqldump.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

#!/bin/bash

#ROOTDIR="/data/rsync_seo_task"
#DATADIR=$ROOTDIR/data
#LOGSDIR=$ROOTDIR/logs
#TMPDIR=$ROOTDIR/tmp
#
## 初始化日志文件
#LOGFILE="${LOGSDIR}/export_seo_task.log"
#ERRFILE="${LOGSDIR}/export_seo_task.err"
#[ ! -f $LOGFILE ] && touch $LOGFILE
#[ ! -f $ERRFILE ] && touch $ERRFILE
#[ -f ${LOGFILE} ] && exec 1>> ${LOGFILE}
#[ -f ${ERRFILE} ] && exec 2>> ${ERRFILE}

#REDIS_IP=r-******.redis.rds.aliyuncs.com
#REDIS_PORT=6379
#REDIS_DB=19
#REDIS_PASSWD='******'
#CMD_REDIS="/bin/redis-cli -h $REDIS_IP -p $REDIS_PORT -a $REDIS_PASSWD -n $REDIS_DB "
#REDIS_KEY="data_transfer"
#SSH_CONN="ssh -ttq -o ConnectTimeout=10"

function seo_task_mysqldump()
{
    while true
    do
        # 判断 redis key值 是否为空
        local ret_cnt=$( $CMD_REDIS scard ${REDIS_KEY} )
            if [ $ret_cnt -eq 0 ]; then
            return 0
        fi
        # 从 redis 取 key 值
        local ret_database_name="$( $CMD_REDIS spop ${REDIS_KEY} )"
#        local ret_database_name="${1}"
        if [ -z "${ret_database_name}" ]; then
            logger "seo_task_mysqldump:spop:${REDIS_KEY}:failed:${REDIS_KEY} is empty or not exist!"
            return 0
        fi
        logger "seo_task_mysqldump:spop:${REDIS_KEY}:${ret_database_name}:success!"

        logger "seo_task_mysqldump:${ret_database_name}:start!"

        # 加锁
        lock_export_seo_task ${ret_database_name}
        if [ $? -ne 0 ]; then
            echo "lock is faild!!!"
            return 1
        fi

        # mysqldump 备份库及数据
        logger "seo_task_mysqldump:mysqldump:${REDIS_KEY}:${ret_database_name}:start!"
        local bak_sql_file="${TMPDIR}/${ret_database_name}.sql"
        create_bases_sql='CREATE DATABASE IF NOT EXISTS '${ret_database_name}' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
        use_database='USE '${ret_database_name}''
        echo "${create_bases_sql}" > ${bak_sql_file}
        echo "${use_database}" >> ${bak_sql_file}
        mysql_dump "${ret_database_name}" >> ${bak_sql_file}
        if [ $? -ne 0 ]; then
            logger "seo_task_mysqldump:mysqldump:${REDIS_KEY}:${ret_database_name}:failed!"
            lock_export_seo_task ${ret_database_name}.mysqldump.failed
            unlock_export_seo_task ${ret_database_name}
            return 1
        fi

        logger "seo_task_mysqldump:mysqldump:${REDIS_KEY}:${ret_database_name}:success!"

        # 开始压缩
        cd $TMPDIR
        tar -zcvf ${ret_database_name}.sql.tar.gz ${ret_database_name}.sql >> /dev/null
        if [ $? -ne 0 ]; then
            logger "seo_task_mysqldump:tar:${REDIS_KEY}:${ret_database_name}:failed!"
            lock_export_seo_task ${ret_database_name}.tar.failed
            unlock_export_seo_task ${ret_database_name}
            return 1
        fi
        logger "seo_task_mysqldump:tar:${REDIS_KEY}:${ret_database_name}:success!"

        # 压缩后删除备份 sql
        rm -rf ${bak_sql_file}

        # 同步备份MySQL文件 --> SEO-collect-tools 服务器
        start_time=$( date "+%Y%m%d%H%M%S" )
        logger "seo_task_mysqldump:scp:${ret_database_name}.sql.tar.gz:${start_time}:start!"
        scp  ${bak_sql_file}.tar.gz SEOcollect:/home/muker/bin/rsync_seo_task/tmp
        if [ $? -ne 0 ]; then
            logger "seo_task_mysqldump:scp:${ret_database_name}.sql.tar.gz:failed!"
            lock_export_seo_task ${ret_database_name}.scp.failed
            unlock_export_seo_task ${ret_database_name}
            return 1
        fi
        end_time=$( date "+%Y%m%d%H%M%S" )
        logger "seo_task_mysqldump:scp:${ret_database_name}.sql.tar.gz:${start_time}:${end_time}:success!"

        # 备份 sql
        mv ${TMPDIR}/${ret_database_name}.sql.tar.gz ${DATADIR}/${DATETIME}-${ret_database_name}.sql.tar.gz
        if [ $? -ne 0 ]; then
            logger "import_mysql:backup:${ret_database_name}.sql.tar.gz:data:failed!"
            return 1
        fi
        logger "import_mysql:backup:${ret_database_name}.sql.tar.gz:data:success!"


#        # 远程传输完毕之后, 删除压缩文件
#       rm -rf ${bak_sql_file}.tar.gz
#        if [ $? -ne 0 ]; then
#            logger "seo_task_mysqldump:delete:${REDIS_KEY}:${ret_database_name}:failed!"
#            lock_export_seo_task ${ret_database_name}.delete.failed
#            unlock_export_seo_task ${ret_database_name}
#            return 1
#        fi
#        logger "seo_task_mysqldump:delete:${REDIS_KEY}:${ret_database_name}:success!"

        # 开始远程执行 数据导入操作
        $SSH_CONN SEOcollect "/home/muker/bin/rsync_seo_task/import_mysql ${ret_database_name}"
        if [ $? -ne 0 ]; then
            logger "seo_task_mysqldump:ssh_conn:SEOcollect:run:import_mysql:${ret_database_name}:failed!"
            lock_export_seo_task ${ret_database_name}.ssh_conn.failed
            unlock_export_seo_task ${ret_database_name}
            return 1
        fi
        logger "seo_task_mysqldump:ssh_conn:SEOcollect:run:import_mysql:${ret_database_name}:success!"

        unlock_export_seo_task ${ret_database_name}

        logger "seo_task_mysqldump:${ret_database_name}:end!"
        return 0
    done
}

seo_task_mysqldump $*

国外

主脚本: import_mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184

#!/bin/bash

ROOTDIR="/home/muker/bin/rsync_seo_task"
DATADIR=$ROOTDIR/data
LOGSDIR=$ROOTDIR/logs
TMPDIR=$ROOTDIR/tmp

[ ! -d ${LOGSDIR} ] && mkdir -p $LOGSDIR
[ ! -d ${LOGSDIR} ] && mkdir -p $TMPDIR

# 初始化日志文件
DATETIME=$(date "+%Y%m%d")
LOGFILE="${LOGSDIR}/export_seo_task.log.${DATETIME}"
ERRFILE="${LOGSDIR}/export_seo_task.err.${DATETIME}"
[ ! -f $LOGFILE ] && touch $LOGFILE
[ ! -f $ERRFILE ] && touch $ERRFILE
[ -f ${LOGFILE} ] && exec 1>> ${LOGFILE}
[ -f ${ERRFILE} ] && exec 2>> ${ERRFILE}

function seo_collect_mysql_run()
{
    export MYSQL_PWD="******"
    local dbhost=127.0.0.1
    local dbport=3306
    local dbuser=root
    local dbname=cms

    local dbrun="/usr/bin/mysql -A -N -h $dbhost  -P $dbport -u $dbuser -D $dbname"

    $dbrun -e "${1}"
    return $?
}

function seo_task_mysql_run()
{
    export MYSQL_PWD="******"
    local dbhost=rm-******.mysql.rds.aliyuncs.com
    local dbport=3306
    local dbuser=root
    local dbname=cms

    local dbrun="/usr/bin/mysql -A -N -h $dbhost  -P $dbport -u $dbuser -D $dbname"

    $dbrun -e "${1}"
    return $?
}

# 日志函数
function logger()
{
    echo $(date "+%Y%m%d%H%M%S"):$*
}

# 加锁函数
function lock_export_seo_task()
{
    local database_name=$1
    local localfile="${LOGSDIR}/export_seo_task_${database_name}.lock"
    if [ -f $localfile ]; then
        return 1
    fi
    touch $localfile
    if [ $? -ne 0 ]; then
        return 1
    fi
    return 0
}

function unlock_export_seo_task()
{
    local database_name=$1
    local localfile="${LOGSDIR}/export_seo_task_${database_name}.lock"
    [ -f "${localfile}" ] && rm -f "${localfile}"
    if [ $? -ne 0 ]; then
        return 1
    fi
    return 0
}

function import_mysql()
{
    local database_name=$1

    # 删除3天前备份文件
    three_ago_date=$( date -d "-3 day" "+%Y%m%d" )
    file_name=$( ls ${DATADIR}/${three_ago_date}* 2>> /dev/null )
    if [[ -n "${file_name}" ]]; then
        logger "check:three_ago_date:${file_name}:exist!"
        for file in ${file_name}
        do
            rm -rf ${file_name}
            if [ $? -ne 0 ];then
                logger "delete:${file_name}:failed!"
            fi
            logger "delete:${file_name}:success!"
        done
    fi

    # 测试数据库连接
    seo_collect_mysql_run "select 1;" >> /dev/null
    if [ $? -ne 0 ]; then
        logger "seo_collect_db:test:failed!"
        unlock_export_seo_task
        return 1
    fi
    logger "seo_collect_db:test:success!"

    seo_task_mysql_run "select 1;" >> /dev/null
    if [ $? -ne 0 ]; then
        logger "seo_task_db:test:failed!"
        unlock_export_seo_task
        return 1
    fi
    logger "seo_task_db:test:success!"

    # 解压更新文件
    cd $TMPDIR
    local remote_file_name="${database_name}.sql.tar.gz"
    local remote_file_name_sql=$( tar -zxvf $remote_file_name )
    if [ $? -ne 0 ]; then
        logger "import_mysql:tar:zxvf:${remote_file_name}:failed!"
        return 1
    fi
    logger "import_mysql:tar:zxvf:${remote_file_name}:success!"

    # 备份 sql
    mv ${remote_file_name} $DATADIR/${DATETIME}-${remote_file_name}
    if [ $? -ne 0 ]; then
        logger "import_mysql:backup:${remote_file_name}:data:failed!"
        return 1
    fi
    logger "import_mysql:backup:${remote_file_name}:data:success!"

    # 导入备份数据
    logger "import_mysql:source:${remote_file_name_sql}:start"
    sql_statement="source $TMPDIR/${remote_file_name_sql}"
    seo_collect_mysql_run "${sql_statement}"
    if [ $? -ne 0 ]; then
        logger "import_mysql:source:${remote_file_name_sql}:failed!"
        return 1
    fi
    logger "import_mysql:source:${remote_file_name_sql}:success!"

    # 删除解压之后的sql
    logger "import_mysql:delete:${remote_file_name}:start!"
    rm -rf ${remote_file_name_sql}
    if [ $? -ne 0 ]; then
        logger "import_mysql:delete:${remote_file_name}:failed!"
        return 1
    fi
    logger "import_mysql:delete:${remote_file_name}:success!"

    # 更新中转数据库状态
    if [[ "${database_name}" =~ ^cz_.* ]]; then
        update_status_sql_cz="UPDATE cz_articles SET upload_status=2 WHERE db_id IN (SELECT id FROM cz_dbs WHERE db_name='${database_name}');"
        seo_task_mysql_run "${update_status_sql_cz}"
        if [ $? -ne 0 ]; then
            logger "import_mysql:seo_task_mysql_run:update:${database_name}:status:failed!"
            return 1
        fi
        logger "import_mysql:seo_task_mysql_run:update:${database_name}:status:success!"

    else
        update_status_sql="UPDATE article_tasks SET upload_status=2 WHERE db_info_id IN (SELECT id FROM db_infos WHERE db_name='${database_name}');"
        seo_task_mysql_run "${update_status_sql}"
        if [ $? -ne 0 ]; then
            logger "import_mysql:seo_task_mysql_run:update:${database_name}:status:failed!"
            return 1
        fi
        logger "import_mysql:seo_task_mysql_run:update:${database_name}:status:success!"
    fi
    # 删除中转数据库表
    drop_tables_sql="drop tables ${database_name}.post,${database_name}.s_article,${database_name}.s_p_content;"
    seo_task_mysql_run "${drop_tables_sql}"
    if [ $? -ne 0 ]; then
        logger "import_mysql:seo_task_mysql_run:drop:${database_name}.post,s_article,s_p_content:failed!"
        return 1
    fi
    logger "import_mysql:seo_task_mysql_run:drop:${database_name}.post,s_article,s_p_content:success!"

}

import_mysql $1
Linux系统相关
Linux系统相关
本文由作者按照 CC BY 4.0 进行授权