Data Management (DMS) provides the shadow table synchronization feature to automatically create a shadow table based on the schema of a source table. DMS generates the name of the shadow table by attaching a prefix or suffix to the name of the source table. You can use this feature for end-to-end stress testing.

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 database in which you want to synchronize shadow tables. For more information about this rule, see Enable SQL-based synchronization.
    Note If this rule is disabled, the shadow table synchronization feature cannot execute SQL statements to synchronize the schemas of databases and tables.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Schemas > Shadow Table Synchronize.
  3. On the Table/Database Synchronization Application tab, set the parameters that are described in the following table.
    Parameter Description
    Source Database The database whose data is to be synchronized.
    Note You must have the read and write permissions on this database. For more information, see View owned permissions.
    Prefix / Suffix The prefix or suffix that is used to create a shadow table name. The name can be in the Prefix + Source table name format 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 one or more tables that you want to synchronize. Valid values:
    • Partial Tables
    • All Tables
    Synchronization Policy The policy that is used for shadow table synchronization. Valid values:
    • Synchronize Now: DMS immediately synchronizes the one or more shadow tables after you submit the ticket. In this case, the tables are synchronized only once.
    • Scheduled Synchronization: DMS synchronizes the one or more shadow tables at the specified time on a regular basis. You can use a crontab expression to schedule synchronization based on your requirements. The minimum interval for synchronization is 1 hour. Only the values of the Hours, Days, and Months fields can be specified. By default, the shadow tables start to be synchronized at 02:00 every day. For more information, see the Crontab expression section in this topic.
    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 shadow tables.
    • 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.

Crontab expression

If you need to schedule the synchronization task to be run in a more precise manner, you can use a crontab expression. The interval for running the task can be specified by using a combination of seconds, minutes, hours, days, weeks, or 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
Seconds 0‒59 Hyphens (-), asterisks (*), and forward slashes (/) N/A
Minutes 0‒59 Hyphens (-), asterisks (*), and forward slashes (/) N/A
Hours 0‒23 Hyphens (-), asterisks (*), and forward slashes (/) N/A
Days 1‒31 Hyphens (-), asterisks (*), question marks (?), forward slashes (/), L, W, and C N/A
Months 1‒12 JAN‒DEC, hyphens (-), asterisks (*), and forward slashes (/) N/A
Weeks 1‒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. Assume that you want to schedule the task to be run on the twentieth day of each month, regardless of what day of the week falls on that date. You can use the following crontab expression: 13 13 15 20 * ?. If you set the Days field to a specific date, such as 20, you must set the Weeks field to a question mark (?) instead of an asterisk (*). If you set the Weeks field to an asterisk (*), the task is run every day of the week instead of on the twentieth day of each month. The values of the Weeks and Days fields affect each other. The expression is invalid.
- Represents a range of values. If you set the Minutes field to 5-20, the task is run every minute during the period from the fifth minute to the twentieth minute of an 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 set the Minutes field to 5/20, the task is run at the fifth, twenty-fifth, and forty-fifth minutes of an hour.
, Represents a list of different values. If you set the Minutes field to 5,20, the task is run at the fifth and twentieth minutes of an hour.
L Represents the last day or week. This character can be used only in the Days or Weeks field. If you set the Weeks field to 5L, the task is run on the fifth day of the last week, which is the last Thursday of a 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. Assume that you set the Days field to 5W. If the fifth day of a month is Saturday, the task is run on the fourth day of the month, which is Friday, the nearest business day. If the fifth day of a month is Sunday, the task is run on the sixth day of the month, which is Monday. If the fifth day of a month is a business day, the task is run on that day. In addition, the nearest business day is limited to the current month.
LW Represents the last business day of a month, which is the last Friday. These two characters can be used together. 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 set the Weeks field to 4#2, the second Wednesday of a month is specified, where 4 indicates Wednesday and 2 indicates the second.
Crontab expression examples
  • 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 day 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 * ?.