Data Management:Database management

Prerequisites

Before you begin, ensure that you have:

• A system role of Administrator, Database Administrator (DBA), or Regular User (Instance Owner)

• A database account with permission to create databases (required for create operations only)

MySQL database management

DMS supports the following MySQL-compatible engines: MySQL, MariaDB, and AnalyticDB for MySQL.

1. Log on to the DMS console V5.0.

2. In the Instance List on the left, right-click the target instance and select Database management.

3. Perform one of the following operations: Create a database Modify a database Click Edit next to the database, then update the Character set or Validation rules. Delete a database Click Delete next to the database, then click Confirm in the dialog.

   1. Click Create DB.

   2. Enter the Database Name, then select a Character set (CHARACTER SET) and Validation rules (COLLATE). > Note: If the instance is in Security Collaboration mode, configure a security rule that permits the CREATE DATABASE command before proceeding. For details, see SQL Correct.

   3. Click OK.

   If the instance is in Security Collaboration mode, configure a security rule that permits the DROP DATABASE command before proceeding.

MongoDB database management

1. Log on to the DMS console V5.0.

2. In the Instance List on the left, right-click the target instance and select Database management.

3. Perform one of the following operations: Create a database Delete a database Click Delete next to the database, then click Confirm.

   1. Click Create DB.

   2. Enter the Database Name and Collection Name (COLLECTION). > Note: When you create a database, you must also create a collection. If you leave Collection Name blank, DMS creates a collection named test by default.

   3. Click OK.

PostgreSQL database management

DMS supports the following PostgreSQL-compatible engines: PostgreSQL and AnalyticDB for PostgreSQL.

For PostgreSQL, DMS supports managing both databases and schemas.

1. Log on to the DMS console V5.0.

2. Navigate to the target:

   • To manage databases: In the Instance List on the left, right-click the target instance and select Database management.

   • To manage schemas: Expand the target instance in the Instance List, right-click the target database, and select Schema Management.

3. Perform one of the following operations: Create a database Modify a database Click Edit next to the database, then adjust the Connection Limit or Table Space. Delete a database Click Delete next to the database, then click Confirm. Create a schema Modify a schema Click Edit next to the schema, then update the Schema Name, Owner, or Remarks. Delete a schema Click Delete next to the schema, then click Confirm.

   1. Click Create Schema.

   2. Configure the schema settings:

      Field	SQL parameter	Description
      Schema Name	SCHEMA_NAME	The name of the new schema
      Owner	SCHEMA_ELEMENT	The owner of the schema. Defaults to the user running the command.
      Remarks	COMMENT	An optional description of the schema.

   3. Click OK.

Next steps

• To control which users can access your databases, set up database account permissions. For details, see Manage database account permissions.

  DMS supports account management only for MySQL, PostgreSQL, and MongoDB engines.

• If a database you created does not appear in the Instance List, right-click the instance name and select Refresh/Sync Dictionary.

FAQ

• Why do I get "Access denied for user 'example_user'@'%' to database 'example_db'" when creating a database?

  The database account doesn't have permission to create databases. Either grant the necessary permissions to the account in your instance console, or log in to DMS with a more privileged account. For details, see Modify account permissions.

• Why did I lose permissions after changing the IP address of a VPC-connected instance?

  A: This occurs because of how DMS handles the reuse of IP addresses.

  • Assume a deleted instance, Instance A, exists in the system with the host IP address IP_X.

  • If you change the host of your current active instance, Instance B, to IP_X, DMS treats this as a restoration of Instance A.

  • As a result, DMS performs the following actions:

    • Marks Instance B as "deleted".

    • Restores and reactivates the original Instance A.

  Because the active instance is now Instance A, its DMS Instance ID is different from that of Instance B. Since permissions are bound to the Instance ID, the permissions associated with Instance B are effectively lost.

• Q: Does the DMS Instance ID change after I modify the IP address of a VPC-connected instance?

  A: Whether the Instance ID changes depends on the history of the new IP address. There are two scenarios:

  • New IP has never been registered with DMS: DMS updates the IP for the current instance. The instance ID stays the same, and permissions are not affected.

  • New IP was previously used by a deleted instance: DMS restores the historical instance and deletes the current one. The active instance ID changes to the historical instance's ID, and the original permissions are lost.