A Guide to Automating MySQL Database Schema Migrations with Liquibase and CI/CD - ITFROMZERO - Share tobe shared! Ditch manual midnight SQL script deployments. Learn how to track MySQL schema changes with Liquibase and build safe CI/CD pipelines without table locks or drift.

Table of Contents

Quick start: Run your first migration in 5 minutes

This scenario probably sounds all too familiar: you send a patch_v2.sql file over Slack at midnight, anxiously waiting for DevOps to run it manually on production. A single missed statement or the wrong execution order, and the entire application crashes. Liquibase completely eliminates this risk by treating database schema as code, with strict version control just like Git.

No complex installation is required. All you need is Docker and two configuration files to get started.

First, create a liquibase.properties file to define your MySQL connection:

changeLogFile=changelog.xml
url=jdbc:mysql://localhost:3306/app_db?useSSL=false&allowPublicKeyRetrieval=true
username=root
password=rootpassword
driver=com.mysql.cj.jdbc.Driver

Next, create a changelog.xml file defining a sample users table:

<?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-latest.xsd">

    <changeSet id="20241015-01" author="hoang-dev">
        <createTable tableName="users">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(50)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

Run the migration with a single-line Docker command:

docker run --rm --network="host" \
  -v $(pwd):/liquibase/changelog \
  liquibase/liquibase \
  --defaults-file=/liquibase/changelog/liquibase.properties \
  update

Check MySQL once the command finishes. Alongside the users table, you will see two new system tables appear: DATABASECHANGELOG and DATABASECHANGELOGLOCK. These form the core of Liquibase.

Under the hood: How does Liquibase work?

Plain SQL files cannot track their own state. When a project involves 15 developers altering schemas across dozens of microservices, there is no easy way to know whether Staging matches Production.

Liquibase manages this entire lifecycle through two core components.

1. ChangeSet and ChangeLog

Every database change is encapsulated within a ChangeSet. A collection of ChangeSets is grouped inside a ChangeLog file.

id: Unique identifier for the change. It is best practice to format this using dates or Jira ticket keys (e.g., PROJ-102-add-phone-col).
author: The author of the change, used for audit trails when investigating logs.
Unique key: The combination of id + author + filepath forms a unique composite key. Liquibase uses this triplet to determine whether to execute or skip a script.

2. Execution flow with two system tables

When you run update, Liquibase executes four sequential steps:

Lock verification: Reads DATABASECHANGELOGLOCK and sets the LOCKED column to 1. This locking mechanism prevents race conditions when two CI/CD pipelines run simultaneously.
Checksum validation: Scans DATABASECHANGELOG and evaluates the MD5 hash of each ChangeSet to identify which scripts have already been applied.
Execution: Applies the pending ChangeSets to MySQL and immediately writes an audit record including the execution time in milliseconds.
Release lock: Resets LOCKED = 0 in DATABASECHANGELOGLOCK, releasing the lock for subsequent processes.

3. Safe rollbacks during incidents

Late-night deployment failures are an ever-present risk. The <rollback> tag allows you to revert the database to its previous state with a single command, such as rollback-count 1:

<changeSet id="20241015-02" author="hoang-dev">
    <addColumn tableName="users">
        <column name="phone_number" type="VARCHAR(20)"/>
    </addColumn>
    <rollback>
        <dropColumn tableName="users" columnName="phone_number"/>
    </rollback>
</changeSet>

Integrating Liquibase into a GitHub Actions pipeline

Never let developers run migrations directly from their local machines against production databases. Every migration must be isolated within a CI/CD pipeline.

Here is a standard GitHub Actions workflow example:

name: Database Migration

on:
  push:
    branches:
      - main
    paths:
      - 'db/changelog/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Source Code
        uses: actions/checkout@v4

      - name: Liquibase Update
        uses: liquibase-github-actions/[email protected]
        with:
          changelogFile: 'db/changelog/changelog.xml'
          url: ${{ secrets.DB_JDBC_URL }}
          username: ${{ secrets.DB_USER }}
          password: ${{ secrets.DB_PASSWORD }}
          headless: true

Adopt a 3-stage process to ensure maximum safety:

Stage 1 (Dry-run & Review): Run update-sql to export the raw SQL as an artifact. DBAs can review the exact statements before any changes touch live data.
Stage 2 (Staging Migration): Automatically run migrations in a Staging environment with production-like data volume to catch syntax errors or timeouts early.
Stage 3 (Production Approval): Configure a Manual Approval Gate in GitHub Actions. Only after the Tech Lead approves will the job connect to the production database.

3 hard-learned lessons when running Liquibase on MySQL

On a MySQL 8.0 cluster with 40 million records (around 85 GB of data), a single careless DDL command can bring down an entire e-commerce system. Keep these battle-tested principles in mind:

Watch out for Metadata Locks on large tables: Although MySQL 8.0 supports ALGORITHM=INSTANT for certain column additions, modifying data types or creating indexes still requires an Exclusive Lock. For tables with over 5 million rows, wrap online schema change tools like gh-ost or pt-online-schema-change inside a <customChange> tag instead of running raw DDL statements directly.
Never modify an already executed ChangeSet: Liquibase enforces integrity using MD5 checksums. If you alter a script that has already run in production, the pipeline will fail immediately with ValidationFailedException: MD5Sum check failed. If you need to modify schema logic, always create a new ChangeSet with the next ID.
Prevent duplicate errors using Preconditions: Use pre-execution checks. The <preConditions> tag allows scripts to be re-run safely without throwing errors for existing tables or indexes:

<changeSet id="20241015-03" author="hoang-dev">
    <preConditions onFail="MARK_RAN">
        <not>
            <indexExists indexName="idx_users_email" tableName="users"/>
        </not>
    </preConditions>
    <createIndex indexName="idx_users_email" tableName="users">
        <column name="email"/>
    </createIndex>
</changeSet>

Automating schema migrations does more than just relieve developers from stressful late-night deployments. More importantly, it transforms the database into a testable, trackable, and recoverable asset in modern DevOps workflows.