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 scripts for schema synchronization. Then, you can run the scripts to synchronize the schemas of the destination database. This topic describes the schema synchronization feature and shows you how to synchronize schemas.

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 in the synchronization process.
  • The destination database does not contain tables to be synchronized. 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 synchronizes the schemas of the source database and destination database by adding or deleting table fields in the destination database to ensure consistent schemas.
    Warning If a field is deleted, the data of the field is also deleted. Proceed with caution.
  • During synchronization, tables with inconsistent schemas in the destination database are not deleted.

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.

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.

Prerequisites

  • Database instances are registered with DMS. For more information, see Register an ApsaraDB instance.
  • One or more of the following databases are used:
    • MySQL series: self-managed MySQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, AnalyticDB for MySQL, and ApsaraDB OceanBase for MySQL
    • SQL Server series: self-managed SQL Server and ApsaraDB RDS for SQL Server
    • PostgreSQL series: self-managed PostgreSQL, ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, and AnalyticDB for PostgreSQL
    • PolarDB-O
    • MariaDB
  • The security rule named Enable execution capability (if closed, other rules are invalid) is enabled for the destination database. For more information about this rule, 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.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Schemas > Schema Synchronization.
  3. On the Table/Database Synchronization Application tab, set the parameters that are described in the following table.
    Parameter Description
    Source Database The name of the source database for schema synchronization. After you select the source database, specify the schema version number of the database. The version number is optional. By default, the latest schema is used. For more information about the schema version number, see Manage schema versions.
    Note You must have the read permissions on the source database. For more information, see View owned permissions.
    Target Database The name of the destination database for schema synchronization.
    Note
    • You must have the write permissions on the destination database. For more information, see View owned permissions.
    • After you specify the schema version number of the destination database, DMS only compares the schemas of two databases.
    Synchronized Table The tables that you want to synchronize. Valid values:
    • Partial Tables: the names of the source and destination tables to be synchronized. You can click the plus icon to add multiple tables at a time. If you do not specify the names of destination tables, DMS will assume that the destination table names are the same as the source table names.
    • All Tables
    Whether to Ignore Error Specifies whether to skip errors 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 for synchronizing the schemas.
    • Ignore: If an error occurs when SQL statements are being executed, DMS skips the error and continues to execute subsequent SQL statements until all remaining statements are executed.
    Business Background(Remarks) The business background of the project, such as the purposes and objectives of the project.
  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.
    After you click Confirm Synchronization, DMS starts to execute the SQL statements to synchronize schemas. You can click Details to view the operations logs that contain detailed information such as the SQL statements, execution duration, and scheduling details.