一文帮你搞定Mysql外表同步所有问题

toms · 2022年10月15日 13:19

Mysql_To_Rocks

github地址：

https://github.com/Toms1999/Mysql-To-Rocks.git

1、业务需求及其痛点

Mysql外表需求和痛点：

业务系统很多表结构一直存储在mysql当中，其中的大表（数据量大）都会同步至rocks中，数据量较小的维表没必要同步至rocks当中，可以通过外表的方式挂载到rocks中，但是创建外表的步骤较为繁琐，只能一张张手动创建，另外mysql中表结构更改后，外表就需要重建

痛点：

①：外部表手动创建繁琐，如100张表全部手动创建

②：mysql表结构更改就需要重新创建外表

2、mysql_to_rocks结构图

工具实现上述优化，优点如下：

shell编写极其轻量，开源即用
简单配置实现全程自动化处理
解决了mysql表结构变更的痛点

架构图：

mysql_to_rocks/
├── bin
│   ├── e_auto.sh --外部表执行脚本
│   └── e_mysql_to_rocks.sh --外部表建表语句生成脚本
├── conf
│   ├── rocks.conf --外部表连接信息
│   ├── rocks_tables --外部表库名.表名（自定义）
│   ├── mysql.conf --外部表连接信息
│   └── mysql_tables --源表库名.表名
└── lib
    └── mysql_to_rocks.sh --mysql映射rocks外表结构转换

介绍：

首先用户需要配置conf目录下的4个配置文件分别是：

rocks.conf：rocks的连接信息以及驱动名称和rocks的密码信息，用户默认为root
mysql.conf：mysql的连接信息
mysql_tables：需要被同步的mysql表名，格式为库名.表名
rocks_tables：同步至rocks中的表名，需要用户自定义出表名称，格式同样为库名.表名

配置完配置文件后即可执行脚本生成外部表建表语句

sh e_mysql_to_rocks.sh

该脚本会将mysql的建表语句转换成rocks的建表语句，生成的语句会在上层目录的result中命名为e_mysql_to_rocks.sql

生成外部表建表语句后即可执行同步脚本，该脚本会运行上述文件，并且后续对用户的配置项和mysql表结构实施监控，即用户新增表或者是mysql的表结构发生了变更，该脚本会首先删除掉所有的rocks中的外表，然后重新创建建表语句并且执行它

nohup sh e_auto.sh &

代码流程：

1、获取建表语句

for table in $(cat ../conf/e_mysql/mysql_tables |grep -v '#' | awk -F '\n' '{print $1}')
        do
        echo "show create table ${table};" |mysql -h$mysql_host -uroot -p$mysql_password  >> $path
done

2、调整格式

awk -F '\t' '{print $2}' $path |awk '!(NR%2)' |awk '{print $0 ";"}' > ../result/tmp111.sql
sed -i 's/\\n/\n/g' ../result/tmp111.sql
sed -n '/CREATE TABLE/,/ENGINE\=/p' ../result/tmp111.sql > ../result/tmp222.sql
##delete tables special struct
sed -i '/^  CON/d' ../result/tmp222.sql
sed -i '/^  KEY/d' ../result/tmp222.sql

3、拼接rocks信息

sed -i '/ENGINE=/a) ENGINE=MYSQL\n COMMENT "MYSQL"\nPROPERTIES (\n"host" = "rocksHostIp",\n"port" = "3306",\n"user" = "root",\n"password" = "rocksHostPassword",\n"database" = "rocksDataBases",\n"table" = "rocksTables",\n"driver" = "MySQL",\n"MYSQL_type" = "mysql");' $path

3、功能演示

mysql外表同步：

mysql中创建表：

mysql -uroot -proot;

