网站导航
> 文章列表 > TiDB实战篇-BR进行数据备份与恢复

TiDB实战篇-BR进行数据备份与恢复

网友: 文章列表 2024-03-21 22:52:27

TiDB实战篇-BR进行数据备份与恢复

简介

使用BR进行数据备份与恢复。

原理

Backup备份的时候在PD上面找到表的元数据，然后找到对应的TiKV数据以后，直接备份到外部系统中（注意如果没有像HDFS这样的分布式文件存储，那么它每个TiKV备份到本地的文件就只有一部分数据）。

Store恢复的时候也会访问PD找到对应的元数据信息恢复TiKV数据。

适用场景

 

  • SST数据文件。
  • backupmeta文件存储一些校验信息。
  • backup.lock文件防止多次备份到同一目录。

BR备份数据的限制

简单的说就是像聚簇索性，New collation 等等如果源的是开着的，那么目标的库也必须是开着的。还有就是BR工具和数据库的版本尽量一致。 还要注意的是，如果开启了TiDB CDC的功能，那么执行BR的时候，下游是没有办法用CDC同步的下游的，这个时候就需要关闭TiDB CDC,然后先还原上游的数据，然后在用BR还原下游的数据，再开启CDC同步上下游的数据。

实战准备

下载

TiDB 社区版 | PingCAP

​

安装

#解压下载好的安装包
tar -zxvf tidb-community-toolkit-v6.5.0-linux-amd64.tar.gz
#进入到文件夹以后解压出dumpling
cd tidb-community-toolkit-v6.5.0-linux-amd64
tar -zxvf br-v6.5.0-linux-amd64.tar.gzvi /etc/profile.d/my.sh
#TOOLKIT_HOME
export TOOLKIT_HOME=/root/tidb-community-toolkit-v6.5.0-linux-amd64
export PATH=$PATH:$TOOLKIT_HOME
source /etc/profile.d/my.sh

打印

[root@master tidb-community-toolkit-v6.5.0-linux-amd64]# br
br is a TiDB/TiKV cluster backup restore tool.Usage:br [command]Available Commands:backup      backup a TiDB/TiKV clustercompletion  Generate the autocompletion script for the specified shellhelp        Help about any commandrestore     restore a TiDB/TiKV clusterFlags:--azblob.access-tier string       Specify the storage class for azblob--azblob.account-key string       Specify the account key for azblob--azblob.account-name string      Specify the account name for azblob--azblob.endpoint string          (experimental) Set the Azblob endpoint URL--ca string                       CA certificate path for TLS connection--cert string                     Certificate path for TLS connection--check-requirements              Whether start version check before execute command (default true)--checksum                        Run checksum at end of task (default true)--crypter.key string              aes-crypter key, used to encrypt/decrypt the data by the hexadecimal string, eg: "0123456789abcdef0123456789abcdef"--crypter.key-file string         FilePath, its content is used as the cipher-key--crypter.method string           Encrypt/decrypt method, be one of plaintext|aes128-ctr|aes192-ctr|aes256-ctr case-insensitively, "plaintext" represents no encrypt/decrypt (default "plaintext")--enable-opentracing              Set whether to enable opentracing during the backup/restore process--gcs.credentials-file string     (experimental) Set the GCS credentials file path--gcs.endpoint string             (experimental) Set the GCS endpoint URL--gcs.predefined-acl string       (experimental) Specify the GCS predefined acl for objects--gcs.storage-class string        (experimental) Specify the GCS storage class for objects-h, --help                            help for br--key string                      Private key path for TLS connection--log-file string                 Set the log file path. If not set, logs will output to temp file (default "/tmp/br.log.2023-04-16T17.43.40+0800")--log-format string               Set the log format (default "text")-L, --log-level string                Set the log level (default "info")-u, --pd strings                      PD address (default [127.0.0.1:2379])--ratelimit uint                  The rate limit of the task, MB/s per node--redact-info-log                 Set whether to redact sensitive info in log--s3.acl string                   (experimental) Set the S3 canned ACLs, e.g. authenticated-read--s3.endpoint string              (experimental) Set the S3 endpoint URL, please specify the http or https scheme explicitly--s3.external-id string           (experimental) Set the external ID when assuming the role to access AWS S3--s3.provider string              (experimental) Set the S3 provider, e.g. aws, alibaba, ceph--s3.region string                (experimental) Set the S3 region, e.g. us-east-1--s3.role-arn string              (experimental) Set the ARN of the IAM role to assume when accessing AWS S3--s3.sse string                   Set S3 server-side encryption, e.g. aws:kms--s3.sse-kms-key-id string        KMS CMK key id to use with S3 server-side encryption.Leave empty to use S3 owned key.--s3.storage-class string         (experimental) Set the S3 storage class, e.g. STANDARD-c, --send-credentials-to-tikv        Whether send credentials to tikv (default true)--status-addr string              Set the HTTP listening address for the status report service. Set to empty string to disable-s, --storage string                  specify the url where backup storage, eg, "s3://bucket/path/prefix"--switch-mode-interval duration   maintain import mode on TiKV during restore (default 5m0s)-V, --version                         Display version information about BRUse "br [command] --help" for more information about a command.

