- Introduction
- Concepts
- Architecture
- 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
- How-to
- Get Started
- Deploy
- Hardware Recommendations
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- Data Migration with Ansible
- Configure
- Secure
- Transport Layer Security (TLS)
- Generate Self-signed Certificates
- Monitor
- Migrate
- Maintain
- Common Ansible Operations
- Backup and Restore
- Identify Abnormal Queries
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Data Types
- Functions and Operators
- Function and Operator Reference
- 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
- List of Expressions for Pushdown
- 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 TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
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
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 CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW SCHEMAS
SHOW STATUS
SHOW [FULL] TABLES
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SPLIT REGION
START TRANSACTION
TRACE
TRUNCATE
UPDATE
USE
- Constraints
- Generated Columns
- Partitioning
- Character Set
- SQL Mode
- Views
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Overview
- Understanding the Query Execution Plan
- The Blocklist of Optimization Rules and Expression Pushdown
- Introduction to Statistics
- TopN and Limit Push Down
- Optimizer Hints
- 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
- TiSpark
- TiKV
- TiDB Binlog
- Tools
- TiDB in Kubernetes
- FAQs
- Support
- Contribute
- Releases
- All Releases
- v3.0
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v3.0).
Table Filter
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 foo*
and 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.
Usage
CLI
Table filters can be applied to the tools using multiple -f
or --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.
./dumpling -f 'foo*.*' -f 'bar*.*' -P 3306 -o /tmp/data/ # ^~~~~~~~~~~~~~~~~~~~~~~
./tidb-lightning -f 'foo*.*' -f 'bar*.*' -d /tmp/data/ --backend tidb # ^~~~~~~~~~~~~~~~~~~~~~~
TOML configuration files
Table filters in TOML files are specified as array of strings. The following lists the example usage in each tool.
Lightning:
[mydumper] filter = ['foo*.*', 'bar*.*']
Syntax
Plain table names
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 (
0
to9
) - letters (
a
toz
,A
toZ
) $
_
- 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.
Wildcards
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.
File import
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:
employees.*
*.WorkOrder
the following two invocations are equivalent:
./dumpling -f '@config/filter.txt'
./dumpling -f 'employees.*' -f '*.WorkOrder'
A filter file cannot further import another file.
Comments and blank lines
Inside a filter file, leading and trailing white-spaces of every line are trimmed. Furthermore, blank lines (empty strings) are ignored.
A leading #
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
Exclusion
An !
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
Escape character
To turn a special character into an identifier character, precede it with a backslash \
.
db\.with\.dots.*
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\0
,\r
,\n
and\t
currently are meaningless.
Quoted identifier
Besides \
, special characters can also be suppressed by quoting using "
or `
.
"db.with.dots"."tbl\1"
`db.with.dots`.`tbl\2`
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*.*
Regular expression
In case very complex rules are needed, each pattern can be written as a regular expression delimited with /
:
/^db\d{2,}$/./^tbl\d{2,}$/
These regular expressions use the Go dialect. The pattern is matched if the identifier contains a substring matching the regular expression. For instance, /b/
matches db01
.
Every /
in the regular expression must be escaped as \/
, including inside […]
. You cannot place an unescaped /
between \Q…\E
.
Multiple rules
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 |
---|---|---|---|---|
irrelevant.table | Default (reject) | |||
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:
INFORMATION_SCHEMA
PERFORMANCE_SCHEMA
METRICS_SCHEMA
INSPECTION_SCHEMA
mysql
sys