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
- Go to the DMS console V5.0.
- In the top navigation bar, click Database Development. In the left-side navigation pane, choose .
- Complete the Table/Database Synchronization Application wizard. The following table describes the parameters involved.
Parameter |
Description |
Source Database |
- 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.
- 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 |
- 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.
- 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.
|
- 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.
- Click Submit for Approval and wait for approval.
- Click Submit and Synchronize to Target Database.
- 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.