使用简介

​

全库备份

​

 

  • --backupts '2022-01-30 07:42:23'  #指定备份的快照时间，如果没有指定那么就是运行的时间。
  • --ratelimit 128  备份的上限128M  

使用实战

数据准备

mysql -h127.0.0.1 -P4000 -uroot -ptidbmysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| emp            |
+----------------+
1 row in set (0.00 sec)

全库备份

#/tmp/backup本地的目录要提前建立起来，还要对应的执行权限
mkdir /tmp/backup
chmod 777 /tmp/backupbr backup full --pd "192.168.66.10:2379" --storage "local:///tmp/backup" --ratelimit 128 --log-file backupfull.log[root@master ~]# br backup full --pd "192.168.66.10:2379" --storage "local:///tmp/backup" --ratelimit 128 --log-file backupfull.log
Detail BR log in backupfull.log 
Full Backup <-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
Checksum <----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
[2023/04/16 18:01:50.276 +08:00] [INFO] [collector.go:73] ["Full Backup success summary"] [total-ranges=32] [ranges-succeed=32] [ranges-failed=0] [backup-checksum=15.350977ms] [backup-fast-checksum=3.671943ms] [backup-total-ranges=80] [backup-total-regions=80] [total-take=1.525427835s] [BackupTS=440831655219036166] [total-kv=1148] [total-kv-size=294.4kB] [average-speed=193kB/s] [backup-data-size(after-compressed)=97.26kB] [Size=97258]

生成的文件

