---
title: "Performing Backup and Restore"
version: "v2"
status: "GA"
description: "In this hands-on lab, you will learn how to perform backup and restore operations. Specifically, we will cover how to deploy Backup & Restore, making a full TiDB cluster backup to locally attached storage with Backup & Restore, back up and restore a database to S3 bucket with Backup & Restore, and back up and restore a table to S3 bucket with Backup & Restore."
---

# Lab 8: Performing Backup and Restore

## Overview

>[!NOTE]
> This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course.

In this lab, you perform backups and restore the database by using `Backup & Restore`. The following shows the deployment topology diagram of TiDB cluster.
   
   ![60%](https://lab-static.pingcap.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithBR_aa_003_20230301.png)  

> [!TIP]
> Click **Start** to launch the lab environment.
> Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**.
> And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks.

## Module 1: Deploying Backup & Restore and Preparing Data

### Step 1. Log into the remote Linux

+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

+ The banner after you logged in:

    ```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/
    ```

### Step 2. Install `Backup & Restore`

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

### Step 3. Extract the data

  ```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/
  ```

### Step 4. Read the file `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"
  ```

### Step 5. Connect to the TiDB, and drop the `emp` database

  ```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)
  ```

### Step 6. Create a user for the lightning task, if needed

+ Check if user `ltask@'%'` exists.

  ```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)
  ```

+ If `ltask@'%'` does not exists, create `ltask@'%'` and grant all privileges.

  ```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;
  ```

### Step 7. Import data into TiDB

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


### Step 8. View the contents of the `nohup.out` file

  ```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
  ```

## Module 2: Backup TiDB Cluster to Local Storage

### Step 1. Create directories on TiKV nodes

  ```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
  ```

### Step 2. Backup 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]
  ```

+ The parameters are explained as follows:
   + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: The IP address of the PD node.
   + `--storage "local:///tmp/backup"`: The path where the backup files are stored.
   + `--ratelimit 128`: The maximum speed per TiKV performing backup tasks (in MiB/s).
   + `--log-file backupfull.log`: The target file for BR logging.

+ If any error occurs, view the contents of the `backupfull.log` file to confirm the cause of the error, and type `:q!` to quit to the terminal (if you use `vi`):

  ```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]      
  ```
    
### Step 3. Check the output files

+ KV1 is used as an example here.
  
  ```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
  ```

### Step 4. Check the metadata of Backup & Restore

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

## Module 3: Backing Up and Restoring a Database

### Step 1. Backup the `emp` database

+ On `Control Machine`, use the `Backup & Restore` utility to back up the `emp` database.
  
  ```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]
  ```

+ The parameters are explained as follows:
  + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: The IP address of the PD node.
  + `--storage "s3://pe-class-oregon/user_unique_tag/emp`: The S3 bucket and object prefix under where the backup files are stored.
  + `--send-credentials-to-tikv=false`: The IAM credentials are got from the EC2 instance role, so no need to pass the credentials. 
  + `--db emp`: specifies the database name.
  + `--ratelimit 128`: The maximum speed per TiKV performing backup tasks (in MiB/s).
  + `--log-file backupdb.log`: The target file for BR logging.
  
+ If any error occurs, view the contents of the `backupfull.log` file to confirm the cause of the error, and type `:q!` to quit to the terminal:
  
  ```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]
  ```

### Step 2. Connect to 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)
  ```

### Step 3. Drop the `emp` database

  ```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
  ```

### Step 4. Restore the `emp` database

+ On `Control Machine`, use the `Backup & Restore` utility to restore the `emp` database.
  
  ```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]
  ```

+ If any error occurs, view the contents of the `backupfull.log` file to confirm the cause of the error.
  
  ```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]   
  ```

### Step 5. Check the restored `emp` database

  ```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;
  ```

## Module 4: Backing Up and Restoring a Table

### Step 1. Backup the `emp.sal` table

+ On `Control Machine`, use the `Backup & Restore` utility to back up the `sal` table in the `emp` database. Replace `{-class_tag}`, `{trainer_tag}` and `{user_tag}` with assigned values by your instructor. 
  
  ```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]
  ```

+ The parameters are explained as follows:
  + `--pd "${HOST_PD1_PRIVATE_IP}:2379"`: The IP address of the PD node.
  + `--storage "s3://pe-class-oregon/user_unique_tag/empsal"`: The S3 bucket and object prefix under where the backup files are stored.
  + `--db emp --table sal`: Specify the database name and table name respectively.
  + `--ratelimit 128`: The maximum speed per TiKV performing backup tasks (in MiB/s).
  + `--log-file backuptable.log`: The target file for `Backup & Restore` logging.
  
+ If any error occurs, view the contents of the `backupfull.log` file to confirm the cause of the error, and type `:q!` to quit to the terminal: 
  
  ```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] 
  ```

### Step 2. Drop the `emp.sal` table

  ```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;
  ```

### Step 3. Restore the `emp.sal` table

+ On `Control Machine`, use the `Backup & Restore` utility to restore the `emp.sal` table.
  
  ```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]
  ```

+ If any error occurs, view the contents of the `backupfull.log` file to confirm the cause of the error.
  
  ```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]
  ```

### Step 4.Check the restored `emp.sql` table

+ Connect to the TiDB and verify that the `sal` table in the `emp` database is restored
  
  ```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)
  ```

## End of Exercises