Manage schema versions of OceanBase Database by using Liquibase

Liquibase is an open-source version control tool that supports version tracking, updates, and rollbacks of database schemas.

Prerequisites

• You have downloaded and installed Liquibase.
• You have downloaded the MySQL Java Database Connectivity (JDBC) driver from the official website to obtain the mysql-connector-j-x.x.x.jar file.

• You have installed OceanBase Database and created a MySQL tenant.

Version compatibility

We recommend that you use OceanBase Database V4.2.3 or a later version for better experience.

Procedure

Step 1: Obtain the OceanBase Database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string. The following is a connection string example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

The parameters are described as follows:

• $host: the IP address for connecting to OceanBase Database. It is the IP address of OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• user_name: the username of the account in the format of username@tenant name#cluster name or username@SERVICE:service name. When the value is in the username@tenant name#cluster name format, the default tenant is 'sys' and the default administrator is 'root'. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase Database tenant by using OBClient.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Use Liquibase

1. Place the JDBC driver in the lib directory of Liquibase.

   On your virtual machine (VM), find the Liquibase folder obtained through decompression and go to the lib directory. Copy the downloaded mysql-connector-j-x.x.x.jar file to this directory.

   You can also run the following command to copy the mysql-connector-j-x.x.x.jar file to this directory:

   mv path/to/mysql-connector-j-x.x.x.jar path/to/liquibase/lib/
   

2. Create a configuration file.

   In the root directory of Liquibase, create a configuration file named liquibase.properties by using any text editor such as nano, vim, or gedit, and then write the following content to the file:

   url: jdbc:mysql://xxx.xxx.xxx.xxx:2881/test
   username: your_username
   password: your_password
   classpath: lib/mysql-connector-j-8.3.0.jar
   

   You need to replace xxx.xxx.xxx.xxx with the host address of your OceanBase database, and enter the port number, username, and password for logging in to your database. For more information about how to obtain the address, port number, username, and password, see Step 1.

3. Generate a snapshot of the current schema status of your database.

   Switch to the root directory of Liquibase and run the following command to generate a snapshot of the current database schema:

   ./liquibase --changeLogFile=mydatabase_changelog.xml generateChangeLog
   

   The parameters may vary based on the Liquibase version. For more information, see Liquibase documentation.

4. Create a table.

   In the root directory of Liquibase, create a file named dbchangelog.xml and write the following content to the file:

   <?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"  
       xmlns:pro="http://www.liquibase.org/xml/ns/pro"  
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd
           http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.5.xsd">
       <changeSet author="BobR" id="myIDNumber123">
           <createTable tableName="actor">
               <column autoIncrement="true" name="id" type="INTEGER">
                   <constraints nullable="false" primaryKey="true" primaryKeyName="actor_pkey"/>
               </column>
               <column name="firstname" type="VARCHAR(255)"/>
               <column name="lastname" type="VARCHAR(255)"/>
               <column name="twitter" type="VARCHAR(15)"/>
           </createTable>
       </changeSet>
   </databaseChangeLog>
   

5. Perform a change.

   Run the following command to create a table named actor:

   ./liquibase update --changeLogFile=dbchangelog.xml
   

6. Perform a rollback.

   Roll back the database to the previous version.

   ./liquibase rollbackCount 1 --changelog-file=dbchangelog.xml
   

More information

For any problems encountered when you use Liquibase, see Liquibase documentation.