- Key Features
- Horizontal Scalability
- MySQL Compatible Syntax
- Replicate from and to MySQL
- Distributed Transactions with Strong Consistency
- Cloud Native Architecture
- Minimize ETL with HTAP
- Fault Tolerance & Recovery with Raft
- Automatic Rebalancing
- Deployment and Orchestration with Ansible, Kubernetes, Docker
- JSON Support
- Spark Integration
- Read Historical Data Without Restoring from Backup
- Fast Import and Restore of Data
- Hybrid of Column and Row Storage
- SQL Plan Management
- Open Source
- Online Schema Changes
- Get Started
- MySQL Compatibility
- SQL Language Structure
- Data Types
- Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- 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
- List of Expressions for Pushdown
- SQL Statements
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
CREATE TABLE LIKE
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW CHARACTER SET
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW CREATE USER
SHOW [FULL] FIELDS FROM
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW [FULL] PROCESSSLIST
SHOW [FULL] TABLES
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
- Generated Columns
- Character Set
- SQL Mode
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Understanding the Query Execution Plan
- The Blocklist of Optimization Rules and Expression Pushdown
- Introduction to Statistics
- TopN and Limit Push Down
- Optimizer Hints
- Follower Read
- Check the TiDB Cluster Status Using SQL Statements
- Execution Plan Binding
- Statement Summary Table
- Tune TiKV
- Operating System Tuning
- Column Pruning
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiDB Binlog
- Use Cases
- TiDB Operator
- Table Filter
- Backup & Restore (BR)
- Data Migration
- TiDB Lightning
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- TiDB in Kubernetes
- All Releases
You are viewing the documentation of an older version of the TiDB database (TiDB v3.1).
The TiDB ecosystem tools operate on all the databases by default, but oftentimes only a subset is needed. For example, you only want to work with the schemas in the form of
bar* and nothing else.
Several TiDB ecosystem tools share a common filter syntax to define subsets. This document describes how to use the table filter feature.
Table filters can be applied to the tools using multiple
--filter command line parameters. Each filter is in the form of
db.table, where each part can be a wildcard (further explained in the next section). The following lists the example usage in each tool.
./br backup full -f 'foo*.*' -f 'bar*.*' -s 'local:///tmp/backup' # ^~~~~~~~~~~~~~~~~~~~~~~ ./br restore full -f 'foo*.*' -f 'bar*.*' -s 'local:///tmp/backup' # ^~~~~~~~~~~~~~~~~~~~~~~
./dumpling -f 'foo*.*' -f 'bar*.*' -P 3306 -o /tmp/data/ # ^~~~~~~~~~~~~~~~~~~~~~~
./tidb-lightning -f 'foo*.*' -f 'bar*.*' -d /tmp/data/ --backend tidb # ^~~~~~~~~~~~~~~~~~~~~~~
Table filters in TOML files are specified as array of strings. The following lists the example usage in each tool.
[mydumper] filter = ['foo*.*', 'bar*.*']
Each table filter rule consists of a "schema pattern" and a "table pattern", separated by a dot (
.). Tables whose fully-qualified name matches the rules are accepted.
db1.tbl1 db2.tbl2 db3.tbl3
A plain name must only consist of valid identifier characters, such as:
- digits (
- letters (
- non ASCII characters (U+0080 to U+10FFFF)
All other ASCII characters are reserved. Some punctuations have special meanings, as described in the next section.
Each part of the name can be a wildcard symbol described in fnmatch(3):
*— matches zero or more characters
?— matches one character
[a-z]— matches one character between "a" and "z" inclusively
[!a-z]— matches one character except "a" to "z".
db[0-9].tbl[0-9a-f][0-9a-f] data.* *.backup_*
"Character" here means a Unicode code point, such as:
- U+00E9 (é) is 1 character.
- U+0065 U+0301 (é) are 2 characters.
- U+1F926 U+1F3FF U+200D U+2640 U+FE0F (🤦🏿♀️) are 5 characters.
To import a file as the filter rule, include an
@ at the beginning of the rule to specify the file name. The table filter parser treats each line of the imported file as additional filter rules.
For example, if a file
config/filter.txt has the following content:
the following two invocations are equivalent:
./dumpling -f '@config/filter.txt' ./dumpling -f 'employees.*' -f '*.WorkOrder'
A filter file cannot further import another file.
Inside a filter file, leading and trailing white-spaces of every line are trimmed. Furthermore, blank lines (empty strings) are ignored.
# marks a comment and is ignored.
# not at start of line is considered syntax error.
# this line is a comment db.table # but this part is not comment and may cause error
! at the beginning of the rule means the pattern after it is used to exclude tables from being processed. This effectively turns the filter into a block list.
*.* #^ note: must add the *.* to include all tables first !*.Password !employees.salaries
To turn a special character into an identifier character, precede it with a backslash
For simplicity and future compatibility, the following sequences are prohibited:
\at the end of the line after trimming whitespaces (use
[ ]to match a literal whitespace at the end).
\followed by any ASCII alphanumeric character (
[0-9a-zA-Z]). In particular, C-like escape sequences like
\tcurrently are meaningless.
\, special characters can also be suppressed by quoting using
The quotation mark can be included within an identifier by doubling itself.
"foo""bar".`foo``bar` # equivalent to: foo\"bar.foo\`bar
Quoted identifiers cannot span multiple lines.
It is invalid to partially quote an identifier:
"this is "invalid*.*
In case very complex rules are needed, each pattern can be written as a regular expression delimited with
These regular expressions use the Go dialect. The pattern is matched if the identifier contains a substring matching the regular expression. For instance,
/ in the regular expression must be escaped as
\/, including inside
[…]. You cannot place an unescaped
When a table name matches none of the rules in the filter list, the default behavior is to ignore such unmatched tables.
To build a block list, an explicit
*.* must be used as the first rule, otherwise all tables will be excluded.
# every table will be filtered out ./dumpling -f '!*.Password' # only the "Password" table is filtered out, the rest are included. ./dumpling -f '*.*' -f '!*.Password'
In a filter list, if a table name matches multiple patterns, the last match decides the outcome. For instance:
# rule 1 employees.* # rule 2 !*.dep* # rule 3 *.departments
The filtered outcome is as follows:
|Table name||Rule 1||Rule 2||Rule 3||Outcome|
|employees.employees||✓||Rule 1 (accept)|
|employees.dept_emp||✓||✓||Rule 2 (reject)|
|employees.departments||✓||✓||✓||Rule 3 (accept)|
|else.departments||✓||✓||Rule 3 (accept)|
In TiDB tools, the system schemas are always excluded regardless of the table filter settings. The system schemas are: