Database CI/CD and Schema Migration with TiDB

A series of articles about Database CI/CD and Schema Migration with TiDB

• Database CI/CD and Schema Migration with TiDB (this one)
• Database CI/CD and Schema Migration with TiDB and GitHub

TiDB is an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads. It is MySQL compatible and can provide horizontal scalability, strong consistency, and high availability.

This tutorial will guide you step-by-step to set up database change management for TiDB in Bytebase. With Bytebase, a team can have a formalized review and rollout process to make TiDB schema change and data change.

Bytebase provides a GUI for teams to perform database changes and retain the full migration history. You can use Bytebase free version to finish the tutorial.

There is also a bonus section about drift detection for those advanced users if needed.

Prerequisites#

Before you start this tutorial, make sure:

• You have deployed a local TiDB Cluster.
• You have Docker installed locally.

Step 1 - Start Bytebase in Docker#

1. Make sure your docker daemon is running, and start the Bytebase docker container by typing the following command in the terminal.

   docker run --rm --init \
     --name bytebase \
     --publish 8080:8080 --pull always \
     --volume ~/.bytebase/data:/var/opt/bytebase \
     bytebase/bytebase:3.0.1

2. Bytebase is running successfully in Docker, and you can visit it via localhost:8080.

3. Visit localhost:8080 in your browser. Register the first admin account which will be granted Workspace Admin.

Step 2 - Add a TiDB cluster in Bytebase#

In Bytebase, ​​an Instance could be your on-premises MySQL instance, an AWS RDS instance etc, in this tutorial, a TiDB account.

1. Visit localhost:8080 and login as Workspace Admin.

2. Click Add Instance.

3. Add a TiDB instance. You need to pay attention to some fields:

• Environment: choose Test, if you choose Prod, you will need approval for all future change requests. In this tutorial, let's try to keep it simple. (However, it’s all configurable later.)
• Account name / Port: host.docker.internal / 4000
• Username and password: root / (leave it empty)

Step 3 - Create a Project#

In Bytebase, 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 Jira, GitLab. So before you deal with the database, a project must be created.

1. After the instance is created, click Projects on the top bar.

2. Click New Project to create a new project TestTiDB, key is TT, mode is standard. Click Create.

Step 4 - Create a database in TiDB via Bytebase#

In Bytebase, 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. After the project is created, go to the project and click New DB.

2. Fill the form with Name - demo, Environment - Test, and Instance - TiDB instance. Click Create.

3. Bytebase will create an issue “CREATE DATABASE ….” automatically. Because it’s for the Test environment, the issue will run without waiting for your approval by default. Click Resolve, and the issue is Done. The database is created.

4. Go back to the home page by clicking Home on the left sidebar. If it’s the first time you use Bytebase, it’ll show a celebration. On the home page, you can see the project, the database, and the issue you just resolved.

Step 5 - Create a table in TiDB via Bytebase#

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 TestTiDB, and click Alter Schema.
2. Choose demo and click Next.
3. Input title, SQL, and Assignee, and click Create.

CREATE TABLE t1 (
  id BIGINT NOT NULL,
  nickname VARCHAR(255)
);

4. Bytebase will do some basic checks and then execute the SQL. Since it’s for Test environment, the issue is automatically approved by default. Click Resolve issue.

5. The issue status will become Done.

6. On the issue page, click view migration. You will see diff for each migration.

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

Bonus Section - Schema Drift Detection#

To follow this section, you need to have Team Plan or Enterprise Plan (you can start 14 days trial directly in the product without credit card).

Now you can see the full migration history of database demo. However, what is Establish new baseline? When should it be used?

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

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

1. Go to the Terminal, and connect to TiDB. and add a COLUMN there. Make sure the new column is added.

2. Wait for 10 mins (as Bytebase does the check roughly every 10 mins). Go back to Bytebase, and you can find the Schema Drift on database demo:

The Anomaly Center also surfaces the drift:

3. Click View diff, you will see the exact drift.

4. Use baseline to reconcile the schema state from the live database schema. Go to demo > Migration History and click Establish new baseline.

5. It will create an issue. Click Resolve to make it done.

6. Go back to demo or Anomaly Center, and you will find the Drift is gone.

Summary and Next#

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

In the next article, you’ll try out GitOps workflow, which will store your TiDB schema in GitHub and trigger the change upon committing the change to the repository, to bring your TiDB change workflow to the next level of Database DevOps - Database as Code.