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. Register the three instances with DMS. When you register the instances, set the Control Mode parameter to Security Collaboration and set the Environment type parameter to Dev, Test, and Product based on the instance names. For more information about how to register 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, move the pointer over the More icon and choose System > Security Rules.
3. On the Security Rules tab, 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. In the left-side navigation pane of the Details dialog box, click the Schema Design tab.
5. On the Schema Design tab, find the rule named 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: Configure security rules for database instances

This section shows you 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 navigation pane.
3. In the shortcut menu that appears, 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, move the pointer over the More icon and choose Schemas > Schema Design.
3. Click Schema Design in the upper-right corner.
   
4. Set the parameters as required and click Submit.
   

   Parameter	Description
   Project Name	The name of the project. This facilitates subsequent management. In this example, the project is named Schema Design Test.
   Project description	The purpose or objective of the project. This reduces communication costs.
   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 that you want to apply. DMS automatically selects security rules based on the selected database.
   Change Stakeholder	The stakeholders of the changes. Click Add to add stakeholders. The 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.

   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 in the field 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 Info, Column Info, and Index Info tabs.
      • Basic Info tab
      • Column Info tab
      • Index Info tab
   4. Click Save. In the Precheck Result message, click Ignore the warning and continue to save.
   5. 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 OK.
   7. After you click OK, wait for 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. 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 a create script and publishes it 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 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.
      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 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.