Data Management (DMS) provides the schema synchronization feature. You can use this feature to compare the schemas of two databases or those of specific tables in the databases and generate SQL statements for schema synchronization. Then, you can execute the statements to synchronize the schemas to the destination database. You can use this feature to compare and synchronize the schemas of databases in different environments, such as databases in a production environment and a test environment, databases in different test environments, or databases in different production environments.

Prerequisites

  • The source and destination databases are of the following types:
    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL V3.0, and MySQL databases from other sources
    • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and SQL Server databases from other sources
    • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and PostgreSQL databases from other sources
    • MariaDB: ApsaraDB for MariaDB TX and MariaDB databases from other sources
    • ApsaraDB for OceanBase in MySQL mode
    • PolarDB for Oracle
  • The security rule named Enable execution capability (if closed, other rules are invalid) is enabled for the destination database. For more information, see Enable SQL-based synchronization.
    Note If this rule is disabled, the schema synchronization feature can only compare the schemas of two different databases but cannot execute SQL statements to synchronize the schemas.
  • The query permissions on the source database are granted to your account. For more information, see View owned permissions.
  • The permissions to alter tables in the destination database are granted to your account. For more information, see View owned permissions.

Usage notes

The schema synchronization feature can be used to synchronize the schemas of the source database to the destination database, whereas data is not synchronized during the synchronization process. Take note of the following two scenarios:

  • The destination database does not contain tables that have the same names as those to be synchronized in the source database. In this case, DMS creates those tables in the destination database.
  • The destination database contains tables that have the same names as those to be synchronized in the source database. In this case, DMS creates fields in or deletes fields from those tables in the destination database to ensure schema consistency. If a field is deleted, the data of the field is also deleted. Proceed with caution.
Note During the synchronization process, DMS does not delete a table in the destination database that is not consistent with any table in the source database.

Procedure

  1. Go to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Schema Change > Schema Synchronization.
  3. Complete the Table/Database Synchronization Application wizard. The following table describes the parameters involved.
    Parameter Description
    Source Database
    1. Select the source database for schema synchronization. You can enter a keyword of the database name in the Source Database field to search for the database.
    2. Optional: Select a schema version number of the database. By default, the latest schema is used. For more information about schema version numbers, see Manage schema versions.
    Target Database
    1. Select the destination database for schema synchronization. You can enter a keyword of the database name in the Target Database field to search for the database.
    2. Optional: Select a schema version number of the database. By default, the latest schema is used. For more information about schema version numbers, see Manage schema versions.
    Synchronized Table The tables that you want to synchronize. Valid values:
    • Partial Tables: the names of the source and destination tables to be synchronized. If you do not specify the names of destination tables, DMS assumes that the destination table names are the same as the source table names.
    • All Tables: All tables in the source database are synchronized.
    Whether to Ignore Error Specifies whether to skip errors that occur when SQL statements are being executed. Valid values:
    • Not Ignore: If an error occurs when SQL statements are being executed, DMS stops executing the current and subsequent SQL statements.
    • Ignore: If an error occurs when SQL statements are being executed, DMS skips the current SQL statement and continues to execute subsequent SQL statements until all remaining statements are executed.
    Business Background(Remarks) The business background of the synchronization operation, such as the purposes or objectives.
  4. Click Submit.
    DMS starts to analyze the schemas.
    Note If the schemas are changed during schema analysis, click Re-analyze in the Schema Analysis step.
  5. Click Submit for Approval and wait for approval.
  6. Click Submit and Synchronize to Target Database.
  7. Check the SQL statements to be executed and click Confirm Synchronization.
    Note The synchronization duration is affected by various factors such as the number of tables to be synchronized and the size of the task queue. We recommend that you synchronize schemas during off-peak hours.
    After you click Confirm Synchronization, DMS starts to execute the SQL statements to synchronize schemas. You can click Details to view the operation logs that contain detailed information such as the SQL statements, execution duration, and scheduling details.