Data Management (DMS) introduces a new feature to change schemas without locking tables. This feature prevents your business from being blocked by table locks due to schema changes and minimizes the latency that is caused by the online DDL feature of MySQL during primary-secondary replication. This feature creates one or more temporary tables that use the new schema, replicates full data and incremental binary logs to the temporary tables, and then stores the temporary tables as permanent tables.
In the following example, the feature changes the data type of the
long_text_a column in the
big_table table from
- Submit a ticket as a regular user.
- Log on to the DMS console as a regular user.
- In the top navigation bar, choose .
- Set the parameters as required and click Submit. The following table describes the parameters.
Parameter Description Database Required. The database in which you want to change data. In this example, select the
Reason Category Required. The reason for the data change. This helps you find the ticket in subsequent operations. Business Background Required. The purpose or objective of the data change. This reduces unnecessary communication. Execution Method Required. The way in which you want the ticket to be submitted for execution. Select Last Auditor Execute. Affected Rows Required. The estimated number of data rows to be affected by this data change. To obtain the actual number of affected rows, you can use the
COUNTfunction in SQL statements on the SQLConsole tab.
SQL Statements for Change Required. The SQL statement that you want to execute to change data. Enter the following
ALTER TABLE `big_table` MODIFY COLUMN `long_text_a` text(1024) NULL AFTER `name`;Note This statement changes the data type of the
SQL Statements for Rollback Optional. The SQL statements that you can execute to roll back the data change. Change Stakeholder Optional. The stakeholders of the data change. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
- On the ticket details tab, confirm the ticket details.
Note You can view the ticket details on the ticket details tab. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, the type of the SQL statement and whether the type matches the type that you configured in the security rules for the ApsaraDB RDS instance, the permissions of the user that submitted the ticket, and the estimated number of rows to scan.
- After the ticket details are confirmed and the precheck is complete, click Submit for Approval. In the message that appears, click OK. Note You can change the ticket details only before you submit the ticket for approval.
- Approve and handle the ticket as a DMS administrator.
- Log on to the DMS console as a DMS administrator.
- On the Workbench tab of the DMS console, click Pending Tickets in the My Tickets section.
- On the My Tickets tab, click the ticket number of the ticket that you want to handle in the Ticket Number column.
- On the ticket details tab, confirm the data change information in the ticket and click Approve.
- In the dialog box that appears, enter comments and click Submit.
- In the Execute step, click Execute Change. In the dialog box that appears, set the parameters as required and click Confirm Execution.
Parameter Description Execute Immediately Specifies whether to run the task immediately or at a scheduled time. Valid values:
- Running immediately: DMS immediately runs the task after you submit the task.
- Schedule: DMS runs the task at the specified point in time.
Transaction Control Specifies whether to enable transaction control.
- on: If an SQL statement fails to be executed, all the executed data manipulation language (DML) statements in the same transaction are rolled back. Data definition language (DDL) statements cannot be rolled back.
- off: The SQL statements are executed one by one. If an SQL statement fails to be executed, the task stops running. The executed statements are not rolled back.
Data Backup Specifies whether to back up data.
Note After you click Confirm Execution, DMS starts to run the task.
- on: DMS generates
INSERTscripts to back up the data that will be affected when
DELETEstatements are executed.
- off: No backup scripts are generated for the preceding data.
View the task progress and verify the schema as a DMS administrator
- Log on to the DMS console as a DMS administrator.
- In the left-side navigation pane, click the
POC_devinstance and double-click the
poc_devdatabase to go to the SQLConsole tab.
- On the SQLConsole tab, enter the following SQL statement to view the tables in the
current database and click Execute:
SHOW TABLES;Note When DMS performs the lock-free schema change, two temporary tables are generated.
- In the top navigation bar, choose to go to the Task tab.
- Click the task number of the task that you want to view. The Execution details dialog box appears.
- Click Progress in the Operation column.
- In the Lock-Free structure change dialog box, view the task progress.
- After the task is complete, verify the schema. The schema of the
big_tabletable is changed, and the temporary tables are deleted.