对于开发者,代码层级,有git帮你管理版本。
但是运维时,常常会遇到有些地方忘记执行某些SQL导致程序无法使用的问题。SQL的版本管理就成为运维时候的一个痛点。
Liquibase就是为解决这件事情而生的。
文章目录
比较方便的是,Springboot对于Liquibase进行了整合,这篇文章先从最简单的一个示例说起。这篇文章以实战为主,概念性知识最多稍提一嘴,主要留到后面章节去介绍。
1.引入相关依赖
liquibase的核心,主要引入的是
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.8.5</version>
</dependency>
我把完整的pom.xml贴一下:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.powersi</groupId>
<artifactId>LiquibaseTest</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>LiquibaseTest Maven Webapp</name>
<url>http://www.example.com</url>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.4.RELEASE</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<!-- liquibase 依赖-->
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.8.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<exclusions>
<exclusion>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId> org.springframework.boot </groupId >
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
</dependencies>
</project>
2.配置
(1)application.yml配置
spring:
liquibase:
change-log: classpath:liquibase/master.xml
user: pcloud
password: pcloud
enabled: true
drop-first: true
url: jdbc:mysql://192.168.133.15:3306/webchat
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.133.15:3306/webchat
username: pcloud
password: pcloud
(2)master.xml
<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
<include file="classpath:liquibase/change_log/2020-02-06-init-schema.xml" relativeToChangelogFile="false"/>
</databaseChangeLog>
(3)2020-02-06-init-schema.xml
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
<property name="autoIncrement" value="true" dbms="mysql"/>
<changeSet id="init-schema" author="Young" >
<comment>init schema</comment>
<createTable tableName="user">
<column name="id" type="bigint" autoIncrement="${autoIncrement}">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="nick_name" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="email" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="register_time" type="timestamp" defaultValueComputed="CURRENT_TIMESTAMP">
<constraints nullable="false"/>
</column>
</createTable>
<modifySql dbms="mysql">
<append value="ENGINE=INNODB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci"/>
</modifySql>
</changeSet>
</databaseChangeLog>
3.验证执行
这里基本不涉及任何代码的书写,springboot的约定大于配置自动帮我们构建好了一个可以运行的环境,只需要在写一个main方法,将springboot应用运行起来即可。
附上现在我创建的工程目录结构:
第一探 初体验,xml方式初始化数据库表
(1)程序运行前
首先检查数据库:
可见数据库中并无任何表。
(2)程序执行后
运行main方法后,logback在控制台输出了如下内容:
2020-02-06 18:38:25.033 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:25.049 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : CREATE TABLE webchat.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` BIT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
2020-02-06 18:38:25.066 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:25.072 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : DELETE FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:25.074 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : INSERT INTO webchat.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)
2020-02-06 18:38:25.078 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT `LOCKED` FROM webchat.DATABASECHANGELOGLOCK WHERE ID=1
2020-02-06 18:38:25.086 INFO 11356 --- [ main] l.lockservice.StandardLockService : Successfully acquired change log lock
2020-02-06 18:38:25.086 INFO 11356 --- [ main] liquibase.command.core.DropAllCommand : Dropping Database Objects in schema: webchat.webchat
2020-02-06 18:38:25.094 INFO 11356 --- [ main] l.c.StandardChangeLogHistoryService : Creating database history table with name: webchat.DATABASECHANGELOG
2020-02-06 18:38:25.095 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : CREATE TABLE webchat.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, `DESCRIPTION` VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL)
2020-02-06 18:38:25.107 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:25.196 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT @@FOREIGN_KEY_CHECKS
2020-02-06 18:38:25.197 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SET FOREIGN_KEY_CHECKS=0
2020-02-06 18:38:25.198 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SET FOREIGN_KEY_CHECKS=1
2020-02-06 18:38:25.199 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM `webchat`.`DATABASECHANGELOG`
2020-02-06 18:38:26.549 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : DROP TABLE `webchat`.`databasechangelog`
2020-02-06 18:38:26.560 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM `webchat`.`DATABASECHANGELOGLOCK`
2020-02-06 18:38:26.588 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : DROP TABLE `webchat`.`databasechangeloglock`
2020-02-06 18:38:26.593 INFO 11356 --- [ main] liquibase.database.core.MySQLDatabase : Successfully deleted all supported object types in schema webchat.webchat.
2020-02-06 18:38:26.595 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.596 INFO 11356 --- [ main] l.lockservice.StandardLockService : Successfully released change log lock
2020-02-06 18:38:26.597 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.600 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.602 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : CREATE TABLE webchat.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` BIT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
2020-02-06 18:38:26.613 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.615 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : DELETE FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.617 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : INSERT INTO webchat.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0)
2020-02-06 18:38:26.620 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT `LOCKED` FROM webchat.DATABASECHANGELOGLOCK WHERE ID=1
2020-02-06 18:38:26.623 INFO 11356 --- [ main] l.lockservice.StandardLockService : Successfully acquired change log lock
2020-02-06 18:38:26.795 INFO 11356 --- [ main] l.c.StandardChangeLogHistoryService : Creating database history table with name: webchat.DATABASECHANGELOG
2020-02-06 18:38:26.797 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : CREATE TABLE webchat.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35) NULL, `DESCRIPTION` VARCHAR(255) NULL, COMMENTS VARCHAR(255) NULL, TAG VARCHAR(255) NULL, LIQUIBASE VARCHAR(20) NULL, CONTEXTS VARCHAR(255) NULL, LABELS VARCHAR(255) NULL, DEPLOYMENT_ID VARCHAR(10) NULL)
2020-02-06 18:38:26.809 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOG
2020-02-06 18:38:26.812 INFO 11356 --- [ main] l.c.StandardChangeLogHistoryService : Reading from webchat.DATABASECHANGELOG
2020-02-06 18:38:26.813 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT * FROM webchat.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-02-06 18:38:26.815 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 18:38:26.831 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : CREATE TABLE webchat.user (id BIGINT AUTO_INCREMENT NOT NULL, nick_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, register_time timestamp DEFAULT NOW() NOT NULL, CONSTRAINT PK_USER PRIMARY KEY (id))ENGINE=INNODB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci
2020-02-06 18:38:26.841 INFO 11356 --- [ main] liquibase.changelog.ChangeSet : Table user created
2020-02-06 18:38:26.843 INFO 11356 --- [ main] liquibase.changelog.ChangeSet : ChangeSet classpath:liquibase/change_log/2020-02-06-init-schema.xml::init-schema::Young ran successfully in 17ms
2020-02-06 18:38:26.846 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT MAX(ORDEREXECUTED) FROM webchat.DATABASECHANGELOG
2020-02-06 18:38:26.848 INFO 11356 --- [ main] liquibase.executor.jvm.JdbcExecutor : INSERT INTO webchat.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('init-schema', 'Young', 'classpath:liquibase/change_log/2020-02-06-init-schema.xml', NOW(), 1, '8:90aeeb431c3781579d93afd4280e6066', 'createTable tableName=user', 'init schema', 'EXECUTED', NULL, NULL, '3.8.5', '0985506816')
2020-02-06 18:38:26.859 INFO 11356 --- [ main] l.lockservice.StandardLockService : Successfully released change log lock
2020-02-06 18:38:26.864 INFO 11356 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-02-06 18:38:26.884 INFO 11356 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2020-02-06 18:38:26.994 INFO 11356 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2020-02-06 18:38:26.997 INFO 11356 --- [ main] com.powersi.MainClass : Started MainClass in 8.028 seconds (JVM running for 10.31)
上面的日志打印的比较直观,从以下这行看出来它似乎成功帮我们建好了表:
CREATE TABLE webchat.user
我们连接到这个库,验证一下是不是真的建好了数据表:
这里可以看到,除了我们配置的user
表以外,还帮我们创建了两个表:databasechangelog
表和databasechangeloglock
表。
databasechangelog
表结构为:
CREATE TABLE `databasechangelog` (
`ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`AUTHOR` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`FILENAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`DATEEXECUTED` datetime(0) NOT NULL,
`ORDEREXECUTED` int(11) NOT NULL,
`EXECTYPE` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`MD5SUM` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`DESCRIPTION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`COMMENTS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`TAG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`LIQUIBASE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CONTEXTS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`LABELS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`DEPLOYMENT_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
databasechangeloglock
表结构为:
CREATE TABLE `databasechangeloglock` (
`ID` int(11) NOT NULL,
`LOCKED` bit(1) NOT NULL,
`LOCKGRANTED` datetime(0) NULL DEFAULT NULL,
`LOCKEDBY` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
databasechangelog中有这么一条记录:
databasechangeloglock表中有这么一条数据:
第二探 利用SQL脚本维护数据
网上说liquibase版本从2.0开始,增加了SQL的支持,接下来写个简单的脚本进行测试(这里为什么是这个写法先不纠结,后面再去深究):
2020-02-06-add-user.sql
--liquibase formatted sql
--changeset luyang:1
insert into user(nick_name,email,register_time) values('young','112626290@163.com',now());
同样,要将这个文件include到master.xml中:
<include file="classpath:liquibase/change_log/2020-02-06-add-user.sql"/>
运行后,日志打印如下图:
看起来执行成功了,到数据库端验证:
另外值得一提的是,databasechangelog表中,有以下两条记录:
第三探 drop-first探究
之前在配置application.yml的时候,把这个参数drop-first: true
设置为了true,根据日志显示,每次程序运行时它都会把相关的表全部删掉。而在实际的业务上面,这个操作完全不可取。改为false,再去启动,会有什么情况发生?
记录并没有发生什么变化。
第四探 版本问题
猜想,如果在保持drop-first: false
前提下,我对数据库表进行了一些修改,会发生什么?
2020-02-06-update-user.sql
--liquibase formatted sql
--changeset luyang:2
update user set nick_name = 'LuYang' where id = 1;
同时记得在master.xml中要include进去:
<include file="classpath:liquibase/change_log/2020-02-06-update-user.sql"/>
再运行程序,可以看到日志输出:
2020-02-06 19:00:28.099 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 19:00:28.112 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOGLOCK
2020-02-06 19:00:28.114 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT `LOCKED` FROM webchat.DATABASECHANGELOGLOCK WHERE ID=1
2020-02-06 19:00:28.125 INFO 13068 --- [ main] l.lockservice.StandardLockService : Successfully acquired change log lock
2020-02-06 19:00:29.735 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT MD5SUM FROM webchat.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL LIMIT 1
2020-02-06 19:00:29.736 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM webchat.DATABASECHANGELOG
2020-02-06 19:00:29.737 INFO 13068 --- [ main] l.c.StandardChangeLogHistoryService : Reading from webchat.DATABASECHANGELOG
2020-02-06 19:00:29.738 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT * FROM webchat.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-02-06 19:00:29.749 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : update user set nick_name = 'LuYang' where id = 1
2020-02-06 19:00:29.751 INFO 13068 --- [ main] liquibase.changelog.ChangeSet : Custom SQL executed
2020-02-06 19:00:29.752 INFO 13068 --- [ main] liquibase.changelog.ChangeSet : ChangeSet classpath:liquibase/change_log/2020-02-06-update-user.sql::2::luyang ran successfully in 5ms
2020-02-06 19:00:29.753 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT MAX(ORDEREXECUTED) FROM webchat.DATABASECHANGELOG
2020-02-06 19:00:29.755 INFO 13068 --- [ main] liquibase.executor.jvm.JdbcExecutor : INSERT INTO webchat.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'luyang', 'classpath:liquibase/change_log/2020-02-06-update-user.sql', NOW(), 3, '8:5882f164036521bc55d78eaf7eb475d4', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.5', '0986829741')
2020-02-06 19:00:29.761 INFO 13068 --- [ main] l.lockservice.StandardLockService : Successfully released change log lock
2020-02-06 19:00:29.762 INFO 13068 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-02-06 19:00:29.774 INFO 13068 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2020-02-06 19:00:29.853 INFO 13068 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2020-02-06 19:00:29.856 INFO 13068 --- [ main] com.powersi.MainClass : Started MainClass in 9.701 seconds (JVM running for 12.267)
user表的记录也被成功修改:
另外,日志记录多了一行:
第五探 错误的版本跳跃?
这里版本直接指定为4,看会不会发生把这条记录删掉的情况:
记录被删掉了。
第六探 那如果写出了重复的版本号呢?
日志上看依旧被执行成功:
数据库也有相应的记录: