CNCF Landscape includes Bytebase as the first-ever database CI/CD solution Check Now

Tutorial

Database Change Management with MySQL and GitHub

Mila
5 min read

This is a series of articles about Database Change Management with MySQL


Overview

In the last article Database Change Management with MySQL, you have tried UI workflow in Bytebase.

This tutorial will bring your MySQL schema change to the next level by introducing the GitOps workflow, where you commit the schema change script to the GitHub repository, which will in turn trigger the schema deployment pipeline in Bytebase.

You can use Bytebase free version to finish the tutorial.

Prerequisites

Before you start this tutorial, make sure you have the following ready:

  • Finished our previous UI-based change tutorial Database Change Management with MySQL.
  • A local MySQL cluster.
  • A GitHub account.
  • A public GitHub repository.
  • Docker installed locally.
  • An ngrok account. (ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address in order to receive webhooks from GitHub. We use ngrok here for demonstration purposes. For production use, we recommend using Caddy.)

ngrok

Step 1 - Run Bytebase in Docker with URL generated by ngrok

To make local-running Bytebase visible to GitHub, we’ll pass the URL generated by ngrok to --external-url. If you’re running Bytebase in Docker without this parameter (as in the previous tutorial), you need to delete that Bytebase Docker container first. The data created in the last tutorial is stored under ~/.bytebase/data by default and will be restored if the Bytebase restarts.

delete-bb-container

  1. Login to ngrok Dashboard and follow its Getting Started steps to install and configure.

  2. Run ngrok http 5678 and obtain your public URL:

ngrok-url

  1. Start the Bytebase Docker container with following command, make sure to replace the last parameter --external-url with the public URL generated by ngrok.
docker run --init \
--name bytebase \
--platform linux/amd64 \
--restart always \
--publish 5678:8080 \
--health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
--health-interval 5m \
--health-timeout 60s \
--volume ~/.bytebase/data:/var/opt/bytebase \
bytebase/bytebase:1.14.0 \
--data /var/opt/bytebase \
--port 8080 \
--external-url https://5300-210-129-49-45.jp.ngrok.io

Step 2 - Find your MySQL in Bytebase

  1. Visit Bytebase Console through the browser via your ngrok URL. Log in using your account created from the previous tutorial.

login

  1. If you followed the previes tutorial, you should see that the project and database created are still in your workspace.

home

Step 3 - Connect Bytebase with GitHub.com

  1. Go to Settings and choose Version Control from the left sidebar.

vc-settings-1

  1. Choose GitHub.com and Click Next.

  2. In this tutorial, we will need your GitHub personal account instead of an organization account STEP 2 - OAuth application info. The configuration is similar.

  3. Go to your GitHub account's Settings page.

  4. Click Developer Settings at the bottom of the left sidebar. Click OAuth Apps, and add a New OAuth App.

github-oauth-apps

  1. Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them in. Click Register application.

vc-settings-2 github-new-oauth

  1. After the OAuth application is created, click Generate a new client secret. Copy the Client ID and the newly generated Client Secret, paste them back into Bytebase's Application ID and Secret.

github-client-id-secrets bb-app-id-secrets

  1. Click Next. You will be redirected to the confirmation page. Click Confirm and add, and the Git provider is successfully added.

settings-vc-git-provider-added

Step 4 - Enable GitOps workflow with MySQL

  1. Go to your project, click Version Control, and choose GitOps Workflow. Click Configure GitOps.

enable-gitops

  1. Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose the one you'd like to make changes to. For the sake of this tutorial, let's keep the rest of the settings default, and click Finish.

gitops-enabled

Step 5 - Change schema for MySQL by pushing SQL schema change files to GitHub

  1. In your GitHub repository MySQL-test-bb-local, create a folder bytebase, and create a SQL file using the naming convention {{ENV_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql. It is the default configuration for the file path template setting in the last step.

The full file path is bytebase/test/uni##202302071000##ddl##create_table.sql:

  • test corresponds to {{ENV_ID}}
  • uni corresponds to {{DB_NAME}}
  • 202302071000 corresponds to {{VERSION}}
  • ddl corresponds to {{TYPE}}
  • create_table corresponds to {{DESCRIPTION}}

Let's create a table subject.

CREATE TABLE subject
(
   id BIGINT NOT NULL,
   course VARCHAR(255)
);

create-table

  1. Commit and push this file.

  2. Go to your project in Bytebase. You’ll find there is a new issue called [uni] Alter schema created automatically.

automatic-issue

  1. Go to the issue page, you’ll see:
  • The issue is created via GitHub.
  • The issue is completed without manual approval because it applies the schema change to a database from the Test environment, which doesn't require manual approval.
  • The SQL is exactly the one we have committed to the GitHub repository.
  • The Assignee is Bytebase, because it’s automatic. If the github user you use to commit the change has the same email address found in the bytebase member list, we will use that member as the assignee.

issue-alter-schema

  1. Click View change, you can see the differences.

diff

  1. Go to your GitHub repository, you will see besides your committed SQL, there is a .uni##LATEST.sql file. Because you have configured Schema path template before, Bytebase will write back the latest schema to that specified path after completing the schema change. Thus you have access to an update-to-date full schema at any time.

latest-schema

  1. Let’s create another SQL file uni##202302072000##ddl##add_lecturer.sql to see how that latest schema file will be updated after applying a new schema change. Paste the SQL script in it.
ALTER TABLE subject ADD lecturer VARCHAR(255);
  1. After pushing and committing the new SQL file, go back to Bytebase and you should find another newly generated issue.

issue-add-column

  1. Click View change and see the difference.

diff-columns

  1. Go back to your GitHub repository and you will find the LATEST SQL has been updated to reflect the latest schema.

latest-schema-lecturer

Summary and What's Next

Now that you have tried the GitOps workflow, which stores your MySQL schema in GitHub and trigger the change upon committing change to the repository, to bring your MySQL change workflow to the next level of Database DevOps - Database as Code.

You can check out GitOps docs to learn more configuration details.

In the real world, you might have separated feature and main branches corresponding to your development and production environment, you can check out GitOps with Feature Branch Workflow to learn the setup. Have a try and look forward to your feedback!