Devops Monk

You’ve read about changesets, tracking tables, and changelog formats. Now you’re going to run your first real migration against a live MySQL database. By the end of this article you will have connected Liquibase to MySQL, written a changelog that creates the users table for our e-commerce app, previewed the SQL it generates, applied it, and verified the result in the database.

This is the article where things become real.

Prerequisites

Before starting, make sure you have:

• MySQL 8.x running locally (or accessible remotely)
• Java 17+ installed (java -version should show 17 or higher)
• Liquibase CLI 4.x or 5.x installed
• A MySQL user with CREATE, ALTER, DROP, and INSERT privileges on a target database

If you haven’t installed Liquibase yet:

# macOS via Homebrew
brew install liquibase

# Verify
liquibase --version

On Linux or Windows, download the binary from liquibase.org and add it to your PATH.

Create the Target Database

Liquibase does not create the database itself — it manages schema objects inside an existing database. Create the database first:

-- Connect to MySQL as root or an admin user
CREATE DATABASE ecommerce
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'lb_user'@'localhost' IDENTIFIED BY 'lb_pass';

GRANT CREATE, ALTER, DROP, INDEX, REFERENCES,
      SELECT, INSERT, UPDATE, DELETE
  ON ecommerce.* TO 'lb_user'@'localhost';

FLUSH PRIVILEGES;

Using utf8mb4 is not optional for MySQL — it is the only character set that correctly stores all Unicode characters including emoji. utf8 in MySQL is a legacy alias for a 3-byte subset that silently corrupts 4-byte characters.

Project Directory Structure

Create the following structure for the project. Articles 6 onward will integrate this into Spring Boot, but for now the Liquibase CLI is enough.

ecommerce-db/
├── liquibase.properties          # Connection config
└── db/
    └── changelog/
        ├── db.changelog-master.yaml   # Master changelog (includes others)
        └── migrations/
            └── 2026/
                └── 05/
                    └── 001-create-users-table.yaml

mkdir -p ecommerce-db/db/changelog/migrations/2026/05
cd ecommerce-db

The date-partitioned directory structure (migrations/YYYY/MM/) becomes important once you have dozens of files. Starting with it now costs nothing.

Configure liquibase.properties

The liquibase.properties file keeps connection details out of every command you type. Create it in the project root (ecommerce-db/liquibase.properties):

# Changelog entry point
changeLogFile=db/changelog/db.changelog-master.yaml

# MySQL connection
url=jdbc:mysql://localhost:3306/ecommerce?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
driver=com.mysql.cj.jdbc.Driver
username=lb_user
password=lb_pass

# Logging (set to INFO during learning, WARNING in CI)
logLevel=INFO

The JDBC URL parameters deserve attention:

Important: Do not commit liquibase.properties with real credentials to version control. Add it to .gitignore and inject credentials via environment variables in CI:

# Override properties file values via environment variables
export LIQUIBASE_COMMAND_URL=jdbc:mysql://prod-host:3306/ecommerce
export LIQUIBASE_COMMAND_USERNAME=lb_user
export LIQUIBASE_COMMAND_PASSWORD=${SECRET_PASSWORD}

Add the MySQL JDBC Driver

Liquibase does not bundle the MySQL JDBC driver. You need to download it and place it in Liquibase’s lib/ directory:

# Find where Liquibase is installed
which liquibase
# e.g., /opt/homebrew/bin/liquibase -> /opt/homebrew/Cellar/liquibase/4.x.x/

# On macOS via Homebrew, the lib directory is:
ls $(brew --prefix liquibase)/lib/

# Download MySQL Connector/J (use the current stable version)
# From https://dev.mysql.com/downloads/connector/j/
# Choose "Platform Independent" -> download the .tar.gz

# Extract and copy the JAR
tar xzf mysql-connector-j-*.tar.gz
cp mysql-connector-j-*/mysql-connector-j-*.jar $(brew --prefix liquibase)/lib/

If you installed Liquibase manually (not via Homebrew), copy the JAR to the lib/ subdirectory of your Liquibase installation directory.

Verify the driver is found:

liquibase validate 2>&1 | head -5
# Should not show "ClassNotFoundException: com.mysql.cj.jdbc.Driver"

Write the Master Changelog

The master changelog is the entry point Liquibase reads. It doesn’t contain changesets — it includes other files. Create db/changelog/db.changelog-master.yaml:

databaseChangeLog:
  - includeAll:
      path: db/changelog/migrations/
      relativeToChangelogFile: false

Using includeAll picks up every changelog file under migrations/ automatically, ordered alphabetically. The date-prefixed directory structure (2026/05/) ensures chronological ordering even as the project grows.

Write Your First Changeset

Create db/changelog/migrations/2026/05/001-create-users-table.yaml:

