Use the schema design feature of DMS to publish schemas

Preparations

1. Create three ApsaraDB RDS for MySQL instances: Schema_Design-dev, Schema_Design-test, and Schema_Design-product. For more information, see Create an ApsaraDB RDS for MySQL instance.
2. Add the three instances to DMS. When you add the instances, set the Control Mode parameter to Security Collaboration and set the Environment type parameter to Dev, Test, and Product based on their instance names. For more information about how to add an ApsaraDB instance, see Register an ApsaraDB instance. For more information about the environment type of a database instance, see Change the environment type of an instance.
3. Create a database for each database instance: dev_database, test_database, and product_database.

Step 1: Configure R&D rules

In DMS, the default R&D process consists of schema design and publishing to the production environment. This section describes how to create security rules and configure the R&D process of schema design, testing, and publishing to the production environment.

1. Log on to the DMS console.
2. In the top navigation bar, choose System Management > Security > Security Rules.
   
3. On the Security Rules page, click Create Rule Set in the upper-left corner. In the Create Rule Set dialog box, set the parameter as required.
   Note You can customize the rules and rule set name. In this example, the rule set is named SD-DT.

   
4. After you create a rule set, the Details page appears. In the left-side navigation pane of the Details page, click Schema Design.
5. On the Schema Design tab, find R & D process and click Edit in the Actions column.
   
6. In the Change Configuration Item dialog box, set the following parameters:
   1. Change the name of the second node to Testing and change the database environment to test.
   2. Add a third node, set the name of the third node to Publishing, and then set the database environment to product. In addition, set the node as an anchor.
      Note Anchor: the point that is used to stop the R&D process. After you set a node as the anchor and complete the node for a ticket, the ticket enters the Published state and cannot be edited.
   The following figure shows the Change Configuration Item dialog box.
   
7. Click Submit.

Step 2: Set security rules for database instances

This section describes how to set the control mode of the Schema_Design-dev database instance whose environment type is dev to Security Collaboration and select the SD-DT rule set that is created in Step 1.

1. Log on to the DMS console.
2. On the homepage of the DMS console, right-click the Schema_Design-dev database instance in the left-side instance list.
3. From the shortcut menu, choose Control Mode > Security Collaboration > SD-DT.
   

Step 3: Design, test, and publish a schema

This section describes how to use the configured R&D process to design, test, and publish a schema for a database.

1. Log on to the DMS console.
2. In the top navigation bar, choose Schemas > Schema Design.
   
3. On the Schema Design page, click Schema Design in the upper-right corner.
   
4. Configure a schema design ticket and click Create Ticket.
   

   Parameter	Description
   Project Name	The name of the project. This facilitates subsequent management. In this example, the project is named Schema Design Test.
   Business Background	The purpose or objective of the project. This helps reduce the cost of communication.
   Change Base Database	The base database to which changes are made. Select the dev_database database from the drop-down list. In Step 2, the SD-DT rule set is selected for the Schema_Design-dev database instance to which the dev_database database belongs. Based on the configured R&D process in the SD-DT rule set, DMS applies the R&D process of schema design, testing, and publishing to the production environment. Note DMS applies the R&D process that is configured for the selected base database to the schema design ticket.
   Security Rules	The security rules to be applied. DMS automatically selects security rules based on the selected base database.
   Change Stakeholder	The stakeholders of changes. Click Add to add stakeholders who can view the ticket and assist in performing the changes. Except for DMS administrators and database administrators (DBAs), users that are irrelevant to the ticket cannot view the ticket details.

   After you create a ticket, the Project Homepage tab appears. The following figure shows the five nodes of the project.
5. Configure the schema design node.
   1. Click Import Table Creation Statements.
   2. In the Import Table Creation Statements dialog box, enter the following statement and click OK.

      CREATE TABLE `teacher` (
          `teacher_id` varchar(20) NOT NULL COMMENT 'Teacher ID',
          `teacher_name` varchar(20) NULL COMMENT 'Teacher name',
          `phonenumber` varchar(20) NULL COMMENT 'Teacher phone number',
          PRIMARY KEY (`teacher_id`)
      ) DEFAULT CHARACTER SET=utf8 COMMENT='Teacher table';

   3. On the Create Physical Table tab, confirm the table information on the Basic Information, Field, and Index tabs.
      • Basic Information tab
      • Field tab
      • Index tab
   4. Click Save. In the Precheck Result message, click Confirm Changes and Submit to Save.
   5. On the Project Homepage tab, click Perform Changes to Base Database.
   6. In the Perform Changes to Base Database dialog box, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statement is correct, click Submit for Execution.
   7. After you click Submit for Execution, wait for the approval. After the execution is approved, DMS writes the new schema to the dev_database database.
   8. Test the dev_database database. You can insert data in the SQLConsole of the dev_database database or submit a data change ticket. For more information, see Manage a database on the SQLConsole tab and Common data change. After the schema design node is verified, you can go to the next node.

      In this example, use the following sample code to insert test data:

      insert into teacher
      (teacher_id, teacher_name, phonenumber)
      values
      ('t001', 'teacher001', '1380000001'),
      ('t002', 'teacher002', '1380000002'),
      ('t003', 'teacher003', '1380000003');

   9. On the Project Homepage tab, click Go to Next Node.
      Note Before you click Go to Next Node, you can work with your colleagues to change the schema of the dev_database database multiple times and verify the changes. After you click Go to Next Node, DMS generates and publishes a create script to the next node.
      
   10. In the Go to Next Node message, click Go to Next Node.
6. Configure the testing node.
   1. On the Project Homepage tab, click Perform Changes to Target Database.
   2. In the Perform Changes to Target Database dialog box, select the test_database database, set the Execution Strategy parameter to Execute Now, and then click Submit for Execution.
      Note After you click Submit for Execution, wait for the approval. After the execution is approved, DMS writes the changed schema to the test_database database.
   3. Test the test_database database. You can insert data in the SQLConsole of the test_database database or submit a data change ticket. For more information, see Manage a database on the SQLConsole tab and Common data change.
      Note After the testing node is verified, you can go to the next node.
   4. On the Project Homepage tab, click Go to Next Node.
   5. In the Go to Next Node message, click Go to Next Node.
7. Configure the node of publishing to the production environment.
   1. On the Project Homepage tab, click Perform Changes to Target Database.
   2. In the Perform Changes to Target Database dialog box, select the product_database database, set the Execution Strategy parameter to Execute Now, and then click Submit for Execution.

      After you click Submit for Execution, wait for the approval. After the execution is approved, DMS writes the changed schema to the product_database database. You can also write data to the product_database database.

   3. On the Project Homepage tab, click Go to Next Node.
   4. In the Go to Next Node message, click Go to Next Node.

      Then, the R&D process ends and the ticket is closed.