主机分布
|
序号 |
IP |
端口 |
角色 |
|
1 |
192.168.234.56 |
27017 |
router |
|
2 |
192.168.234.55 |
27019 |
configserver |
|
3 |
192.168.234.54 |
27019 |
configserver |
|
4 |
192.168.234.53 |
27019 |
configserver |
|
5 |
192.168.234.55 |
27018 |
replica01-primary |
|
6 |
192.168.234.54 |
27018 |
replica01-secondary |
|
7 |
192.168.234.53 |
27018 |
replica01-arbitary |
|
8 |
192.168.234.55 |
27028 |
replica02-primary |
|
9 |
192.168.234.54 |
27028 |
replica02-arbitary |
|
10 |
192.168.234.53 |
27028 |
replica02-secondary |
一.安装准备
- 1.环境检查
默认数据目录 /var/log/mongo
- 默认日志目录 /var/log/mongodb
- 用户组mongodb:mongodb groupadd -r mongodb && useradd -r -g mongodb mongodb
- 关闭SELinux
- yum install libcurl openssl xz-libs
- 2.启停检查命令
systemctl enable/status/start/stop/restart mongod
- 3.tar包安装
yum install libcurl openssl xz-libs
wget https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-5.0.4.tgz
wget https://fastdl.mongodb.org/linux/mongodb-shell-linux-x86_64-rhel70-5.0.4.tgz
解压出来的文件拷贝到 /usr/local/mongodb-5.0.4/bin
# cloudcanal不支持5.x
wget https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-4.4.10.tgz
wget https://fastdl.mongodb.org/linux/mongodb-shell-linux-x86_64-rhel70-4.4.10.tgz
- 4.keyfile文件准备，并拷贝到所有实例节点
openssl rand -base64 741 > keyfile
二.安装分片1复制集群
1.包含主机端口：192.168.234.53:27018 192.168.234.54:27018 192.168.234.55:27018 - 2.各个节点创建目录
mkdir -p /data/mongodb/replica01_27018/{data,log}
- 3.配置文件 /data/mongodb/replica01_27018/mongod.conf
systemLog:
destination: file
logAppend: true
path: /data/mongodb/replica01_27018/log/mongod.log
storage:
dbPath: /data/mongodb/replica01_27018/data
journal:
enabled: true
directoryPerDB: true
wiredTiger:
engineConfig:
cacheSizeGB: 1
directoryForIndexes: true
collectionConfig:
blockCompressor: zlib
indexConfig:
prefixCompression: true
processManagement:
fork: true
pidFilePath: /data/mongodb/replica01_27018/mongod.pid
timeZoneInfo: /usr/share/zoneinfo
net:
port: 27018
bindIp: 0.0.0.0
security:
authorization: enabled
keyFile: /data/mongodb/keyfile
replication:
oplogSizeMB: 2048
replSetName: replica01
sharding:
clusterRole: shardsvr
- 4.配置分片1集群 - 非仲裁节点执行
mongo --port 27018
use admin
config = {_id: 'replica01', members: [
{_id: 0, host: '192.168.234.53:27018'},
{_id: 1, host: '192.168.234.54:27018'},
{_id: 2, host: '192.168.234.55:27018', "arbiterOnly": true}]
}
rs.initiate(config)
三.安装分片2复制集群
1.包含主机端口192.168.234.53:27028 192.168.234.54:27028 192.168.234.55:27028
- 2.各个节点创建目录
mkdir -p /data/mongodb/replica02_27028/{data,log}
- 3.配置文件 /data/mongodb/replica02_27028/mongod.conf
systemLog:
destination: file
logAppend: true
path: /data/mongodb/replica02_27028/log/mongod.log
storage:
dbPath: /data/mongodb/replica02_27028/data
journal:
enabled: true
directoryPerDB: true
wiredTiger:
engineConfig:
cacheSizeGB: 1
directoryForIndexes: true
collectionConfig:
blockCompressor: zlib
indexConfig:
prefixCompression: true
processManagement:
fork: true
pidFilePath: /data/mongodb/replica02_27028/mongod.pid
timeZoneInfo: /usr/share/zoneinfo
net:
port: 27028
bindIp: 0.0.0.0
security:
authorization: enabled
keyFile: /data/mongodb/keyfile
replication:
oplogSizeMB: 2048
replSetName: replica02
sharding:
clusterRole: shardsvr
- 4.配置分片2集群 - 非仲裁节点执行
mongo --port 27028
use admin
config = {_id: 'replica02', members: [
{_id: 0, host: '192.168.234.53:27028'},
{_id: 1, host: '192.168.234.54:27028', "arbiterOnly": true},
{_id: 2, host: '192.168.234.55:27028'}]
}
rs.initiate(config)
四.安装配置服务集群
1.包含主机端口：192.168.234.53:27019 192.168.234.54:27019 192.168.234.55:27019
- 2.各个节点创建目录
mkdir -p /data/mongodb/configsrv_27019/{data,log}
- 3.配置文件 /data/mongodb/configsrv_27019/mongod.conf
systemLog:
destination: file
logAppend: true
path: /data/mongodb/configsrv_27019/log/mongod.log
storage:
dbPath: /data/mongodb/configsrv_27019/data
journal:
enabled: true
directoryPerDB: true
wiredTiger:
engineConfig:
cacheSizeGB: 1
directoryForIndexes: true
collectionConfig:
blockCompressor: zlib
indexConfig:
prefixCompression: true
processManagement:
fork: true
pidFilePath: /data/mongodb/configsrv_27019/mongod.pid
timeZoneInfo: /usr/share/zoneinfo
net:
port: 27019
bindIp: 0.0.0.0
security:
authorization: enabled
keyFile: /data/mongodb/keyfile
replication:
oplogSizeMB: 2048
replSetName: configserver
sharding:
clusterRole: configsvr
- 4.组成配置服务集群
mongo --port 27019
use admin;
config = {_id: 'configserver', members: [
{_id: 0, host: '192.168.234.53:27019'},
{_id: 1, host: '192.168.234.54:27019'},
{_id: 2, host: '192.168.234.55:27019'}]
}
rs.initiate(config)
五.安装路由服务
1.只有一个节点：192.168.234.56:27017
- 2.创建目录
mkdir -p /data/mongodb/mongos
- 3.配置文件 /data/mongodb/mongos_27017/mongos.conf
systemLog:
destination: file
logAppend: true
path: /data/mongodb/mongos_27017/mongos.log
processManagement:
fork: true
pidFilePath: /data/mongodb/mongos_27017/mongos.pid
timeZoneInfo: /usr/share/zoneinfo
net:
port: 27017
bindIp: 0.0.0.0
security:
keyFile: /data/mongodb/keyfile
sharding:
configDB: configserver/192.168.234.53:27019,192.168.234.54:27019,192.168.234.55:27019
- 4.将前两个复制集群添加到分片集群
mongo 127.0.0.1:27017/admin
use admin
sh.addShard("replica01/192.168.234.53:27018,192.168.234.54:27018,192.168.234.55:27018")
sh.addShard("replica02/192.168.234.53:27028,192.168.234.54:27028,192.168.234.55:27028")
六.创建账号
- 创建管理账号
use admin
db.createUser(
{
user: "xxx",
pwd: "xxx",
roles: [ { role: "root", db: "admin" } ]
}
)
- 创建业务账号
use arch_dbname
db.createUser(
{
user: "xxx",
pwd: "xxx",
roles: [
{ role: "readWrite", db: "arch_dbname" },
{ role: "dbOwner", db: "arch_dbname" }
]
}
)
- 创建测试账号
db.createUser(
{
user: "test_user",
pwd: "test_user",
roles: [
{ role: "readWrite", db: "test_user" },
{ role: "dbOwner", db: "test_user" }
]
}
)
- 登录
mongo --host 192.168.234.56 --port 27017 --authenticationDatabase "arch_dbname" -u "xxx" -p
七.查看集群状态
- db.runCommand({listshards: 1})
mongos> db.runCommand({listshards: 1})
{
"shards" : [
{
"_id" : "replica01",
"host" : "replica01/192.168.234.53:27018,192.168.234.54:27018",
"state" : 1,
"topologyTime" : Timestamp(1637247453, 1)
},
{
"_id" : "replica02",
"host" : "replica02/192.168.234.53:27028,192.168.234.55:27028",
"state" : 1,
"topologyTime" : Timestamp(1637247472, 1)
}
],
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1637247646, 24),
"signature" : {
"hash" : BinData(0,"MTLU3fhix3gv0DAr3N2zCN0BkJE="),
"keyId" : NumberLong("7031921465775620119")
}
},
"operationTime" : Timestamp(1637247646, 24)
}
- sh.status()
mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"minCompatibleVersion" : 5,
"currentVersion" : 6,
"clusterId" : ObjectId("61966751fde244e48f462ab2")
}
shards:
{ "_id" : "replica01", "host" : "replica01/192.168.234.53:27018,192.168.234.54:27018", "state" : 1, "topologyTime" : Timestamp(1637247453, 1) }
{ "_id" : "replica02", "host" : "replica02/192.168.234.53:27028,192.168.234.55:27028", "state" : 1, "topologyTime" : Timestamp(1637247472, 1) }
active mongoses:
"5.0.4" : 1
autosplit:
Currently enabled: yes
balancer:
Currently enabled: yes
Currently running: no
Failed balancer rounds in last 5 attempts: 0
Migration results for the last 24 hours:
33 : Success
databases:
{ "_id" : "config", "primary" : "config", "partitioned" : true }
config.system.sessions
shard key: { "_id" : 1 }
unique: false
balancing: true
chunks:
replica01 991
replica02 33
too many chunks to print, use verbose if you want to force print
mongos>
八.导入数据
- 1.table_prefix_order
- 获取原表字段
select group_concat(column_name) from information_schema.columns where table_name='table_prefix_order';
"id","order_type","order_no","customer_code","customer_name","sales_company_code","sales_company_name","source_document","source_order_no","order_status","order_date","sales_volume","total_amount","shop_discount_order","platform_discount_order","shop_discount","platform_discount","shipping_cost","order_payment","paid_amount","data_source","order_source","ec_order_no","shop_order_id","reissue_order_no","shop_code","shop_name","buyer_id","review_time","gross_weight","tax_plan_code","tax_plan_name","tax_plan_added_value_tax_flag","tax_plan_tax_rate","tax_plan_tax_way","cusbill_id","del_flag","create_by","create_by_id","update_by","update_by_id","create_time","update_time","currency_code","pay_time","import_cause","associated_order","house_code","house_name","order_fair_value","apply_no","del_tid"
- mysql2mongodb_table_prefix_order_full.job 全量追加插入 ，首次使用，"writeMode": {"isReplace": "false","replaceKey": "id"}
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "xxx",
"password": "xxx",
"column": ["id","order_type","order_no","customer_code","customer_name","sales_company_code","sales_company_name","source_document","source_order_no","order_status","order_date","sales_volume","total_amount","shop_discount_order","platform_discount_order","shop_discount","platform_discount","shipping_cost","order_payment","paid_amount","data_source","order_source","ec_order_no","shop_order_id","reissue_order_no","shop_code","shop_name","buyer_id","review_time","gross_weight","tax_plan_code","tax_plan_name","tax_plan_added_value_tax_flag","tax_plan_tax_rate","tax_plan_tax_way","cusbill_id","del_flag","create_by","create_by_id","update_by","update_by_id","create_time","update_time","currency_code","pay_time","import_cause","associated_order","house_code","house_name","order_fair_value","apply_no","del_tid"],
"splitPk": "id",
"connection": [
{ "table": ["table_prefix_order"],
"jdbcUrl": ["jdbc:mysql://192.168.234.204:3310/dbname?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai"]
}
]
}
},
"writer": {
"name": "mongodbwriter",
"parameter": {
"address": ["192.168.234.56:27017"],
"userName": "xxx",
"userPassword": "xxx",
"collectionName": "table_prefix_order",
"column": [{"name": "id", "type": "Long"}, {"name": "order_type", "type": "String"}, {"name": "order_no", "type": "String"}, {"name": "customer_code", "type": "String"}, {"name": "customer_name", "type": "String"}, {"name": "sales_company_code", "type": "String"}, {"name": "sales_company_name", "type": "String"}, {"name": "source_document", "type": "String"}, {"name": "source_order_no", "type": "String"}, {"name": "order_status", "type": "Long"}, {"name": "order_date", "type": "date"}, {"name": "sales_volume", "type": "Long"}, {"name": "total_amount", "type": "Double"}, {"name": "shop_discount_order", "type": "Double"}, {"name": "platform_discount_order", "type": "Double"}, {"name": "shop_discount", "type": "Double"}, {"name": "platform_discount", "type": "Double"}, {"name": "shipping_cost", "type": "Double"}, {"name": "order_payment", "type": "Double"}, {"name": "paid_amount", "type": "Double"}, {"name": "data_source", "type": "Long"}, {"name": "order_source", "type": "Long"}, {"name": "ec_order_no", "type": "String"}, {"name": "shop_order_id", "type": "String"}, {"name": "reissue_order_no", "type": "String"}, {"name": "shop_code", "type": "Long"}, {"name": "shop_name", "type": "String"}, {"name": "buyer_id", "type": "String"}, {"name": "review_time", "type": "date"}, {"name": "gross_weight", "type": "Double"}, {"name": "tax_plan_code", "type": "String"}, {"name": "tax_plan_name", "type": "String"}, {"name": "tax_plan_added_value_tax_flag", "type": "Long"}, {"name": "tax_plan_tax_rate", "type": "Double"}, {"name": "tax_plan_tax_way", "type": "Long"}, {"name": "cusbill_id", "type": "String"}, {"name": "del_flag", "type": "Long"}, {"name": "create_by", "type": "String"}, {"name": "create_by_id", "type": "Long"}, {"name": "update_by", "type": "String"}, {"name": "update_by_id", "type": "Long"}, {"name": "create_time", "type": "date"}, {"name": "update_time", "type": "date"}, {"name": "currency_code", "type": "String"}, {"name": "pay_time", "type": "date"}, {"name": "import_cause", "type": "Long"}, {"name": "associated_order", "type": "String"}, {"name": "house_code", "type": "String"}, {"name": "house_name", "type": "String"}, {"name": "order_fair_value", "type": "Double"}, {"name": "apply_no", "type": "String"}, {"name": "del_tid", "type": "Long"}],
"dbName": "arch_dbname",
"writeMode": {"isReplace": "false","replaceKey": "id"}
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
- mysql2mongodb_table_prefix_order_incr_lastDay.job 增量覆盖插入，定期任务
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "xxx",
"password": "xxx",
"column": ["id","order_type","order_no","customer_code","customer_name","sales_company_code","sales_company_name","source_document","source_order_no","order_status","order_date","sales_volume","total_amount","shop_discount_order","platform_discount_order","shop_discount","platform_discount","shipping_cost","order_payment","paid_amount","data_source","order_source","ec_order_no","shop_order_id","reissue_order_no","shop_code","shop_name","buyer_id","review_time","gross_weight","tax_plan_code","tax_plan_name","tax_plan_added_value_tax_flag","tax_plan_tax_rate","tax_plan_tax_way","cusbill_id","del_flag","create_by","create_by_id","update_by","update_by_id","create_time","update_time","currency_code","pay_time","import_cause","associated_order","house_code","house_name","order_fair_value","apply_no","del_tid"],
"splitPk": "id",
"where": "update_time >= date_sub(curdate(),interval 1 day) and update_time < date_sub(curdate(),interval 0 day)",
"connection": [
{ "table": ["table_prefix_order"],
"jdbcUrl": ["jdbc:mysql://192.168.234.204:3310/dbname?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai"]
}
]
}
},
"writer": {
"name": "mongodbwriter",
"parameter": {
"address": ["192.168.234.56:27017"],
"userName": "xxx",
"userPassword": "xxx",
"collectionName": "table_prefix_order",
"column": [{"name": "id", "type": "Long"}, {"name": "order_type", "type": "String"}, {"name": "order_no", "type": "String"}, {"name": "customer_code", "type": "String"}, {"name": "customer_name", "type": "String"}, {"name": "sales_company_code", "type": "String"}, {"name": "sales_company_name", "type": "String"}, {"name": "source_document", "type": "String"}, {"name": "source_order_no", "type": "String"}, {"name": "order_status", "type": "Long"}, {"name": "order_date", "type": "date"}, {"name": "sales_volume", "type": "Long"}, {"name": "total_amount", "type": "Double"}, {"name": "shop_discount_order", "type": "Double"}, {"name": "platform_discount_order", "type": "Double"}, {"name": "shop_discount", "type": "Double"}, {"name": "platform_discount", "type": "Double"}, {"name": "shipping_cost", "type": "Double"}, {"name": "order_payment", "type": "Double"}, {"name": "paid_amount", "type": "Double"}, {"name": "data_source", "type": "Long"}, {"name": "order_source", "type": "Long"}, {"name": "ec_order_no", "type": "String"}, {"name": "shop_order_id", "type": "String"}, {"name": "reissue_order_no", "type": "String"}, {"name": "shop_code", "type": "Long"}, {"name": "shop_name", "type": "String"}, {"name": "buyer_id", "type": "String"}, {"name": "review_time", "type": "date"}, {"name": "gross_weight", "type": "Double"}, {"name": "tax_plan_code", "type": "String"}, {"name": "tax_plan_name", "type": "String"}, {"name": "tax_plan_added_value_tax_flag", "type": "Long"}, {"name": "tax_plan_tax_rate", "type": "Double"}, {"name": "tax_plan_tax_way", "type": "Long"}, {"name": "cusbill_id", "type": "String"}, {"name": "del_flag", "type": "Long"}, {"name": "create_by", "type": "String"}, {"name": "create_by_id", "type": "Long"}, {"name": "update_by", "type": "String"}, {"name": "update_by_id", "type": "Long"}, {"name": "create_time", "type": "date"}, {"name": "update_time", "type": "date"}, {"name": "currency_code", "type": "String"}, {"name": "pay_time", "type": "date"}, {"name": "import_cause", "type": "Long"}, {"name": "associated_order", "type": "String"}, {"name": "house_code", "type": "String"}, {"name": "house_name", "type": "String"}, {"name": "order_fair_value", "type": "Double"}, {"name": "apply_no", "type": "String"}, {"name": "del_tid", "type": "Long"}],
"dbName": "arch_dbname",
"writeMode": {"isReplace": "true","replaceKey": "id"}
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
- 小心执行
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb/full/mysql2mongodb_table_prefix_order_full.job
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb/incr/mysql2mongodb_table_prefix_order_incr_lastDay.job
- 2.table_prefix_order_ass
- 获取原表字段
select group_concat(column_name) from information_schema.columns where table_name='table_prefix_order_ass';
"id","order_no","del_flag","associated_order"
- mysql2mongodb_table_prefix_order_ass_full.job 全量覆盖插入-没有更新时间戳 ，首次使用，"writeMode": {"isReplace": "false","replaceKey": "id"}
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "xxx",
"password": "xxx",
"column": ["id","order_no","del_flag","associated_order"],
"splitPk": "id",
"connection": [
{ "table": ["table_prefix_order_ass"],
"jdbcUrl": ["jdbc:mysql://192.168.234.204:3310/dbname?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai"]
}
]
}
},
"writer": {
"name": "mongodbwriter",
"parameter": {
"address": ["192.168.234.56:27017"],
"userName": "xxx",
"userPassword": "xxx",
"collectionName": "table_prefix_order_ass",
"column": [{"name": "id", "type": "Long"}, {"name": "order_no", "type": "String"}, {"name": "del_flag", "type": "Long"}, {"name": "associated_order", "type": "String"}],
"dbName": "arch_dbname",
"writeMode": {"isReplace": "true","replaceKey": "id"}
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
- 小心执行
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb_table_prefix_order_ass_full.job
- 小心执行
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb/full/mysql2mongodb_table_prefix_order_ass_full.job
- 3.table_prefix_order_detail
- 获取原表字段
select group_concat(column_name) from information_schema.columns where table_name='table_prefix_order_detail';
"id","shop_code","ec_order_no","order_no","line_no","component_flag","writeoff_flag","outter_id","product_code","product_code_original","product_name","gift_flag","quantity","unit","has_tax","quotation","total_amount","shop_discount","platform_discount","payment","paid_amount","fair_adjust_value","tax_plan","payment_without_tax","tax","shipping_cost","source_document","source_order_no","source_document_line","gross_weight","del_flag","parent_line_no","package_product_code","order_product_detail_id","order_product_detail_id_line_no","update_time","sale_type","invoice_gist","account_gist","is_allow_random_send"
- mysql2mongodb_table_prefix_order_detail_full.job 全量追加插入 ，首次使用，"writeMode": {"isReplace": "false","replaceKey": "id"}
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "xxx",
"password": "xxx",
"column": ["id","shop_code","ec_order_no","order_no","line_no","component_flag","writeoff_flag","outter_id","product_code","product_code_original","product_name","gift_flag","quantity","unit","has_tax","quotation","total_amount","shop_discount","platform_discount","payment","paid_amount","fair_adjust_value","tax_plan","payment_without_tax","tax","shipping_cost","source_document","source_order_no","source_document_line","gross_weight","del_flag","parent_line_no","package_product_code","order_product_detail_id","order_product_detail_id_line_no","update_time","sale_type","invoice_gist","account_gist","is_allow_random_send"],
"splitPk": "id",
"connection": [
{ "table": ["table_prefix_order_detail"],
"jdbcUrl": ["jdbc:mysql://192.168.234.204:3310/dbname?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai"]
}
]
}
},
"writer": {
"name": "mongodbwriter",
"parameter": {
"address": ["192.168.234.56:27017"],
"userName": "xxx",
"userPassword": "xxx",
"collectionName": "table_prefix_order_detail",
"column": [{"name": "id", "type": "Long"}, {"name": "shop_code", "type": "Long"}, {"name": "ec_order_no", "type": "String"}, {"name": "order_no", "type": "String"}, {"name": "line_no", "type": "String"}, {"name": "component_flag", "type": "Long"}, {"name": "writeoff_flag", "type": "Long"}, {"name": "outter_id", "type": "String"}, {"name": "product_code", "type": "String"}, {"name": "product_code_original", "type": "String"}, {"name": "product_name", "type": "String"}, {"name": "gift_flag", "type": "Long"}, {"name": "quantity", "type": "Long"}, {"name": "unit", "type": "String"}, {"name": "has_tax", "type": "Long"}, {"name": "quotation", "type": "Double"}, {"name": "total_amount", "type": "Double"}, {"name": "shop_discount", "type": "Double"}, {"name": "platform_discount", "type": "Double"}, {"name": "payment", "type": "Double"}, {"name": "paid_amount", "type": "Double"}, {"name": "fair_adjust_value", "type": "Double"}, {"name": "tax_plan", "type": "String"}, {"name": "payment_without_tax", "type": "Double"}, {"name": "tax", "type": "Double"}, {"name": "shipping_cost", "type": "Double"}, {"name": "source_document", "type": "String"}, {"name": "source_order_no", "type": "String"}, {"name": "source_document_line", "type": "Long"}, {"name": "gross_weight", "type": "Double"}, {"name": "del_flag", "type": "Long"}, {"name": "parent_line_no", "type": "String"}, {"name": "package_product_code", "type": "String"}, {"name": "order_product_detail_id", "type": "Long"}, {"name": "order_product_detail_id_line_no", "type": "String"}, {"name": "update_time", "type": "date"}, {"name": "sale_type", "type": "Long"}, {"name": "invoice_gist", "type": "Long"}, {"name": "account_gist", "type": "Long"}, {"name": "is_allow_random_send", "type": "Long"}],
"dbName": "arch_dbname",
"writeMode": {"isReplace": "false","replaceKey": "id"}
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
- mysql2mongodb_table_prefix_order_detail_incr_lastDay.job 增量覆盖插入，定期任务
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "xxx",
"password": "xxx",
"column": ["id","shop_code","ec_order_no","order_no","line_no","component_flag","writeoff_flag","outter_id","product_code","product_code_original","product_name","gift_flag","quantity","unit","has_tax","quotation","total_amount","shop_discount","platform_discount","payment","paid_amount","fair_adjust_value","tax_plan","payment_without_tax","tax","shipping_cost","source_document","source_order_no","source_document_line","gross_weight","del_flag","parent_line_no","package_product_code","order_product_detail_id","order_product_detail_id_line_no","update_time","sale_type","invoice_gist","account_gist","is_allow_random_send"],
"splitPk": "id",
"where": "update_time >= date_sub(curdate(),interval 1 day) and update_time < date_sub(curdate(),interval 0 day)",
"connection": [
{ "table": ["table_prefix_order_detail"],
"jdbcUrl": ["jdbc:mysql://192.168.234.204:3310/dbname?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai"]
}
]
}
},
"writer": {
"name": "mongodbwriter",
"parameter": {
"address": ["192.168.234.56:27017"],
"userName": "xxx",
"userPassword": "xxx",
"collectionName": "table_prefix_order_detail",
"column": [{"name": "id", "type": "Long"}, {"name": "shop_code", "type": "Long"}, {"name": "ec_order_no", "type": "String"}, {"name": "order_no", "type": "String"}, {"name": "line_no", "type": "String"}, {"name": "component_flag", "type": "Long"}, {"name": "writeoff_flag", "type": "Long"}, {"name": "outter_id", "type": "String"}, {"name": "product_code", "type": "String"}, {"name": "product_code_original", "type": "String"}, {"name": "product_name", "type": "String"}, {"name": "gift_flag", "type": "Long"}, {"name": "quantity", "type": "Long"}, {"name": "unit", "type": "String"}, {"name": "has_tax", "type": "Long"}, {"name": "quotation", "type": "Double"}, {"name": "total_amount", "type": "Double"}, {"name": "shop_discount", "type": "Double"}, {"name": "platform_discount", "type": "Double"}, {"name": "payment", "type": "Double"}, {"name": "paid_amount", "type": "Double"}, {"name": "fair_adjust_value", "type": "Double"}, {"name": "tax_plan", "type": "String"}, {"name": "payment_without_tax", "type": "Double"}, {"name": "tax", "type": "Double"}, {"name": "shipping_cost", "type": "Double"}, {"name": "source_document", "type": "String"}, {"name": "source_order_no", "type": "String"}, {"name": "source_document_line", "type": "Long"}, {"name": "gross_weight", "type": "Double"}, {"name": "del_flag", "type": "Long"}, {"name": "parent_line_no", "type": "String"}, {"name": "package_product_code", "type": "String"}, {"name": "order_product_detail_id", "type": "Long"}, {"name": "order_product_detail_id_line_no", "type": "String"}, {"name": "update_time", "type": "date"}, {"name": "sale_type", "type": "Long"}, {"name": "invoice_gist", "type": "Long"}, {"name": "account_gist", "type": "Long"}, {"name": "is_allow_random_send", "type": "Long"}],
"dbName": "arch_dbname",
"writeMode": {"isReplace": "true","replaceKey": "id"}
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
- 小心执行
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb/full/mysql2mongodb_table_prefix_order_detail_full.job
cd /usr/local/datax && python ./bin/datax.py ./job/mysql2mongodb/incr/mysql2mongodb_table_prefix_order_detail_incr_lastDay.job
- 4.conrtab任务
- 1.规整目录和job文件
[root@tmallorder-mysql-234-8 datax]# pwd
/usr/local/datax
[root@tmallorder-mysql-234-8 datax]# mkdir -p ./job/mysql2mongodb/{incr,full}
[root@tmallorder-mysql-234-8 datax]# tree job
job
├── job.json
├── mysql2mongodb
│ ├── full
│ │ ├── mysql2mongodb_table_prefix_order_ass_full.job
│ │ ├── mysql2mongodb_table_prefix_order_detail_full.job
│ │ └── mysql2mongodb_table_prefix_order_full.job
│ └── incr
│ ├── mysql2mongodb_table_prefix_order_detail_incr_lastDay.job
│ └── mysql2mongodb_table_prefix_order_incr_lastDay.job
- 2.定义shell进行执行
#!/bin/
cmd_python=/usr/bin/python
cmd_datax=/usr/local/datax/bin/datax.py
job_incr_dir=/usr/local/datax/job/mysql2mongodb/incr/
log_dir=/usr/local/datax/log_job/incr/
for file in $(ls $job_incr_dir);
do
full_job_json=${job_incr_dir}${file}
current_date=$(date "+%Y%m%d_%H%M%S")
log_file=${log_dir}${file}_${current_date}.log
cmd_job=${cmd_python}' '${cmd_datax}' '${full_job_json}
echo $cmd_job
echo $cmd_job '>' $log_file > ${log_file}
$($cmd_job >> ${log_file} )
done
- 3.每天增量抽前一天的数据
05 0 * * * /usr/bin/sh /usr/local/datax/bin/incr_job.sh
九.指定hash分片
- 1.创建索引
use arch_dbname
db.table_prefix_order.createIndex({id:"hashed"},{background:true})
db.table_prefix_order_ass.createIndex({id:"hashed"},{background:true})
db.table_prefix_order_detail.createIndex({id:"hashed"},{background:true})
- 2.日志返回
mongos> db.table_prefix_order.createIndex({id:"hashed"},{background:true})
{
"raw" : {
"replica02/192.168.234.53:27028,192.168.234.54:27028,192.168.234.55:27028" : {
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"commitQuorum" : "votingMembers",
"ok" : 1
}
},
"ok" : 1,
"operationTime" : Timestamp(1637311261, 3),
"$clusterTime" : {
"clusterTime" : Timestamp(1637311261, 3),
"signature" : {
"hash" : BinData(0,"QfWB2/2ZcIhMtRCdhBX/m7nri+U="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
mongos> db.table_prefix_order_ass.createIndex({id:"hashed"},{background:true})
{
"raw" : {
"replica02/192.168.234.53:27028,192.168.234.54:27028,192.168.234.55:27028" : {
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"commitQuorum" : "votingMembers",
"ok" : 1
}
},
"ok" : 1,
"operationTime" : Timestamp(1637311292, 6),
"$clusterTime" : {
"clusterTime" : Timestamp(1637311292, 6),
"signature" : {
"hash" : BinData(0,"M5ISYC2qXSI2toTfQ+1OidQQNqg="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
mongos> db.table_prefix_order_detail.createIndex({id:"hashed"},{background:true})
{
"raw" : {
"replica02/192.168.234.53:27028,192.168.234.54:27028,192.168.234.55:27028" : {
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"commitQuorum" : "votingMembers",
"ok" : 1
}
},
"ok" : 1,
"operationTime" : Timestamp(1637311302, 4),
"$clusterTime" : {
"clusterTime" : Timestamp(1637311302, 4),
"signature" : {
"hash" : BinData(0,"tzBg42LLUPAamP4xARwauPW0QQA="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
- 3.分片 - 在admin库下执行
db.runCommand({enablesharding: "arch_dbname"})
db.runCommand({shardcollection:'arch_dbname.table_prefix_order', key:{id:"hashed"}})
db.runCommand({shardcollection:'arch_dbname.table_prefix_order_ass', key:{id:"hashed"}})
db.runCommand({shardcollection:'arch_dbname.table_prefix_order_detail', key:{id:"hashed"}})
- 4.日志返回
mongos> use admin
switched to db admin
mongos> db.runCommand({enablesharding: "arch_dbname"})
{
"ok" : 1,
"operationTime" : Timestamp(1637312508, 4),
"$clusterTime" : {
"clusterTime" : Timestamp(1637312508, 4),
"signature" : {
"hash" : BinData(0,"JAQmDRdkmbjj7zLANt0gbhMSJk8="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
mongos> db.runCommand({shardcollection:'arch_dbname.table_prefix_order', key:{id:"hashed"}})
{
"collectionsharded" : "arch_dbname.table_prefix_order",
"collectionUUID" : UUID("507342f3-05d0-4663-9c1f-58bc2eb86d9b"),
"ok" : 1,
"operationTime" : Timestamp(1637312532, 5),
"$clusterTime" : {
"clusterTime" : Timestamp(1637312532, 5),
"signature" : {
"hash" : BinData(0,"SyVu7CQV8Bv5tQnZ86Bgydf4oJ4="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
mongos> db.runCommand({shardcollection:'arch_dbname.table_prefix_order_ass', key:{id:"hashed"}})
{
"collectionsharded" : "arch_dbname.table_prefix_order_ass",
"collectionUUID" : UUID("41fc8ce5-1d7e-490d-aa0b-d53000137aec"),
"ok" : 1,
"operationTime" : Timestamp(1637312539, 6),
"$clusterTime" : {
"clusterTime" : Timestamp(1637312539, 6),
"signature" : {
"hash" : BinData(0,"NTaD+JvffgAfNeuhCGGGvj5ZMBs="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
mongos> db.runCommand({shardcollection:'arch_dbname.table_prefix_order_detail', key:{id:"hashed"}})
{
"collectionsharded" : "arch_dbname.table_prefix_order_detail",
"collectionUUID" : UUID("80d5b953-530a-4abc-9d43-c45b288c917c"),
"ok" : 1,
"operationTime" : Timestamp(1637312542, 51),
"$clusterTime" : {
"clusterTime" : Timestamp(1637312542, 51),
"signature" : {
"hash" : BinData(0,"DHf5D0YFkq2jD3J0oFdp4Rxyxy8="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
十.查看状态
- 1.集群分片 sh.status() db.printShardingStatus()
mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"minCompatibleVersion" : 5,
"currentVersion" : 6,
"clusterId" : ObjectId("619756c4946eeb46926a0f82")
}
shards:
{ "_id" : "replica01", "host" : "replica01/192.168.234.53:27018,192.168.234.54:27018", "state" : 1 }
{ "_id" : "replica02", "host" : "replica02/192.168.234.53:27028,192.168.234.55:27028", "state" : 1 }
active mongoses:
"4.4.10" : 1
autosplit:
Currently enabled: yes
balancer:
Currently enabled: yes
Currently running: no
Failed balancer rounds in last 5 attempts: 0
Migration Results for the last 24 hours:
549 : Success
databases:
{ "_id" : "arch_dbname", "primary" : "replica02", "partitioned" : true, "version" : { "uuid" : UUID("e2b8d06b-f81e-4d0f-9bd0-94fc7a5bfd79"), "lastMod" : 1 } }
arch_dbname.table_prefix_order
shard key: { "id" : "hashed" }
unique: false
balancing: true
chunks:
replica01 21
replica02 21
too many chunks to print, use verbose if you want to force print
arch_dbname.table_prefix_order_ass
shard key: { "id" : "hashed" }
unique: false
balancing: true
chunks:
replica02 1
{ "id" : { "$minKey" : 1 } } -->> { "id" : { "$maxKey" : 1 } } on : replica02 Timestamp(1, 0)
arch_dbname.table_prefix_order_detail
shard key: { "id" : "hashed" }
unique: false
balancing: true
chunks:
replica01 16
replica02 16
too many chunks to print, use verbose if you want to force print
{ "_id" : "config", "primary" : "config", "partitioned" : true }
config.system.sessions
shard key: { "_id" : 1 }
unique: false
balancing: true
chunks:
replica01 512
replica02 512
too many chunks to print, use verbose if you want to force print
- 2.数据库状态 db.stats()
mongos> db.stats()
{
"raw" : {
"replica01/192.168.234.53:27018,192.168.234.54:27018,192.168.234.55:27018" : {
"db" : "arch_dbname",
"collections" : 2,
"views" : 0,
"objects" : 1032717,
"avgObjSize" : 1203.0164943542131,
"dataSize" : 1242375585,
"storageSize" : 167464960,
"indexes" : 4,
"indexSize" : 83501056,
"totalSize" : 250966016,
"scaleFactor" : 1,
"fsUsedSize" : 12985925632,
"fsTotalSize" : 105550635008,
"ok" : 1
},
"replica02/192.168.234.53:27028,192.168.234.54:27028,192.168.234.55:27028" : {
"db" : "arch_dbname",
"collections" : 5,
"views" : 0,
"objects" : 2021367,
"avgObjSize" : 1201.9029998016194,
"dataSize" : 2429487061,
"storageSize" : 306704384,
"indexes" : 8,
"indexSize" : 99672064,
"totalSize" : 406376448,
"scaleFactor" : 1,
"fsUsedSize" : 9010630656,
"fsTotalSize" : 105550635008,
"ok" : 1
}
},
"objects" : 3054084,
"avgObjSize" : 1201.6762859174796,
"dataSize" : 3671862646,
"storageSize" : 474169344,
"totalSize" : 657342464,
"indexes" : 12,
"indexSize" : 183173120,
"scaleFactor" : 1,
"fileSize" : 0,
"ok" : 1,
"operationTime" : Timestamp(1637312765, 1),
"$clusterTime" : {
"clusterTime" : Timestamp(1637312772, 1),
"signature" : {
"hash" : BinData(0,"UWKvrlBDfbRflWikOknfBJ7ZsMk="),
"keyId" : NumberLong("7032184742975897620")
}
}
}
- 3.查看表状态
db.table_prefix_order.stats()
db.table_prefix_order_ass.stats()
db.table_prefix_order_detail.stats()
十一.数据删除
待补充(通过调度脚本获取存在mongodb 200天之前的数据id，在源库进行删除：源库加锁-查询-确认已落库-源库删除-提交)
十二.优势
- 1.解决了归档库因单点配置达到上线无法扩容问题
- 2.数据落地到归档库可自动实现自动均衡
- 3.可利用datax-web进行可视化配置和调度
十三.不足
- 1.数据类型异构数据库间无法完全1v1对应
- 2.全量insert速度快，但upsert全量数据速度慢，存在超时风险(可多批次，小批量解决)
- 3.datax-web的邮件告警功能不生效(配置问题？)，需要额外增加监控实现