## Module 3: Migrate Laravel Application from Aurora (MySQL) to TiDB Cloud

In this module, you will complete the migration of table definitions and data from a Aurora (MySQL) database to the TiDB Cloud Starter database.

### Step 1. Update the TiDB Cloud Starter connection parameters in the `.env` file

You have already authorized your TiDB Cloud Starter when starting the lab, its connection parameters are already saved in the `.env` file. Now we need to update the `.env` file to use the TiDB Cloud Starter connection parameters.

+ Do the following changes to the `.env` file:
  - Delete the local Aurora (MySQL) connection parameters
  - Uncomment the tidb cloud cluster connection parameters
  - Save the file

  ```properties
  # tidb cloud cluster
  DB_CONNECTION=mysql
  DB_PORT=${LAB:SERVERLESS_CLUSTER_PORT}
  DB_HOST=${LAB:SERVERLESS_CLUSTER_HOST}
  DB_USERNAME='${LAB:SERVERLESS_CLUSTER_USERNAME}'
  DB_PASSWORD='${LAB:SERVERLESS_CLUSTER_PASSWORD}'
  DB_DATABASE=${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  MYSQL_ATTR_SSL_CA="/etc/ssl/certs/ca-certificates.crt"
  ...
  ```

### Step 2. Prepare the table definitions in TiDB Cloud

+ In the VS Code terminal, use the `php artisan migrate` command to create the tables needed in TiDB Cloud.

   ```bash
   php artisan migrate
   ```

   ```output
   INFO  Preparing database.

   Creating migration table ........................................................................................................... 843.87ms DONE

   INFO  Running migrations.

   0001_01_01_000000_create_users_table .................................................................................................... 15s DONE
   0001_01_01_000001_create_cache_table ..................................................................................................... 1s DONE
   0001_01_01_000002_create_jobs_table ...................................................................................................... 8s DONE
   2025_12_20_112907_create_transactions_table .............................................................................................. 7s DONE
   2025_12_20_120002_create_products_table .................................................................................................. 3s DONE
   2026_01_03_040247_create_product_type_table ........................................................................................ 547.59ms DONE
   2026_01_03_040331_create_pay_types_table ........................................................................................... 651.96ms DONE
   ```

### Step 3. Modify the table's `AUTO_INCREMENT` attribute to `AUTO_RANDOM` to prevent data hotspots

+ In the VS Code terminal, connect to TiDB Cloud by the Aurora (MySQL) client.

   ```bash
   mysql --comments -u ${LAB:SERVERLESS_CLUSTER_USERNAME} \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl \
   --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   --ssl-verify-server-cert \
   -p${LAB:SERVERLESS_CLUSTER_PASSWORD} \
   ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
   ```

   ```output
   Your MySQL connection id is 61317775
   Server version: 8.0.11-TiDB-v7.5.2-serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]>
   ```

