Introduction

With the emerging of cloud computing, more and more enterprises migrate the databases running on local servers or other virtual servers to the cloud, considering the stability and operation and maintenance costs of databases. We recommend that you use DTS for migration. In the following, the DTS migration plan is introduced in detail according to the steps of migration assessment, migration preparation, migration operation, business switching, verification, etc. in combination with cases.

Advantages of DTS Migration Plan

• The cost of manual operation is reduced, and the problem of data migration caused by human misoperation is avoided.
• In the case of network problems, the costs of intermediate point finding and re-migration are avoided, which can resume from break-point and ensure the controllable migration progress.
• DTS solves the problem of connectivity across networks with lower cost and a more transparent user migration process.

DTS Non-stop Migration Steps

Through real-time synchronization, the user migration process does not affect the normal business and can realize the second-level shutdown switching. The specific steps are as follows:

• Authorize the DTS server at the source instance entry, allowing the DTS service to read data and pull increments from the source database;

• The source instance needs to enable binlog in ROW format so that incremental data can be synchronized in a real-time manner (supported in versions of 5.1 and above);

  **Notice:** If the source portal uses the slave database with ROW format enabled as the synchronization source, the synchronization thread of the slave database, after dynamic modification of the binlog format, needs to be restarted for the modified binlog format to take effect .

• Carry out "Structure plus Full plus Incremental Data Migration" using DTS. For specific operation steps, see Data Migration.
• Stop application writing. And, after the data of the new database is consistent with that of the old master database, modify the database access address of the application and start writing.
• End the DTS migration task, and complete the cloud migration.

Migration Plan

As one of the key components in the application, migrating the database to a new platform is a very big change, which may affect the function, stability, and performance of the applications, so sufficient investigation and testing must be performed before migration. The migration stages include the following:

Migration Assessment Stage

Before data migration, the impacts on business and application before and after migration need to be assessed, including:

• Does the version and new features of the database affect normal functions after migrating to the cloud? (If myisam tables are not allowed by RDS, they are converted to innodb tables automatically)
• Do you need to modify the code to adapt to the environment on the cloud?
• Will the capacity before and after migration increase the response time, causing a decrease in business performance? (Performance test is required to ensure that response time has little different)
• The RDS synchronizes across regions through dedicated lines, which requires to consider the write delay in multiple regions.

Migration Preparation Stage

The migration preparation mainly includes 4 checks before migration: Authorization check, BINLOG format check, whether the datasheet has a primary key, and sql_mode check.

1. Add authorization to DTS server in the source database.

   • For the exit IP of DTS server varies, all network segments need to be authorized for cloud migration of users on external networks.

   • For the cloud migration on a private network database, you only need to authorize several fixed IPs. For specific authorized IPs, you need to consult DTS maintenance personnel. The authorization statement is as follows: `GRANT SELECT, LOCK TABLES, SUPER,REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW on . to dts_trans@ '%' identified by 'your_password"; the privileges required by different migration types are described as follows:

     • Structure or full migration: SELECT, LOCK TABLES, SUPER, SHOW VIEW (the LOCK TABLES privilege is to migrate tables without a primary key, and the SUPER privilege is to check whether the source database binlog exists).
     • Incremental migration: REPLICATION SLAVE, and REPLICATION CLIENT.
     • If the data verification function is required, you need to authorize the CREATE, INSERT, UPDATE, DELETE, and DROP privileges to baidudts system database, otherwise, you cannot use advanced functions such as data verification etc. For data verification authorization commands, please see `GRANT CREATE,INSERT,UPDATE,DELETE,DROP on baidu_dts.* to _dts_trans@'%' identified by 'your_password'`.
   • After authorization, please check whether the authorization takes effect. Execute command mysql -h (Source Database ip) -P (Source Database Port) -u (User) -p (Password), and connect mysql with account and password. After successful login, execute `SHOW GRANTS FOR dts_trans@' %" to view the account privilege.

   

