sqoop导出数据到mysql_一次mysql数据库跨版本5.7至5.5导出导入记录

背景：

本地数据库版本5.7.12-log，windows 10

远程服务器版本5.5 ，centos 7.6

程序：wgcloud运维监控系统

直接使用Navicat premium 工具中的数据同步功能出错，所以分开导出导入处理。

本地使用Navicat premium版本导出

/* Navicat Premium Data Transfer Source Server         : localhost Source Server Type    : MySQL Source Server Version : 50722 Source Host           : localhost:3306 Source Schema         : wgcloudos Target Server Type    : MySQL Target Server Version : 50722 File Encoding         : 65001 Date: 09/12/2020 11:03:30*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for app_info-- ----------------------------DROP TABLE IF EXISTS `app_info`;CREATE TABLE `app_info`  (  `ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  `HOST_NAME` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `APP_PID` char(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `CREATE_TIME` timestamp(0) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),  `APP_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `CPU_PER` double(8, 2) DEFAULT NULL,  `MEM_PER` double(10, 2) DEFAULT NULL,  `APP_TYPE` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `STATE` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `ACTIVE` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  PRIMARY KEY (`ID`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for app_state-- ----------------------------DROP TABLE IF EXISTS `app_state`;CREATE TABLE `app_state`  (  `ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  `APP_INFO_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,  `CPU_PER` double(8, 2) DEFAULT NULL,  `MEM_PER` double(10, 2) DEFAULT NULL,  `CREATE_TIME` timestamp(0) DEFAULT NULL,  PRIMARY KEY (`ID`) USING BTREE,  INDEX `APP_STAT_INDEX`(`APP_INFO_ID`, `CREATE_TIME`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- -----------------------------------------------------------其他表类似省略

然后再次使用命令或者sql导入工具运行后提示以下错误：

ERROR 1067 (42000): Invalid default value for 'CREATE_TIME'

注意原因是因为timestamp函数的长度在5.7以上版本才支持，比如timestamp(0)，timestamp(3)等。5.5不支持，修改为以下样式。

CREATE TABLE `APP_INFO` (  `ID` char(32) NOT NULL,  `HOST_NAME` char(50) DEFAULT NULL,  `APP_PID` char(200) DEFAULT NULL,  `CREATE_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  `APP_NAME` varchar(50) DEFAULT NULL,  `CPU_PER` double(8,2) DEFAULT NULL,  `MEM_PER` double(10,2) DEFAULT NULL,  `APP_TYPE` char(1) DEFAULT NULL,  `STATE` char(1) DEFAULT NULL,  `ACTIVE` char(1) DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

wgcloud 3.2.8之前版本的数据库表名是小写字母，以后是大写字母，在windows上没有区别，但 Linux系统中如果配置my.cnf文件lower_case_table_names字段的话就会产生错误无法识别表，会重新创建。

后续会增加wgcloud运维工具的使用介绍，欢迎订阅关注。