# 演習 8: バックアップとリストアの実行

## 概要

これらの演習では、`Backup & Restore` を使用してデータベースのバックアップとリストアを行います。以下は TiDB クラスタの展開トポロジ図です。

   ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithBR_aa_003_20230301.png)


> [!TIP]
> 演習環境を起動するには、**開始**をクリックしてください。
> 環境の準備が整ったら、下部のツールバーで **次のモジュール** を選択して **モジュール 1** に進みます。
> そして、ステップバイステップの演習ガイドに従って、ハンズオン環境に接続し、タスクを開始してください。

## モジュール 1: Backup & Restore の展開およびデータ準備

### 手順 1. 仮想マシンにログイン

+ ここをクリック - [ターミナル 1](ssh://monitor1-ssh#1) で、ハンズオン演習用に割り当てられた仮想マシンに接続された新しいターミナルを開きます。

+ ログイン後に表示されるバナー：

    ```output
     ,     #_
    ~\_  ####_        Amazon Linux 2
    ~~  \_#####\
    ~~     \###|      
    ~~       \#/ ___
    ~~       V~' '->
    ~~~         /    A newer version of Amazon Linux is available!
     ~~._.   _/
        _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
      _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
    ```

### 手順 2. `Backup & Restore` をインストール

  ```bash
  source .bash_profile
  ```
  ```bash
  tiup install br:v7.5.1
  ```

### 手順 3. データを抽出

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

  ```bash
  ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV1_PRIVATE_IP} unzip -u stage/tidb-admin-dataset-v2.zip -d stage/
  ```

### 手順 4. ファイル `solution-lightning-init.toml` を読む

  ```bash
  cat solution-lightning-init.toml
  ```
  ```output
  [lightning]
  level = "info"
  file = "tidb-lightning.log"

  [tikv-importer]
  incremental-import = true
  backend = "local"
  sorted-kv-dir = "/tmp/sorted-kv-dir"

  [mydumper]
  data-source-dir = "/home/ec2-user/stage/TiDB-Administration-exercise-data-v2/exercise-br"
  filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*']

  [tidb]
  host = "10.90.2.124"
  port = 4000
  user = "ltask"
  password = "q1w2e3R4_"
  status-port = 10080
  pd-addr = "10.90.2.84:2379"
  ```

### 手順 5. TiDB に接続し、`emp` データベースを削除

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```
  ```sql
  DROP DATABASE IF EXISTS emp;
  ```
  ```output
  mysql> DROP DATABASE IF EXISTS emp;
  Query OK, 0 rows affected (0.51 sec)
  ```

### 手順 6. 必要に応じて lightning タスク用のユーザーを作成

+ ユーザー `ltask@'%'` が存在するか確認します。

  ```sql
  SELECT user FROM mysql.user;
  ```
  ```output
  mysql> SELECT user FROM mysql.user;
  +-------+
  | user  |
  +-------+
  | ltask |
  | r_emp |
  | r_mgr |
  | root  |
  | jack  |
  +-------+
  5 rows in set (0.01 sec)
  ```

+ `ltask@'%'` が存在しない場合、`ltask@'%'` を作成し、すべての権限を付与します。

  ```sql
  CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_';
  ```
  ```output
  mysql> CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_';
  Query OK, 0 rows affected (0.05 sec)
  ```

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

  ```sql
  EXIT;
  ```
  ```output  
  mysql> EXIT;
  ```

### 手順 7. TiDB にデータをインポート

  ```bash
  nohup tiup tidb-lightning:v7.5.1 -config solution-lightning-init.toml > nohup.out &
  ```
  ```output
  ...
  [1]+  Done                    nohup tiup tidb-lightning:v7.5.1 -config solution-lightning-init.toml > nohup.out
  ```


### 手順 8. `nohup.out` ファイルの内容を表示

  ```bash
  cat nohup.out
  ```
  ```output
  Verbose debug logs will be written to tidb-lightning.log

  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  # | CHECK ITEM                                                                                                             | TYPE        | PASSED |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  1 | Source csv files size is proper                                                                                        | performance | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  2 | checkpoints are valid                                                                                                  | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  3 | table schemas are valid                                                                                                | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  4 | Cluster is available                                                                                                   | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  5 | Lightning has the correct storage permission                                                                           | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  6 | sorted-kv-dir:/tmp/sorted-kv-dir and data-source-dir:/root/sample/init are in the same disk, may slow down performance | performance | false  |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  7 | local disk resources are rich, estimate sorted data size 343.6MiB, local available is 6.459GiB                         | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  8 | Cluster available is rich, available is 55.03GiB, we need 1.007GiB                                                     | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  |  9 | Cluster doesn't have too many empty regions                                                                            | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+
  | 10 | Cluster region distribution is balanced                                                                                | critical    | true   |
  +----+------------------------------------------------------------------------------------------------------------------------+-------------+--------+

  tidb lightning exit successfully
  ```

## モジュール 2: TiDB クラスタをローカルストレージにバックアップ

### 手順 1. TiKV ノード上にディレクトリを作成

  ```bash
  ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV1_PRIVATE_IP} mkdir /tmp/backup
  ```
  ```bash
  ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV2_PRIVATE_IP} mkdir /tmp/backup
  ```
  ```bash
  ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV3_PRIVATE_IP} mkdir /tmp/backup
  ```

### 手順 2. TiDB をバックアップ

  ```bash
  tiup br:v7.5.1 backup full \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --storage "local:///tmp/backup" \
    --ratelimit 128 \
    --log-file backupfull.log
  ```
  ```output
  Detail BR log in backupfull.log
  Full Backup <----------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  Checksum <-------------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  [2022/06/25 18:39:20.183 +08:00] [INFO] [collector.go:69] ["Full Backup success summary"] [total-ranges=36] [ranges-succeed=36] [ranges-failed=0] [backup-checksum=951.052235ms] [backup-fast-checksum=4.138427ms] [backup-total-ranges=73] [backup-total-regions=78] [total-take=4.187101891s] [BackupTS=434150717785899011] [total-kv=5999621] [total-kv-size=322.9MB] [average-speed=77.11MB/s] [backup-data-size(after-compressed)=65.71MB] [Size=65706363]
  ```

+ パラメータの説明は以下の通りです:
   + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: PD ノードの IP アドレス。
   + `--storage "local:///tmp/backup"`: バックアップファイルが格納されるパス。
   + `--ratelimit 128`: バックアップタスクを実行する TiKV 1 台あたりの最大速度 (MiB/s 単位)。
   + `--log-file backupfull.log`: BR ログのターゲットファイル。

+ エラーが発生した場合は、エラーの原因を確認するために `backupfull.log` ファイルの内容を確認し、`vi` を使用している場合は `:q!` を入力してターミナルに戻ります:

  ```bash
  cat backupfull.log
  ```
  ```output
  [2022/06/25 18:39:15.996 +08:00] [INFO] [info.go:49] ["Welcome to Backup & Restore (BR)"] [release-version=v6.1.0] [git-hash=1a89decdb192cbdce6a7b0020d71128bc964d30f] [git-branch=heads/refs/tags/v6.1.0] [go-version=go1.18.2] [utc-build-time="2022-06-05 05:09:12"] [race-enabled=false]
  [2022/06/25 18:39:15.996 +08:00] [INFO] [common.go:692] [arguments] [__command="br backup full"]
  ...
  [2022/06/25 18:39:20.183 +08:00] [INFO] [collector.go:70] ["Full Backup success summary"] [total-ranges=36] [ranges-succeed=36] [ranges-failed=0] [backup-checksum=951.052235ms] [backup-fast-checksum=4.138427ms] [backup-total-ranges=73] [backup-total-regions=78] [total-take=4.187101891s] [BackupTS=434150717785899011] [total-kv=5999621] [total-kv-size=322.9MB] [average-speed=77.11MB/s] [backup-data-size(after-compressed)=65.71MB] [Size=65706363]
  ```

### 手順 3. 出力ファイルを確認

+ ここでは KV1 を例に使用します。

  ```bash
  ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV1_PRIVATE_IP} find /tmp/backup/
  ```
  ```output
  /tmp/backup/
  /tmp/backup/1
  /tmp/backup/1/38_19_8dd75043152f9a7a07ef9bc0b24b629d464621806e3c93219cac918ec3d5b346_1670169544111_write.sst
  ...
  /tmp/backup/1/147_51_a005fa70db819be4f64dc51d7e50f9a5c5694304295cf5f41b0b7f7df19b5e0e_1670169550220_write.sst
  /tmp/backup/1/147_51_82164855cb8e661a0ef5d566634576dd53a3dd7cd3954478f03955a43c453a63_1670169550222_write.sst
  ```

### 手順 4. Backup & Restore のメタデータを確認

  ```bash
  ls /tmp/backup/
  ```
  ```output
  backup.lock  backupmeta  checkpoint.meta  checkpoints
  ```

## モジュール 3: データベースのバックアップとリストア

### 手順 1. `emp` データベースをバックアップ

+ `Control Machine` 上で、`Backup & Restore` ユーティリティを使用して `emp` データベースをバックアップします。
  
  ```bash
  tiup br:v7.5.1 backup db \
    --db emp \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --storage "s3://pe-class-oregon/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]
  ```

+ パラメータの説明は以下の通りです:
  + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: PD ノードの IP アドレス。
  + `--storage "s3://pe-class-oregon/user_unique_tag/emp"`: バックアップファイルが格納される S3 バケットとオブジェクトプレフィックス。
  + `--send-credentials-to-tikv=false`: IAM クレデンシャルは EC2 インスタンスロールから取得されるため、クレデンシャルを渡す必要はありません。
  + `--db emp`: データベース名を指定。
  + `--ratelimit 128`: バックアップタスクを実行する TiKV 1 台あたりの最大速度 (MiB/s 単位)。
  + `--log-file backupdb.log`: BR ログのターゲットファイル。

+ エラーが発生した場合は、エラーの原因を確認するために `backupfull.log` ファイルの内容を確認し、`vi` を使用している場合は `:q!` を入力してターミナルに戻ります:
  
  ```bash
  cat backupdb.log
  ```
  ```output
  ...
  [2022/12/04 16:30:55.978 +00:00] [INFO] [global.go:34] ["Backup calculated table checksum into metas"] [take=1.769275336s]
  [2022/12/04 16:30:55.978 +00:00] [INFO] [global.go:34] ["write metas finished"] [type=schema]
  [2022/12/04 16:30:55.978 +00:00] [INFO] [global.go:34] ["finish the write metas"] [item=18] [type=schema] [costs=1.769280576s]
  [2022/12/04 16:30:55.978 +00:00] [INFO] [global.go:34] ["save backup meta"] [size=14617]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["checksum success"] [db=emp] [table=t]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["checksum success"] [db=emp] [table=emp]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["checksum success"] [db=emp] [table=sal]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["checksum success"] [db=emp] [table=dep]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
  [2022/12/04 16:30:56.101 +00:00] [INFO] [global.go:34] ["[pd] exit tso dispatcher"] [dc-location=global]
  [2022/12/04 16:30:56.102 +00:00] [INFO] [global.go:34] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
  [2022/12/04 16:30:56.102 +00:00] [INFO] [global.go:34] ["[pd] exit tso dispatcher"] [dc-location=global]
  [2022/12/04 16:30:56.102 +00:00] [INFO] [global.go:34] ["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                |
  | my_db              |
  | mysql              |
  | test               |
  +--------------------+
  7 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 |
  | my_db              |
  | mysql              |
  | test               |
  +--------------------+
  6 rows in set (0.00 sec)
  ```

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

### 手順 4. `emp` データベースをリストア

+ `Control Machine` 上で、`Backup & Restore` ユーティリティを使用して `emp` データベースをリストアします。
  
  ```bash
  tiup br:v7.5.1 restore db \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --db "emp" \
    --storage "s3://pe-class-oregon/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]
  ```

+ エラーが発生した場合は、エラーの原因を確認するために `backupfull.log` ファイルの内容を確認し、`vi` を使用している場合は `:q!` を入力してターミナルに戻ります:
  
  ```bash
  cat restoredb.log
  ```
  ```output
  [2022/06/25 20:00:52.260 +08:00] [INFO] [info.go:49] ["Welcome to Backup & Restore (BR)"] [release-version=v6.1.0] [git-hash=1a89decdb192cbdce6a7b0020d71128bc964d30f] [git-branch=heads/refs/tags/v6.1.0] [go-version=go1.18.2] [utc-build-time="2022-06-05 05:09:12"] [race-enabled=false]
  [2022/06/25 20:00:52.260 +08:00] [INFO] [common.go:692] [arguments] [__command="br restore db"] [db=emp] [log-file=restoredb.log] [pd="[172.31.0.233:2379]"] [storage=local:///tmp/emp]
  ...
  [2022/06/25 20:00:56.030 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
  [2022/06/25 20:00:56.030 +08:00] [INFO] [client.go:706] ["[pd] exit tso dispatcher"] [dc-location=global]
  [2022/06/25 20:00:56.031 +08:00] [INFO] [collector.go:70] ["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                |
  | my_db              |
  | mysql              |
  | test               |
  +--------------------+
  7 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)
  ```

  ```sql
  EXIT;
  ```

## モジュール 4: テーブルのバックアップとリストア

### 手順 1. `emp.sal` テーブルをバックアップ

+ `Control Machine` 上で、`Backup & Restore` ユーティリティを使用して `emp` データベース内の `sal` テーブルをバックアップします。`{-class_tag}`、`{trainer_tag}`、および `{user_tag}` を講師から割り当てられた値に置き換えてください。
  
  ```bash
  tiup br:v7.5.1 backup table \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --db emp \
    --table sal \
    --storage "s3://pe-class-oregon/user_unique_tag/empsal"  \
    --send-credentials-to-tikv=false \
    --ratelimit 128 \
    --log-file backuptable.log
  ```
  ```output
  Detail BR log in backuptable.log
  Table Backup <----------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  Checksum <--------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  [2022/06/25 20:25:08.432 +08:00] [INFO] [collector.go:69] ["Table Backup success summary"] [total-ranges=6] [ranges-succeed=6] [ranges-failed=0] [backup-checksum=963.74443ms] [backup-fast-checksum=219.535µs] [backup-total-ranges=2] [backup-total-regions=6] [total-take=4.664522392s] [Size=58300863] [BackupTS=434152381823320066] [total-kv=5688094] [total-kv-size=301MB] [average-speed=64.54MB/s] [backup-data-size(after-compressed)=58.3MB]
  ```

+ パラメータの説明は以下の通りです:
  + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: PD ノードの IP アドレス。
  + `--storage "s3://pe-class-oregon/user_unique_tag/empsal"`: バックアップファイルが格納される S3 バケットとオブジェクトプレフィックス。
  + `--db emp --table sal`: データベース名とテーブル名をそれぞれ指定します。
  + `--ratelimit 128`: バックアップタスクを実行する TiKV 1 台あたりの最大速度 (MiB/s 単位)。
  + `--log-file backuptable.log`: `Backup & Restore` ログのターゲットファイル。

+ エラーが発生した場合は、エラーの原因を確認するために `backupfull.log` ファイルの内容を確認し、`vi` を使用している場合は `:q!` を入力してターミナルに戻ります:
  
  ```bash
  cat backuptable.log
  ```
  ```output
  [2022/06/25 20:25:03.768 +08:00] [INFO] [info.go:49] ["Welcome to Backup & Restore (BR)"] [release-version=v6.1.0] [git-hash=1a89decdb192cbdce6a7b0020d71128bc964d30f] [git-branch=heads/refs/tags/v6.1.0] [go-version=go1.18.2] [utc-build-time="2022-06-05 05:09:12"] [race-enabled=false]
  [2022/06/25 20:25:03.768 +08:00] [INFO] [common.go:692] [arguments] [__command="br backup table"] [db=emp] [log-file=backuptable.log] [pd="[172.31.0.233:2379]"] [ratelimit=128] [storage=local:///tmp/empsal] [table=sal]
  ...
  [2022/06/25 20:25:08.432 +08:00] [INFO] [client.go:706] ["[pd] exit tso dispatcher"] [dc-location=global]
  [2022/06/25 20:25:08.433 +08:00] [INFO] [collector.go:70] ["Table Backup success summary"] [total-ranges=6] [ranges-succeed=6] [ranges-failed=0] [backup-checksum=963.74443ms] [backup-fast-checksum=219.535µs] [backup-total-ranges=2] [backup-total-regions=6] [total-take=4.664522392s] [Size=58300863] [BackupTS=434152381823320066] [total-kv=5688094] [total-kv-size=301MB] [average-speed=64.54MB/s] [backup-data-size(after-compressed)=58.3MB]
  ```

### 手順 2. `emp.sal` テーブルを削除

  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```
  ```sql
  SELECT COUNT(*) FROM emp.sal;
  ```
  ```output
  mysql> SELECT COUNT(*) FROM emp.sal;
  +----------+
  | COUNT(*) |
  +----------+
  |  2844047 |
  +----------+
  1 row in set (0.00 sec)
  ```

  ```sql
  DROP TABLE emp.sal; 
  ```
  ```output
  mysql> DROP TABLE emp.sal; 
  ```

  ```sql
  SELECT COUNT(*) FROM emp.sal;
  ```
  ```output
  mysql> SELECT COUNT(*) FROM emp.sal;
  ERROR 1146 (42S02): Table 'emp.sal' doesn't exist
  ```

  ```sql
  EXIT;
  ```

### 手順 3. `emp.sal` テーブルをリストア

+ `Control Machine` 上で、`Backup & Restore` ユーティリティを使用して `emp.sal` テーブルをリストアします。
  
  ```bash
  tiup br:v7.5.1 restore table \
    --pd "${HOST_PD1_PRIVATE_IP}:2379" \
    --db "emp" \
    --table "sal" \
    --storage "s3://pe-class-oregon/user_unique_tag/empsal" \
    --send-credentials-to-tikv=false \
    --log-file restoretable.log
  ```
  ```output
  Detail BR log in restoretable.log
  Table Restore <--------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
  [2022/06/25 20:39:21.447 +08:00] [INFO] [collector.go:69] ["Table Restore success summary"] [total-ranges=7] [ranges-succeed=7] [ranges-failed=0] [split-region=150.684µs] [restore-ranges=6] [total-take=3.206398749s] [restore-data-size(after-compressed)=58.3MB] [Size=58300863] [BackupTS=434152605812523049] [total-kv=5688094] [total-kv-size=301MB] [average-speed=93.89MB/s]
  ```

+ エラーが発生した場合は、エラーの原因を確認するために `backupfull.log` ファイルの内容を確認し、`vi` を使用している場合は `:q!` を入力してターミナルに戻ります:
  
  ```bash
  cat restoretable.log
  ```
  ```output
  [2022/06/25 20:39:18.240 +08:00] [INFO] [info.go:49] ["Welcome to Backup & Restore (BR)"] [release-version=v6.1.0] [git-hash=1a89decdb192cbdce6a7b0020d71128bc964d30f] [git-branch=heads/refs/tags/v6.1.0] [go-version=go1.18.2] [utc-build-time="2022-06-05 05:09:12"] [race-enabled=false]
  [2022/06/25 20:39:18.241 +08:00] [INFO] [common.go:692] [arguments] [__command="br restore table"] [db=emp] [log-file=restoretable.log] [pd="[172.31.0.233:2379]"] [storage=local:///tmp/empsal] [table=sal]
  ...
  [2022/06/25 20:39:21.446 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
  [2022/06/25 20:39:21.446 +08:00] [INFO] [client.go:706] ["[pd] exit tso dispatcher"] [dc-location=global]
  [2022/06/25 20:39:21.447 +08:00] [INFO] [collector.go:70] ["Table Restore success summary"] [total-ranges=7] [ranges-succeed=7] [ranges-failed=0] [split-region=150.684µs] [restore-ranges=6] [total-take=3.206398749s] [restore-data-size(after-compressed)=58.3MB] [Size=58300863] [BackupTS=434152605812523049] [total-kv=5688094] [total-kv-size=301MB] [average-speed=93.89MB/s]
  [2022/06/25 20:39:21.447 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global]
  ```

### 手順 4. リストアされた `emp.sql` テーブルを確認

+ TiDB に接続して、`emp` データベース内の `sal` テーブルがリストアされたことを確認します
  
  ```bash
  mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
  ```
  ```bash
  SELECT COUNT(*) FROM emp.sal;
  ```
  ```output
  mysql> SELECT COUNT(*) FROM emp.sal;
  +----------+
  | COUNT(*) |
  +----------+
  |  2844047 |
  +----------+
  1 row in set (0.53 sec)
  ```

## 演習終了