MySQL多表全量同步

mysql_init.sh  
#!/bin/bash
. /etc/profile

# 读库的变量
r_ip="10.20.30.28"
r_port="3316"
r_username="arch_wt"
r_password="xxxxxx"

# 写入库的变量
w_ip="10.20.30.28"
w_port="6336"
w_username="db_user"
w_password="xxxxxx"

# 获取库名
Mysql_Names=`mysql -h$r_ip -u$r_username -p$r_password -P$r_port -e "show databases\G" |grep 'Database'|awk -F'Database: ' '{print $2}' |grep -v 'information_schema\|performance_schema\|test\|sys\|mysql\|test1|'`

function mysql_init(){
    mysql_path="/data/datax/mysql/"
    mkdir $mysql_path
    for DataBase in $Mysql_Names;
        do
        #1.导出数据库结构：
        mysqldump -d ${DataBase} -h$r_ip -u$r_username -p$r_password -P$r_port > ${mysql_path}${DataBase}.sql
        #2.创建数据库
        mysql -h$w_ip -u$w_username -p$r_password -P$w_port -e "CREATE database ${DataBase} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
        #3.导入数据库结构：
        mysql -u$w_username -h$w_ip -P$w_port -p$w_password ${DataBase} < ${mysql_path}${DataBase}.sql
    done
}
#数据库初始化导出、导入数据库
mysql_init



all_task.sh
#!/bin/bash
. /etc/profile

# 读库的变量
r_ip="10.20.30.28"
r_port="3316"
r_username="arch_wt"
r_password="xxxxxx"

# 写入库的变量
w_ip="10.20.30.28"
w_port="6336"
w_username="db_user"
w_password="xxxxxx"

Tool_Datax='/bin/python3  /data/datax/bin/datax.py'

# 获取库名
Mysql_Names=`mysql -h$r_ip -u$r_username -p$r_password -P$r_port -e "show databases\G" |grep 'Database'|awk -F'Database: ' '{print $2}' |grep -v 'information_schema\|performance_schema\|test\|sys\|mysql\|test1|'`

for dbname in $Mysql_Names;
    do
        # 获取表名
        table_tchema=`mysql -h$r_ip -u$r_username -p$r_password -P$r_port -e "use ${dbname}; show full tables;"|grep 'TABLE'|awk '{print $1}'`
        #echo $table_tchema;
        
        #循环导入数据库
        for table_name in $table_tchema;
            do
                echo $table_name;
                $Tool_Datax  /data/datax/job/mysql2mysql_All.json -p "-Dr_ip=$r_ip -Dr_port=$r_port -Dr_dbname=$dbname -Dr_username=$r_username -Dr_password=$r_password -Dw_ip=$w_ip -Dw_port=$w_port -Dw_dbname=$dbname -Dw_username=$w_username -Dw_password=$w_password -Dtable_name=$table_name"
        done
done


#DataX全量同步(某一张表)
#$Tool_Python  /data/datax/job/mysql2mysql_dzzoffice.json -p "-Dr_ip=$r_ip -Dr_port=$r_port -Dr_dbname=$r_dbname -Dr_username=$r_username -Dr_password=$r_password -Dw_ip=$w_ip -Dw_port=$w_port -Dw_dbname=$w_dbname -Dw_username=$w_username -Dw_password=$w_password"

# DataX全量同步(多个文件直接写多个执行命令)
#$Tool_Python  /data/datax/job/mysql2mysql_All.json -p "-Dr_ip=$r_ip -Dr_port=$r_port -Dr_dbname=$r_dbname -Dr_username=$r_username -Dr_password=$r_password -Dw_ip=$w_ip -Dw_port=$w_port -Dw_dbname=$w_dbname -Dw_username=$w_username -Dw_password=$w_password -Dtable_name=$table_name"




job/mysql2mysql_All.json
{
    "job": {
        "setting": {
            "speed": {
                "channel": 10
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader", 
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://${r_ip}:${r_port}/${r_dbname}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull"],
                                "table": ["${table_name}"]
                            }
                        ], 
                        "username": "${r_username}",
                        "password": "${r_password}"
                    }
                }, 
                "writer": {
                    "name": "mysqlwriter", 
                    "parameter": {
                        "writeMode": "update",
                        "column": ["*"],
                        "session": [
                            "set session sql_mode='ANSI'"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://${w_ip}:${w_port}/${w_dbname}?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull", 
                                "table": ["${table_name}"]
                            }
                        ],
                        "username": "${w_username}",
                        "password": "${w_password}"
                    }
                }
            }
        ]
    }
}