Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: mysql数据迁移至Tidb操作指南

- Data Migration (Migrating from MySQL to TiDB)
Introduction:
This section introduces how to use TiDB Lightning to migrate full data from MySQL SQL files to TiDB.
1.1 Deploy TiDB Lightning
Precautions:
● After TiDB Lightning runs, the TiDB cluster will not be able to provide services normally.
● If tidb-lightning crashes, the cluster will remain in “import mode”. If you forget to switch back to “normal mode”, the cluster will generate a large number of uncompressed files, consuming CPU and causing delays. At this point, you need to manually switch the cluster back to “normal mode” using tidb-lightning-ctl:
bin/tidb-lightning-ctl --switch-mode=normal
● TiDB Lightning requires the following permissions on the downstream TiDB:
Permissions: Scope
SELECT: Tables
INSERT: Tables
UPDATE: Tables
DELETE: Tables
CREATE: Databases, Tables
DROP: Databases, Tables
ALTER: Tables
Download the TiDB Lightning installation package (TiDB Lightning is fully compatible with lower versions of the TiDB cluster, it is recommended to choose the latest stable version)
[root@bogon ~]# wget https://download.pingcap.org/tidb-toolkit-v4.0.8-linux-amd64.tar.gz
[root@localhost ~]# tar -xf tidb-toolkit-v4.0.8-linux-amd64.tar.gz
1.2 Data Migration
- Generate MySQL source data
[root@localhost ~]# yum -y install mariadb-server
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# ll t100w.sql
-rw-r--r--. 1 root root 51253648 Dec 9 21:06 t100w.sql
[root@localhost ~]# mysql
MariaDB [(none)]> create database qq;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use qq
Database changed
MariaDB [qq]> source /root/t100w.sql
Check data:
MariaDB [qq]> show tables;
+--------------+
| Tables_in_qq |
+--------------+
| t100w |
+--------------+
1 row in set (0.00 sec)
MariaDB [qq]> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 1030345 |
+----------+
1 row in set (0.22 sec)
- Export data
Use dumpling to export data from MySQL as follows:
[root@localhost ~]# cd tidb-toolkit-v4.0.8-linux-amd64
[root@localhost ~]# ./bin/dumpling -h 127.0.0.1 -P 3306 -u root -t 16 -F 256MB -B qq -o /opt/
Explanation:
-B test: Export from the test database.
-f test.t[12]: Only export the test.t1 and test.t2 tables.
-t 16: Use 16 threads to export data.
-F 256MB: Split each table into multiple files, each file is about 256 MB.
- Configure the data source for TiDB Lightning
[root@localhost tidb-toolkit-v4.0.8-linux-amd64]# vi tidb-lightning.toml
[lightning]
# The number of concurrent data conversions, default is the number of logical CPUs, no need to configure.
# In the case of mixed deployment, it can be configured to 75% of the logical CPU size.
# region-concurrency =
# Log
level = "info"
file = "tidb-lightning.log"
[tikv-importer]
# Set backend to local mode
backend = "local"
# Set local temporary storage path
sorted-kv-dir = "/tidb"
[mydumper]
# Source data directory.
data-source-dir = "/opt"
[tidb]
# Information of the target cluster. The listening address of tidb-server, fill in one.
host = "192.168.43.101"
port = 4000
user = "root"
password = ""
# Table schema information is obtained from the "status port" of TiDB.
status-port = 10080
# Address of pd-server, fill in one
pd-addr = "192.168.43.101:2379"
- Run tidb-lightning. If you start the program directly in the command line with nohup, it may exit due to the SIGHUP signal. It is recommended to put nohup in the script, such as:
[root@localhost ~]# cd tidb-toolkit-v4.0.8-linux-amd64
[root@localhost tidb-toolkit-v4.0.8-linux-amd64]# vi tidb-lightning.sh
#!/bin/bash
nohup ./bin/tidb-lightning -config tidb-lightning.toml > nohup.out &
[root@localhost tidb-toolkit-v4.0.8-linux-amd64]# sh tidb-lightning.sh
Tip: If there is a running import task, it is recommended to upgrade TiDB Lightning after the task is completed. Otherwise, you may need to re-import from the beginning, as it cannot be guaranteed that breakpoints can work across versions.