具体迁移过程
具体迁移过程
切的具体操作过程
mysql, 腾讯云的迁移服务
流量 ——> 老代码 ——> 老DB
提前改好代码(改数据库连接)，部好环境，断要切的表的写操作(锁表)，将原来表的数据同步到新的数据库表，将流量切到新代码。
流量 ——> 新代码 ——> 新DB
mongo: - 写操作比较少的, 使用腾讯云的迁移服务做同步, oplog 做增量同步 - 写操作比较多的, 停服, 同步数据, 切流量
流量 ——> 老代码 ——> 老DB
提前改好代码(改数据库连接)，部好环境，断同步数据库, 将流量切到新代码, 将原来表的增量数据通过 oplog 同步到新的DB。
流量 ——> 新代码 ——> 新DB
注: mongoDB不支持手动锁collection, 只能锁整个实例(文档).
一期
切 notify 和部分 merchandise.
mysql 使用锁表策略, mongo使用 oplog 增量同步策略.
涉及库表及工程: notify, merchandise
| 库表名 | 原始位置 | 迁移位置 | 涉及工程 |
|---|---|---|---|
| notify(整个库) | mysql | mysql.extra.notify | notify |
| xnn_merchandise(整个库) | mongo | mongo.base.merchandise | merchandise |
二期
mysql 使用锁表策略, mongo使用 oplog 增量同步策略.
涉及工程: station、ma、 admin
| 库表名 | 原始位置 | 迁移位置 | 涉及工程 | 数据量 |
|---|---|---|---|---|
| 基础数据 | ||||
| tbl_permission | mysql.management | mysql.base.common | ma | 118 |
| auth_permission | mysql.xnn_core_product_2 | mysql.base.common | station | 197 |
| tbl_permission_class | mysql.management | mysql.base.common | admin | 46 |
| tbl_permission_class | mysql.xnn_core_product_2 | mysql.base.common | admin | 35 |
| tbl_select_choose | mysql.management | mysql.base.common | station、ma | 175 |
| 账户 | ||||
| tbl_driver_session | mysql.management | mysql.base.account | station | 556 |
| tbl_drivers | mysql.management | mysql.base.account | station | 3929 |
| tbl_purchaser_supplier | mysql.xnn_core_product_2 | mysql.base.account | station | 7889 |
| tbl_station_permission | mysql.xnn_core_product_2 | mysql.base.account | admin | 328768 |
| auth_role | mysql.xnn_core_product_2 | mysql.base.account | station | 6855 |
| auth_role_permission | mysql.xnn_core_product_2 | mysql.base.account | station | 420497 |
| auth_user | mysql.xnn_core_product_2 | mysql.base.account | station | 9592 |
| auth_user_role | mysql.xnn_core_product_2 | mysql.base.account | station | 12220 |
| station_user | mysql.xnn_core_product_2 | mysql.base.account | station | 9554 |
| openapi_user | mongo.xnn_core_product_2 | mongo.base.account | station | 10 |
| tbl_employee_role | mysql.management | mysql.base.account | ma | 3515 |
| tbl_role_permission | mysql.management | mysql.base.account | ma | 51295 |
| tbl_partner_permission | mysql.management | mysql.base.account | ma | 44429 |
| employee_station_limit | mongo.xnn_core_product_2 | mongo.base.account | ma | 240 |
| tbl_department | mysql.management | mysql.base.account | station、ma | 948 |
| tbl_role | mysql.management | mysql.base.account | station、ma | 1522 |
| tbl_employee_region | mysql.management | mysql.base.account | station、ma | 3622 |
| station运营 | ||||
| tbl_address_route | mysql.management | mysql.base.operation | station | 810 |
| tbl_address_route_detail | mysql.management | mysql.base.opeartion | station | 15568 |
| tbl_distribute_config | mysql.xnn_core_product_2 | mysql.base.opeartion | station | 11409 |
| tbl_distribute_config_address | mysql.xnn_core_product_2 | mysql.base.opeartion | station | 126401 |
| partner_site | mongo.xnn_core_product_2 | mongo.base.opeartion | station | 1 |
| default_config | mongo.xnn_core_product_2 | mongo.base.operation | station | 297 |
| user_config | mongo.xnn_core_product_2 | mongo.base.operation | station | 636 |
| 商品 | ||||
| purchase_specification | mongo.xnn_core_product_2 | mongo.base.merchandise | station | 3799279 |
| 订单 | ||||
| tbl_refund_history | mysql.management | mysql.order.order | station | 111122 |
| tbl_order_fix | mysql.management | mysql.order.order | ma | 9141 |
| tbl_exception | mysql.management | mysql.order.order | station、ma | 357022 |
| tbl_exception_history | mysql.management | mysql.order.order | station、ma | 398050 |
| tbl_refund | mysql.management | mysql.order.order | station、ma | 55193 |
| tbl_order_remark | mysql.management | mysql.order.order | station、ma | 931 |
| tbl_order_sales | mysql.management | mysql.order.order | station、ma | 2671257 |
| order_count | mongo.xnn_core_product_2 | mongo.order.order | station | 3398 |
| order_statistics_cycletime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 170195 |
| order_statistics_orderedtime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 236948 |
| order_statistics_receivedtime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 110041 |
| global_lock | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 529 |
| 其他 | ||||
| tbl_food_security_display_config | mysql.xnn_core_product_2 | mysql.extra.other | station | 131 |
| tbl_food_security_info | mysql.xnn_core_product_2 | mysql.extra.other | station | 482296 |
| tbl_food_security_report | mysql.xnn_core_product_2 | mysql.extra.other | station | 940 |
| tbl_food_security_report_spu | mysql.xnn_core_product_2 | mysql.extra.other | station | 61719 |
| system_key_generator | mongo.xnn_core_product_2 | mongo.extra.other | station | 1 |
| print_log | mongo.xnn_core_product_2 | mongo.extra.other | station | 294630 |
三期
mysql 使用锁表策略, mongo使用 oplog 增量同步策略.
切分拣(sorting)、配送(delivery)、采购(purchase)、进销存(inventory)
涉及工程: station
| 库表名 | 原始位置 | 迁移位置 | 涉及工程 | 数据量 |
|---|---|---|---|---|
| 分拣 | ||||
| tbl_sku_sorting | mysql.management | mysql.sorting.sorting | station | 3119337 |
| tbl_sku_sorting_back | mysql.management | mysql.sorting.sorting | 无 | 19783 |
| tbl_weighting_data | mysql.management | mysql.sorting.sorting | station | 9134 |
| weigh_group_new | mongo.xnn_core_product_2 | mongo.sorting.sorting | station | 80 |
| weighting_baskets | mongo.xnn_core_product_2 | mongo.sorting.sorting | station | 328 |
| weighting_latest | mongo.xnn_core_product_2 | mongo.sorting.sorting | station | 14171812 |
| weighting_op_log | mongo.xnn_core_product_2 | mongo.sorting.sorting | station | 26822805 |
| order_change | mongo.xnn_core_product_2 | mongo.sorting.sorting | station | 378731 |
| 配送 | ||||
| tbl_driver_collection | mysql.management | mysql.sorting.delivery | station | 1421 |
| tbl_car_model | mysql.management | mysql.sorting.delivery | station | 2715 |
| tbl_carrier | mysql.management | mysql.sorting.delivery | station | 3607 |
| distribution_abnormal_sku | mysql.xnn_core_product_2 | mysql.sorting.delivery | station | 473 |
| distribution_order | mysql.xnn_core_product_2 | mysql.sotring.deliveru | station | 4585 |
| distribution_sku | mysql.xnn_core_product_2 | mysql.sorting.delivery | station | 66618 |
| driver_location | mongo.xnn_core_product_2 | mongo.sorting.delivery | station | 11219484 |
| 采购 | ||||
| tbl_quoted_price | mysql.xnn_core_product_2 | mysql.stock.purchase | station | 3953912 |
| purchase_price_limit | mongo.xnn_core_product_2 | mongo.stock.purchase | station | 15 |
| purchase_sheet | mongo.xnn_core_product_2 | mongo.stock.purchase | station | 281446 |
| purchase_sku_log | mongo.xnn_core_product_2 | mongo.stock.purchase | station | 3125476 |
| purchase_tool_price | mongo.xnn_core_product_2 | mongo.stock.purchase | station | 42 |
| 进销存 | ||||
| tbl_shelf | mysql.xnn_core_product_2 | mysql.stock.inventory | station | 196 |
| tbl_shelf_location | mysql.xnn_core_product_2 | mysql.stock.inventory | station | 293 |
| batch_stock | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 3482442 |
| batch_stock_flow | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 5383089 |
| in_stock_log | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 6082234 |
| out_batch_stock_flow | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 695649 |
| out_stock_log | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 23218263 |
| settle_sheet | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 641091 |
| return_to_supply_log | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 30119 |
| stock_loss_log_new | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 403351 |
| stock_value_new | mongo.xnn_core_product_2 | mongo.stock.inventory | station | 7366176 |
四期
mysql 使用锁表策略, mongo使用 oplog 增量同步策略.
涉及工程: station、 bshop、 ma
| 库表名 | 原始位置 | 迁移位置 | 涉及工程 | 数据量 |
|---|---|---|---|---|
| 账户 | ||||
| tbl_subaccount_sid | mysql.management | mysql.base.account | bshop、ma | 1926 |
| tbl_partner | mysql.management | mysql.base.account | station、bshop、ma | 825 |
| tbl_partner_station | mysql.management | mysql.base.account | station、bshop、ma | 1340 |
| tbl_employee | mysql.management | mysql.base.account | station、ma、bshop | 4188 |
| 运营 | ||||
| tbl_invitation_code | mysql.management | mysql.base.opeartion | ma、bshop | 196057 |
| tbl_user_pay_method | mysql.management | mysql.base.opeartion | station、bshop、ma | 148447 |
| tbl_credit_limit | mysql.management | mysql.base.operation | station、bshop、ma | 3098 |
| customized_info | mongo.xnn_core_product_2 | mongo.base.operation | station、bshop | 264 |
| spu_remark_new | mongo.xnn_core_product_2 | mongo.base.opeartion | station、bshop | 2290701 |
| salemenu | mongo.xnn_core_product_2 | mongo.base.operation | station、bshop、ma | 11696 |
| glus_supply_chains | mongo.xnn_core_product_2 | mongo.base.operation | station、bshop、ma | 46 |
| 商品 | ||||
| marketing_promotion | mysql.xnn_core_product_2 | mysql.base.merchandise | station、bshop | 806 |
| promotion_label_2 | mysql.xnn_core_product_2 | mysql.base.merchandise | station、bshop | 2504 |
| promotion_sku | mysql.xnn_core_product_2 | mysql.base.merchandise | station、bshop | 131381 |
| 订单 | ||||
| tbl_trade_flow | mysql.management | mysql.order.order | station、bshop、ma | 419983 |
五期
停服迁移
涉及工程: station, ma, bshop, order
| 库表名 | 原始位置 | 迁移位置 | 涉及工程 | 数据量 |
|---|---|---|---|---|
| 基础数据 | ||||
| tbl_deal_code_sequence | mysql.management | mysql.base.common | ma、bshop、order | 16/16K |
| tbl_region | mysql.management | mysql.base.common | station、ma、bshop、order | 437/48K |
| tbl_area | mysql.management | mysql.base.common | station、ma、bshop、order | 46609/3.5M |
| 账户 | ||||
| tbl_user | mysql.management | mysql.base.account | station、bshop、ma、order | 141808/25.5M |
| tbl_address | mysql.management | mysql.base.account | station、bshop、ma、order | 134848/26.5M |
| station | mongo.xnn_core_product_2 | mongo.base.account | station、bshop、ma、order | 22008/13M |
| 运营 | ||||
| tbl_tax_rule | mysql.management | mysql.base.opeartion | station、order | 85/16K |
| tbl_address_tax_rate | mysql.management | mysql.base.opeartion | station、order | 847/80K |
| tbl_spu_tax_rate | mysql.management | mysql.base.opeartion | station、order | 12749/1.5M |
| tbl_address_salemenu | mysql.management | mysql.base.opeartion | station、bshop、ma、order | 194894/20M |
| price_rule | mongo.xnn_core_product_2 | mongo.base.opeartion | station、bshop、order | 9992/59M |
| wx_pay_info | mongo.xnn_core_product_2 | mongo.base.opeartion | station、bshop、order | 668/128K |
| freight | mongo.xnn_core_product_2 | mongo.base.opeartion | station、bshop、ma、order | 1029/160K |
| service_time | mongo.xnn_core_product_2 | mongo.base.opeartion | station、bshop、ma、order | 2033/264K |
| 商品库 | ||||
| sku_product_snapshot_new | mongo.xnn_core_product_2 | mongo.base.merchandise | station、ma、order | 12470324/3G |
| sku_product_new | mongo.xnn_core_product_2 | mongo.base.merchandise | station、bshop、ma、order、merchandise | 4913146/1G |
| 订单 | ||||
| tbl_order_pay_flow | mysql.management | mysql.order.order | order | 361167/43.6M |
| tbl_order_refund_flow | mysql.management | mysql.order.order | order | 6951/528K |
| tbl_trade_flow_new | mysql.management | mysql.order.order | station、order | 562335/138M |
| tbl_tax_number | mysql.management | mysql.order.order | station、order | 451099/9M |
| tbl_user_balance | mysql.management | mysql.order.order | bshop、ma、order | 29242/5M |
| tbl_user_gift_balance | mysql.management | mysql.order.order | bshop、ma、order | 18218/3M |
| tbl_saas_user_balance | mysql.management | mysql.order.order | bshop、ma、order | 38758/5M |
| tbl_saas_user_gift_balance | mysql.management | mysql.order.order | bshop、ma、order | 22/32K |
| tbl_strike_balance | mysql.management | mysql.order.order | station、ma、order | 334530/49.6M |
| tbl_order_deal | mysql.management | mysql.order.order | bshop、ma、order | 608897/71M |
| tbl_order_strike_flow | mysql.management | mysql.order.order | station、ma、order | 469120/45M |
| order_statistics_cycletime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 170195/1G |
| order_statistics_orderedtime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 236948/2G |
| order_statistics_receivedtime | mongo.xnn_core_product_2 | mongo.order.order | station、ma | 110041/1G |
| order_time_locker | mongo.xnn_core_product_2 | mongo.order.order | station、order | 306403/13M |
| order_new | mongo.xnn_core_product_2 | mongo.order.order | station、bshop、ma、order | 4865625/14G |
| 配送 | ||||
| tbl_delivery_record | mysql.management | mysql.sorting.delivery | station、order | 2020306/320M |
| 进销存 | ||||
| stock_new | mongo.xnn_core_product_2 | mongo.stock.inventory | station、bshop、ma、order | 554875/265M |
| mq | ||||
| msg | mysql.mq | mysql.extra.mq | station、order | 18957578/106G |
| push | mysql.mq | mysql.extra.mq | station、order | 16853671/4.7G |
| subscription | mysql.mq | mysql.extra.mq、order | statin、order | 3/16K |
| subscription_new | mysql.mq | mysql.extra.mq、order | station、order | 8/16K |