- Docs Home
- About TiDB
- Quick Start
- Deploy
- Software and Hardware Requirements
- Environment Configuration Checklist
- Plan Cluster Topology
- Install and Start
- Verify Cluster Status
- Test Cluster Performance
- Migrate
- Maintain
- Upgrade
- Scale
- Backup and Restore
- Use BR Tool (Recommended)
- Configure Time Zone
- Daily Checklist
- Maintain TiFlash
- Maintain TiDB Using TiUP
- Modify Configuration Online
- Monitor and Alert
- Troubleshoot
- TiDB Troubleshooting Map
- Identify Slow Queries
- Analyze Slow Queries
- SQL Diagnostics
- Identify Expensive Queries
- Statement Summary Tables
- Troubleshoot Hotspot Issues
- Troubleshoot Increased Read and Write Latency
- Troubleshoot Cluster Setup
- Troubleshoot High Disk I/O Usage
- Troubleshoot Lock Conflicts
- Troubleshoot TiFlash
- Troubleshoot Write Conflicts in Optimistic Transactions
- Performance Tuning
- System Tuning
- Software Tuning
- SQL Tuning
- Overview
- Understanding the Query Execution Plan
- SQL Optimization Process
- Overview
- Logic Optimization
- Physical Optimization
- Prepare Execution Plan Cache
- Control Execution Plans
- Tutorials
- Multiple Data Centers in One City Deployment
- Three Data Centers in Two Cities Deployment
- Read Historical Data
- Use Stale Read (Recommended)
- Use the
tidb_snapshot
System Variable
- Best Practices
- Use Placement Rules
- Use Load Base Split
- Use Store Limit
- TiDB Tools
- Overview
- Use Cases
- Download
- TiUP
- TiDB Operator
- Backup & Restore (BR)
- TiDB Binlog
- TiDB Lightning
- TiDB Data Migration
- TiCDC
- Dumpling
- sync-diff-inspector
- TiSpark
- Reference
- Cluster Architecture
- Key Monitoring Metrics
- Secure
- Privileges
- SQL
- SQL Language Structure and Syntax
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER INDEX
ALTER INSTANCE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BACKUP
BEGIN
CHANGE COLUMN
COMMIT
CHANGE DRAINER
CHANGE PUMP
CREATE [GLOBAL|SESSION] BINDING
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE SEQUENCE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP [GLOBAL|SESSION] BINDING
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP SEQUENCE
DROP STATS
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLASHBACK TABLE
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
RESTORE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW [BACKUPS|RESTORES]
SHOW [GLOBAL|SESSION] BINDINGS
SHOW BUILTINS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CONFIG
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW DRAINER STATUS
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW PLUGINS
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW PROFILES
SHOW PUMP STATUS
SHOW SCHEMAS
SHOW STATS_HEALTHY
SHOW STATS_HISTOGRAMS
SHOW STATS_META
SHOW STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SHUTDOWN
SPLIT REGION
START TRANSACTION
TABLE
TRACE
TRUNCATE
UPDATE
USE
WITH
- Data Types
- Functions and Operators
- Overview
- Type Conversion in Expression Evaluation
- Operators
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Window Functions
- Miscellaneous Functions
- Precision Math
- Set Operations
- List of Expressions for Pushdown
- TiDB Specific Functions
- Clustered Indexes
- Constraints
- Generated Columns
- SQL Mode
- Transactions
- Garbage Collection (GC)
- Views
- Partitioning
- Character Set and Collation
- System Tables
mysql
- INFORMATION_SCHEMA
- Overview
ANALYZE_STATUS
CLIENT_ERRORS_SUMMARY_BY_HOST
CLIENT_ERRORS_SUMMARY_BY_USER
CLIENT_ERRORS_SUMMARY_GLOBAL
CHARACTER_SETS
CLUSTER_CONFIG
CLUSTER_HARDWARE
CLUSTER_INFO
CLUSTER_LOAD
CLUSTER_LOG
CLUSTER_SYSTEMINFO
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
DATA_LOCK_WAITS
DDL_JOBS
DEADLOCKS
ENGINES
INSPECTION_RESULT
INSPECTION_RULES
INSPECTION_SUMMARY
KEY_COLUMN_USAGE
METRICS_SUMMARY
METRICS_TABLES
PARTITIONS
PROCESSLIST
SCHEMATA
SEQUENCES
SESSION_VARIABLES
SLOW_QUERY
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_STORAGE_STATS
TIDB_HOT_REGIONS
TIDB_INDEXES
TIDB_SERVERS_INFO
TIDB_TRX
TIFLASH_REPLICA
TIKV_REGION_PEERS
TIKV_REGION_STATUS
TIKV_STORE_STATUS
USER_PRIVILEGES
VIEWS
METRICS_SCHEMA
- UI
- TiDB Dashboard
- Overview
- Maintain
- Access
- Overview Page
- Cluster Info Page
- Key Visualizer Page
- Metrics Relation Graph
- SQL Statements Analysis
- Slow Queries Page
- Cluster Diagnostics
- Search Logs Page
- Profile Instances Page
- Session Management and Configuration
- FAQ
- CLI
- Command Line Flags
- Configuration File Parameters
- System Variables
- Storage Engines
- Telemetry
- Errors Codes
- Table Filter
- Schedule Replicas by Topology Labels
- FAQs
- Release Notes
- All Releases
- v5.1
- v5.0
- v4.0
- v3.1
- v3.0
- v2.1
- v2.0
- v1.0
- Glossary
Backup & Restore FAQ
This document lists the frequently asked questions (FAQs) and the solutions about Backup & Restore (BR).
What should I do if the error message could not read local://...:download sst failed
is returned during data restoration?
When you restore data, each node must have access to all backup files (SST files). By default, if local
storage is used, you cannot restore data because the backup files are scattered among different nodes. Therefore, you have to copy the backup file of each TiKV node to the other TiKV nodes.
It is recommended to mount an NFS disk as a backup disk during backup. For details, see Back up a single table to a network disk.
How much does it affect the cluster during backup using BR?
When you use the oltp_read_only
scenario of sysbench
to back up to a disk (make sure the backup disk and the service disk are different) at full rate, the cluster QPS is decreased by 15%-25%. The impact on the cluster depends on the table schema.
To reduce the impact on the cluster, you can use the --ratelimit
parameter to limit the backup rate.
Does BR back up system tables? During data restoration, do they raise conflict?
Before v5.1.0, BR filtered out data from the system schema mysql
during the backup. Since v5.1.0, BR backs up all data by default, including the system schemas mysql.*
. But the technical implementation of restoring the system tables in mysql.*
is not complete yet, so the tables in the system schema mysql
are not restored by default. For more details, refer to the Back up and restore table data in the mysql
system schema (experimental feature).
What should I do to handle the Permission denied
or No such file or directory
error, even if I have tried to run BR using root in vain?
You need to confirm whether TiKV has access to the backup directory. To back up data, confirm whether TiKV has the write permission. To restore data, confirm whether it has the read permission.
During the backup operation, if the storage medium is the local disk or a network file system (NFS), make sure that the user to start BR and the user to start TiKV are consistent (if BR and TiKV are on different machines, the users' UIDs must be consistent). Otherwise, the Permission denied
issue might occur.
Running BR with the root access might fail due to the disk permission, because the backup files (SST files) are saved by TiKV.
You might encounter the same problem during data restoration. When the SST files are read for the first time, the read permission is verified. The execution duration of DDL suggests that there might be a long interval between checking the permission and running BR. You might receive the error message Permission denied
after waiting for a long time.
Therefore, It is recommended to check the permission before data restoration.
What should I do to handle the Io(Os...)
error?
Almost all of these problems are system call errors that occur when TiKV writes data to the disk. For example, if you encounter error messages such as Io(Os {code: 13, kind: PermissionDenied...})
or Io(Os {code: 2, kind: NotFound...})
, you can first check the mounting method and the file system of the backup directory, and try to back up data to another folder or another hard disk.
For example, you might encounter the Code: 22(invalid argument)
error when backing up data to the network disk built by samba
.
What should I do to handle the rpc error: code = Unavailable desc =...
error occurred in BR?
This error might occur when the capacity of the cluster to restore (using BR) is insufficient. You can further confirm the cause by checking the monitoring metrics of this cluster or the TiKV log.
To handle this issue, you can try to scale out the cluster resources, reduce the concurrency during restore, and enable the RATE_LIMIT
option.
Where are the backed up files stored when I use local
storage?
When you use local
storage, backupmeta
is generated on the node where BR is running, and backup files are generated on the Leader nodes of each Region.
How about the size of the backup data? Are there replicas of the backup?
During data backup, backup files are generated on the Leader nodes of each Region. The size of the backup is equal to the data size, with no redundant replicas. Therefore, the total data size is approximately the total number of TiKV data divided by the number of replicas.
However, if you want to restore data from local storage, the number of replicas is equal to that of the TiKV nodes, because each TiKV must have access to all backup files.
What should I do when BR restores data to the upstream cluster of TiCDC/Drainer?
The data restored using BR cannot be replicated to the downstream. This is because BR directly imports SST files but the downstream cluster currently cannot obtain these files from the upstream.
Before v4.0.3, DDL jobs generated during the BR restore might cause unexpected DDL executions in TiCDC/Drainer. Therefore, if you need to perform restore on the upstream cluster of TiCDC/Drainer, add all tables restored using BR to the TiCDC/Drainer block list.
You can use filter.rules
to configure the block list for TiCDC and use syncer.ignore-table
to configure the block list for Drainer.
Does BR back up the SHARD_ROW_ID_BITS
and PRE_SPLIT_REGIONS
information of a table? Does the restored table have multiple Regions?
Yes. BR backs up the SHARD_ROW_ID_BITS
and PRE_SPLIT_REGIONS
information of a table. The data of the restored table is also split into multiple Regions.
Why is the region is unavailable
error reported for a SQL query after I use BR to restore the backup data?
If the cluster backed up using BR has TiFlash, TableInfo
stores the TiFlash information when BR restores the backup data. If the cluster to be restored does not have TiFlash, the region is unavailable
error is reported.
Does BR support in-place full recovery of some historical backup?
No. BR does not support in-place full recovery of some historical backup.
How can I use BR for incremental backup in the Kubernetes environment?
To get the commitTs
field of the last BR backup, run the kubectl -n ${namespace} get bk ${name}
command using kubectl. You can use the content of this field as --lastbackupts
.
How can I convert BR backupTS to Unix time?
BR backupTS
defaults to the latest timestamp obtained from PD before the backup starts. You can use pd-ctl tso timestamp
to parse the timestamp to obtain an accurate value, or use backupTS >> 18
to quickly obtain an estimated value.
After BR restores the backup data, do I need to execute the ANALYZE
statement on the table to update the statistics of TiDB on the tables and indexes?
BR does not back up statistics (except in v4.0.9). Therefore, after restoring the backup data, you need to manually execute ANALYZE TABLE
or wait for TiDB to automatically execute ANALYZE
.
In v4.0.9, BR backs up statistics by default, which consumes too much memory. To ensure that the backup process goes well, the backup for statistics is disabled by default starting from v4.0.10.
If you do not execute ANALYZE
on the table, TiDB will fail to select the optimized execution plan due to inaccurate statistics. If query performance is not a key concern, you can ignore ANALYZE
.
- What should I do if the error message could not read local://...:download sst failed is returned during data restoration?
- How much does it affect the cluster during backup using BR?
- Does BR back up system tables? During data restoration, do they raise conflict?
- What should I do to handle the Permission denied or No such file or directory error, even if I have tried to run BR using root in vain?
- What should I do to handle the Io(Os...) error?
- What should I do to handle the rpc error: code = Unavailable desc =... error occurred in BR?
- Where are the backed up files stored when I use local storage?
- How about the size of the backup data? Are there replicas of the backup?
- What should I do when BR restores data to the upstream cluster of TiCDC/Drainer?
- Does BR back up the SHARD_ROW_ID_BITS and PRE_SPLIT_REGIONS information of a table? Does the restored table have multiple Regions?
- Why is the region is unavailable error reported for a SQL query after I use BR to restore the backup data?
- Does BR support in-place full recovery of some historical backup?
- How can I use BR for incremental backup in the Kubernetes environment?
- How can I convert BR backupTS to Unix time?
- After BR restores the backup data, do I need to execute the ANALYZE statement on the table to update the statistics of TiDB on the tables and indexes?