databaseChangeLog:

  - changeSet:
      id: "001"
      author: abhay
      comment: Create users table for e-commerce app
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: BIGINT UNSIGNED
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false

              - column:
                  name: email
                  type: VARCHAR(255)
                  constraints:
                    nullable: false
                    unique: true

              - column:
                  name: full_name
                  type: VARCHAR(255)
                  constraints:
                    nullable: false

              - column:
                  name: password_hash
                  type: VARCHAR(255)
                  constraints:
                    nullable: false

              - column:
                  name: role
                  type: ENUM('customer', 'admin', 'vendor')
                  defaultValue: customer
                  constraints:
                    nullable: false

              - column:
                  name: status
                  type: ENUM('active', 'inactive', 'banned')
                  defaultValue: active
                  constraints:
                    nullable: false

              - column:
                  name: created_at
                  type: DATETIME
                  defaultValueComputed: CURRENT_TIMESTAMP
                  constraints:
                    nullable: false

              - column:
                  name: updated_at
                  type: DATETIME
                  defaultValueComputed: CURRENT_TIMESTAMP
                  constraints:
                    nullable: false

      rollback:
        - dropTable:
            tableName: users

  - changeSet:
      id: "002"
      author: abhay
      comment: Add index on users.email for login lookup performance
      changes:
        - createIndex:
            indexName: idx_users_email
            tableName: users
            columns:
              - column:
                  name: email
      rollback:
        - dropIndex:
            indexName: idx_users_email
            tableName: users

A few things to notice:

• Changeset IDs are quoted strings ("001", "002"). YAML would parse bare 001 as integer 1, which breaks ID matching on subsequent runs.
• Every changeset has a rollback block. Write rollback at the same time as the change — not later. Once the changeset is deployed, you have one window to get rollback right before it becomes dangerous to modify.
• Two separate changesets — one for the table, one for the index. This is the “one logical change per changeset” rule in action. If the index creation fails, the table still exists and was tracked.
• ENUM columns use type: ENUM(...) directly. Liquibase passes this through to MySQL as raw SQL for that column type.

The ON UPDATE CURRENT_TIMESTAMP Problem

You may notice the updated_at column does not have ON UPDATE CURRENT_TIMESTAMP. This is a MySQL-specific feature that Liquibase’s createTable change type cannot express directly. The workaround is a sql changeset:

  - changeSet:
      id: "003"
      author: abhay
      comment: Fix updated_at to auto-update on row modification
      changes:
        - sql:
            sql: >
              ALTER TABLE users
              MODIFY COLUMN updated_at DATETIME NOT NULL
              DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP;              
      rollback:
        - sql:
            sql: >
              ALTER TABLE users
              MODIFY COLUMN updated_at DATETIME NOT NULL
              DEFAULT CURRENT_TIMESTAMP;              

Add this as changeset 003 in the same file, after 002. This is a recurring MySQL pattern — any time you need ON UPDATE CURRENT_TIMESTAMP, use a sql changeset to express it explicitly.

Step 1: Validate the Changelog

Before running anything against the database, validate the changelog syntax:

cd ecommerce-db
liquibase validate

Expected output:

####################################################
##   _     _             _ _                      ##
##  | |   (_)           | | |                     ##
...
Liquibase Version: 4.x.x
Liquibase Community 4.x.x by Liquibase

No validation errors found.

If you see errors like YAML not valid or Unknown column type, fix them before proceeding. Common causes: missing quotes on IDs, wrong indentation, or a column type Liquibase doesn’t recognise.

Step 2: Check Status

liquibase status shows which changesets are pending (not yet applied):

liquibase status

Expected output:

3 changesets have not been applied to lb_user@localhost/ecommerce

     ecommerce-db/db/changelog/migrations/2026/05/001-create-users-table.yaml::001::abhay
     ecommerce-db/db/changelog/migrations/2026/05/001-create-users-table.yaml::002::abhay
     ecommerce-db/db/changelog/migrations/2026/05/001-create-users-table.yaml::003::abhay

Liquibase command 'status' was executed successfully.

If the database connection fails, you will see a JDBC error here — fix it before proceeding. Common cause: wrong URL, wrong credentials, or the MySQL JDBC driver not in lib/.

Step 3: Preview the SQL with updateSQL

Never run update on a database you care about without previewing the SQL first. updateSQL prints exactly what Liquibase will execute:

liquibase updateSQL

The output will include the actual CREATE TABLE and CREATE INDEX statements, plus the INSERT INTO DATABASECHANGELOG statements that Liquibase appends for tracking. Read through it. Check that the column types match your intent. Verify the ENUM values are correct.

-- Output excerpt
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('customer', 'admin', 'vendor') DEFAULT 'customer' NOT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active' NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT PK_USERS PRIMARY KEY (id),
    CONSTRAINT UQ_USERS_EMAIL UNIQUE (email)
);
...

INSERT INTO ecommerce.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID)
  VALUES ('001', 'abhay', 'db/changelog/migrations/2026/05/001-create-users-table.yaml', NOW(), 1, '9:...', 'createTable tableName=users', 'Create users table for e-commerce app', 'EXECUTED', NULL, NULL, '4.x.x', '...');

updateSQL is safe to run repeatedly — it does not modify the database. Use it every time before applying a migration, especially on production.

Step 4: Apply the Migration

Once you’ve reviewed the SQL, apply the changes:

liquibase update

Expected output:

