- 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
- Key Features
- Get Started
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- SQL Language Structure
- Data Types
- Numeric Types
- Date and Time Types
- String 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
- Miscellaneous Functions
- Precision Math
- SQL Statements
CREATE TABLE LIKE
SET [NAMES|CHARACTER SET]
SET [GLOBAL|SESSION] <variable>
SHOW CHARACTER SET
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW [FULL] FIELDS FROM
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW [FULL] PROCESSSLIST
SHOW [FULL] TABLES
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
- System Databases
- Key Monitoring Metrics
- Best Practices
- TiDB Binlog
- TiDB Lightning
- All Releases
You can use
mydumper to export data from MySQL and
loader to import the data into TiDB.
Although TiDB also supports the official
mysqldumptool from MySQL for data migration, it is not recommended to use it. Its performance is much lower than
loaderand it takes much time to migrate large amounts of data. It is important to use the
mydumperprovided by TiDB and not the upstream
mydumperversion. See Mydumper for more information.
loader can be downloaded as part of Enterprise Tools.
mydumper tool to export data from MySQL by using the following command:
./bin/mydumper -h 127.0.0.1 -P 3306 -u root -t 16 -F 64 -B test -T t1,t2 --skip-tz-utc -o ./var/test
In this command,
-B test: means the data is exported from the
-T t1,t2: means only the
t2tables are exported.
-t 16: means 16 threads are used to export the data.
-F 64: means a table is partitioned into chunks and one chunk is 64MB.
--skip-tz-utc: the purpose of adding this parameter is to ignore the inconsistency of time zone setting between MySQL and the data exporting machine and to disable automatic conversion.
On the Cloud platforms which require the
super privilege, such as on the Amazon RDS, add the
--no-locksparameter to the command. If not, you might get the error message that you don't have the privilege.
loader to import the data from MySQL to TiDB. See Loader instructions for more information.
./bin/loader -h 127.0.0.1 -u root -P 4000 -t 32 -d ./var/test
After the data is imported, you can view the data in TiDB using the MySQL client:
mysql -h127.0.0.1 -P4000 -uroot mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ mysql> select * from t1; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+ mysql> select * from t2; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | +----+------+
To migrate data quickly, especially for huge amount of data, you can refer to the following recommendations.
- Keep the exported data file as small as possible and it is recommended keep it within 64M. You can use the
-Fparameter to set the value.
- You can adjust the
loaderbased on the number and the load of TiKV instances. For example, if there are three TiKV instances,
-tcan be set to 3 * (1 ~ n). If the load of TiKV is too high and the log
backoffer.maxSleep 15000ms is exceededis displayed many times, decrease the value of
-t; otherwise, increase it.
- The total size of the exported files is 214G. A single table has 8 columns and 2 billion rows.
- The cluster topology:
- 12 TiKV instances: 4 nodes, 3 TiKV instances per node
- 4 TiDB instances
- 3 PD instances
- The configuration of each node:
- CPU: Intel Xeon E5-2670 v3 @ 2.30GHz
- 48 vCPU [2 x 12 physical cores]
- Memory: 128G
- Disk: sda [raid 10, 300G] sdb[RAID 5, 2T]
- Operating System: CentOS 7.3
mydumperis set to 16 and the
loaderis set to 64.
Results: It takes 11 hours to import all the data, which is 19.4G/hour.