Guide for Migrating MySQL Data to TiDB

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: mysql数据迁移至Tidb操作指南

| username: Hacker_zuGnSsfP

  1. 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

  1. 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)
  1. 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.

  1. 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"
  1. 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.

| username: 像风一样的男子 | Original post link

The simplest method should be to use mysqldump to export the SQL file from MySQL, and then source it into TiDB.

| username: TiDBer_小阿飞 | Original post link

Are they all offline? If not, what about incremental data synchronization?

| username: 像风一样的男子 | Original post link

Use DM for incremental.

| username: zhanggame1 | Original post link

TiDB has a DM that supports both full and incremental replication.

| username: Fly-bird | Original post link

Migrating from MySQL to TiDB using DM. Professional!