####################################################
...
Running Changeset: db/changelog/migrations/2026/05/001-create-users-table.yaml::001::abhay
Running Changeset: db/changelog/migrations/2026/05/001-create-users-table.yaml::002::abhay
Running Changeset: db/changelog/migrations/2026/05/001-create-users-table.yaml::003::abhay

Liquibase command 'update' was executed successfully.

Each changeset runs in its own transaction (for non-DDL statements; MySQL DDL is auto-committed). If changeset 003 fails, changesets 001 and 002 are already committed and tracked in DATABASECHANGELOG. Liquibase will skip them on the next run and retry only 003.

Step 5: Verify in MySQL

Connect to MySQL and confirm the results:

USE ecommerce;

-- Verify the table was created
SHOW TABLES;
-- users

-- Inspect the schema
DESCRIBE users;

-- Inspect the tracking table
SELECT ID, AUTHOR, FILENAME, DATEEXECUTED, EXECTYPE, MD5SUM
  FROM DATABASECHANGELOG
  ORDER BY ORDEREXECUTED;

The DATABASECHANGELOG query should return three rows — one per changeset, each with EXECTYPE = 'EXECUTED'. The MD5SUM column stores a hash of the changeset content. Liquibase uses this to detect if a deployed changeset has been modified (which is forbidden — more on that in a moment).

Also verify the lock table is clean:

SELECT * FROM DATABASECHANGELOGLOCK;
-- LOCKED should be 0 (false)

If LOCKED is 1 and no Liquibase process is running, the lock was left behind by a crashed process. Release it with:

liquibase releaseLocks

What Happens When You Run update Again

Run liquibase update a second time:

liquibase update
# Liquibase command 'update' was executed successfully.
# (No changesets ran)

Liquibase found all three changesets in DATABASECHANGELOG and skipped them. This is idempotent execution — the same command is safe to run multiple times. This property is what makes Liquibase safe to invoke at application startup (as Spring Boot does by default).

What Happens If You Modify a Deployed Changeset

Modify the id column type in changeset 001 from BIGINT UNSIGNED to BIGINT, then run update:

liquibase update
# ERROR: Validation failed for changeset
# db/changelog/migrations/2026/05/001-create-users-table.yaml::001::abhay:
#   MD5Sum mismatch: expected 9:abc123... found 9:def456...

Liquibase compares the stored MD5SUM in DATABASECHANGELOG against the hash of the current changeset content. A mismatch means the changeset was modified after deployment. This is the core invariant: a deployed changeset is immutable.

Revert your edit and create a new changeset 004 to make the column change instead.

Targeting a Specific Count with updateCount

Sometimes you want to apply only the next N changesets — useful when testing or when rolling out changes incrementally:

# Apply only the next 1 pending changeset
liquibase updateCount 1

This runs one changeset and stops, regardless of how many are pending. The remaining changesets stay pending until the next update or updateCount.

Common Mistakes

Using useSSL=true without a real certificate in local dev: With MySQL 8 and useSSL=true, the connector attempts to verify the server certificate. Without a proper CA chain configured, connections fail. Use useSSL=false in local development and configure SSL properly in production.

Forgetting to quote changeset IDs in YAML: A bare 001 in YAML becomes the integer 1. Liquibase stores the ID as 1 in DATABASECHANGELOG. On the next run, Liquibase reads the YAML again and parses 001 as 1 again — this actually matches, so the problem is subtle. But if you later write a changeset with ID 1 (unquoted integer literal), you get a duplicate ID error that is confusing to debug. Always quote IDs in YAML.

Running update without updateSQL first on a non-dev database: The updateSQL step catches type mismatches, wrong defaults, and other issues before they hit the database. Make it a reflex: status → updateSQL → update.

Best Practices

• liquibase.properties is local config — add it to .gitignore, inject credentials in CI via environment variables
• One changeset per logical change — the users table and the email index are separate changesets so a failing index doesn’t orphan the table creation
• Always run updateSQL before update outside of local dev — it is free and catches mistakes before they become incidents
• Write rollback at creation time — dropTable for createTable, dropIndex for createIndex; these are trivial to write now and painful to reconstruct later
• ENUM columns and ON UPDATE CURRENT_TIMESTAMP require raw SQL changesets in MySQL — use sql changeset type for anything Liquibase’s change types can’t express
• Never modify a deployed changeset — create a new one; Liquibase’s MD5 check will catch and block accidental modifications

What You’ve Learned

• MySQL setup: create the database and user before Liquibase touches it
• liquibase.properties keeps connection config out of command-line flags; never commit credentials
• MySQL JDBC driver must be added to Liquibase’s lib/ directory manually
• validate → status → updateSQL → update is the safe four-step workflow
• Liquibase creates DATABASECHANGELOG and DATABASECHANGELOGLOCK automatically on first run
• update is idempotent — safe to run multiple times; already-applied changesets are skipped
• MD5 checksums enforce changeset immutability — modifying a deployed changeset is blocked
• MySQL-specific features like ON UPDATE CURRENT_TIMESTAMP require sql changeset type

Next: Article 5 — Core Commands: update, rollback, status, history, validate, diff — a complete reference to the commands you’ll use every day, with MySQL examples for each.