+ In the VS Code terminal, use the MySQL client to view the definitions of the tables `shop.products` and `shop.transactions`. VS Code will pop up a window asking "Are you sure you want to paste 2 lines of text into the terminal?", please click "Paste".

   ![204_3_3_1.png 85%](https://lab-static.pingcap.com/images/2026/2/4/e5c082347f448be9f8db4c13b4952a204feb5346.png)

   ```sql
   SHOW CREATE TABLE products\G
   SHOW CREATE TABLE transactions\G
   ```

   ```output
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> SHOW CREATE TABLE products\G
   *************************** 1. row ***************************
         Table: products
   Create Table: CREATE TABLE `products` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `status` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'C',
   `name` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `introduction` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `photo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `price` decimal(10,2) NOT NULL DEFAULT '0',
   `remain_count` int(11) NOT NULL DEFAULT '0',
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `product_type` int(11) NOT NULL DEFAULT '1',
   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
   KEY `idx_name_product` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   1 row in set (0.163 sec)

   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]>
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> SHOW CREATE TABLE transactions\G
   *************************** 1. row ***************************
         Table: transactions
   Create Table: CREATE TABLE `transactions` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `user_id` bigint(20) unsigned NOT NULL,
   `product_id` bigint(20) unsigned NOT NULL,
   `price` decimal(10,2) NOT NULL,
   `buy_count` int(11) NOT NULL,
   `total_price` decimal(18,2) NOT NULL,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `pay_type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
   KEY `user_transaction_idx` (`user_id`),
   KEY `product_transaction_idx` (`product_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   1 row in set (0.163 sec)
   ```

> [!TIP]
>
> + TiDB Cloud Starter is a distributed database. It is recommended to change the primary key `AUTO_INCREMENT` attribute of tables `SHOP`.`PRODUCTS` and `SHOP`.`TRANSACTIONS` to `AUTO_RANDOM` to avoid hot spots.
> + Note that this step is optional. Skipping it will not affect the migration result.
> + In addition, if you choose to modify it, you must drop and recreate the 2 tables, and cannot use a method like `ALTER TABLE t MODIFY COLUMN id BIGINT AUTO_RANDOM(5);` mentioned in the document. The reasons are two-fold:
> + Firstly, if all 64 bits of the id column's BIGINT type are valid, an integer overflow error may occur in the later application due to the `PHP_INT_MAX` limit on integers in the PHP language.
> + Secondly, if a setting less than 64 bits like `AUTO_RANDOM(3, 32)` is used for the id column, the `ALTER TABLE t MODIFY COLUMN` command will assume the integer range has been reduced and may result in data loss, thus generating an error.

+ In the VS Code terminal, rebuild the empty tables `shop.products` and `shop.transactions` using the MySQL client, and change the primary key `AUTO_INCREMENT` attribute of the tables `shop.products` and `shop.transactions` to `AUTO_RANDOM`. VS Code will pop up a window asking "Are you sure you want to paste 33 lines of text into the terminal?", please click "Paste".

   ![204_3_3_2.png 85%](https://lab-static.pingcap.com/images/2026/2/6/55971cef685fd59926c137c5ebe555f6663fdcb0.png)

   ```sql
   DROP TABLE `products`;

   CREATE TABLE `products` (
   `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(3, 32) */,
   `status` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'C',
   `name` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `introduction` text COLLATE utf8mb4_unicode_ci NOT NULL,
   `photo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `price` decimal(10,2) NOT NULL DEFAULT '0',
   `remain_count` int(11) NOT NULL DEFAULT '0',
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `product_type` int(11) NOT NULL DEFAULT '1',
   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
   KEY `idx_name_product` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

   DROP TABLE `transactions`;

   CREATE TABLE `transactions` (
   `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(3, 32) */,
   `user_id` bigint(20) unsigned NOT NULL,
   `product_id` bigint(20) unsigned NOT NULL,
   `price` decimal(10,2) NOT NULL,
   `buy_count` int(11) NOT NULL,
   `total_price` decimal(18,2) NOT NULL,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `pay_type` bigint(20) unsigned NOT NULL DEFAULT 1,
   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
   KEY `user_transaction_idx` (`user_id`),
   KEY `product_transaction_idx` (`product_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   ```

   ```output
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]>
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> CREATE TABLE `products` (
      -> `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(3, 32) */,
      -> `status` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'C',
      -> `name` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      -> `introduction` text COLLATE utf8mb4_unicode_ci NOT NULL,
      -> `photo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      -> `price` decimal(10,2) NOT NULL DEFAULT '0',
      -> `remain_count` int(11) NOT NULL DEFAULT '0',
      -> `created_at` timestamp NULL DEFAULT NULL,
      -> `updated_at` timestamp NULL DEFAULT NULL,
      -> `product_type` int(11) NOT NULL DEFAULT '1',
      -> PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
      -> KEY `idx_name_product` (`name`)
      -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   Query OK, 0 rows affected, 1 warning (0.407 sec)

   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]>
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> DROP TABLE `transactions`;
   Query OK, 0 rows affected (0.559 sec)

   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]>
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> CREATE TABLE `transactions` (
      -> `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(3, 32) */,
      -> `user_id` bigint(20) unsigned NOT NULL,
      -> `product_id` bigint(20) unsigned NOT NULL,
      -> `price` decimal(10,2) NOT NULL,
      -> `buy_count` int(11) NOT NULL,
      -> `total_price` decimal(18,2) NOT NULL,
      -> `created_at` timestamp NULL DEFAULT NULL,
      -> `updated_at` timestamp NULL DEFAULT NULL,
      -> `pay_type` bigint(20) unsigned NOT NULL DEFAULT 1,
      -> PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
      -> KEY `user_transaction_idx` (`user_id`),
      -> KEY `product_transaction_idx` (`product_id`)
      -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   Query OK, 0 rows affected, 1 warning (0.384 sec)

   ```

+ In the VS Code terminal, exit the MySQL client to prepare for data migration.

   ```sql
   EXIT
   ```

   ```output
   MySQL [${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}]> EXIT
   Bye
   root@0c2c53eace8f:~/Mshop#
   ```

### Step 4. Data migration

+ In the VS Code terminal, use the dumpling tool to export the data from the required tables under the shop database to a SQL file.

   ```bash
   tiup dumpling -u ${LAB:AWS_AURORA_SERVERLESS_MASTER_USERNAME} -p${LAB:AWS_AURORA_SERVERLESS_MASTER_PASSWORD} -h ${LAB:AWS_AURORA_SERVERLESS_ENDPOINT} -P 3306 -B {SERVERLESS_CLUSTER_DATABASE_NAME} \
   -T ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.users \
   -T ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.products \
   -T ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.transactions \
   -T ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.product_type \
   -T ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.pay_type \
   --filetype sql \
   -o ./dumpling_output
   ```

   ```output
   Checking updates for component dumpling... Timedout (after 2s)
   The component `dumpling` version  is not installed; downloading from repository.
   ...
   [2026/01/21 05:10:11.018 +00:00] [INFO] [dump.go:276] ["All the dumping transactions have started. Start to unlock tables"]
   [2026/01/21 05:10:11.048 +00:00] [INFO] [collector.go:264] ["backup success summary"] [total-ranges=11] [ranges-succeed=11] [ranges-failed=0] [total-take=29.166482ms] [total-kv-size=3.933kB] [average-speed=134.8kB/s] [total-rows=35]
   [2026/01/21 05:10:11.049 +00:00] [INFO] [main.go:82] ["dump data successfully, dumpling will exit now"]
   ```

+ In the VS Code terminal, use the TiDB lightning tool to import data exported from a Aurora (MySQL) database into TiDB Cloud.

   ```bash
   cat > .lightning_config <<EOF
   [mydumper]
   data-source-dir = "/root/Mshop/dumpling_output"

   [tidb]
   host = "${LAB:SERVERLESS_CLUSTER_HOST}"
   port = ${LAB:SERVERLESS_CLUSTER_PORT}
   user = "${LAB:SERVERLESS_CLUSTER_USERNAME}"
   password = "${LAB:SERVERLESS_CLUSTER_PASSWORD}"
   tls = "cluster"

   [security]
   ca-path = "/etc/ssl/certs/ca-certificates.crt"
   EOF

   tiup tidb-lightning --backend=tidb --check-requirements=false --config .lightning_config
   ```

   ```output
   Starting component tidb-lightning: /root/.tiup/components/tidb-lightning/v8.5.5/tidb-lightning --backend=tidb --check-requirements=false --config /dev/fd/63
   Verbose debug logs will be written to /tmp/lightning.log.2026-01-18T05.20.02Z

   tidb lightning exit successfully
   ```

### Step 5. Start the Laravel application based on TiDB Cloud

+ In the VS Code terminal, run the command `composer run dev` to start the Laravel application.

  ```bash
  composer run dev
  ```

  ```output
   > Composer\Config::disableProcessTimeout
   > @php artisan serve --host=0.0.0.0

      INFO  Server running on [http://0.0.0.0:8000].

   Press Ctrl+C to stop the server
  ```

+ There will be a popup window saying "Your application running on port 8000 is available." click "Open in Browser".

  ![204_1_port_8000.png 85%](https://lab-static.pingcap.com/images/2026/2/5/4f681e3a7458c7631f2187c53100aa127e5bcaf4.png)

+ Proceed to the next module.