Create shadow tables for synchronization - Data Management

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, ApsaraDB MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL V3.0, and MySQL databases from other sources
- SQL Server: ApsaraDB RDS for SQL Server, ApsaraDB MyBase for SQL Server, and SQL Server databases from other sources
- PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, ApsaraDB MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and PostgreSQL databases from other sources
- MariaDB: ApsaraDB RDS for MariaDB and MariaDB databases from other sources
- ApsaraDB for OceanBase in MySQL mode
- PolarDB for PostgreSQL (Compatible with 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 execution capability.
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

Log on to the DMS console V5.0.
In the top navigation bar, choose Database Development > Schema Change > Shadow Table Synchronization.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the console and choose All Features > Database Development > Schema Change > Shadow Table Synchronization.

On the Table Sync Tickets page, set the parameters that are described in the following table. Some parameters are described as follows:

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.

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.

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 * ?.