2. Check the BINLOG format of the source database.

   • First, execute command mysql -h (Source Database ip) -P (Source Database Port) -u (User) -p (Password) to see whether the BINLOG of the source database is enabled.
   • After successful login, execute `show variables like 'log_bin" to see whether log_bin is enabled.
   • Execute show master logs to see whether binlog is being written in a real-time manner.
   • Finally, execute `show variables like 'binlog_format" to check whether the binlog format is changed to ROW format.

   The normal check results are as follows:

   

   Notice:
   If a slave database of MYSQL is selected as the source database and binlog_format (set global binlog_format='ROW' ;) is modified dynamically, you must restart the synchronous thread (stop slave; start slave;), to make the ROW format take effect.
   If the source database is a single instance of the self-built database, after modifying the binlog format, you need to check and kill the long connection of the source database, to ensure that the binlog format takes effect.

   The best way is to execute mysql/bin/mysqlbinlog mysql-bin.000040 and check whether the latest binlog content is changed to ROW format. If similar content (non-sql readable content) as shown in the following red box appears, it indicates the modification is successful.

   

3. Check whether the tables in the database to migrate a primary key.

   In order to ensure the smooth migration process, we require that the migrated tables must have a primary so that interruptions caused by network or other reasons during the full migration can be resumed from the breakpoint to ensure the controllable migration progress. The inspection method is as follows:

   • Execute mysql -h (Source Database ip) -P (Source Database Port) -u (User) -p (Password) command to connect the database.
   • After successful login, execute select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA=' db1' and TABLE_NAME='t1' and CONSTRAINT_TYPE='PRIMARY KEY" orshow create table db1.t1` to check whether there is a primary key.

4. Check sql_mode configuration

   For the risk of potential data inconsistency of sql_mode in some cases, sql_mode needs to be set to one or several items in the following sets.

   Legal sql_mode sets ("", "REAL_AS_FLOAT", "PIPES_AS_CONCAT", "ANSI_QUOTES", "IGNORE_SPACE", "ONLY_FULL_GROUP_BY", "NO_UNSIGNED_SUBTRACTION", "NO_DIR_IN_CREATE", "POSTGRESQL", "ORACLE", "MSSQL", "DB2", "MAXDB", "NO_KEY_OPTIONS", "NO_TABLE_OPTIONS", "NO_FIELD_OPTIONS", "MYSQL323", "MYSQL40", "ANSI", "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", "STRICT_TRANS_TABLES", "STRICT_ALL_TABLES", "NO_ZERO_IN_DATE", "NO_ZERO_DATE", "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", "TRADITIONAL", "HIGH_NOT_PRECEDENCE", "NO_ENGINE_SUBSTITUTION", "PAD_CHAR_TO_FULL_LENGTH", "NO_AUTO_CREATE_USER")

   We recommend to execute the command `set global sql_mode=' STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" to set the mode after successful login.

Migration Operation Stage

1. To create a database instance in Baidu AI Cloud's RDS service, please see "Log in and Create RDS for MySQL Instance" (GettingStarted.html#Log in and Create RDS for MySQL Instance).
2. Enter the DTS product page and click Create Migration Task.

3. Fill in the source database and the target database information.

4. Select the database table to migrate.

5. After the check results are approved, start the migration.

6. View the task progress and the incremental delay.

Details of structural migration progress:

Details of full migration progress:

Details of incremental migration progress:

Proceed to the incremental data migration stage, and if the delay is 0, it indicates that synchronization is reached, and the next step is to cut off traffic.

Business Switching Stage

1. All writes still go to the master database of the source database, and data is synchronized in a real-time manner through DTS.
2. Deploy the business application to the cloud server. In this case, the read-write database can also read and write the source database.
3. The process of cloud migration of database traffic: Read test -> read small traffic for a single region -> switch to cloud at the stage of read percentage for the single region -> switch to cloud at the stage of read percentage for other regions -> switch to cloud for full write traffic.
4. For the "switch to cloud for full write traffic" progress, you need to select a proper traffic slack time, stop writing (there's MQ at the business level, avoiding loss), and check that the data is synchronized to the cloud in a real-time manner, stop the DTS task, and switch the write logic to cloud, and start the business.
5. If the rollback is required, you need to create a DTS incremental synchronization task to synchronize incremental data from RDS to the master database of the source database. When rollback is required, traffic cutback is performed.

Verification Stage

The business needs to verify whether the normal functions of the application are available and observe the business application error logs. Observe the RDS database load. If it is found that the capacity is insufficient, expand the capacity timely to supplement the read-only copy to complete the migration. At this point, the cloud migration process of the database is complete. If data verification is required before switching traffic, please contact DTS personnel to initiate the data verification process to further ensure the data consistency before and after the cloud migration.