Change MySQL Schemas with Zero Downtime via DMS - Data Management

Direct ALTER TABLE statements lock the table for the duration of the change, blocking reads and writes. MySQL's online DDL reduces this impact on the primary, but it still executes sequentially on replicas, causing replication lag that can affect production workloads.

Data Management Service (DMS) solves this with lock-free schema changes. DMS creates temporary tables with the new schema, replicates full data and incremental binary log data into them, and then stores the temporary tables as permanent tables — keeping your tables available throughout.

Prerequisites

Before you begin, make sure that:

The database instance is managed in Stable Change or Security Collaboration mode in DMS. See Control modes.
The lock-free schema change feature is enabled for the database instance. See Enable the lock-free schema change feature.

The following example uses an ApsaraDB RDS for MySQL instance in a simulated development environment.

How it works

A lock-free schema change runs in three stages:

Temporary table creation — DMS creates one or more temporary tables that use the new schema.
Data synchronization — DMS copies all existing rows (full data) and continuously applies incoming changes (incremental binary log data) to the temporary tables until they are in sync with the source table.
Table swap — DMS replaces the original table with the synchronized temporary tables, then drops the temporary tables.

While a lock-free schema change is running, two temporary tables are visible in the database. After the swap completes, both temporary tables are removed.

Submit and execute a lock-free schema change

The following example changes the data type of the long_text_a column in the big_table table from varchar(1024) to text.

Step 1: Submit a ticket (regular user)

Log on to the DMS console V5.0DMS console V5.0DMS console V5.0 as a regular user.
In the top navigation bar, choose Database Development > Data Change > Lockless Change.
In simple mode, move the pointer over the icon in the upper-left corner and choose All functions > Database Development > Data Change > Lockless Change.

Configure the following parameters and click Submit. For the SQL statements for change field, enter:

Parameter	Description
Database	Required. The database to change. In this example, the `poc_dev` database in Secure Collaboration mode is selected.
Reason category	Required. The reason for the change, used to find the ticket later.
Business background	Required. The purpose of the change, to reduce unnecessary communication.
Execution method	Required. How the ticket is executed after approval. Select Last Auditor Execute.
Affected rows	Required. The estimated number of rows affected. To get the exact count, run a `COUNT` query in SQL Console before submitting.
SQL statements for change	Required. The DDL statement to execute. In this example, the statement changes the data type of `long_text_a` from `varchar(1024)` to `text`.
SQL statements for rollback	Optional. The SQL to roll back the change if needed.
Change stakeholder	Optional. Other users who can view ticket details and assist in the approval process. Users not listed cannot view the ticket, except DMS administrators and DBAs.

ALTER TABLE `big_table`
  MODIFY COLUMN `long_text_a` text NULL AFTER `name`;

On the Ticket Details page, review the ticket.
DMS runs a precheck on the SQL statement automatically. The precheck covers SQL syntax, whether the statement type matches your security rules, the submitter's permissions, and the estimated number of rows to scan. You can modify ticket details only before submitting for approval.
After the precheck passes, click Submit for Approval, then click OK in the confirmation dialog.
Important
After you submit the ticket for approval, ticket details can no longer be modified.

Step 2: Approve the ticket (DMS administrator)

Log on to the DMS console V5.0DMS console V5.0DMS console V5.0 as a DMS administrator.
On the home page, click Pending Tickets in the My Tickets section.
On the My Tickets page, find the ticket and click its number in the Ticket Number column.
In the Ticket Details panel, review the change information and click Approve.
Enter comments and click Submit.

Step 3: Execute the ticket (regular user)

Log on to the DMS console V5.0DMS console V5.0DMS console V5.0 as a regular user.
In the Execute section of the Ticket Details panel, click Execute Change.

Configure the execution parameters and click Confirm Execution.

DMS starts the task immediately after you click Confirm Execution.

Parameter	Description	Default
Execute strategy	When to run the task. Running immediately starts the task as soon as you confirm. Schedule runs the task at a time you specify.	Running immediately
Transaction control	Whether to roll back on failure. on: if a statement fails, all DML statements in the same transaction are rolled back (DDL statements cannot be rolled back). off: statements run one at a time; if one fails, the transaction stops, but already-executed statements are not rolled back.	off
Data backup	Whether to generate backup scripts. on: DMS generates `INSERT` scripts to back up rows affected by `UPDATE` or `DELETE` statements. off: no backup scripts are generated.	on

Monitor progress and verify the schema

As a DMS administrator, move the pointer over the database name in the Basic Information section and click Query to open SQL Console.
In SQL Console, run the following statement to list all tables in the database:
While a lock-free schema change is running, two temporary tables appear in the results.
```
SHOW TABLES;
```
In the top navigation bar, choose O&M > Task.
In simple mode, move the pointer over the icon in the upper-left corner and choose All functions > O&M > Task.
Click the task number to open the Execution details dialog.
Click Progress in the Operation column to view the Lock-Free structure change message and monitor real-time progress for the lock-free schema change.
After the task completes, verify the schema. The big_table table now uses the new schema, and the temporary tables have been removed.