Using DRS to Vertically Split Databases
Description
If an RDS for MySQL instance is overloaded, you can vertically split a database or table from the instance to another instance. For example, if an RDS for MySQL instance has databases db1 and db2, to reduce the load on the instance, you can vertically split db2 to another instance.
- A dual-write solution is a common data migration solution. It ensures no downtime during migration and allows rollback if needed. However, this solution may require application changes and affect performance.
- A two-way synchronization solution sets up a reverse synchronization link during migration. Data is written to the destination instance and also synchronized back to the source instance, allowing for workload rollback if needed. However, this process requires downtime, so only use this solution during off-peak hours.
This topic describes how to split data using dual-write and two-way synchronization solutions. This topic uses two RDS for MySQL instances as the source and destination.
Prerequisites
- You have an RDS for MySQL instance with active workloads as the source instance.
- You have another empty, idle RDS for MySQL instance as the destination instance. The database account permissions of the destination instance must be the same as those of the source instance.
Precautions
Performing a two-way synchronization solution will cause a brief interruption, so only use this solution during off-peak hours.
Dual-Write Solution
- Create a migration task.
- Go to the Create Migration Task page.
- Specify Region, Project, Task Name, and Description, configure replication instance details, and click Create Now.
- Source instance: instance test1, which has active workloads
- Destination instance: empty instance test2, which is idle
- Migration object: database test_db
For details about how to create a migration task, see Creating a Migration Task.
- Query the migration progress.
- After the migration task is submitted, click the task name on the Online Migration Management page.
- On the displayed page, click Migration Progress.
Wait until the migration progress changes to incremental migration and the delay is less than 5 seconds.
- Compare migration items.
On the Migration Comparison page, compare the data in the source and destination versions of database test_db. For more configurations, see Data Comparison.
If the data is consistent, stop the migration task.
- Use your application to write data to database test_db of both the source and destination instances.
- Log in to the source and destination instances separately, and run the command below on test_db to query session details. Ensure that there are new sessions for write operations.
SHOW processlist;
- After the workloads are stable for a period of time and all functions are normal, delete test_db from the source instance.
Two-Way Synchronization Solution
- Create a migration task.
- Go to the Create Migration Task page.
- Specify Region, Project, Task Name, and Description, configure replication instance details, and click Create Now.
- Source instance: instance test1, which has active workloads
- Destination instance: empty instance test2, which is idle
- Migration object: database test_db
For details about how to create a migration task, see Creating a Migration Task.
- Query the migration progress.
- After the migration task is submitted, click the task name on the Online Migration Management page.
- On the displayed page, click Migration Progress.
Wait until the migration progress changes to incremental migration and the delay is less than 5 seconds.
- Compare migration items.
On the Migration Comparison page, compare the data in the source and destination versions of database test_db. For more configurations, see Data Comparison.
If the data is consistent, stop the migration task.
- Disconnect your application from the source database test_db.
- Log in to the source instance and run the command below on test_db to query session details. Ensure that there are no new sessions for write operations.
SHOW processlist;
- Create a reverse synchronization task.
The reverse synchronization task created in this step is used for data rollback. If an exception occurs after workloads are resumed, you can switch the workloads over to the source database.
- On the Data Synchronization Management page, click Create Synchronization Task.
- On the displayed page, specify Region, Project, Task Name, and Description, configure synchronization instance details, and click Create Now.
- Source instance: test2
- Destination instance: test1
- Synchronization mode: incremental
Run the command below to query the position of the destination instance and set the start point for incremental synchronization to this position. In this way, the incremental data generated in the destination database test_db can be migrated to the source database test_db.
SHOW processlist;
For details about data synchronization, see From MySQL to MySQL.
- Disconnect the source database test_db from the application and switch the workloads over to the destination database test_db to restore services.
- Log in to the destination instance and run the command below on test_db to query session details. Ensure that there are new sessions for write operations.
SHOW processlist;
- After the workloads are stable for a period of time and all functions are normal, delete test_db from the source instance and stop the reverse data synchronization task.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot