1.datax同步多表

#!/bin/bash
. /etc/profile

# 读库的变量
r_ip="192.168.1.6"
r_port="3306"
r_username="root"
r_password="123456"

# 写入库的变量
w_ip="192.168.1.4"
w_port="61920"
w_username="star"
w_password="123456"

# 获取库名
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

# vi /data/datax/script/all_Sync_Task.sh
填写以下内容
#!/bin/bash
. /etc/profile

# 读库的变量
r_ip="192.168.1.6"
r_port="3306"
r_username="root"
r_password="123456"

# 写入库的变量
w_ip="192.168.1.4"
w_port="61920"
w_username="star"
w_password="123456"

Tool_Datax='/usr/bin/python2.7 /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": {
"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}"
}
}
}
]
}
}