> For the complete documentation index, see [llms.txt](https://eric-zhang-seattle.gitbook.io/mess-around/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://eric-zhang-seattle.gitbook.io/mess-around/traditional-db/db_migration.md).

# DB migration

* [Flowchart](#flowchart)
* [Initial load phase](#initial-load-phase)
  * [mysqldump](#mysqldump)
    * [Best practices](#best-practices)
  * [XtraBackup](#xtrabackup)
* [Dual write phase](#dual-write-phase)
  * [Primary key](#primary-key)
    * [innodb\_autoinc\_lock\_mode parameter](#innodb_autoinc_lock_mode-parameter)
    * [Consistency](#consistency)
  * [Dual write flag in ORM/AOP](#dual-write-flag-in-ormaop)
    * [UpdateTime field based approach](#updatetime-field-based-approach)
      * [Flowchart](#flowchart-1)
      * [Soft delete on source table](#soft-delete-on-source-table)
    * [Binlog triggered](#binlog-triggered)

## Flowchart

* The motivation for introduce the third phase: In case something goes wrong on the target table, you could rollback to the original table.

![DB migration flowchart](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-16e19f804939753912f9d6a83acc71baa6604c21%2Fdb_migration_flowchart.png?alt=media)

## Initial load phase

* There are two typically used DB backup tools: mysqldump and XtraBackup.

### mysqldump

* Pros:
  * Free
  * Could backup the entire DB, including table structure and data
* Cons:
  * Backup time could be long

#### Best practices

* To increase speed when using mysqldump tool, there are a couple options:
  * Close the unique key check and external key check because source table already guarantees this.
  * Close binlog
  * Adjust the disk flush time of redo log by setting innodb\_flush\_log\_at\_trx\_commit = 0

### XtraBackup

* Pros:
  * Faster backup speed
  * Support incremental backup
  * Backup process won't interrupt executing transactions
* Cons:
  * Only applicable to InnoDB engine
  * Poor cross-platform compatibility.

## Dual write phase

### Primary key

#### innodb\_autoinc\_lock\_mode parameter

* "innodb\_autoinc\_lock\_mode" parameter determines how InnoDB generates primary key. All three cases rely on table-wise lock.
  * 0: The table lock will be released after insert statements are executed.
  * 1: The table lock release times depend on SQL statements:
    * For INSERT INTO VALUE / INSERT INTO VALUES, the primary key lock will be released immediately after primary keys are generated, instead of after SQL statements are executed.
    * For INSERT / SELECT, the primary key lock will be released after SQL statements are executed.
  * 2: The table lock will be released immediately after primary keys are generated.

#### Consistency

* When writing to the first table of dual write, return the primary key and use it in subsequent table.

![Primary key consistency](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-4c877f2525ff6662a5fd436a99cde0c66db484e3%2Fdb_migration_primarykey_consistency.png?alt=media)

### Dual write flag in ORM/AOP

* Typically there is a dual write flag inside the AOP/ORM.
* This flag controls the dual write mode.

![Dual write flag structure](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-dd12d88673d332f25a7178d1f5b505f1386b009c%2Fdb_migration_structure.png?alt=media)

#### UpdateTime field based approach

**Flowchart**

```java
for {
  // Execute the SQL query
  // SELECT * FROM xx WHERE update_time >= last_time
  rows := findUpdatedRows()

  for row in rows {
    // Use row primary key / id to find corresponding rows in target table
    tgtRow = findTgt(row.id)
    if row != tgtRow {
      // fix data
      fix()
    }
  }

  // Record the maximum timestamp of these records, use for next query. 
  last_time = maxUpdateTime(row)

  // Sleep for a while
  sleep(1s)
}
```

**Soft delete on source table**

* If using hard delete on source table, delete failures on target table won't be discovered.

![Hard delete problems](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-98f24e54022e8486d11f1ef36af9a7b4ed708991%2Fdb_migration_updatedTime_harddelete.png?alt=media)

* If we need to use hard delete on source table, there needs to be a reverse check from target table to source table.

![Hard delete with reverse check](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-d20fc3f0312098da6d44f79040d47f211ad568bb%2Fmigration_updatedTime_harddelete_reverseCheck.png?alt=media)

#### Binlog triggered

![Binlog triggered consistency](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-ad1828454bbebe8702a653c1d55d81ee1b72d1d7%2Fdb_migration_binlogTriggered_consistency.png?alt=media)