- Docs Home
- About TiDB Cloud
- Get Started
- Develop Applications
- Quick Start
- Example Applications
- Connect to TiDB
- Design Database Schema
- Write Data
- Read Data
- Cloud Native Development Environment
- Manage Cluster
- Plan Your Cluster
- Create a TiDB Cluster
- Connect to Your TiDB Cluster
- Set Up VPC Peering Connections
- Use an HTAP Cluster with TiFlash
- Scale a TiDB Cluster
- Upgrade a TiDB Cluster
- Delete a TiDB Cluster
- Migrate Data
- Import Sample Data
- Migrate Data into TiDB
- Configure Amazon S3 Access and GCS Access
- Migrate from MySQL-Compatible Databases
- Migrate Incremental Data from MySQL-Compatible Databases
- Migrate from Amazon Aurora MySQL in Bulk
- Import or Migrate from Amazon S3 or GCS to TiDB Cloud
- Import CSV Files from Amazon S3 or GCS into TiDB Cloud
- Import Apache Parquet Files from Amazon S3 or GCS into TiDB Cloud
- Troubleshoot Access Denied Errors during Data Import from Amazon S3
- Export Data from TiDB
- Back Up and Restore
- Monitor and Alert
- Tune Performance
- Analyze Performance
- SQL Tuning
- Understanding the Query Execution Plan
- SQL Optimization Process
- Logic Optimization
- Physical Optimization
- Prepare Execution Plan Cache
- Control Execution Plans
- TiKV Follower Read
- Coprocessor Cache
- Garbage Collection (GC)
- Tune TiFlash performance
- Manage User Access
- TiDB Cluster Architecture
- TiDB Cloud Cluster Limits and Quotas
- TiDB Limitations
- Explore SQL with TiDB
- SQL Language Structure and Syntax
- SQL Statements
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER TABLE COMPACT
CREATE [GLOBAL|SESSION] BINDING
CREATE TABLE LIKE
DROP [GLOBAL|SESSION] BINDING
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW CHARACTER SET
SHOW [FULL] COLUMNS FROM
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DRAINER STATUS
SHOW [FULL] FIELDS FROM
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW [FULL] PROCESSSLIST
SHOW PUMP STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
- Data Types
- Functions and Operators
- 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
- Locking 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
- Generated Columns
- SQL Mode
- Table Attributes
- Temporary Tables
- Cached Tables
- Character Set and Collation
- Read Historical Data
- System Tables
- System Variables
- Storage Engines
- Table Filter
- Troubleshoot Inconsistency Between Data and Indexes
- Release Notes
The TiDB migration 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.
Since TiDB 4.0, all TiDB migration 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.
./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
./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.
[mydumper] filter = ['foo*.*', 'bar*.*']
[filter] rules = ['foo*.*', 'bar*.*'] [[sink.dispatchers]] matcher = ['db1.*', 'db2.*', 'db3.*'] dispatcher = 'ts'
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
This section is not applicable to TiDB Cloud. Currently, TiDB Cloud only supports one table filter rule.
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 in the default configuration. The system schemas are: