This document describes how to back up and restore the data of TiDB. Currently, this document only covers full backup and restoration.
Here we assume that the TiDB service information is as follows:
Use the following tools for data backup and restoration:
mydumper: to export data from TiDB
loader: to import data into TiDB
# Download the tool package. wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.tar.gz wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.sha256 # Check the file integrity. If the result is OK, the file is correct. sha256sum -c tidb-enterprise-tools-latest-linux-amd64.sha256 # Extract the package. tar -xzf tidb-enterprise-tools-latest-linux-amd64.tar.gz cd tidb-enterprise-tools-latest-linux-amd64
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.
loaderis more powerful. For more information, see https://github.com/maxbube/mydumper.
To quickly backup and restore data (especially large amounts of data), refer to the following recommendations:
-Fparameter to set the value.
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.
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.
mydumper to backup data from TiDB.
./bin/mydumper -h 127.0.0.1 -P 4000 -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.
To restore data into TiDB, use
loader to import the previously exported data. 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 | +----+------+