自动化同步多服务器数据库表结构
当项目每次进行版本升级的时候，如果在这次迭代中涉及表结构变更，需要将不同的生产环境下，都需要同步表结构的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文件
insert into hd_version(id,version, remark, created) values (uuid(),#{version}, #{remark}, #{created})
4、 编写实现类
注意，这里是将整段逻辑放在ApplicationRunner接口下执行，即当Spring容器加载完之后，会立即执行该方法。
@Order(1)
@Component
@Slf4j
public class HdSchemaExecutor implements ApplicationRunner {
@Autowired
HdCommonDao hdCommonDao;
// 数据库脚本文件列表
private static final String PREFIX = "--v";
@Override
@Transactional
public 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 versionList = new ArrayList<>();
String[] lines = sqlScript.split("
");
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("
");
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:dockerdatamysql8.0configmy.cnf:/etc/mysql/my.cnf
-v D:dockerdatamysql8.0data:/var/lib/mysql
-v D:dockerdatamysql8.0logs:/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 查看容器最后一百行日志
