The lock-free schema change feature in Data Management Service (DMS) lets you modify table schemas without locking tables. This prevents business interruptions and mitigates primary-standby replication latency from native online DDL operations. This topic uses an ApsaraDB RDS for MySQL instance as an example to show you how to perform a lock-free schema change in DMS.
Add instance and enable lock-free schema change
Log in to DMS 5.0.
In the navigation pane on the left, click the
icon.NoteIf you are using the console in simple mode, click Database Instances in the navigation pane on the left, and then click the
icon.In the Add Instance dialog box, configure the following parameters:
On the Alibaba Cloud tab, select MySQL.
Configure the parameters in the Basic Information and Advanced Information sections. For more information, see Register a cloud database instance.
Category
Parameter
Description
Data source
-
Alibaba Cloud-MySQL is selected by default. You can select another database type.
Basic information
Database type
Select the type of the database instance.
NoteAfter you select a database type, the parameters in the Basic Information section are automatically refreshed.
Instance region
Select the region where the database instance is located.
Other primary accounts
This parameter appears if you select Cross-Alibaba Cloud account instance. Select the Alibaba Cloud account to which the database instance belongs.
NoteIf the Alibaba Cloud account is not in the list, click Add User to add the account that was used to purchase the instance. For more information, see Add a user.
Registration method
Select the method to register the database instance. You can register by Instance ID or Connection String.
Instance ID or Connection String
Enter the Instance ID or connection string.
NoteA connection string consists of an internal network address and a port. Example for ApsaraDB RDS for MySQL:
rm-XXXXXXX.mysql.rds.aliyuncs.com:3306.Access mode
Select whether to enable security hosting for the instance and the logon method. When security hosting is enabled, users are not directly exposed to credentials, which allows for fine-grained permission control.
Security Hosting - Automatic (Recommended): DMS automatically enables security hosting for the instance and creates a database account and password for logging on to DMS.
NoteThis parameter is available only when you register an ApsaraDB RDS instance.
The automatically created account can be viewed in the ApsaraDB RDS console. Do not modify or delete this account to ensure that DMS features function as expected.
Accounts automatically created by DMS for ApsaraDB RDS instances, except for PostgreSQL, do not have permissions to create databases or database accounts.
Security Hosting - Manual: DMS automatically enables security hosting for the instance, but you must manually enter the credentials of an existing database account.
Security Hosting - KMS: DMS automatically enables security hosting for the instance, but you must manually select an RDS credential created in Key Management Service (KMS) to log on to the database.
NoteThis parameter is available only when you register an ApsaraDB RDS instance.
No Hosting (Not Recommended): If security hosting is disabled, you must frequently use your database account and password to log on, which may affect normal operations.
Add-on feature pack
Select a feature pack.
Security Collaboration or Stable Change (choose one): Security Collaboration supports all the capabilities of Stable Change and provides DevOps features for you to flexibly customize development and approval workflows. Stable Change provides solutions for more stable database operations, such as lock-free schema change and SQL review.
NoteIf you do not select Security Collaboration or Stable Change, the instance uses the Flexible Management mode by default.
Sensitive Data Protection: This feature allows you to manage and mask sensitive data. For more information, see Enable Sensitive Data Protection.
Security rules
This parameter appears if you select the Security Collaboration feature pack.
You can select the default system rules or custom security rules to implement fine-grained control over the database.
Classification and grading template
The configuration option for the data classification template appears only when Sensitive Data Protection is enabled.
You can bind a data classification and grading template to an instance to identify whether fields in the instance's databases and tables match the rules in the template. If a field matches the rules, it is tagged with a classification and grading label to protect fields with high sensitivity levels.
Advanced information
Environment type
Select the environment type of the instance.
Instance name
Clear the Synchronize Instance Name checkbox to specify a custom display name for the instance in DMS.
NoteDMS synchronizes the instance name from the cloud service only during the first registration. You can later change the instance name by editing the instance.
Lock-free Schema Change
Select whether to enable the lock-free schema change feature. Two execution modes are supported: Execute with DMS lock-free change and Prioritize native lock-free, switch to DMS on failure.
NoteThis parameter is available only for MySQL databases.
Enable SSL
NoteThis parameter is available only for MySQL or Redis databases.
By default, SSL connections are disabled in DMS.
If you need to connect to the database over SSL, you must enable SSL in DMS and ensure that SSL is also enabled on the database server.
Secure Sockets Layer (SSL) encrypts network connections at the transport layer to improve data security and integrity, but it can increase connection latency.
Instance DBA
Select a DBA role for subsequent workflows such as permission requests.
Query timeout (s)
This setting terminates query statements in the SQL window that exceed the specified time limit to help protect the database.
Export timeout (s)
This setting terminates export tasks from the SQL window that exceed the specified time limit to help protect the database.
After you configure the parameters, click Test Connection in the lower-left corner of the Basic Information section and wait for the test to succeed.
NoteIf the connection test fails, check and modify your settings based on the error message.
After the connection test succeeds, click Submit.
Lock-free schema change
Log in to DMS 5.0.
In the top navigation bar, choose .
NoteIf you use the DMS console in simple mode, move the pointer over the
icon in the upper-left corner of the console and choose . Configure the lock-free change ticket. The following table describes key parameters. For more information about other parameters, see Lock-free DML change.
Parameter
Description
Database
Search for and select one or more destination databases.
Have Permission: Shows only databases for which you have change permissions.
All: Shows all databases except for those with metadata access control enabled.
NoteIf you do not have change permissions on the target database, choose in the top navigation bar. Then, click to apply for the required permissions.
SQL statements for change
Enter Data Definition Language (DDL) statements, such as
ALTER TABLEorOPTIMIZE.NoteIf you enter Data Manipulation Language (DML) statements, it initiates a lock-free data change task. For more information, see Lock-free DML change.
Click Submit Application.
The system automatically performs an SQL precheck. If the precheck fails, you can click Modify SQL to revise the SQL statements based on the error and then resubmit.
After the ticket is approved, click Execute Change in the Execute section of the ticket details page.
Configure the task execution parameters.
Parameter
Description
Execution strategy
Execute Immediately: This is the default option. The task starts immediately after you click Confirm Execution.
Schedule: Select a start time for the task. The task will run at the specified time after you click Confirm Execution.
Specify end time
On: Select an end time for the task. If the task is not completed by the specified end time, the system stops the task. This prevents tasks from running during peak hours and affecting business operations.
Off: This is the default option.
Click Confirm Execution.
NoteA paused task restarts from the beginning.
In the Execute section, you can view the task status, task settings, scheduling logs, and other information.
You can also view the progress of the lock-free schema change task by choosing . For more information, see View the progress of a lock-free change task.
Related operations
Track the progress of change tasks in the Task Management feature of DMS. For more information, see Task Management.
If the lock-free schema change feature is enabled, you can also perform Standard Data Change tasks without locking tables. For more information, see Standard Data Change.