# **练习-3 : 使用 `BR` 进行单库备份与恢复** ## 概述 + 在本练习中，将使用备份恢复工具 BR 进行单库 emp 的备份和恢复｡ ## 实验环境要求 + 1. 注意，您的实验环境（包括IP，端口号，用户名，密码和目录）可能和手册不同。 + 2. 可以连接到实验用虚拟机。 + 3. TiDB 数据库提前部署完毕。 + 4. 清理掉之前的实验数据。 + 5. 需要完成练习-1。 ## 步骤 ----------------------------------------------- #### 1.　在所有的 TiKV 节点创建文件夹 /tmp/emp，用来存储本节点的备份文件(SST 文件)，并将文件夹的权限设置为可以读写｡ + **依次登录到 TiKV 节点，之后执行:** ``` # mkdir /tmp/emp ``` + **将文件夹的权限设置为可以读写** ``` # chmod 777 /tmp/emp ``` ----------------------------------------------- #### 2. 使用 BR 进行单库备份。 + **单库备份**： ``` br backup db --pd "172.31.0.232:2379" --db emp --storage "local:///tmp/emp" --ratelimit 128 --log-file backupdb.log ``` (对于参数解释如下: --pd "172.31.0.232:2379" :连接 TiDB 数据库的 PD 节点。 --storage "local:///tmp/emp" :备份文件存储在 TiKV 节点上的位置｡ --db emp : 备份 emp 库下面所有的表｡ --ratelimit 128 :对于备份所用存储带宽限速，以免影响线上业务｡ --log-file backupdb.log : 备份日志文件｡ ) + **查看备份日志是否包含错误**： ``` # vi backupdb.log ``` ----------------------------------------------- #### 3. 接下来，登录到 TiDB 数据库上，将刚刚备份的 emp 数据库删除。 + **首先，查看 emp 数据库:(请根据自己实验环境输入密码)** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> show databases; ``` ```sql +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | +--------------------+ 6 rows in set (0.00 sec) ``` + **其次，删除 emp 数据库:** ```sql mysql> drop database emp; ``` ```sql mysql> show databases; ``` ```sql +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) ``` ----------------------------------------------- #### 4. 接下来准备进行恢复，首先，依次将所有 TiKV 目录 /tmp/emp 下面的备份文件拷贝到其他节点，保证在所有的 TiKV 节点上都有所有的备份文件｡ + **登录到 TiKV 节点，进入目录 /tmp/emp/** ``` # cd /tmp/emp/ ``` + **将备份文件拷贝到其他节点，如下:** ``` # scp * 172.31.0.79:/tmp/emp # scp * 172.31.0.95:/tmp/emp ``` + **循环操作，更换不同节点，使所有节点都有所有的备份文件** ----------------------------------------------- #### 5. 开始进行恢复｡ + **使用 BR 工具进行 emp 数据库的恢复，如下：** ``` # br restore db --pd "172.31.0.232:2379" --db "emp" --storage "local:///tmp/emp" --log-file restoredb.log ``` + **查看恢复日志是否包含错误输出，如下：** ``` # vi restoredb.log ``` ----------------------------------------------- #### 6. 最后，登录到 TiDB 数据库上，验证 emp 数据库是否恢复。 + **首先，查看 emp 数据库:(请根据自己实验环境输入密码)** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> show databases; ``` ```sql +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | emp | | mysql | | test | +--------------------+ 6 rows in set (0.00 sec) ``` + **其次，查看 emp 数据库中的表:** ```sql mysql> use emp ``` ```sql mysql> show tables; ``` ```sql +---------------+ | Tables_in_emp | +---------------+ | dep | | emp | | sal | | t | +---------------+ 4 rows in set (0.00 sec) ``` + **最后，查看各个表中的数据:** ```sql mysql> select count(*) from dep; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from sal; +----------+ | count(*) | +----------+ | 2844047 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) ``` ----------------------------------------------- ## 输出样例 ----------------------------------------------- #### 步骤2-全库备份输出参考： ``` br backup db --pd "172.31.0.232:2379" --db emp --storage "local:///tmp/emp" --ratelimit 128 --log-file backupdb.log ``` ``` Detail BR log in backupdb.log Database Backup <-------------------------------------------------------------------------------------------------------------------------------------> 100.00% Checksum <--------------------------------------------------------------------------------------------------------------------------------------------> 100.00% [2022/06/25 19:35:24.729 +08:00] [INFO] [collector.go:69] ["Database Backup success summary"] [total-ranges=11] [ranges-succeed=11] [ranges-failed=0] [backup-fast-checksum=634.319µs] [backup-checksum=943.725065ms] [backup-total-ranges=7] [backup-total-regions=12] [total-take=4.012982839s] [BackupTS=434151599834660867] [total-kv=5988147] [total-kv-size=322.2MB] [average-speed=80.28MB/s] [backup-data-size(after-compressed)=65.55MB] [Size=65549823] ``` ----------------------------------------------- #### 步骤2-查看备份日志是否包含错误输出参考： ``` # vi backupdb.log ``` ``` [2022/06/25 19:35:20.716 +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 19:35:20.716 +08:00] [INFO] [common.go:692] [arguments] [__command="br backup db"] [db=emp] [log-file=backupdb.log] [pd="[172.31.0.232:2379]"] [ratelimit=128] [storage=local:///tmp/emp] [2022/06/25 19:35:20.716 +08:00] [INFO] [conn.go:257] ["new mgr"] [pdAddrs=172.31.0.232:2379] [2022/06/25 19:35:20.717 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 19:35:20.722 +08:00] [INFO] [base_client.go:332] ["[pd] update member urls"] [old-urls="[http://172.31.0.232:2379]"] [new-urls="[http://172.31.0.175:2379,http://172.31.0.232:2379,http://172.31.0.233:2379]"] [2022/06/25 19:35:20.722 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 19:35:20.722 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 19:35:20.722 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 19:35:20.723 +08:00] [INFO] [conn.go:232] ["checked alive KV stores"] [aliveStores=3] [totalStores=3] [2022/06/25 19:35:20.723 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 19:35:20.724 +08:00] [INFO] [base_client.go:332] ["[pd] update member urls"] [old-urls="[http://172.31.0.232:2379]"] [new-urls="[http://172.31.0.175:2379,http://172.31.0.232:2379,http://172.31.0.233:2379]"] [2022/06/25 19:35:20.724 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 19:35:20.724 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 19:35:20.724 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 19:35:20.725 +08:00] [INFO] [tidb.go:74] ["new domain"] [store=tikv-7111180120874660827] ["ddl lease"=1s] ["stats lease"=-1ns] ["index usage sync lease"=0s] ... [2022/06/25 19:35:24.728 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global] [2022/06/25 19:35:24.728 +08:00] [INFO] [client.go:706] ["[pd] exit tso dispatcher"] [dc-location=global] [2022/06/25 19:35:24.729 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global] [2022/06/25 19:35:24.729 +08:00] [INFO] [client.go:706] ["[pd] exit tso dispatcher"] [dc-location=global] [2022/06/25 19:35:24.729 +08:00] [INFO] [collector.go:70] ["Database Backup success summary"] [total-ranges=11] [ranges-succeed=11] [ranges-failed=0] [backup-fast-checksum=634.319µs] [backup-checksum=943.725065ms] [backup-total-ranges=7] [backup-total-regions=12] [total-take=4.012982839s] [BackupTS=434151599834660867] [total-kv=5988147] [total-kv-size=322.2MB] [average-speed=80.28MB/s] [backup-data-size(after-compressed)=65.55MB] [Size=65549823] ``` ----------------------------------------------- #### 步骤5-使用 BR 工具进行 emp 数据库的恢复输出参考： ``` br restore db --pd "172.31.0.232:2379" --db "emp" --storage "local:///tmp/emp" --log-file restoredb.log ``` ``` 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] [root@vm172-31-0-135 ~]# vi restoredb.log ``` ----------------------------------------------- #### 步骤5-查看恢复日志是否包含错误输出参考： ``` # vi restoredb.log ``` ``` [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.232:2379]"] [storage=local:///tmp/emp] [2022/06/25 20:00:52.260 +08:00] [INFO] [conn.go:257] ["new mgr"] [pdAddrs=172.31.0.232:2379] [2022/06/25 20:00:52.261 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:00:52.264 +08:00] [INFO] [base_client.go:332] ["[pd] update member urls"] [old-urls="[http://172.31.0.232:2379]"] [new-urls="[http://172.31.0.175:2379,http://172.31.0.232:2379,http://172.31.0.233:2379]"] [2022/06/25 20:00:52.264 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:00:52.264 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:00:52.264 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:00:52.265 +08:00] [INFO] [conn.go:232] ["checked alive KV stores"] [aliveStores=3] [totalStores=3] [2022/06/25 20:00:52.265 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:00:52.267 +08:00] [INFO] [base_client.go:332] ["[pd] update member urls"] [old-urls="[http://172.31.0.232:2379]"] [new-urls="[http://172.31.0.175:2379,http://172.31.0.232:2379,http://172.31.0.233:2379]"] [2022/06/25 20:00:52.267 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:00:52.267 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:00:52.267 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:00:52.268 +08:00] [INFO] [tidb.go:74] ["new domain"] [store=tikv-7111180120874660827] ["ddl lease"=1s] ["stats lease"=-1ns] ["index usage sync lease"=0s] ... [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.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] ``` -----------------------------------------------