DevOps: Database Change Management with MariaDB
Tags: mariadb, devops, cicd, sql
Published: 2023-06-17 16:43:14


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](https://dev.to/adela_bytebase/devops-database-change-management-with-mariadb-and-github-76f)

---

[](https://mariadb.org/) 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 
- [](https://www.docker.com/) installed. You can follow the [official guide](https://docs.docker.com/get-docker/) to install it.
- In this tutorial, we'll use [StackBricks](https://stackbricks.app/) 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:

   ```bash
    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
   ```

2. Run StackBricks, and create two  instances: 
   - ` test`,`3307`
   - ` prod`,`3308`

   ![stackbricks](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/stackbricks-2-instances.webp)

3.  and  are now running via .
   ![docker](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/docker.webp)

## 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`](https://www.bytebase.com/docs/concepts/roles-and-permissions).
   ![bb-register-admin.webp](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-register-admin.webp)

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.
   ![bb-issue-demo_db_test-done](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-issue-demo_db_test-done.webp)

3. 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.

4. 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.
   ![bb-alter-schema-test-prod](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-alter-schema-test-prod.webp)

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.

   ![bb-issue-test-done-prod](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-issue-test-done-prod.webp)

5. Click **Rollout** and the SQL will execute and the issue will become `Done`.

6. On the issue page, click **View change**. You will see the difference.
   ![bb-issue-diff](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-issue-diff.webp)

7. 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.
   ![bb-demo-ui-history](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb/bb-demo-ui-history.webp)

## 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](https://www.bytebase.com/docs/sql-editor/admin-mode)

2. 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).