"Project experience" Data Migration summary

In the early stage of product iteration or during the period of system reconfiguration, the adjustment of business model brings the change of data structure, and the migration is unavoidable. Good data migration needs to be well considered, and ready to do a good job, otherwise, if there is inconsistent data problems, high error correction costs, while the core business data errors, will cause customer/business party complaints, the team will also bear great pressure.

This paper combines the data migration process of a recent actual project, tells the step-over pits, plus some of his own thinking of some of the methodology, and finally gives a sample of data migration script

Goal

Ensure that new clients are able to access the new business model as they would normally query before data, such as orders, and no data inconsistencies occur.

Principle

• Impact controllable-only changes to the data that need to be migrated can not affect other data; No downtime is allowed to migrate data, so the shorter the migration window the better, the less data problems caused by user behavior during the Migration window period;
• Fallback-Once you find a problem with the migrated data, you can fall back to the previous data state;
• Traceability-there are problems, can have log or backup data can be checked, database Binlog, migration program log can be used as a basis;
• Testable-The migration scenario must be testable, and to be testable, the migration scenario must be a general-purpose scenario.

Ideas

• Backup first, then migrate;
• Data alignment is required after migration to ensure data consistency;
• If there is a problem, consider whether to make a fallback "not all scenarios can be returned directly";
• If the traffic is large, consider stopping the service migration to avoid conflicts between user behavior and data migration.

Steps

• Backup-To back up the data to be migrated to the Bak table, any data that will be modified during the migration should be backed up, and any data used in the insert scene in front of the original data should be backed up;
• Migration--The Migration script/program only operates on the target data in the Bak table;
• Verification-after the migration is completed, data reconciliation is required to ensure data consistency;
• Fallback-The fallback script also operates only on the target data in the Bak table, and behaves as opposed to the migration script behavior.

Select Database script for the scenario

Very suitable for small data volumes, simple business scenarios, simple and lightweight, and data migration through SQL scripts. But here are a few points to consider:

1. Can you write generic SQL? If you cannot give up, the principle of ' testable ' is destroyed because different scripts are prepared for different environments;
2. Is the amount of data being manipulated too much? If too many (usually more than 10,000), it may cause the script commit to time out;
3. Is the business scenario complex? If there are too many tables involved (more than 3 sheets), the execution of the script is in order, at this time the risk will be greatly increased by the man-made guarantee;
4. Is the SQL Execution Tool/environment consistent between the test environment and the on-line environment? If there are inconsistencies (many companies have DBA tools that restrict syntax and formatting, such as the inability to break lines, the inability to have half-width semicolons in comments, etc.), the principle of ' testable ' can also be broken;
5. is the online SQL execution process lengthy? If the process is lengthy (the company's process may require the approval of the TL and DBA, the DBA as a third-party resource is not controllable), and the script is many, it will lengthen the window period of data migration, the business risk is greatly increased, undermining the ' influence controllable ' principle.

Migrating programs

Contrary to the ' database scripting ' approach, the written ' migration program ' avoids these drawbacks and is better suited to scenarios where business scenarios are complex and data volumes are large.

Scenario Comparison

Item	Script	Program	Note
Versatility	Not exactly	Completely	Such as: When relying on third-party data, the script cannot be generalized
Complex scenario Support	Not suitable for	For	Complex business scenario scripts do not fit, such as circular calls, third-party system data, multi-table dependencies, etc.
Big Data Volume support	Not suitable for	For	Large data volumes can cause script submissions to time out, typically more than 10,000 scripts are not appropriate, and most DBA tools often limit the amount of data that is manipulated
Development costs increase with complexity	Index	Linear

Both ' Database scripting ' and ' migration program ' are required to follow the ' principles ' and steps above.

Thinking

What is the sequencing of data migration and application publishing?

In the case of large volume of business, data migration process, the data is modified by user behavior how to do?

Migration failed, under what circumstances do fallback, what circumstances can not do fallback?

The hole that I stepped on

In the Transfer 2.0 project, the pre-data migration of the business complexity of the estimated insufficient, the choice of ' database script ' way, stepped on a lot of pits:

1. SQL scripts passed in the test environment are not directly executed on-line, because the online database tools are more restrictive in SQL format and content validation (no line breaks are allowed);
2. During the testing process, it is necessary to realize the scenario of generating multiple coupon codes for an order cycle, although it is implemented indirectly through SQL, but the complexity is high and the development cost is increased.
3. Due to the large number of SQL and the high complexity (for example, using Insert Select), the Data Migration window is stretched by two-step approval by TL and DBA.

Migration Script Sample

Business Scenario: Update order Status to 4 for order Status 2 and 3 in order form

Backup

Create Table  like Order ; Insert  into Select *  from Order where inch (2,3);

Note: Order is a business table, Order_bak is a backup table, and the goal is to update orders with an order status of 3 to 4. Migration

Update Order Set = 4 where inch (Select from Order_bak);

NOTE: If you operate directly on the order's original table, it cannot be rolled back once the error is made.

Check

Select Count (*as fromorderwhere!=4 and in ( Select  from Order_bak);

NOTE: If cont > 1 You need to consider whether data migration is successful

Rollbak

Update Order Inner Join  on = Set = bak.status;

Note: If there is a user behavior change status state before and after the migration, it can not be directly rollback, need specific analysis; it is not possible, and there are many such data, consider the outage service migration.