#查看备份以后生成的文件
cd /tmp/backup
lltotal 212
drwxr-xr-x 2 root root   4096 Apr 16 18:01 1
-rw-r--r-- 1 root root     78 Apr 16 18:01 backup.lock
-rw-r--r-- 1 root root 195245 Apr 16 18:01 backupmeta
-rw-r--r-- 1 root root   7898 Apr 16 18:01 checkpoint.metacd 1
ls
[root@master 1]# ll
total 168
-rw-r--r-- 1 root root  1624 Apr 16 18:01 6001_67_1fff44e252e098d387cf9bf71223dfa1244c3bdfce045cbf684f8972cefa3348_1681639310056_write.sst
-rw-r--r-- 1 root root  1865 Apr 16 18:01 6001_67_2d2f68c250f77d93ffb8ea543a425eebeea7c92d1f033e9d32cdb914ca4341a9_1681639310157_write.sst
-rw-r--r-- 1 root root  1614 Apr 16 18:01 6001_67_2f8a960de6866770ba4656ea3edcacb8bdffca14a1f51dca1232d8101e92e1e1_1681639310148_write.sst
-rw-r--r-- 1 root root  2044 Apr 16 18:01 6001_67_302753bc98ec93e98edea12aeac15074da779bb66c735c8ce184eacf43f1e7c2_1681639310190_write.sst
-rw-r--r-- 1 root root 16595 Apr 16 18:01 6001_67_355f0b5a4a8313c61daccaf1f055ac6ce3e0abaeb3d9fda7eb60557014f38dac_1681639310077_write.sst
-rw-r--r-- 1 root root  2741 Apr 16 18:01 6001_67_36adb8cedcd7af34708edff520499e712e2cfdcb202f5707dc9305a031d55a98_1681639310080_write.sst
-rw-r--r-- 1 root root  1836 Apr 16 18:01 6001_67_398bfbc7e7f90f381d53912673cc84f98a52f8fe4cf37a5d36c3593a10ae4734_1681639310237_write.sst
-rw-r--r-- 1 root root  1624 Apr 16 18:01 6001_67_3f6d12724d959fbc78994fce604770562322868fba9dd533f7daf7d93dbd1ad7_1681639310200_write.sst
-rw-r--r-- 1 root root 12621 Apr 16 18:01 6001_67_495c5d2e3a1901df43133318e03236d08d1c8882cdd7b452579a4fcd82ccb718_1681639310072_write.sst
-rw-r--r-- 1 root root  1759 Apr 16 18:01 6001_67_5880ab7469329cec0484a1ba2cbf3fc7e97bb9fc9dbd3c189d79be965d0733cc_1681639310063_write.sst
-rw-r--r-- 1 root root  1835 Apr 16 18:01 6001_67_5ee3395c74d5aeb37e1f20733281e3865ad14754c92e7c3bc00fead026166d54_1681639310143_write.sst
-rw-r--r-- 1 root root  1644 Apr 16 18:01 6001_67_63ed7d8a2b3c9d0f343314b5cc83be282978edb60deaea275725b80a21b1a8c9_1681639310233_write.sst
-rw-r--r-- 1 root root  1732 Apr 16 18:01 6001_67_6d95378ebf12295206532143634e65a359d39c26f40c8a1fa9b239de19fe54b2_1681639310229_write.sst
-rw-r--r-- 1 root root  1611 Apr 16 18:01 6001_67_755836e42bfb568bf6e89d931d86a7d31453a1bd6f7ffb924e19eb28be74aa00_1681639310087_write.sst
-rw-r--r-- 1 root root  1615 Apr 16 18:01 6001_67_759f1781e3eb9e6aae280a9aa33f2cb97d799e11265ff74363f00f942787a311_1681639310091_write.sst
-rw-r--r-- 1 root root  1644 Apr 16 18:01 6001_67_7c29d2d214a7a70e5c20091535dc0515fe9e2d8c4dffbfad1e3ca1fa7decdc14_1681639310089_write.sst
-rw-r--r-- 1 root root  1657 Apr 16 18:01 6001_67_8dd75043152f9a7a07ef9bc0b24b629d464621806e3c93219cac918ec3d5b346_1681639310154_write.sst
-rw-r--r-- 1 root root  2011 Apr 16 18:01 6001_67_900c129aebf3b73e914b71b71b30c0f172f46c1208483c738484ad1e41930971_1681639310240_write.sst
-rw-r--r-- 1 root root  1602 Apr 16 18:01 6001_67_9fb8afb3f7322e93f506f0d9d11e9b1569bc90b7c3779da9bb7e35137e8e6597_1681639310207_write.sst
-rw-r--r-- 1 root root  1644 Apr 16 18:01 6001_67_a24c493bf7e801abe1238806dd31796f911e7dd0970118b9315f0a7366532873_1681639310158_write.sst
-rw-r--r-- 1 root root  1974 Apr 16 18:01 6001_67_afb978bcbf32cf0ba18923daa88bdab19c00ad22f4fe49863f102827eb831e21_1681639310096_write.sst
-rw-r--r-- 1 root root  2198 Apr 16 18:01 6001_67_c11c7f8053ba1d8a3efe53decec79ee83321252578aaa0e09024538869d60d0d_1681639310186_write.sst
-rw-r--r-- 1 root root  2415 Apr 16 18:01 6001_67_c2baa1d0dee98e26766aa462a873c405eaad66653a52db6bc9e1d584f6aebcc7_1681639310081_write.sst
-rw-r--r-- 1 root root  1581 Apr 16 18:01 6001_67_c6fbd0b37cfd9d13cc1f10f54fe19ef336e6b488d52e11af728256afa096a611_1681639310251_write.sst
-rw-r--r-- 1 root root  1798 Apr 16 18:01 6001_67_ceff1d011f7457e3d444017065830a5a5d03832641246a25dac12707846937e3_1681639310150_write.sst
-rw-r--r-- 1 root root  1688 Apr 16 18:01 6001_67_d03f5545842231b64ac143f64290e1efab5eb039773fde78f51cc6d98d01e020_1681639310162_write.sst
-rw-r--r-- 1 root root  1959 Apr 16 18:01 6001_67_d0a5865efa1d90636cf0c26e991e1f13ad4ef35c631530b967aa1e342d73208b_1681639310094_write.sst
-rw-r--r-- 1 root root  1605 Apr 16 18:01 6001_67_de8304485bb5b1def0f07f35f376594d100067cec03a21a00f157e91b6148940_1681639310160_write.sst
-rw-r--r-- 1 root root  1795 Apr 16 18:01 6001_67_ea55f4e75cb43a63969bf83a306fc68e3fa49ff84f6f51a3c873ad531a18691d_1681639310132_write.sst
-rw-r--r-- 1 root root 15303 Apr 16 18:01 6001_67_ed85b58a2c9d34fd5a6d89e79e2ab51ddb78227e5ad4656082bb663714b43d77_1681639310106_default.sst
-rw-r--r-- 1 root root  1956 Apr 16 18:01 6001_67_ed85b58a2c9d34fd5a6d89e79e2ab51ddb78227e5ad4656082bb663714b43d77_1681639310106_write.sst
-rw-r--r-- 1 root root  1668 Apr 16 18:01 6001_67_ff781abda573afc7d7878f20390a290dcdb471bf12a8cbabf359ccf9e8d37084_1681639310165_write.sst

