## 模块 3：备份与恢复数据库

### 步骤 1. 备份 `emp` 数据库到 Amazon S3

+ 在 [终端 1](ssh://monitor1-ssh#1)，使用 `Backup & Restore` 工具备份 `emp` 数据库。
+ 参数说明如下：
  + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`：PD 节点的 IP 地址。
  + `--storage "s3://pe-class-${LAB:REGION_NAME}/${LAB:USER_UNIQUE_TAG}/emp"`：备份文件存储的 S3 存储桶和对象前缀。
  + `--send-credentials-to-tikv=false`：IAM 凭证由 EC2 实例角色获取，无需手动传递凭证。
  + `--db emp`：指定要备份的数据库名称。
  + `--ratelimit 128`：每个 TiKV 执行备份任务的最大速度（单位：MiB/s）。
  + `--log-file backupdb.log`：BR 日志输出目标文件。

  ```bash
  source ~/hosts-env.sh
  tiup br:v7.5.1 backup db \
    --db emp \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --storage "s3://pe-class-${LAB:REGION_NAME}/${USER_UNIQUE_TAG}/emp"  \
    --send-credentials-to-tikv=false \
    --ratelimit 128 \
    --log-file backupdb.log
  ```

  ```output
  Detail BR log in backupdb.log
  Database Backup <--------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  Checksum <---------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  [2022/12/04 16:30:56.102 +00:00] [INFO] [collector.go:69] ["Database Backup success summary"] [total-ranges=14] [ranges-succeed=14] [ranges-failed=0] [backup-checksum=1.769284373s] [backup-fast-checksum=495.092µs] [backup-total-ranges=7] [backup-total-regions=26] [total-take=10.347769213s] [BackupTS=437825423763570689] [total-kv=5988147] [total-kv-size=322.2MB] [average-speed=31.13MB/s] [backup-data-size(after-compressed)=65.51MB] [Size=65506701]
  ```

### 步骤 2. 连接到 TiDB

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```sql
  SHOW DATABASES;
  ```

  ```output
  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | INFORMATION_SCHEMA |
  | METRICS_SCHEMA     |
  | PERFORMANCE_SCHEMA |
  | emp                |
  | mysql              |
  | test               |
  +--------------------+
  6 rows in set (0.00 sec)
  ```

### 步骤 3. 删除 `emp` 数据库

  ```sql
  DROP DATABASE emp;
  ```

  ```output
  mysql> DROP DATABASE emp;
  Query OK, 0 rows affected (39.90 sec)
  ```

  ```sql
  SHOW DATABASES;
  ```

  ```output
  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | INFORMATION_SCHEMA |
  | METRICS_SCHEMA     |
  | PERFORMANCE_SCHEMA |
  | mysql              |
  | test               |
  +--------------------+
  5 rows in set (0.00 sec)
  ```

  ```sql
  EXIT
  ```

  ```output
  mysql> EXIT;
  Bye
  ```

### 步骤 4. 从 Amazon S3 恢复 `emp` 数据库

+ 使用 `Backup & Restore` 工具恢复 `emp` 数据库。

  ```bash
  source ~/hosts-env.sh
  tiup br:v7.5.1 restore db \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --db "emp" \
    --storage "s3://pe-class-${LAB:REGION_NAME}/${USER_UNIQUE_TAG}/emp" \
    --send-credentials-to-tikv=false \
    --log-file restoredb.log
  ```

  ```output
  Detail BR log in restoredb.log
  DataBase Restore <------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  [2022/06/25 20:00:56.031 +08:00] [INFO] [collector.go:69] ["DataBase Restore success summary"] [total-ranges=15] [ranges-succeed=15] [ranges-failed=0] [split-region=201.381µs] [restore-ranges=11] [total-take=3.770701239s] [restore-data-size(after-compressed)=65.55MB] [Size=65549823] [BackupTS=434152001321566251] [total-kv=5988147] [total-kv-size=322.2MB] [average-speed=85.44MB/s]
  ```

### 步骤 5. 验证 `emp` 数据库已恢复

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```

  ```sql
  SHOW DATABASES;
  ```

  ```output
  mysql> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | INFORMATION_SCHEMA |
  | METRICS_SCHEMA     |
  | PERFORMANCE_SCHEMA |
  | emp                |
  | mysql              |
  | test               |
  +--------------------+
  6 rows in set (0.00 sec)
  ```

  ```sql
  USE emp;
  ```

  ```output
  mysql> USE emp;
  Database changed
  ```

  ```sql
  SHOW TABLES;
  ```

  ```output
  mysql> SHOW TABLES;
  +---------------+
  | Tables_in_emp |
  +---------------+
  | dep           |
  | emp           |
  | sal           |
  | t             |
  +---------------+
  4 rows in set (0.00 sec)
  ```

  ```sql
  SELECT COUNT(*) FROM dep;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM dep;
  +----------+
  | COUNT(*) |
  +----------+
  |        9 |
  +----------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SELECT COUNT(*) FROM emp;
  ```

  ```output
  mysql> SELECT COUNT(*) FROM emp;
  +----------+
  | COUNT(*) |
  +----------+
  |   300024 |
  +----------+
  1 row in set (0.00 sec)
  ```