## Module 2: Migrating Data From Two MySQL Instances (Schema Preparation)

### Step 1. Setup source MySQL instance on PD1

+ Login as user ec2-user on PD1. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.

  ```bash
  ./ssh-to-pd1.sh
  ```

+ Start the MySQL instance on `PD1`.

  ```bash
  sudo service mysqld start
  ```

  ```output
  Redirecting to /bin/systemctl start mysqld.service
  ```

+ Connect to MySQL server on PD1 using user `root@'localhost'`

  ```bash
  mysql -h localhost -P 3306 -uroot -pq1w2e3R4_
  ```

  ```output
  Enter password:
  ...
  Server version: 5.7.41-log
  ...
  mysql>
  ```

+ Create a new user `dm_user@'%'`, and grant privileges to the user:

  ```sql
  CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  ```

  ```output
  mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  ```

  ```output
  mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  Query OK, 0 rows affected (0.00 sec)
  ```

+ Turn on `show_compatibility_56`.

  ```sql
  SET @@global.show_compatibility_56=ON;
  ```

  ```output
  mysql> SET @@global.show_compatibility_56=ON;
  Query OK, 0 rows affected (0.00 sec)
  ```

+ Exit to terminal and exit to the Control Machine (monitor1_address):

  ```sql
  EXIT
  ```

  ```output
  mysql> EXIT
  Bye
  ```

  ```bash
  exit
  ```

  ```output
  logout
  Connection to XX.XX.XX.XX closed.
  ```

### Step 2. Setup source MySQL instance on PD2

+ Login as user ec2-user on PD2. The returned message may be slightly different from what is displayed here.

  ```bash
  ./ssh-to-pd2.sh
  ```

+ Start the MySQL instance on `PD2`.

  ```bash
  sudo service mysqld start
  ```

  ```output
  Redirecting to /bin/systemctl start mysqld.service
  ```

+ Connect to MySQL server on PD2 using user `root@'localhost'`.

  ```bash
  mysql -h localhost -P 3306 -uroot -pq1w2e3R4_
  ```

  ```output
  Enter password:
  ...
  Server version: 5.7.41-log
  ...
  mysql>
  ```

+ Create a new user `dm_user@'%'`, and grant privileges to the user.

  ```sql
  CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  ```

  ```output
  mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  ```

  ```output
  mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  Query OK, 0 rows affected (0.00 sec)
  ```

+ Turn on `show_compatibility_56`.

  ```sql
  SET @@global.show_compatibility_56=ON;
  ```

  ```output
  mysql> SET @@global.show_compatibility_56=ON;
  Query OK, 0 rows affected (0.00 sec)
  ```

+ Exit to terminal and exit to the Control Machine.

  ```sql
  EXIT
  ```

  ```output
  mysql> EXIT
  Bye
  ```

  ```bash
  exit
  ```

  ```output
  logout
  Connection to XX.XX.XX.XX closed.
  ```

### Step 3. Import data into two MySQL instances

+ Make sure the sample is extract.

  ```bash
  unzip -u stage/tidb-admin-dataset-v2.zip -d stage/
  ```

+ Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD1.

  ```bash
  mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD1_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3306db.sql
  ```

+ Import the `user`, `store`, `log` and `salesdb` databases into MySQL on PD2.

  ```bash
  mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD2_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3307db.sql
  ```

### Step 4. Setup target TiDB cluster schemas

+ Connect to the TiDB:

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot
  ```

  ```output
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 405
  Server version: 5.7.25-TiDB-v7.5.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
  ...
  mysql>
  ```

+ Create new user called `dm_user`, and grant privileges to the user.

  ```sql
  CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  ```

  ```output
  mysql> CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
  Query OK, 0 rows affected (0.04 sec)
  ```

  ```sql
  GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  ```

  ```output
  mysql> GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
  Query OK, 0 rows affected (0.02 sec)
  ```

+ Create a database on the TiDB called `user_north`, with table called `information` and `trace`. The `information` table should have a primary key column called `id` and a `VARCHAR(64)` column called `info`. The `trace` table should have a primary key column called `id` and a `VARCHAR(64)` column called `content`.

  ```sql
  CREATE DATABASE user_north;
  CREATE TABLE user_north.information(id INT PRIMARY KEY, info VARCHAR(64));
  CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64));
  ```

  ```output
  mysql> CREATE DATABASE user_north;
  Query OK, 0 rows affected (0.08 sec)
  ...
  mysql> CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64));
  Query OK, 0 rows affected (0.09 sec)
  ```

+ Create a database on the TiDB called `user_east`, with tables called `information` and `trace`. The `information` table should have a primary key column called `id` and a `VARCHAR(64)` column called `info`. The `trace` table should have a primary key column called `id` and a `VARCHAR(64)` column called `content`.

  ```sql
  CREATE DATABASE user_east;
  CREATE TABLE user_east.information(id INT PRIMARY KEY, info VARCHAR(64));
  CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64));
  ```

  ```output
  mysql> CREATE DATABASE user_east;
  Query OK, 0 rows affected (0.08 sec)
  ...
  mysql> CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64));
  Query OK, 0 rows affected (0.09 sec)

+ Create a database on the TiDB called `store`, with tables called `store_bj`, `store_tj`, `store_sh` and `store_suzhou`. The `store_bj` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_tj` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_sh` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`. The `store_suzhou` table should have a primary key column called `id` and a `VARCHAR(64)` column called `pname`.

  ```sql
  CREATE DATABASE store;
  CREATE TABLE store.store_bj(id INT PRIMARY KEY, pname VARCHAR(64));
  CREATE TABLE store.store_tj(id INT PRIMARY KEY, pname VARCHAR(64));
  CREATE TABLE store.store_sh(id INT PRIMARY KEY, pname VARCHAR(64));
  CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64));
  ```

  ```output
  mysql> CREATE DATABASE store;
  Query OK, 0 rows affected (0.09 sec)
  ...
  mysql> CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64));
  Query OK, 0 rows affected (0.09 sec)
  ```

+ Create a database on the TiDB called `salesdb`, with a table called `sales`. The `sales` table should have a primary key column called `id`, a `VARCHAR(20)` column called `pname` and an `int` column called `cnt`.

  ```sql
  CREATE DATABASE salesdb;
  CREATE TABLE salesdb.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int);
  ```

  ```output
  mysql> CREATE DATABASE salesdb;
  Query OK, 0 rows affected (0.09 sec)
  mysql> CREATE TABLE sales.sales(id INT PRIMARY KEY, pname VARCHAR(20), cnt int);
  Query OK, 0 rows affected (0.09 sec)
  ```

+ Create a database on the TiDB called `log`, with a table called `messages`. The `messages` table should have a primary key column called `id`, and a `VARCHAR(64)` column called `msg`. Then exit the session.

  ```bash
  CREATE DATABASE log;
  CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64));
  ```

  ```output
  mysql> CREATE DATABASE log;
  Query OK, 0 rows affected (0.08 sec)
  mysql> CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64));
  Query OK, 0 rows affected (0.08 sec)
  ```

  ```sql
  EXIT
  ```

  ```output
  mysql> EXIT
  Bye
  ```