全库恢复

先删库

mysql -h127.0.0.1 -P4000 -uroot -ptidb
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)mysql> drop database test;
Query OK, 0 rows affected (0.27 sec)mysql> drop database mysql;
ERROR 1105 (HY000): Drop 'mysql' database is forbidden

数据恢复

#使得每一台机器都有全部的数据
scp -r /tmp/backup root@192.168.66.20:/tmp
scp -r /tmp/backup root@192.168.66.21:/tmp#执行备份
br restore full --pd "192.168.66.10:2379" --storage "local:///tmp/backup" --ratelimit 128 --log-file restorefull.log#打印
[root@master 1]# br restore full --pd "192.168.66.10:2379" --storage "local:///tmp/backup" --ratelimit 128 --log-file restorefull.log
Detail BR log in restorefull.log 
Full Restore <------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
[2023/04/16 18:26:00.093 +08:00] [INFO] [collector.go:73] ["Full Restore success summary"] [total-ranges=18] [ranges-succeed=18] [ranges-failed=0] [split-region=559.242µs] [restore-ranges=9] [total-take=4.077901672s] [total-kv=27] [total-kv-size=2.459kB] [average-speed=603B/s] [restore-data-size(after-compressed)=16.46kB] [Size=16465] [BackupTS=440831655219036166] [RestoreTS=440832034371010561]

恢复结果

mysql -h127.0.0.1 -P4000 -uroot -ptidb
show databases;mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from emp;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

单库备份

单库恢复

 

单表备份

 

单表恢复

 

多表备份

多表恢复

 

增量备份

 

注意一定得先有一个全备 。

LAST_BACKUP_TS表示上一次全备的时间点。

增量恢复

前提是要把之前的数据恢复完。 

注意事项

 

 

网络标签：

相关问题