How-To

How to Synchronize Database Schemas

Ningjing
4 min read

This article briefly describes the general scenarios of database schema synchronization and how to use this feature smoothly in Bytebase with pure UI operations.

General Scenarios

Bytebase's database schema synchronization feature (introduced in 1.8.0) supports copying a specific schema version from one database to another. Without this feature, developers have to write SQL statements cautiously and apply them manually; Now with this feature, they can simply tell Bytebase which database schema needs to be replicated to the target database, and the whole process can be done from UI operations. This feature can solve the following scenarios:

Scenario 1

During the product release process, for different environments, such as dev / staging / prod, you need to do schema synchronization. For example, the R&D release process from dev to staging, then to prod in the forward direction, or from prod to dev for testing purposes in the reverse direction.

Scenario 2

For the production environment, databases with identical schemas, such as SaaS, multi-region game deployment, etc.

  1. Manual modification by humans due to special ad-hoc reasons - causes database schema drift, which needs to reconciliation
  2. The vendor maintains multiple product versions, so it also needs to reconcile the schema.

Easy Steps

In the Bytebase Console:

  1. Select the project first;
  2. Select a specific database schema as the synchronization source;
  3. Select the database you want to sync to;

Bytebase will then calculate the schema differences between these two and automatically generate the suggested DDL statements. (e.g., ALTER TABLE ......)

sync-schema

Tutorial: Experience the feature in 5 mins

Here is how we install Bytebase and try out schema synchronization. Even if you have never tried Bytebase before, you can complete the process within 5 mins.

Preparation Phase

  1. Make sure you installed Docker.

  2. Copy and paste the commands to start one Bytebase and two MySQL instances via Docker.

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:%%bb_version%% \
  --data /var/opt/bytebase \
  --port 8080
docker run --name mysqldtest \
  --publish 3307:3306 \
  -e MYSQL_ROOT_HOST=172.17.0.1 \
  -e MYSQL_ROOT_PASSWORD=testpwd1 \
  mysql/mysql-server:8.0
docker run --name mysqldprod \
  --publish 3308:3306 \
  -e MYSQL_ROOT_HOST=172.17.0.1 \
  -e MYSQL_ROOT_PASSWORD=testpwd1 \
  mysql/mysql-server:8.0
  1. Register and sign in Bytebase Console. Add two instances in two environments (Test/Prod), respectively. Username: root, Password:testpwd1 2 instances

  2. Create project sync-schema, and within the project, click Create DB to create databases as follows: 2 databases

  3. Go back to project sync-schema page, and click Alter Schema. Choose dbtest in Test, and click Next. Copy and paste the SQL below and click Create.

CREATE TABLE t1 (
   person_id INT,
   last_name VARCHAR(255),
   first_name VARCHAR(255),
   address VARCHAR(255),
   city VARCHAR(255)
);
  1. Go back to project sync-schema page, and click Alter Schema. Choose dbprod in Prod, and click Next. Copy and paste the SQL below and click Create.
CREATE TABLE t2 (
   person_id INT,
   full_name VARCHAR(255)
);

Feature Experience Phase

  1. Go back to the project sync-schema page, click Schema-Sync, and fill in steps 1-3 to synchronize the schema of the dbtest database to dbprod: sync-schema UI

  2. Click Preview Issue, you will go to the new issue page with preview: issue SQL

  3. Click Create, and the Issue is truly created. Approve and Fix the issue, the schema synchronization will be completed. dbprod t1

Enterprise Plan

In Bytebase Free or Team Plan, you can choose the latest history version; In Enterprise Plan, you can choose an arbitrary schema version from the full migration history.

From Bytebase 1.8.0, you can run a 14-day Team Plan trial without hub registration. While having Team Plan trial, you can upgrade to the Enterprise Plan trial by clicking Upgrade in a specific feature. dbprod t1

Is the experience smooth? Or do you encounter any problems? Feel free to join our Discord Group to talk about it!

In the following article, we’ll explain how Bytebase implements schema synchronization for MySQL from a technical perspective. Stay tuned!