Database CI/CD and Schema Migration with MySQL

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

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

---

MySQL is the most popular open-source relational database management system.

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

Bytebase provides a GUI for teams to perform database changes and retain full migration history. Bytebase Free Plan is suffice for this tutorial.

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

Prerequisites#

Before you start, make sure you have the following downloaded and installed:

• MySQL
• Docker

Step 1 - Deploy Bytebase via Docker#

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

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

2. Bytebase is now running via Docker, and you can access it via localhost:8080.

3. Visit localhost:8080 in your browser. Register an account.

Step 2 - Add a MySQL Instance to Bytebase#

In this tutorial, ​an Instance is your on-premises MySQL instance.

1. Login to the Bytebase Console.

2. Click Add Instance.

3. Add a MySQL instance. Pay attention to these fields:

• Environment: choose Test, if you choose Prod, you'll need approval for all future change requests, let's keep it simple for the sake of this tutorial.
• Username and password: your MySQL username and password.

Step 3 - Create a Project#

In Bytebase, Project groups logically-related Databases, Issues and Users together, which is similar to the project concept in other DevTools such as Jira and GitLab. So before you deal with the database, a Project must be created.

After the instance is created, go to the Projects tab, click on New Project to create a new project.

Step 4 - Create a MySQL database via Bytebase#

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

1. After the project is created. Click on New DB to create a new database.

2. Fill the form, and pay attention to these fields:

• Name: can be anything, here we used uni
• Environment - Test
• Instance - choose the instance you added in Step 2.

And click on Create.

3. Bytebase will create an issue to create the database automatically. As it's the Test environment, the issue will run without waiting for your approval by default. Click Resolve, and the database is created.

Step 5 - Create a table in MySQL via Bytebase#

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

1. Visit your project, and click on Alter Schema.

2. Choose your database and click Next. This is where you get to try out the new Schema Editor. It’s a visual editor for schema changes.

Here, we are going to create a table called student with 2 columns: id and last_name.

3. Hit Preview issue, and Bytebase will automatically create an issue with the corresponding SQL statement to create a table with the columns. Check to see if it's correct, and click Create.

4. Since it’s for the Test environment, the issue is automatically approved by default. Click Resolve issue. The issue status will become Done.

5. From the issue page, click view migration, and you can see differences.

Bonus Section - Schema Drift Detection#

To follow this section, you need to activate the Enterprise Plan (you can start a 14-day trial directly, no credit card required).

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

By adopting Bytebase, we expect teams to use Bytebase exclusively for all schema changes. Meanwhile, if someone has made MySQL 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 your Terminal, connect to MySQL, and add a column age to table student.

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 student:

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

4. Go to Databases > select the database in drift > Change History > Establish new baseline, this step establishes a new baseline to reconcile the schema state from the live database schema.

4. Bytebase will create an issue to establish the new baseline, click Create, and then Resolve to mark it done.

5. Go back to your database page, where you will find the drift is gone and no anomalies detected.

Summary and What's Next#

Now you have connected MySQL with Bytebase, and used the UI workflow to accomplish schema change. Bytebase will record the full migration history for you. Furthermore, the Enterprise Plan is equipped with Schema Drift Detection to detect out-of-band schema changes performed outside of Bytebase.

In the next post, you’ll try out GitOps workflow: store your MySQL schema in GitHub and Bytebase will pick up the changes to the repo, bringing your MySQL change workflow to the next level, aka Database DevOps - Database as Code.