自动化同步多服务器数据库表结构

当项目每次进行版本升级的时候，如果在这次迭代中涉及表结构变更，需要将不同的生产环境下，都需要同步表结构的DDL语句，比较麻烦，而且还有可能忘记同步脚本，导致生产环境报错....

该方案采用SpringBoot+Mybatis/MybatisPlus框架，完成在项目启动时，自动化执行sql脚本，并且同时支持版本号【如果当前版本号高于该sql文件，则不执行】。

1、先创建一张表，专门用来记录已经同步过的sql脚本文件名、对应的版本号。

CREATE TABLE `hd_version` (`id` varchar(64) NOT NULL,`version` varchar(64) DEFAULT NULL COMMENT '版本号',`created` datetime DEFAULT NULL COMMENT '创建时间',`remark` varchar(500) DEFAULT NULL COMMENT '备注',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据版本';

import java.util.Date;
import lombok.Data;
@Data
public class HdVersionEntity {/*** 主键id*/private String id;/*** 版本号（一般是文件名去掉文件后缀）*/private String version;/*** 文件名*/private String remark;/*** 创建时间*/private Date created;
}

import lombok.Data;@Data
public class SchemaData {/*** 版本号*/public String version;/*** 文件名*/public String fileName;public SchemaData(String version, String fileName) {this.version = version;this.fileName = fileName;}
}

 2、接着编写dao层

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;@Mapper
public interface HdCommonDao  {/*** 查询表中是否存在当前版本号* @param version* @return*/int selectVersion(@Param("version") String version);/*** 插入版本* @param entity* @return*/int insertVersion(HdVersionEntity entity);/*** 执行sql，可以是DML、DDL* @param sql*/@Update("${sql}")void updateSql(@Param("sql") String sql);
}

3、以及对应的Mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--这里文件空间命名改成自己路径下的-->
<mapper namespace="com.xxx.DatabaseAutoFill.HdCommonDao"><select id="selectVersion" resultType="int">selecT count(1) from hd_versionwhere version = #{version}</select><select id="selectTableExist" resultType="int">select count(*) count  from information_schema.TABLES where TABLE_NAME = #{tableName} and  table_schema = (select database())</select><insert id="insertVersion">insert into hd_version(id,version, remark, created) values (uuid(),#{version}, #{remark}, #{created})</insert></mapper>

4、 编写实现类

注意，这里是将整段逻辑放在ApplicationRunner接口下执行，即当Spring容器加载完之后，会立即执行该方法。

@Order(1)
@Component
@Slf4j
public class HdSchemaExecutor implements ApplicationRunner {@AutowiredHdCommonDao hdCommonDao;// 数据库脚本文件列表private static final String PREFIX = "--v";@Override@Transactionalpublic void run(ApplicationArguments args) throws IOException {String basePath = "/dbVersion/MySQL.sql";InputStream inputStream = this.getClass().getResourceAsStream(basePath);String sqlScript = IoUtil.readUtf8(inputStream);assert inputStream != null;inputStream.close();/*** 一次至多只会执行一个版本，其实我们可以拿到所有的版本并执行最后一个版本即可*/List<String> versionList = new ArrayList<>();String[] lines = sqlScript.split("\n");for (String line : lines) {if(line.toLowerCase().contains(PREFIX)){versionList.add(line);}}// 得到版本号整串String latestVersion = versionList.get(versionList.size()-1);// 写入数据库的版本号前缀String version = latestVersion.substring(latestVersion.lastIndexOf("-")+1).trim().toLowerCase();int index = sqlScript.lastIndexOf(latestVersion); // 查找s2在s1中的起始位置String result = "";if (index != -1) {// 截取s2在s1中结束位置之后的部分result = sqlScript.substring(index + latestVersion.length());} else {log.info("current version exception:{}",version);LogUtil.info(version, "current version exception");}//String[] resultList = result.split("\n");String[] resultList = result.split(";");int cnt = hdCommonDao.selectVersion(version);boolean successInsert = false;// 说明不需要写入库if(cnt ==1 )return;for (String line : resultList) {if(!line.toLowerCase().contains("drop") && !line.toLowerCase().contains("delete") && line.length() > 25 && !line.contains("--")) {//开始执行插入操作try {hdCommonDao.updateSql(line.trim());successInsert = true;log.info("version:{},start sql script:{}",version,line.trim());LogUtil.info("version, sql script:",version,line.trim());} catch (Exception e) {log.info("version:{},sql执行异常:{}",version,line.trim());LogUtil.info("sql执行异常",line.trim());}}}if(successInsert){HdVersionEntity entity = new HdVersionEntity();entity.setVersion(version);entity.setCreated(new Date());hdCommonDao.insertVersion(entity);}log.info("auto deploying sql finished...");}
}

这里主要干三件事：

读取指定路径下的文件夹中的所有文件
根据这些文件的文件名去表里查，是否插入过，没有说明需要被插入，即需要执行的sql脚本
执行sql脚本

我这里的路径是resources下的相对路径，因为我这个代码是要打包放到线上环境的，用绝对路径可能会报（FILE NOT FOUND ERROR）FNFE。 

PS

以上方法对于Spring容器加载时，没有强依赖的表，是可以通用的 （可能有点拗口）。

即，如果Spring容器启动时，如果需要依赖某张表，否则启动失败的话怎么办，还能用我们上述方法吗？

理论上是不行的，我这里将容器启动时，必须强依赖的表（Quartz框架）删去，启动时报错。
那对应这种情况，该怎么解决呢？

 其实这种框架，都会提供注解，如：

表明，在项目启动的时候，会自动完成jdbc的初始化，即如果你没有表，会先给你执行表的创建，因此不需要我们去考虑。

spring.quartz.jdbc.initialize-schema=always

Quartz也起来了。 

写在最后

由于这个工程是临时突加的，我也不好随便就测试环境的库来删删改改，因此我在本地windows上用docker部署了mysql，来测试的。以下是在windows上的docker部署mysql步骤：

docker pull mysql:8.0

在c盘用户目录下，创建conf、data、logs三个文件夹

 在conf目录下，创建my.cnf文件，里面编写如下内容。

[mysql]
#设置mysql客户端默认字符集
default-character-set=UTF8MB4
[mysqld]
#设置3306端口
port=3306
#允许最大连接数
max_connections=200
#允许连接失败的次数
max_connect_errors=10
#默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=UTF8MB4
#开启查询缓存
explicit_defaults_for_timestamp=true
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#等待超时时间秒
wait_timeout=60
#交互式连接超时时间秒
interactive-timeout=600
# 对数据库表大小写不敏感设置，默认设置为小写，比较也全部设置为小写在比较
lower-case-table-names=1
# 设置默认时区
default-time_zone='+8:00'

启动容器，注意在windows下 需要把每行后面的 `\`删去，否在windows下会启动失败

 docker run --name mysql8.0 \
-v D:\docker\data\mysql8.0\config\my.cnf:/etc/mysql/my.cnf \
-v D:\docker\data\mysql8.0\data:/var/lib/mysql \
-v D:\docker\data\mysql8.0\logs:/logs -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \

-e TZ=Asia/Shanghai \
-d mysql:8.0 \
--lower-case-table-names=1

这样，理论上就能启动成功了。

分享几个常用的命令：
docker exec -it 容器名称/容器id  bash  #进入容器

docker logs 容器名称/容器id -f -n=100 查看容器最后一百行日志