DevOps: Database Change Management with MariaDB

This is a series of articles about : Database Change Management with .

• Database Change Management with MariaDB (this one)
• Database Change Management with MariaDB and GitHub

is a community-developed, commercially supported fork of the management system (), intended to remain under the .

This tutorial is a step-by-step guide to setting up Database Change Management for in . With , a team can have a formalized review and rollout process to make change and data change.

provides a GUI for teams to perform Database Changes and retain full Change History. Free Plan is sufficient for this tutorial.

In the end, there is a bonus section about Schema Drift Detection for those advanced users.

Features included

• Change Workflow
• Change History
• SQL Editor Admin Mode
• Drift Detection

Prerequisites

Before you start, make sure you have

• installed. You can follow the official guide to install it.
• In this tutorial, we'll use StackBricks to run . You can also use other tools.

Step 1 - Deploy Bytebase via

1. Make sure your is running, and start the Bytebase with following command:

    run --init \
   --name  \
   --platform /amd64 \
   --restart always \
   --publish 5678:8080 \
   --health-cmd "curl --fail http://:5678/healthz || exit 1" \
   --health-interval 5m \
   --health-timeout 60s \
   --volume ~/./data:/var/opt/ \
   bytebase/bytebase:%%bb_version%% \
   --data /var/opt/bytebase \
   --port 8080

1. Run StackBricks, and create two instances:
   • test,3307
   • prod,3308

1. and are now running via .

Step 2 - Add in

In , ​​an Instance could be your on-premises instance, an instance and etc. In this tutorial, it's instances.

1. Visit :5678 in your browser. Register the first which will be granted Workspace Owner.
   

2. Click Instances on the top bar and click Add instance, choose ``. Fill the form with the following information and click Create.

   • Instance Name: test
   • Environment: Test
   • Host or Socket and Port: host.docker.internal and 3307
   • Username and password: root and your password

3. Click Add instance again, choose ``. Fill the form with the following information and click Create.

   • Instance Name: prod
   • Environment: Prod
   • Host or Socket and Port: host.docker.internal and 3307
   • Username and password: root and your password

Step 3 - Create a Project

In , Project is the container to group logically related Databases, Issues and Users together, which is similar to the project concept in other dev tools such as , . So before you deal with the database, a project must be created.

1. Click Projects on the top bar.
2. Click New Project to create a new one with Demo UI as Project Name, DUI as Key and Standard as Mode.

Step 4 - Create a database in via

In , a Database is the one created by "CREATE DATABASE xxx". A database always belongs to a single Project. Issue represents a specific collaboration activity between Developer and DBA such as creating a database, altering a schema. It's similar to the issue concept in other issue management tools.

1. Go to the project Demo UI, and click New DB.

2. Fill the form with the following information and click Create.

• Name: demo_db
• Environment: Test
• Instance: test

It will create an issue "CREATE DATABASE …" automatically. Because it’s for Test environment, the issue will automatically run then becomes ``. The database is created.

1. Click New DB again, fill the form with the following information and click Create.

• Name: demo_db
• Environment: Prod
• Instance: prod

It will create an issue "CREATE DATABASE …" automatically. Because it’s for environment, the issue will require manual rollout. Click **Rollout** to run then becomes . The database is created.

1. Go back to project Demo UI, click Databases tab, and you can see there're two databases there.

Step 5 - Create a table in via

In Step 4, you actually created an issue in UI workflow and then executed it. Let’s make it more explicit.

1. Go to project Demo UI, and click Alter Schema. Here you can choose either one or both.
   

2. Check both demo_db and click Next. It will generate a pipeline.

3. Input the SQL as following, and click Apply to other tasks. Click Create.
   `sql
CREATE TABLE t1(
Id INT AUTO_INCREMENT,
(Id)
)
`

4. will run SQL automatically on Test environment, but wait for manual rollout on . It's by default configuration, you can adjust it on Environments page.

1. Click Rollout and the SQL will execute and the issue will become Done.

2. On the issue page, click View change. You will see the difference.
   

3. You can also go to Change History under the project to view the full history. Or go into a specific database to view its history.
   

Bonus Section - Schema Drift Detection

This section requires you to have Enterprise Plan (you can start 14 days trial directly in the product without ). Click Start free trial on the bottom of left sidebar.

By adopting , we expect teams to use exclusively for all schema changes. Meanwhile, if someone has made schema change *outside of *, obviously won’t know it. And because has recorded its own copy of schema, when compares that with the live schema having that schema change, it will notice a discrepancy and surface a schema drift anomaly. If that change is intended, then you should use baseline the schema state again to reconcile.

In this section, you’ll be guided through this process.

1. You can use an external GUI or terminal to make a change to demo-db. In this tutorial, we use SQL Editor’s Admin mode which also counts when we say change outside of **. Go to **SQL Editor, and switch to Admin mode.

When you make a change in Admin mode, it will not record any history as in a normal process /docs/sql-editor/admin-mode

1. Paste the following and then press Enter:

`sql
ALTER TABLE t1
ADD COLUMN name (255);`

   ![bb-sql-editor-admin-alter-t1](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-sql-editor-admin-alter-t1.webp)
   Admin mode skips  system and works as an external input. The change will take effect in Bytebase GUI in the next sync.

1. Wait for 10 mins. Go to **Anomaly Center**, and you can find the **Schema Drift**.
   ![bb-anomaly-center-1-drift](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-anomaly-center-1-drift.webp)

2.  , and you’ll see the drift.
   ![bb-demo_db-drift-diff](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-demo_db-drift-diff.webp)

3. You may also find the drift by clicking **Databases** > `demo_db`.

4. Go to **Databases** > `demo_db` > **Change History** and click **Establish new baseline**.
   ![bb-demo_db-establish-new-baseline](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-demo_db-establish-new-baseline.webp)

5. It will create an issue and the issue will be `Done`.
   ![bb-issue-demo_db-baseline-done](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-issue-demo_db-baseline-done.webp)

6. Go back to **Databases** > `demo_db` or **Anomaly Center**, and you will find the drift is gone.

## Summary and Next

Now you have connected  with Bytebase, and tried out the UI workflow to do schema change. Bytebase will record the full change history for you. With **Enterprise Plan**, you can even have schema drift detection.

In the next article, you’ll try out GitOps workflow, which will store your  schema in  and trigger the change upon committing the change to the repository, to bring your  change workflow to the next level of Database  - [Database as Code](https://www.bytebase.com/blog/database-as-code).