All Products
Search
Document Center

Change schemas without locking tables

Last Updated: May 29, 2020

This topic takes an ApsaraDB RDS for MySQL instance as an example to describe how to get started with Data Management Service (DMS) and change schemas without locking tables.

Procedure of enabling this feature

  1. Log on to the DMS console.
  2. Move the pointer over the plus sign (+) in the upper-left corner and select Add instance. The Add instance dialog box appears.
  3. On the Cloud tab, click MySQL.
  4. In the Basic Information/Advanced information step, set the parameters as required. The following table describes the parameters.
    Register an ApsaraDB instance
    Advanced information

    Section Parameter Description
    Basic Information Database Source The category of the data source. Set this parameter to Cloud.
    Database type The type of the ApsaraDB instance to be registered. Set this parameter to MySQL.
    Instance Area The region where the ApsaraDB instance is deployed.
    Entry mode The mode of registering the ApsaraDB instance. Valid values: Instance ID and Connection string address.
    Instance ID or Connection string address The instance ID or the endpoint that is used to connect to the ApsaraDB instance.
    Database account The username that you use to log on to the ApsaraDB instance.
    Database password The password that you use to log on to the ApsaraDB instance.
    Control Mode The control mode that is used to manage the instance in the DMS console. Set this parameter to Secure Collaboration. For more information, see Control modes.
    Security Rules The security rule set used to manage the ApsaraDB instance. This parameter is available when you set Control Mode to Secure Collaboration. Select mysql default from the drop-down list.
    Advanced Information Environment type The type of environment where the ApsaraDB instance is deployed.
    Instance Name The name that you specify for the instance in the DMS console.
    Enable DSQL Specifies whether to enable the cross-database query feature for the ApsaraDB instance. For more information, see Cross-database query.
    OnlineDDL Specifies whether to allow changing schemas without locking tables for the ApsaraDB instance and the specific method. Set this parameter to Open(DMS OnlineDDL first). For more information, see Change schemas without locking tables.
    DBA The DBA of the instance who is allowed to grant permissions to users.
    query timeout(s) The timeout interval of the statement that is used to query data. When the specified time interval is reached, the target statement run in the SQL editor is stopped to protect the database security.
    export timeout(s) The timeout interval of the statement that is used to export statistics. When the specified time interval is reached, the target statement run in the SQL editor is stopped to protect the database security.

    Note:

  5. In the Basic Information section, click Test connection in the lower-left corner. Wait until the connectivity test is passed.

    If the test fails, check the parameter values that you specify.

  6. Click Submit.

Procedure of using this feature

After you enable this feature, you can submit a ticket on the Normal Data Modify page in the Data Plans module to change schemas without locking tables.

  1. In the top navigation bar, choose Data Plans > Data Changes > Normal Data Modify.
  2. On the page that appears, set the following parameters to create a data change ticket:
    Normal Data Modify 1
    • Reason Category: the reason for the data change, which can help you find the ticket.
    • Business Background: the detailed reason for or goal of the data change, which can reduce communication costs.
    • Change Stakeholder: the stakeholders of the data change. The stakeholders can view the data change ticket and assist in submitting the ticket and performing the data change. Except for the DMS administrator and DBA, users irrelevant to the ticket are not allowed to view the ticket details.
    • Execution Method: specifies the way in which the data change is to be performed. Valid values:
    • After Audit Approved, Order Submitter Execute
    • After Audit Approved, Auto Execute
    • Last Auditor Execute
    • Database: the database on which you have the change permission. You cannot submit a data change ticket if you only have the permission to query databases or change data in tables.
    • Affected Rows: the number of rows in which data will be affected by the data change. You can execute the COUNT statement in the SQLConsole to query the statistics.
    • SQL Statements for Change: the executable SQL statements for changing data. DMS automatically checks whether the syntax of the SQL statements is correct when you submit the ticket. If the syntax is incorrect, you cannot submit the ticket. You can directly write SQL statements in the field or upload TXT or SQL files.
    • SQL Statements for Rollback: the executable SQL statements for rolling back the data change performed by the SQL statements for changing data. You can directly write SQL statements in the field or upload TXT or SQL files.
    • Attachments: You can upload images or files to add more information about the data change.
  3. Click Submit. Wait for the ticket to be prechecked. If the ticket fails the precheck, troubleshoot issues as prompted and submit the ticket again.
  4. After your ticket passes the precheck, click Submit for Approval in the Approval step. Wait for the ticket to be approved.

    If an error occurs indicating that certain SQL statements are not supported, modify the security rules used for the instance. For more information, see SQL Correct.

  5. After your ticket is approved, click Execute Change in the Execute step.

  6. Wait until the task is executed. To view the details of the task, follow these steps:

    1. In the top navigation bar, choose System Management > Task.
    2. On the Task page, click the ID of the target task.
    3. In the Execution details dialog box that appears, click Progress in the Operation column of the target execution.

      Then, you can view the details of the task, as shown in the following figure.

  7. After the task is executed, choose SQLConsole > Single Database query in the top navigation bar. On the page that appears, submit a ticket to check the updated schema of the database.