Data Management (DMS) provides the shadow table synchronization feature to automatically create a shadow table in a database based on the schema of a source table in the database. DMS generates the name of the shadow table by attaching a prefix or suffix to the name of the source table. Synchronization on a regular basis is supported. You can use this feature in various scenarios such as end-to-end stress testing.

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 query and change permissions on the source database are granted to your account. For more information, see View owned permissions.
  • The security rule named Enable execution capability is enabled for the database in which you want to create shadow tables. For more information, see Enable SQL-based synchronization.
    Note If this rule is disabled, the schema synchronization feature can compare the schemas of different tables but cannot execute SQL statements to synchronize the schemas.

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 > Shadow Table Synchronization.
  3. On the Table/Database Synchronization Application page, set the parameters that are described in the following table.
    Parameter Description
    Source Database The source database in which you want to create shadow tables.
    Note You must have the query and change permissions on the source database. For more information, see View owned permissions.
    Prefix / Suffix The prefix or suffix that is used to generate a shadow table name. The name can be in the Prefix + Source table name or Source table name + Suffix format. You can use a custom prefix or suffix as needed. By default, the Prefix + Source table name format is used.

    Default shadow table name: __test_Source table name.

    Synchronized Table The tables that you want to synchronize. Valid values:
    • Partial Tables: DMS creates shadow tables in the source database for the tables that you select.
    • All Tables: DMS creates shadow tables in the source database for all tables in the database.
    Synchronization Policy The policy that is used for shadow table synchronization. Valid values:
    • Synchronize Now: DMS immediately performs synchronization after you submit the ticket. In this case, synchronization is performed only once.
    • Scheduled Synchronization: DMS performs synchronization at the specified time on a regular basis. You can use a crontab expression to schedule synchronization based on your requirements. For more information, see the Crontab expressions section of this topic.

      The minimum interval for synchronization is 1 hour. By default, shadow table synchronization starts at 02:00 every day.

    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 task, 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.
    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.

Crontab expressions

You can specify the interval for running the synchronization task by using a combination of seconds, minutes, hours, days, weeks, and months. A crontab expression is a string that consists of six fields. The six fields are separated by spaces. The following table describes the valid values of these fields.

Field Valid value Supported special character Description
Second 0 to 59 Hyphen (-), asterisks (*), and forward slashes (/) N/A
Minute 0 to 59 Hyphens (-), asterisks (*), and forward slashes (/) N/A
Hour 0 to 23 Hyphens (-), asterisks (*), and forward slashes (/) N/A
Day 1 to 31 Hyphens (-), asterisks (*), question marks (?), forward slashes (/), L, W, and C N/A
Month 1 to 12 JAN‒DEC, hyphens (-), asterisks (*), and forward slashes (/) N/A
Week 1 to 7 SUN‒SAT, hyphens (-), asterisks (*), question marks (?), forward slashes (/), L, C, and number signs (#) A value of 1 indicates Sunday and a value of 2 indicates Monday. In other words, the seven days of the week from Sunday to Saturday are indicated by values 1 to 7.
Note You can specify a value for only one of the Days and Weeks fields. Otherwise, the values of the two fields affect each other. If you have specified a value for the Days field, set the Weeks field to a question mark (?). Similarly, if you have specified a value for the Weeks field, set the Days field to a question mark (?).

The following table describes the special characters.

Character Description Example
* Represents all values of a field. If you set the Minutes field to an asterisk (*), the task is run every minute.
? Represents an arbitrary value of a field. This character can be used only in the Days or Weeks field because the values of these two fields affect each other. If you want to schedule the task to be run on the twentieth day of each month, you can use the following crontab expression: 13 13 15 20 * ?.
Note In this case, 20 is specified for the Days field, and thus you can specify only a question mark (?) rather than an asterisk (*) for the Weeks field.
- Represents a range of values. If you specify 5-20 for the Minutes field, the task is run every minute from the fifth to the twentieth minute in the specified hour.
/ Represents the start time and the interval, which are separated by this character. The task is run at the start time and then at the specified intervals. If you specify 5/20 for the Minutes field, the task is run at intervals of 20 minutes starting from the fifth minute in the specified hour. In this case, the task is run for the second time at the twenty-fifth minute in the specified hour.
, Represents a list of different values. If you specify 5,20 for the Minutes field, the task is run at the fifth and twentieth minutes in the specified hour.
L Represents the last day or week. This character can be used only in the Days or Weeks field. If you specify 5L for the Weeks field, the task is run on the last Thursday of the specified month.
W Represents the business days from Monday to Friday. This character can be used only in the Days field. The task is run on the nearest business day of the specified date. If you specify 5W for the Days field, one of the following situations occur:
  • The fifth day of the specified month is a business day. In this case, the task is run on the fifth day of this month.
  • The fifth day of the specified month is a Saturday. In this case, the task is run on the fourth day of this month, which is a Friday and the nearest business day.
  • The fifth day of the specified month is a Sunday. In this case, the task is run on the sixth day of this month, which is a Monday and the nearest business day.
LW Represents the last business day of a month. N/A
# Represents the N-th occurrence of a day of the week in a month. This character can be used only in the Weeks field. If you specify 4#2 for the Weeks field, the task is run on the Wednesday of the second week in the specified month.
  • The number 4 indicates Wednesday.
  • The number 2 indicates the second week in the specified month.
Sample crontab expressions:
  • To schedule the task to be run at 23:00 every Saturday and Sunday, use the following crontab expression: 0 0 23 ? * 7,1.
  • To schedule the task to be run at 09:30 on the fifth, fifteenth, and twenty-fifth days of each month, use the following crontab expression: 0 30 9 5,15,25 * ?.
  • To schedule the task to be run at 00:00 every two days, use the following crontab expression: 0 0 0 */2 * ?.