Using Liquibase with Percona XtraDB Cluster

Percona XtraDB Cluster is a database clustering solution for MySQL. It integrates Percona Server for MySQL and Percona XtraBackup with the Galera library to enable synchronous multi-source replication. For more information, see the Percona XtraDB Cluster documentation page.

Supported Versions

  • 5.7 – officially certified and tested with Test Harness and Liquibase
  • 8.0 – officially certified and tested with Test Harness and Liquibase

Prerequisites

Before using Liquibase with your database, ensure you have:

    Tip: Liquibase provides the init project command, which creates a project folder with configuration files – changelog with changesets and the Liquibase properties file, liquibase.properties. To create these files automatically, run liquibase init project and choose [C] to customize parameters for Percona XtraDB Cluster.

Driver Information

To use Liquibase and Percona XtraDB Cluster, you need to have the JDBC file:

  1. Ensure you have downloaded the MySQL JDBC Driver to connect to Percona XtraDB Cluster.
  2. Place the mysql-connector-java-version.jar file in the liquibase/lib directory.

Note: If you place the mysql-connector-java-version.jar file in a different directory, specify the path in the Liquibase properties file, as follows: classpath:../path_to_drivers/mysql-connector-java-version.jar. For more information, see Specifying Properties in a Connection Profile.

If you use Maven, you also need to download the MySQL driver jar file and put the driver in a location that your Maven build can access. Configure the Maven pom.xml file to use the local copy of the driver jar file. For example:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

Testing Your Connection

For Liquibase and Percona XtraDB Cluster to work, you need to:

  1. Ensure your Percona XtraDB Cluster is configured. You can check the status by running the wsrep_cluster_status command or verifying replication:
  1. Specify the database URL in the Liquibase properties file, as follows:
  2. url: jdbc:mysql://<servername>:<port>/<dbname>

    Tip: To apply a Liquibase Pro key to your project, add the following property to the Liquibase properties file: liquibaseProLicenseKey: <paste code here>

  3. Run the following SQL script to create the Liquibase metatables:
  4. --  Create Database Lock Table
    DROP TABLE IF EXISTS `DATABASECHANGELOGLOCK`;
    CREATE TABLE `DATABASECHANGELOGLOCK` (
    					ID INT NOT NULL,
    					`LOCKED` BIT(1) NOT NULL,
    					LOCKGRANTED datetime NULL,
    					LOCKEDBY VARCHAR(255) NULL,
    					PRIMARY KEY (ID)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci;
    
    --  Create Database Change Log Table
    DROP TABLE IF EXISTS `DATABASECHANGELOG`;
    CREATE TABLE `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) DEFAULT NULL,
    					`DESCRIPTION` varchar(255) DEFAULT NULL,
    					`COMMENTS` varchar(255) DEFAULT NULL,
    					`TAG` varchar(255) DEFAULT NULL,
    					`LIQUIBASE` varchar(20) DEFAULT NULL,
    					`CONTEXTS` varchar(255) DEFAULT NULL,
    					`LABELS` varchar(255) DEFAULT NULL,
    					`DEPLOYMENT_ID` varchar(10) DEFAULT NULL,
    					PRIMARY KEY (ID)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci;
  5. Create a text file called changelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset.
  1. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful. You can pass arguments in the CLI or keep them in the Liquibase properties file.
  2. liquibase --username=test --password=test --changelog-file=<changelog.xml> status
  3. Run your first update with the update command, which makes changes to your database. You can also run the update-sql command to inspect the SQL before running the update command.
  4. liquibase --changelog-file=<changelog.xml> update-sql
    liquibase --changelog-file=<changelog.xml> update

From a database UI tool, ensure that your database contains the table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Related Links