# Lab 10: Performing Backup and Restore
### Overview
In these exercises, you perform backups and restore the database by using `Backup & Restore`. The following shows the deployment topology diagram of TiDB cluster.
### Notes
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this demo.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.
+ You may use `ff7.sh` to install, deploy, and start a TiDB cluster and relevant utilities. Make sure you have executed `source ~/hosts-env.sh` and `source ~/.bash_profile` to set environment variables.
### Assumptions
+ You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): `chmod 400 [~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)`
+ Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md) for private key preparation.
## Exercise 1
### Subject: Deploying `Backup & Restore` and Preparing Data
### Overview
In this exercise, you deploy `Backup & Restore` utility and prepare data for exercise. You will perform all tasks on the Control Machine (monitor1_address).
### Duration
+ This exercise should take you approximately 25 minutes to complete.
### Tasks
1. Install `Backup & Restore` utility.
2. Prepare the data.
3. Input data into TiDB using `TiDB Lightning`.
### Solution
1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. If prompted, answer `yes`.
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
```
```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. Install `Backup & Restore` utility using `TiUP`.
```command
$ source .bash_profile
```
```command
$ tiup install br:v6.5.1
```
3. Extract the data directories archive from tidb-admin-dataset.zip to ./stage/ on `Control Machine` and `TiKV server 1`. Enter the following command at the terminal prompt and receive the results shown:
```command
$ unzip -u stage/tidb-admin-dataset.zip -d stage/
```
```output
Archive: stage/tidb-admin-dataset.zip
```
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV1_PRIVATE_IP} unzip -u stage/tidb-admin-dataset.zip -d stage/
```
4. Read the file `solution-lightning-init.toml`. The IP addresses shown below will be different from yours.
```command
$ 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/exercise-10"
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. Connect to the TiDB, and drop the `emp` database. Create a user for the lightning task, if needed. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
DROP DATABASE IF EXISTS emp;
```
```output
mysql> DROP DATABASE IF EXISTS emp;
Query OK, 0 rows affected (0.51 sec)
```
+ Check if user `ltask@'%'` exists.
```command
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.
```command
CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_';
```
```output
mysql> CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_';
Query OK, 0 rows affected (0.05 sec)
```
```command
GRANT ALL PRIVILEGES ON *.* TO ltask@'%';
```
```output
mysql> GRANT ALL PRIVILEGES ON *.* TO ltask@'%';
Query OK, 0 rows affected (0.05 sec)
```
```command
EXIT;
```
```output
mysql> EXIT;
```
6. Use a `nohup` command to start the `tidb-lightning` process. Enter the following command at the terminal prompt and receive the results shown:
```command
$ nohup tiup tidb-lightning:v6.5.1 -config solution-lightning-init.toml > nohup.out &
```
```output
...
[1]+ Done nohup tidb-lightning:v6.5.1 -config solution-lightning-init.toml > nohup.out
```
7. View the contents of the `nohup.out` file to verify that `TiDB Lightning` is exited. Enter the following command at the terminal prompt and receive the results shown:
```command
$ 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
```
8. Connect to the TiDB and display all of the databases. Does the list include the `emp` database? Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
SHOW DATABASES;
```
```output
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| emp |
| my_db |
| mysql |
| test |
+--------------------+
7 rows in set (0.01 sec)
```
## Exercise 2
### Subject: Making a Full TiDB Cluster Data Backup to Locally Attached Storage with `Backup & Restore`
### Overview
In this exercise, you use `Backup & Restore` to make a physical full backup. The backup files are stored on the TiKV nodes.
#### Note:
+ You have cleaned up the old practice data on `TiKV server 1`, `TiKV server 2` and `TiKV server 3`.
### Duration
+ This exercise should take you approximately 30 minutes to complete.
### Tasks
1. Backup the data in TiDB into local disks of the TiKV nodes.
2. Check the backup files.
### Solution
1. Create a new directory named `/tmp/backup` on `TiKV server 1`, `TiKV server 2` and `TiKV server 3`, and change the permissions on the `/tmp/backup` folder to read and write access. Enter `yes` if prompted by `ssh`. Enter the following command at the terminal prompt and receive the results shown:
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV1_PRIVATE_IP} mkdir /tmp/backup
```
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV2_PRIVATE_IP} mkdir /tmp/backup
```
```command
$ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@${HOST_KV3_PRIVATE_IP} mkdir /tmp/backup
```
2. On `Control Machine`, run the `tiup br backup full` command to backup the database. Enter the following command at the terminal prompt and receive the results shown:
```command
$ tiup br:v6.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`):
```command
$ 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. List the files generated in the `/tmp/backup/` directory from `TiKV server 1`, `TiKV server 2` and `TiKV server 3`. Enter the following command at the terminal prompt and receive the results shown, for example, to check on `TiKV server 1`:
```command
$ 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. On `Control Machine`, list the `metadata` and `backup.lock` files created in the `/tmp/backup/` directory. Enter the following command at the terminal prompt and receive the results shown:
```command
$ ls /tmp/backup/
```
```output
backup.lock backupmeta checkpoint.meta checkpoints
```
## Exercise 3
### Subject: Backing Up and Restoring a Database to S3 Bucket with `Backup & Restore`
### Overview
In this exercise, you back up and restore a database in the TiDB cluster by using the `Backup & Restore`. In the previous exercise, you backed up data to the local disks connected to `TiKV` servers, in this exercise, you will back up database data to S3 bucket, it is available for each `TiKV` server to read and write. Shared file system and Cloud storage is a preferred way to work with `Backup & Restore (BR)`, it's convenient for `TiKV` to access.
### Duration
+ This exercise should take you approximately 30 minutes to complete.
### Tasks
1. Back up the `emp` database in `TiDB` to `Amazon S3` bucket.
2. Drop the `emp` database in `TiDB`
3. Use the `Backup & Restore` utility to restore the `emp` database.
### Solution
1. On `Control Machine`, use the `Backup & Restore` utility to back up the `emp` database. Replace `{-class_tag}`, `{trainer_tag}` and `{user_tag}` with assigned values by your instructor. Enter the following command at the terminal prompt and receive the results shown:
```command
$ tiup br:v6.5.1 backup db --db emp --pd "${HOST_PD1_PRIVATE_IP}:2379" --storage "s3://pe-class-{location_tag}/{trainer_tag}/{user_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-{location_tag}/{trainer_tag}/{user_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:
```command
$ 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. Connect to TiDB and list the databases. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
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. Drop the `emp` database. Enter the following command at the terminal prompt and receive the results shown:
```command
DROP DATABASE emp;
```
```output
mysql> DROP DATABASE emp;
Query OK, 0 rows affected (39.90 sec)
```
```command
SHOW DATABASES;
```
```output
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| my_db |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)
```
```command
EXIT
```
```output
mysql> EXIT;
Bye
```
4. On `Control Machine`, use the `Backup & Restore` utility to restore the `emp` database. Replace `{-class_tag}`, `{trainer_tag}` and `{user_tag}` with assigned values by your instructor. Enter the following command at the terminal prompt and receive the results shown:
```command
$ tiup br:v6.5.1 restore db --pd "${HOST_PD1_PRIVATE_IP}:2379" --db "emp" --storage "s3://pe-class-{location_tag}/{trainer_tag}/{user_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.
```command
$ 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. Connect to the TiDB and list the databases. Does the list include the `emp` database? Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
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)
```
6. Review the tables in the `emp` database. Enter the following command at the terminal prompt and receive the results shown:
```command
USE emp;
```
```output
mysql> USE emp;
Database changed
```
```command
SHOW TABLES;
```
```output
mysql> SHOW TABLES;
+---------------+
| Tables_in_emp |
+---------------+
| dep |
| emp |
| sal |
| t |
+---------------+
4 rows in set (0.00 sec)
```
7. Verify that the `dep`, `emp`, `sql` and `t` tables are restored. Enter the following command at the terminal prompt and receive the results shown:
```command
SELECT COUNT(*) FROM dep;
```
```output
mysql> SELECT COUNT(*) FROM dep;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.01 sec)
```
```command
SELECT COUNT(*) FROM emp;
```
```output
mysql> SELECT COUNT(*) FROM emp;
+----------+
| COUNT(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.00 sec)
```
```command
SELECT COUNT(*) FROM sal;
```
```output
mysql> SELECT COUNT(*) FROM sal;
+----------+
| COUNT(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.00 sec)
```
```command
SELECT COUNT(*) FROM t;
```
```output
mysql> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
```
## Exercise 4
### Subject: Backing Up and Restoring a Table to S3 Bucket with `Backup & Restore`
### Overview
In this exercise, you backup and restore a table in the TiDB cluster by using the `Backup & Restore`. You will back up table data to S3 bucket, it is available for each `TiKV` server to read and write. Shared file system and Cloud storage is a preferred way to work with `Backup & Restore (BR)`, it's convenient for `TiKV` to access.
### Duration
+ This exercise should take you approximately 30 minutes to complete.
### Tasks
1. Backup the `sal` table in the `emp` database to the `Amazon S3` bucket.
2. Drop the `sal` table in `emp` database.
3. Use the `Backup & Restore` utility to restore the `sal` table in `emp` database.
### Solution
1. 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. Enter the following command at the terminal prompt and receive the results shown:
```command
$ tiup br:v6.5.1 backup table --pd "${HOST_PD1_PRIVATE_IP}:2379" --db emp --table sal --storage "s3://pe-class-{location_tag}/{trainer_tag}/{user_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-{location_tag}/{trainer_tag}/{user_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:
```command
$ 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. Connect to the TiDB and drop the `sal` table in the `emp` database. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
SELECT COUNT(*) FROM emp.sal;
```
```output
mysql> SELECT COUNT(*) FROM emp.sal;
+----------+
| COUNT(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.00 sec)
```
```command
DROP TABLE emp.sal;
```
```output
mysql> DROP TABLE emp.sal;
```
```command
SELECT COUNT(*) FROM emp.sal;
```
```output
mysql> SELECT COUNT(*) FROM emp.sal;
ERROR 1146 (42S02): Table 'emp.sal' doesn't exist
```
3. On `Control Machine`, use the `Backup & Restore` utility to restore the `emp.sal` table. Replace `{-class_tag}`, `{trainer_tag}` and `{user_tag}` with assigned values by your instructor. Enter the following command at the terminal prompt and receive the results shown:
```command
$ tiup br:v6.5.1 restore table --pd "${HOST_PD1_PRIVATE_IP}:2379" --db "emp" --table "sal" --storage "s3://pe-class-{location_tag}/{trainer_tag}/{user_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.
```command
$ 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. Connect to the TiDB and verify that the `sal` table in the `emp` database is restored. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
```command
$ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
```
```command
SELECT COUNT(*) FROM emp.sal;
```
```output
mysql> SELECT COUNT(*) FROM emp.sal;
+----------+
| COUNT(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.53 sec)
```
5. Log out of the client and close all open terminal windows.
## End of Exercises