create database t_demo;
use t_demo;
CREATE TABLE `t_cickp_charge_connector` (
  `ID` varchar(32) NOT NULL COMMENT '主键',
  `E_ID` varchar(32) NOT NULL COMMENT '关联充电设备表ID',
  `CONNECTOR_ID` varchar(26) NOT NULL,
  `CONNECTOR_NAME` varchar(30) DEFAULT NULL,
  `CONNECTOR_TYPE` smallint DEFAULT NULL,
  `VOLTAGE_UPPER_LIMIT` int DEFAULT NULL,
  `VOLTAGE_LOWER_LIMIT` int DEFAULT NULL,
  `CONNECTOR_CURRENT` int DEFAULT NULL,
  `CONNECTOR_POWER` decimal(19,10) DEFAULT NULL,
  `PARK_NO` varchar(10) DEFAULT NULL,
  `VOLTAGE` int DEFAULT NULL,
  `BMS_POWER_TYPE` smallint DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `idx_equipment_id` (`E_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='充电枪表';

insert into t_demo.t_cickp_charge_connector (ID, E_ID, CONNECTOR_ID, CONNECTOR_NAME, CONNECTOR_TYPE, VOLTAGE_UPPER_LIMIT, VOLTAGE_LOWER_LIMIT, CONNECTOR_CURRENT, CONNECTOR_POWER, PARK_NO, VOLTAGE, BMS_POWER_TYPE, CREATE_TIME, UPDATE_TIME)
values  ('000256eb359d470082b20ad4a4edf88e', '207f80bec5e14c27b38042d46930282d', '000000001001132__0', '充电枪01', 3, 750, 220, 112, 84.0000000000, '', 0, 3, '2022-08-22 10:50:12', null),
        ('00081df6bbeb4463bc945bde3076b1b0', 'd619e677456f4bf8998e241dae28692b', 'TS1704140074001', 'TS1704140074', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 12:30:04', null),
        ('0010e36ba5b6432b812901512d2767b2', 'd7cd94fa66944c80951997ac81b9f53f', 'HE121118010071001', 'HE121118010071', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 15:40:43', null),
        ('001a672ca25f45deab52deae778a98e1', '3bc1663886a942febf04cb39ea8ea803', '500085001', '500085A', 4, 0, 0, 200, 120.0000000000, '', 750, 1, '2021-10-12 16:05:53', null),
        ('001cca79ea2f4ac7a73e830165ceb0cd', '02823a48b93047dd955839f34948084b', 'HE121118062736_1001', 'HE121118062736_1', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 12:29:56', null),
        ('002f30773c674d32960ae5ec1230ee16', 'bc296f5e53c24594b839ebea34ff565f', 'HE121118040817_1001', 'HE121118040817_1', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 12:29:52', null),
        ('003b630113fb45f6ad52337ad4e21619', '0313985f1b274a50a861a29287776551', 'HE121122010801001', 'HE121122010801', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 12:30:19', null),
        ('003dc6e54c60427cb5a1003de4504877', '178de38f88454d85bc3fd36fb2c63c28', 'GP1804280341001', 'GP1804280341', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 12:30:25', null),
        ('003e884f521442b4ab9bf050395f0b7f', 'fc9b29532bfe4cc9a10647f4ed2ea7e2', 'HE121118063280_1001', 'HE121118063280_1', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 15:40:44', null),
        ('004e481d713242c1b49026b45042809a', '3949d30b22e7414b953bca61ee973476', 'HE121121050028001', 'HE121121050028', 3, 220, 220, 32, 7.0000000000, null, 220, 255, '2022-07-23 15:40:44', null);


CREATE TABLE `t_cickp_charge_equipment` (
  `ID` varchar(32) NOT NULL COMMENT '主键',
  `S_ID` varchar(32) NOT NULL COMMENT '关联充电站表ID',
  `EQUIPMENT_ID` varchar(24) NOT NULL,
  `MANUFACTURER_ID` varchar(10) DEFAULT NULL,
  `EQUIPMENT_MODEL` varchar(20) DEFAULT NULL,
  `PRODUCTION_DATE` varchar(10) DEFAULT NULL,
  `EQUIPMENT_TYPE` smallint DEFAULT NULL,
  `EQUIPMENT_LNG` decimal(19,6) DEFAULT NULL,
  `EQUIPMENT_LAT` decimal(19,6) DEFAULT NULL,
  `EQUIPMENT_NAME` varchar(30) DEFAULT NULL,
  `EQUIPMENT_TOTAL_POWER` decimal(19,1) DEFAULT NULL,
  `MANUFACTURER_NAME` varchar(30) DEFAULT NULL,
  `EQUIPMENT_ORDER` varchar(255) DEFAULT NULL,
  `EQUIPMENT_STATUS` smallint DEFAULT NULL,
  `EQUIPMENT_POWER` decimal(19,1) DEFAULT NULL,
  `NEW_NATIONAL_STANDARD` smallint DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `ACCURACY_LEVEL` smallint DEFAULT NULL,
  `CHECK_TIME_LAST` varchar(30) DEFAULT NULL,
  `CHECK_TIME_NEXT` varchar(30) DEFAULT NULL,
  `CERTIFICATE` varchar(255) DEFAULT NULL,
  `start_using_date` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '投入使用时间',
  `put_into_use` datetime(3) DEFAULT NULL COMMENT '投入使用时间',
  PRIMARY KEY (`ID`),
  KEY `idx_station_id` (`S_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='充电桩表';

insert into t_demo.t_cickp_charge_equipment (ID, S_ID, EQUIPMENT_ID, MANUFACTURER_ID, EQUIPMENT_MODEL, PRODUCTION_DATE, EQUIPMENT_TYPE, EQUIPMENT_LNG, EQUIPMENT_LAT, EQUIPMENT_NAME, EQUIPMENT_TOTAL_POWER, MANUFACTURER_NAME, EQUIPMENT_ORDER, EQUIPMENT_STATUS, EQUIPMENT_POWER, NEW_NATIONAL_STANDARD, CREATE_TIME, UPDATE_TIME, ACCURACY_LEVEL, CHECK_TIME_LAST, CHECK_TIME_NEXT, CERTIFICATE, start_using_date, put_into_use)
values  ('0014d314ec694faead804302efabfeee', '4392ed2f04154473b9f8b91c2a938741', 'HE121121050250', '360051856', 'ZL30-A6', '2021-10-08', 2, 113.905457, 22.771673, 'HE121121050250', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:30:24', null, null, null, null, null, null, null),
        ('001e0b7c5dc7436fb5479179080983f5', 'bf036a0583334522acab7b2f33444608', 'HE121119050138', '360051856', 'ZL30-A6', '2020-05-18', 2, 114.322876, 22.693771, 'HE121119050138', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:30:29', null, null, null, null, null, null, null),
        ('002c8597b9b241978b0a2d700e32cfae', '3465e6c1098a41d9893aa8b52727798d', '500020', 'MA5DRRDX1', 'ZDDC120BG', null, 1, 0.000000, 0.000000, '17', 120.0, '深圳智电新能源科技有限公司', null, 50, 120.0, 2, '2021-10-12 16:01:34', '2022-06-20 12:12:43', null, null, null, null, null, null),
        ('008b368751c643219da292fd76d569f1', '65ce25d50c1642799bd97759da568373', '000000001046001', 'MA5DA0053', 'CL5823', '2017-06-02', 1, 114.352464, 22.711021, '1号桩', 120.0, '深圳车电网', null, 50, 120.0, 2, '2022-08-22 10:50:06', null, null, null, null, null, null, null),
        ('009cb3f213384199b9d1816fb6900dcc', '1713051024a74b15874ffabd0411890c', '000000001063015', 'MA5DA0053', 'CL5899', '2021-11-27', 2, 113.920228, 22.535621, '交流充电桩', 7.0, '深圳市车电网络有限公司', null, 50, 7.0, 2, '2022-05-30 11:19:17', '2022-06-14 16:32:47', null, null, null, null, null, null),
        ('00ba38325d0e4804960bbb591f0f69e1', '7a69943d322a411e92132ca6989c55f7', 'HE121121052645', '360051856', 'ZL30-A6', '2022-01-10', 2, 113.909195, 22.601933, 'HE121121052645', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:30:04', null, null, null, null, null, null, null),
        ('00c25458804446d8b2fad1d007c1c812', 'cd14b93718ee468cbeccf1e84a2583cb', 'HE121119052764', '360051856', 'ZL30-A6', '2019-07-26', 2, 114.049232, 22.701670, 'HE121119052764', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:29:55', null, null, null, null, null, null, null),
        ('00d113034d9b4067bd12afbea03a1b52', '1c0a1b912d974a828ba82c620e0bba26', 'HE121120041074', '360051856', 'ZL30-A6', '2021-05-12', 2, 113.946404, 22.498718, 'HE121120041074', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:30:15', null, null, null, null, null, null, null),
        ('00d61d48e8004df095eb74985258d493', '8b7214a83fd74cd1bce14ace5e129107', 'HE121119030713_1', '360051856', 'ZL30-A6', '2019-05-15', 2, 113.883667, 22.788374, 'HE121119030713_1', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:29:57', null, null, null, null, null, null, null),
        ('00d70d75e8ce44ba9eed370f67432bff', '4f52fcd099f547db8b44421d22d53dec', 'TS1704250011', '360051856', 'JL7-A2-TS', '2017-07-17', 2, 113.930763, 22.523027, 'TS1704250011', 7.0, null, null, 50, 7.0, 2, '2022-07-23 12:30:11', null, null, null, null, null, null, null);
        
create database p_demo;
use p_demo;
CREATE TABLE `p_inspection_task` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '检定任务id',
  `name` varchar(63) DEFAULT NULL COMMENT '任务名称',
  `type` int DEFAULT NULL COMMENT '任务类型(0:指定检定,1:桩随机抽检,2:双随机抽检)',
  `task_issue_date` datetime DEFAULT NULL COMMENT '任务下达日期',
  `status` int DEFAULT '0' COMMENT '状态(0:新建,1:已下达,2:进行中,3:已完成)',
  `task_achieve_time` datetime DEFAULT NULL COMMENT '实际完成时间',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deadline` datetime DEFAULT NULL COMMENT '计划完成时间',
  `charger_platform_task` bigint DEFAULT NULL COMMENT '充电桩平台下发的任务id',
  `creator` int DEFAULT NULL COMMENT '创建者',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8mb3 COMMENT='检定任务表';

insert into p_demo.p_inspection_task (id, name, type, task_issue_date, status, task_achieve_time, create_time, update_time, deadline, charger_platform_task, creator)
values  (1, '任务1', 0, '2022-06-22 10:44:14', 0, '2022-06-23 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (2, '任务2', 0, '2022-06-22 11:07:24', 0, '2022-06-09 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (3, '任务3', 1, '2022-06-22 11:19:23', 0, '2022-06-01 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (4, '预警检定任务-1', 0, '2022-06-22 14:07:23', 0, '2022-06-30 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (5, '预警检定-2', 0, '2022-06-22 14:09:47', 0, '2022-06-29 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (6, '预警检定任务-1', 0, '2022-06-22 14:22:03', 0, '2022-06-10 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (7, '预警检定任务-2', 0, '2022-06-22 14:22:28', 2, '2022-06-29 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (8, '预警检定任务XX', 0, '2022-06-23 10:43:59', 0, '2022-06-30 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (9, 'A任务1', 0, '2022-06-23 10:56:23', 0, '2022-06-30 00:00:00', '2022-07-13 16:34:32', null, null, null, null),
        (10, 'A任务2', 1, '2022-06-23 11:06:10', 0, '2022-06-28 00:00:00', '2022-07-13 16:34:32', null, null, null, null);
        
        
CREATE TABLE `p_inspection_result_record` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `inspect_task_detail_id` varchar(255) NOT NULL COMMENT '检定任务详情id',
  `looks_check` int DEFAULT NULL COMMENT '外观检查(0:不合格,1:合格)',
  `insulation_resistance_check` int DEFAULT NULL COMMENT '绝缘电阻(0:不合格,1:合格)',
  `work_temp_amend_value` varchar(16) DEFAULT NULL COMMENT '工作误差温度修正值',
  `accuracy_level` varchar(16) DEFAULT NULL COMMENT '工作误差精确度等级',
  `value_temp_amend_value` varchar(16) DEFAULT NULL COMMENT '示值误差温度修正值',
  `value_errors_check` int DEFAULT NULL COMMENT '示值误差检查(0:不合格,1:合格)',
  `pay_errors_check` int DEFAULT NULL COMMENT '付费金额误差检查(0:不合格,1:合格)',
  `clock_errors` int DEFAULT NULL COMMENT '时钟求值误差检查(0:不合格,1:合格)',
  `conclusion` int DEFAULT NULL COMMENT '结论(0:不合格,1:合格)',
  `check_time` datetime DEFAULT NULL COMMENT '检定时间',
  `report_state` int DEFAULT NULL COMMENT '报告审批状态(0审批驳回,1审批通过,2进行中)',
  `inspect_report` varchar(255) DEFAULT NULL COMMENT '鉴定报告',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb3 COMMENT='检定结果记录';

insert into p_demo.p_inspection_result_record (id, inspect_task_detail_id, looks_check, insulation_resistance_check, work_temp_amend_value, accuracy_level, value_temp_amend_value, value_errors_check, pay_errors_check, clock_errors, conclusion, check_time, report_state, inspect_report)
values  (1, '10000', 1, 1, '0', '1', '0', 0, 1, 1, null, '2022-07-16 14:20:59', 2, null),
        (2, '44444', 1, null, null, null, null, 1, 1, 1, null, '2022-09-17 15:37:24', null, null),
        (3, '3713841537fc462d8304e3ec60cf803a', null, null, null, null, null, null, null, null, null, '2022-08-02 08:00:00', null, null),
        (4, '537b3970e78441f097a8c6b42dbebdef', null, null, null, null, null, null, null, null, null, '2022-08-03 08:00:00', null, null),
        (5, '319d093d14c44c90810ed90e179d8ec6', 0, 1, '121', '1', '133', 1, 0, 1, null, '2022-11-09 08:00:00', null, null),
        (6, '7271e97cd6cd4472b30f881170ae0d4d', null, null, null, null, null, null, null, null, null, '2022-08-09 08:00:00', null, null),
        (7, 'f2522ac4834546e7aaf31630631ab53b', null, null, null, null, null, null, null, null, null, '2022-08-09 08:00:00', null, null),
        (8, 'fc52a61adc174d7b95de600f7033a336', null, null, null, null, null, null, null, null, null, '2022-08-04 08:00:00', null, null),
        (9, '4a2d79a855374c098a7a4704077e7b73', null, null, null, null, null, null, null, null, null, '2022-08-09 08:00:00', null, null),
        (10, 'e95419eb804f438aa43ef31138cc282e', null, null, null, null, null, null, null, null, null, '2022-08-02 08:00:00', null, null);

配置文件

rocks.conf
rocks_tables
mysql.conf
mysql_tables

mysql_tables

t_demo.t_cickp_charge_connector
t_demo.t_cickp_charge_equipment
p_demo.p_inspection_task
p_demo.p_inspection_result_record

rocks_tables

demo.demo1
demo.demo2
demo.demo3
demo2.demo

执行脚本

sh e_mysql_to_rocks.sh
nohup sh e_auto.sh &

可以清楚看到外表已经创建完成并且查询也是OK，包括元数据变更等在此不过多演示，工具使用方法以及github地址如下：

git clone https://github.com/Toms1999/Mysql-To-Rocks.git

后续有任何问题直接通过该在此github仓库提交issue即可，本人会第一时间去完善该工具

Kate_Shao · 2022年10月17日 11:42

写的很不错，爱了爱了！

U_1697190541146_5157 · 2023年10月13日 09:51

starrocks我们用的是2.3的版本，mysql中的bit类型可以通过外表导入到starrocks吗？将bit类型定义成tinyint解析报错，定义成varchar(1)导入后是乱码。

toms · 2023年10月16日 03:51

这个和sr版本没多大关系目前的情况是，mysql里面的数据类型可能支持的不是很完整（类型匹配）,问题看看能不能自己修复一下，不行的话可以私聊我吧