# **练习-4 : 使用 `BR` 进行单表备份与恢复** ## 概述 + 在本练习中，将使用备份恢复工具 BR 进行单表 emp.sal 的备份和恢复。 ## 实验环境要求 + 1. 注意，您的实验环境（包括IP，端口号，用户名，密码和目录）可能和手册不同。 + 2. 可以连接到实验用虚拟机。 + 3. TiDB 数据库提前部署完毕。 + 4. 清理掉之前的实验数据。 + 5. 需要完成练习-1。 ## 步骤 ----------------------------------------------- #### 1.　在所有的 TiKV 节点创建文件夹 /tmp/empsal，用来存储本节点的备份文件(SST 文件)，并将文件夹的权限设置为可以读写｡ + **依次登录到 TiKV 节点，之后执行:** ``` # mkdir /tmp/empsal ``` + **将文件夹的权限设置为可以读写** ``` # chmod 777 /tmp/empsal ``` ----------------------------------------------- #### 2. 使用 BR 进行单表备份。 + **单表备份**： ``` br backup table --pd "172.31.0.232:2379" --db emp --table sal --storage "local:///tmp/empsal" --ratelimit 128 --log-file backuptable.log ``` (对于参数解释如下: --pd "172.31.0.232:2379" :连接 TiDB 数据库的 PD 节点。 --storage "local:///tmp/empsal" :备份文件存储在 TiKV 节点上的位置｡ --db emp --table sal : 备份 emp 库下面的 sal 表｡ --ratelimit 128 :对于备份所用存储带宽限速，以免影响线上业务｡ --log-file backuptable.log : 备份日志文件｡ ) + **查看备份日志是否包含错误**： ``` # vi backuptable.log ``` ----------------------------------------------- #### 3. 接下来，登录到 TiDB 数据库上，将刚刚备份的 emp.sal 表删除。 + **首先，查看 emp.sal 表(请根据自己实验环境输入密码)** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select count(*) from emp.sal; ``` ```sql +----------+ | count(*) | +----------+ | 2844047 | +----------+ 1 row in set (0.00 sec) ``` + **其次，删除 emp.sal 表:** ```sql mysql> drop table emp.sal; ``` ```sql mysql> select count(*) from emp.sal; ``` ```sql ERROR 1146 (42S02): Table 'emp.sal' doesn't exist ``` ----------------------------------------------- #### 4. 接下来准备进行恢复，首先，依次将所有 TiKV 目录 /tmp/empsal 下面的备份文件拷贝到其他节点，保证在所有的 TiKV 节点上都有所有的备份文件｡ + **登录到 TiKV 节点，进入目录 /tmp/empsal/** ``` # cd /tmp/empsal ``` + **将备份文件拷贝到其他节点，如下:** ``` # scp * 172.31.0.79:/tmp/empsal # scp * 172.31.0.95:/tmp/empsal ``` + **循环操作，更换不同节点，使所有节点都有所有的备份文件** ----------------------------------------------- #### 5. 开始进行恢复｡ + **使用 BR 工具进行 emp.sal 表的恢复，如下：** ``` # br restore table --pd "172.31.0.232:2379" --db "emp" --table "sal" --storage "local:///tmp/empsal" --log-file restoretable.log ``` + **查看恢复日志是否包含错误输出，如下：** ``` # vi restoretable.log ``` ----------------------------------------------- #### 6. 最后，登录到 TiDB 数据库上，验证 emp 数据库下的 sal 表是否恢复。 + **登陆到 TiDB 数据库，查看 emp.sal 表的数据:(请根据自己实验环境输入密码)** ``` # mysql -h172.31.0.135 -P4000 -uroot -ptidb ``` ```sql mysql> select count(*) from emp.sal; ``` ```sql +----------+ | count(*) | +----------+ | 2844047 | +----------+ 1 row in set (0.53 sec) ``` ----------------------------------------------- ## 输出样例 ----------------------------------------------- #### 步骤2-全库备份输出参考： ``` br backup table --pd "172.31.0.232:2379" --db emp --table sal --storage "local:///tmp/empsal" --ratelimit 128 --log-file backuptable.log ``` ``` 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] ``` ----------------------------------------------- #### 步骤2-查看备份日志是否包含错误输出参考： ``` # vi backuptable.log ``` ``` [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.232:2379]"] [ratelimit=128] [storage=local:///tmp/empsal] [table=sal] [2022/06/25 20:25:03.768 +08:00] [INFO] [conn.go:257] ["new mgr"] [pdAddrs=172.31.0.232:2379] [2022/06/25 20:25:03.769 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:25:03.771 +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:25:03.771 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:25:03.772 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:25:03.772 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:25:03.772 +08:00] [INFO] [conn.go:232] ["checked alive KV stores"] [aliveStores=3] [totalStores=3] [2022/06/25 20:25:03.772 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:25:03.774 +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:25:03.774 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:25:03.774 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:25:03.774 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:25:03.775 +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:25:08.431 +08:00] [INFO] [worker.go:73] ["table checksum finished"] [db=emp] [table=sal] [Crc64Xor=13301323058925287498] [TotalKvs=5688094] [TotalBytes=301036020] [take=963.603072ms] [2022/06/25 20:25:08.431 +08:00] [INFO] [schema.go:146] ["backup checksum"] [take=963.733655ms] [2022/06/25 20:25:08.431 +08:00] [INFO] [metafile.go:596] ["write metas finished"] [type=schema] [2022/06/25 20:25:08.432 +08:00] [INFO] [metafile.go:636] ["finish the write metas"] [item=7] [type=schema] [costs=963.839143ms] [2022/06/25 20:25:08.432 +08:00] [INFO] [metafile.go:656] ["save backup meta"] [size=4755] [2022/06/25 20:25:08.432 +08:00] [INFO] [validate.go:82] ["checksum success"] [db=emp] [table=sal] [2022/06/25 20:25:08.432 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global] [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.432 +08:00] [INFO] [client.go:768] ["[pd] stop fetching the pending tso requests due to context canceled"] [dc-location=global] [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] ``` ----------------------------------------------- #### 步骤5-使用 BR 工具进行 emp 数据库的恢复输出参考： ``` br restore table --pd "172.31.0.232:2379" --db "emp" --table "sal" --storage "local:///tmp/empsal" --log-file restoretable.log Detail BR log in restoretable.log ``` ``` 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] ``` ----------------------------------------------- #### 步骤5-查看恢复日志是否包含错误输出参考： ``` # vi restoretable.log ``` ``` [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.232:2379]"] [storage=local:///tmp/empsal] [table=sal] [2022/06/25 20:39:18.241 +08:00] [INFO] [conn.go:257] ["new mgr"] [pdAddrs=172.31.0.232:2379] [2022/06/25 20:39:18.242 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:39:18.245 +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:39:18.245 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:39:18.245 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:39:18.245 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:39:18.245 +08:00] [INFO] [conn.go:232] ["checked alive KV stores"] [aliveStores=3] [totalStores=3] [2022/06/25 20:39:18.245 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[172.31.0.232:2379]"] [2022/06/25 20:39:18.247 +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:39:18.247 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://172.31.0.232:2379] [old-leader=] [2022/06/25 20:39:18.247 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7111180120874660827] [2022/06/25 20:39:18.247 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global] [2022/06/25 20:39:18.250 +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: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] ``` -----------------------------------------------