| title | summary |
|---|---|
Migration FAQs |
Learn about the FAQs related to data migration. |
This document summarizes the FAQs related to TiDB data migration.
Yes. See Mydumper Instructions.
Yes. See Loader Instructions.
Because TiDB supports most MySQL syntax, generally you can migrate your applications to TiDB without changing a single line of code in most cases.
If I accidentally import the MySQL user table into TiDB, or forget the password and cannot log in, how to deal with it?
Restart the TiDB service, add the -skip-grant-table=true parameter in the configuration file. Log into the cluster without password and recreate the user, or recreate the mysql.user table. For the specific table schema, search the official documentation.
TiDB can provide services while Loader is running because Loader inserts the data logically. But do not perform the related DDL operations.
Currently, TiDB does not support select into outfile. You can use the following methods to export the data in TiDB:
- See MySQL uses mysqldump to export part of the table data in Chinese and export data using mysqldump and the
WHEREclause. - Use the MySQL client to export the results of
selectto a file.
To migrate all the data or migrate incrementally from DB2 or Oracle to TiDB, see the following solution:
- Use the official migration tool of Oracle, such as OGG, Gateway, CDC (Change Data Capture).
- Develop a program for importing and exporting data.
- Export Spool as text file, and import data using Load infile.
- Use a third-party data migration tool.
Currently, it is recommended to use OGG.
Error: java.sql.BatchUpdateExecption:statement count 5001 exceeds the transaction limitation while using Sqoop to write data into TiDB in batches
In Sqoop, --batch means committing 100 statements in each batch, but by default each statement contains 100 SQL statements. So, 100 * 100 = 10000 SQL statements, which exceeds 5000, the maximum number of statements allowed in a single TiDB transaction.
Two solutions:
-
Add the
-Dsqoop.export.records.per.statement=10option as follows:{{< copyable "shell-regular" >}}
sqoop export \ -Dsqoop.export.records.per.statement=10 \ --connect jdbc:mysql://mysql.example.com/sqoop \ --username sqoop ${user} \ --password ${passwd} \ --table ${tab_name} \ --export-dir ${dir} \ --batch
-
You can also increase the limited number of statements in a single TiDB transaction, but this will consume more memory.
Yes, it does. And it supports DDL as well. For details, see how TiDB reads data from history versions.
See Parsing TiDB online data synchronization tool Syncerin Chinese.
See Syncer User Guide.
Download and import Syncer Json to Grafana. Edit the Prometheus configuration file and add the following content:
- job_name: 'syncer_ops' // task name
static_configs:
- targets: [’10.10.1.1:10096’] // Syncer monitoring address and port, informing Prometheus to pull the data of Syncer
Restart Prometheus.
Is there a current solution to replicating data from TiDB to other databases like HBase and Elasticsearch?
No. Currently, the data replication depends on the application itself.
Yes. For details, see Syncer User Guide
Frequent DDL operations may affect the replication speed. For Sycner, DDL operations are executed serially. When DDL operations are executed during data replication, data will be replicated serially and thus the replication speed will be slowed down.
If the machine that Syncer is in is broken and the directory of the syncer.meta file is lost, what should I do?
When you replicate data using Syncer GTID, the syncer.meta file is constantly updated during the replication process. The current version of Syncer does not contain the design for high availability. The syncer.meta configuration file of Syncer is directly stored on the hard disks, which is similar to other tools in the MySQL ecosystem, such as Mydumper.
Two solutions:
- Put the
syncer.metafile in a relatively secure disk. For example, use disks with RAID 1. - Restore the location information of history replication according to the monitoring data that Syncer reports to Prometheus regularly. But the location information might be inaccurate due to the delay when a large amount of data is replicated.
If the downstream TiDB data is not consistent with the MySQL data during the replication process of Syncer, will DML operations cause exits?
- If the data exists in the upstream MySQL but does not exist in the downstream TiDB, when the upstream MySQL performs the
UPDATEorDELETEoperation on this row of data, Syncer will not report an error and the replication process will not exit, and this row of data does not exist in the downstream. - If a conflict exists in the primary key indexes or the unique indexes in the downstream, preforming the
UPDATEoperation will cause an exit and performing theINSERToperation will not cause an exit.
It is recommended to build a multi-source MySQL -> TiDB real-time replication environment using Syncer tool. You can migrate the read and write traffic in batches by editing the network configuration as needed. Deploy a stable network LB (HAproxy, LVS, F5, DNS, etc.) on the upper layer, in order to implement seamless migration by directly editing the network configuration.
The total read capacity has no limit. You can increase the read capacity by adding more TiDB servers. Generally the write capacity has no limit as well. You can increase the write capacity by adding more TiKV nodes.
Due to the limitation of the underlying storage engine, each key-value entry (one row) in TiDB should be no more than 6MB.
Distributed transactions need two-phase commit and the bottom layer performs the Raft replication. If a transaction is very large, the commit process would be quite slow and the write conflict is more likely to occur. Moreover, the rollback of a failed transaction leads to an unnecessary performance penalty. To avoid these problems, we limit the total size of key-value entries to no more than 100MB in a transaction by default. If you need larger transactions, modify the value of txn-total-size-limit in the TiDB configuration file. The maximum value of this configuration item is up to 10G. The actual limitation is also affected by the physical memory of the machine.
There are similar limits on Google Cloud Spanner.
When you import data, insert in batches and keep the number of rows within 10,000 for each batch.
None of the DELETE, TRUNCATE and DROP operations release data immediately. For the TRUNCATE and DROP operations, after the TiDB GC (Garbage Collection) time (10 minutes by default), the data is deleted and the space is released. For the DELETE operation, the data is deleted but the space is not released according to TiDB GC. When subsequent data is written into RocksDB and executes COMPACT, the space is reused.
No. None of the DDL operations can be executed on the target table when you load data, otherwise the data fails to be loaded.
Yes. But the load data does not support the replace into syntax.
Deleting a large amount of data leaves a lot of useless keys, affecting the query efficiency. Currently the Region Merge feature is in development, which is expected to solve this problem. For details, see the deleting data section in TiDB Best Practices.
When deleting a large amount of data, it is recommended to use Delete * from t where xx limit 5000;. It deletes through the loop and uses Affected Rows == 0 as a condition to end the loop, so as not to exceed the limit of transaction size. With the prerequisite of meeting business filtering logic, it is recommended to add a strong filter index column or directly use the primary key to select the range, such as id >= 5000*n+m and id < 5000*(n+1)+m.
If the amount of data that needs to be deleted at a time is very large, this loop method will get slower and slower because each deletion traverses backward. After deleting the previous data, lots of deleted flags remain for a short period (then all will be processed by Garbage Collection) and influence the following Delete statement. If possible, it is recommended to refine the Where condition. See details in TiDB Best Practices.
- The Lightning tool is developed for distributed data import. It should be noted that the data import process does not perform a complete transaction process for performance reasons. Therefore, the ACID constraint of the data being imported during the import process cannot be guaranteed. The ACID constraint of the imported data can only be guaranteed after the entire import process ends. Therefore, the applicable scenarios mainly include importing new data (such as a new table or a new index) or the full backup and restoring (truncate the original table and then import data).
- Data loading in TiDB is related to the status of disks and the whole cluster. When loading data, pay attention to metrics like the disk usage rate of the host, TiClient Error, Backoff, Thread CPU and so on. You can analyze the bottlenecks using these metrics.
You can configure concurrent GC to increase the speed of reclaiming storage space. The default concurrency is 1, and you can modify it to at most 50% of the number of TiKV instances using the following command:
{{< copyable "sql" >}}