The process of R & D often involves database changes, the repair of table structure and the modification of data. In order to ensure that each environment can be changed correctly, we may need to maintain a database upgrade document to save these records. The environment that needs to be upgraded will be upgraded according to the document.
This manual maintenance has several disadvantages:
- There is no guarantee that every environment will perform as required
- If there is a problem, there may not be a relative rollback statement
- Can't automate
In order to solve these problems, we have carried out some research. The main research objects are Liquibase and Flyway. We hope to achieve the following goals through the database version management tool:
- Database upgrade
- Database rollback
- Version mark
During the investigation, it is found that the Flyway database rollback function is a value-added function and the implementation logic is "intelligent" degradation through our upgrade script, which is not in line with our current use scenario. For the introduction of Flyway, please see another introduction in my earlier part: https://segmentfault.com/a/11...
Liquibase
Liquibase helps teams track, version, and deploy database architecture and logical changes
install
Check JRE
$ java -version java version "1.8.0_231" Java(TM) SE Runtime Environment (build 1.8.0_231-b11) Java HotSpot(TM) 64-Bit Server VM (build 25.231-b11, mixed mode)
If Java is not installed, please install it yourself
Installing Liquibase
download Liquibase versionාbin.tar.gz file，
Unpack the package and add the directory to the environment variable
$ export PATH="/opt/liquibase-3.8.2:$PATH"
This command will not take effect if you restart the command line. You need to set the lead to. bashrc or. zshrc if you want to keep it available
Verify the installation by running the help command
$ liquibase --help 17:12:10.389 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties Starting Liquibase at Wednesday, 04 December 2019 17:12:10 CST (version 3.8.2 #26 built at Tue Nov 26 04:53:39 UTC 2019) Usage: java -jar liquibase.jar [options] [command] Standard Commands: ...
configuration file
Here is the configuration file with mysql as an example
$ cat liquibase.properties driver: com.mysql.cj.jdbc.Driver classpath: ./mysql-connector-java-8.0.18.jar url: jdbc:mysql://127.0.0.1/test username: root password: 123456 changeLogFile: myChangeLog.xml
Database upgrade
Create the myChangeLog.xml file, which is used to record the upgrade information and initialization contents
$ cat myChangeLog.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.8.xsd"> </databaseChangeLog>
Liquibase supports the creation of databases through SQL descriptions
$ cat myChangeLog.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.8.xsd"> <changeSet id="1.0" author="bohan"> <sql> CREATE TABLE `deparment` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; </sql> </changeSet> </databaseChangeLog> $ liquibase update Liquibase Community 3.8.2 by Datical Liquibase: Update has been successful.
Upgrade by executing liquibase update. The upgraded database is as follows. The database has been created for us. At the same time, Liquibase has generated two tables to manage database upgrade records
$ mysql -h 127.0.0.1 -uroot -p123456 test -e "show tables;" mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------------+ | Tables_in_test | +-----------------------+ | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | | deparment | +-----------------------+
Continue with upgrade
$ cat myChangeLog.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.8.xsd"> <changeSet id="1.0" author="bohan"> <sql> CREATE TABLE `deparment` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; </sql> </changeSet> <changeSet id="1.1" author="bohan"> <sql> insert into deparment values(1, "test"); </sql> </changeSet> </databaseChangeLog> $ liquibase update Liquibase Community 3.8.2 by Datical Liquibase: Update has been successful. $ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;" Enter password: +----+------+ | id | name | +----+------+ | 1 | test | +----+------+
Data added as expected
Through SQL file
Database changes can also be referenced in the form of sql files to avoid the too large file of myChangeLog.xml
<changeSet id="1.1" author="bohan"> <sqlFile path="./update_deparment_name.sql"></sqlFile> </changeSet>
Database rollback
liquibase --help Usage: java -jar liquibase.jar [options] [command] Standard Commands: rollbackCount <value> Rolls back the last <value> change sets applied to the database
Let's perform rollback count for rollback
$ liquibase rollbackCount 1 Liquibase Community 3.8.2 by Datical Rolling Back Changeset:myChangeLog.xml::1.0::bohan Unexpected error running Liquibase: No inverse to liquibase.change.core.RawSQLChange created For more information, please use the --logLevel flag
Prompt that SQL is not rolled back, modify our myChangeLog.xml
$ cat myChangeLog.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.8.xsd"> <changeSet id="1.0" author="bohan"> <sql> CREATE TABLE `deparment` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; </sql> <rollback> DROP TABLE deparment; </rollback> </changeSet> <changeSet id="1.1" author="bohan"> <sql> insert into deparment values(1, "test"); </sql> <rollback> DELETE FROM deparment WHERE id = 1; </rollback> </changeSet> </databaseChangeLog>
Perform a rollback and find that there are no new records
liquibase rollbackCount 1 Liquibase Community 3.8.2 by Datical Rolling Back Changeset:myChangeLog.xml::1.1::bohan Liquibase: Rollback has been successful. $ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;" Enter password:
Again, the database is deleted as expected
$ liquibase rollbackCount 1 Liquibase Community 3.8.2 by Datical Rolling Back Changeset:myChangeLog.xml::1.0::bohan Liquibase: Rollback has been successful. $ mysql -h 127.0.0.1 -uroot -p test -e "show tables;" Enter password: +-----------------------+ | Tables_in_test | +-----------------------+ | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | +-----------------------+
Version mark
Liquibase provides perfect tagging function. After just rolling back to the last operation, we have only implemented the change with ID 1.0 at present
mysql> select * from DATABASECHANGELOG; +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | 1.0 | bohan | myChangeLog.xml | 2019-12-05 03:15:18 | 1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql | | NULL | 3.8.2 | NULL | NULL | 5515718387 | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ 1 row in set (0.00 sec)
In actual development, when we upgrade the version, we usually need to execute multiple changes at the same time. If there is a problem with variables, it is more difficult to roll back by quantity. We need to label our changes. The following commands may be used:
liquibase --help 11:21:31.994 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties Starting Liquibase at Thursday, 05 December 2019 11:21:31 CST (version 3.8.2 #26 built at Tue Nov 26 04:53:39 UTC 2019) Usage: java -jar liquibase.jar [options] [command] Standard Commands: rollback <tag> Rolls back the database to the the state is was Maintenance Commands tag <tag string> 'Tags' the current database state for future rollback tagExists <tag string> Checks whether the given tag is already existing
For the current database, we use liquibase tag to label
$ liquibase tag v1.0 Liquibase Community 3.8.2 by Datical Successfully tagged 'root@172.17.0.1@jdbc:mysql://127.0.0.1/test' Liquibase command 'tag' was executed successfully.
View record found that record TAG with ID 1.0 has been set to v1.0, which is in line with our expectation
mysql> select * from DATABASECHANGELOG; +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | 1.0 | bohan | myChangeLog.xml | 2019-12-05 03:15:18 | 1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql | | v1.0 | 3.8.2 | NULL | NULL | 5515718387 | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ 1 row in set (0.00 sec)
After the update, if you need to roll back, just use liquibase rollback v1.0
$ liquibase update Liquibase Community 3.8.2 by Datical Liquibase: Update has been successful. mysql> select * from DATABASECHANGELOG; +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | 1.0 | bohan | myChangeLog.xml | 2019-12-05 03:15:18 | 1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql | | v1.0 | 3.8.2 | NULL | NULL | 5515718387 | | 1.1 | bohan | myChangeLog.xml | 2019-12-05 03:28:06 | 2 | EXECUTED | 8:695a5ec0b2b3ddc4a9beeeca530adebc | sql | | NULL | 3.8.2 | NULL | NULL | 5516486105 | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ 2 rows in set (0.00 sec) $ liquibase rollback v1.0 Liquibase Community 3.8.2 by Datical Rolling Back Changeset:myChangeLog.xml::1.1::bohan Liquibase: Rollback has been successful. mysql> select * from DATABASECHANGELOG; +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ | 1.0 | bohan | myChangeLog.xml | 2019-12-05 03:15:18 | 1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql | | v1.0 | 3.8.2 | NULL | NULL | 5515718387 | +-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+ 1 row in set (0.00 sec)