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 in various scenarios such as end-to-end stress testing.
- 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 Edition
- 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, 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.
- Log on to the DMS console V5.0.
- In the top navigation bar, click Database Development. In the left-side navigation pane, choose . Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose .
- On the Table/Database Synchronization Application page, 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 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 shadow tables after you submit the ticket. In this case, the tables are synchronized only once.
- Scheduled Synchronization: DMS synchronizes 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 expressions section of this topic.
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 project, such as the purpose or objective of the project.
- 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.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.
If you need to schedule the synchronization task to be run in a finer-grained 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.
|Field||Valid value||Supported special character||Description|
|Seconds||0 to 59||Hyphens (-), asterisks (*), and forward slashes (/)||N/A|
|Minutes||0 to 59||Hyphens (-), asterisks (*), and forward slashes (/)||N/A|
|Hours||0 to 23||Hyphens (-), asterisks (*), and forward slashes (/)||N/A|
|Days||1 to 31||Hyphens (-), asterisks (*), question marks (?), forward slashes (/), L, W, and C||N/A|
|Months||1 to 12||JAN‒DEC, hyphens (-), asterisks (*), and forward slashes (/)||N/A|
|Weeks||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.|
?). 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.
|*||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.||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:
|-||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.||You have 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.|
- 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 * ?.