How does everyone schedule regular backups for the TiDB database?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 请问下，tidb数据库大家都是怎么进行定时备份的

| username: TiDBer_Y2d2kiJh

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.0
[Reproduction Path] How does everyone perform scheduled backups for the TiDB database?
[Encountered Issues: Problem Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: xfworld | Original post link

Write a script and schedule it to run at regular intervals.

3
| username: TiDBer_Y2d2kiJh | Original post link

Are you using dumpling for the backup?

4
| username: xfworld | Original post link

Definitely br

5
| username: TiDBer_Y2d2kiJh | Original post link

br should be backed up on shared storage, right?

6
| username: xfworld | Original post link

NFS and S3 are both fine.

7
| username: 啦啦啦啦啦 | Original post link

It depends on the data volume. For small databases, you can use Dumpling. For large databases, using Dumpling might take an entire day to back up, and it would be even slower if you need to restore it.

8
| username: zhanggame1 | Original post link

Use BR for backup and enable logging.

9
| username: redgame | Original post link

Use br for backup.

10
| username: 像风一样的男子 | Original post link

For full backups, I set up a scheduled task to back up. Incremental backups can back up binlog logs. If you have the resources, you can set up a replica to create a master-slave configuration. If the master fails, switch to the replica.

11
| username: Anna | Original post link

Use BR for backup and enable logging.

12
| username: TiDBer_Y2d2kiJh | Original post link

To restore using br, can it only be restored to an empty database? My data volume is small, and I don’t have shared storage, so I use dumpling.

13
| username: 像风一样的男子 | Original post link

For small amounts of data, Dumpling backup is fine. For larger amounts of data, it is recommended to use BR.

14
| username: Billmay表妹 | Original post link

TiDB supports scheduled backups using the BR tool. Here are the specific steps for the backup:

  1. Prepare the backup script

Create a backup script on the TiDB server, for example:

#!/bin/bash
export DATEDIR=`date +%Y%m%d`
export BASEDIR=/tidbbak/db
mkdir -p $BASEDIR/$DATEDIR
/usr/local/bin/br backup full --pd "192.168.3.221:2379" --storage "local://$BASEDIR/$DATEDIR" --ratelimit 120 --log-file $BASEDIR/$DATEDIR/fullbackup_`date +%Y%m%d`.log
sync
sleep 10
find ${BASEDIR} -type f -mtime +31 -exec rm {} \;
find ${BASEDIR} -type d -empty -delete

In this script, br backup full indicates a full backup, the --pd parameter specifies the PD address, the --storage parameter specifies the backup storage location, the --ratelimit parameter specifies the backup rate, and the --log-file parameter specifies the backup log location.

  1. Add a scheduled task

Use the crontab command to add a scheduled task, for example:

## 1. Allow the tidb user to add crond scheduled tasks
~]# echo tidb >> /etc/cron.allow
## 2. Add the br backup task
0 2 * * * /bin/bash /root/scripts/fulldbbak.sh

In this example, 0 2 * * * means performing the backup at 2 AM every day, and /bin/bash /root/scripts/fulldbbak.sh indicates executing the backup script.

In summary, you can achieve scheduled backups for TiDB using the BR tool and the crontab command.

15
| username: 像风一样的男子 | Original post link

It would be perfect if you add a section on mounting a shared disk to all TiKV nodes in this manual.