Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 分布式 | 利用 TiDB DM 将数据从 MySQL 迁移至 DBLE
|
username: 爱可生开源社区
Author: Shen Guangyu
Member of Aikesen South District DBA team, mainly responsible for MySQL fault handling and performance optimization. Dedicated to technology and responsible for customers.
Source: Original submission
- Produced by Aikesen Open Source Community, original content cannot be used without authorization, please contact the editor and indicate the source for reprinting.
I. Background
A certain customer’s MySQL instance has a large amount of data (over a terabyte), and it needs to quickly migrate the full/incremental data from MySQL to DBLE. TiDB DM supports multi-threaded data export/import and incremental synchronization, so TiDB DM was chosen as the data migration tool. This article uses this case as a background to introduce the method of using TiDB DM to migrate data from MySQL to DBLE and some encountered issues.
II. Data Migration Diagram and Server Environment
(1) The data migration diagram is as follows:
(2) The server environment is as follows:
| Server IP | Role | Port | Version | Remarks |
|---|---|---|---|---|
| 10.186.65.83 | dm master | 8261/8291 | V2.0.7 | tiup installation node |
| 10.186.65.118 | dm worker | 8262 | V2.0.7 | |
| 10.186.65.14 | SRC MySQL | 4490 | MySQL 8.0.18 | |
| 10.186.65.4 | DBLE | 8066 | DBLE 3.21.10.6 | |
| 10.186.65.61 | DBLE datanode | 4408 | MySQL 8.0.25 | |
| 10.186.65.65 | DBLE datanode | 4408 | MySQL 8.0.25 |
III. Installing TiDB DM
(1) Prepare the environment and install tiup
# Create tidb user on dm master and worker nodes, password is dmadmin, and configure sudo
shell> useradd tidb
shell> echo "dmadmin" | passwd tidb --stdin
shell> echo "tidb ALL=(ALL) NOPASSWD: ALL" > /etc/sudoers.d/tidb
# Switch to tidb user, generate key, and set up ssh trust, all machines in the dm cluster need to set up ssh trust
shell> su - tidb
shell> ssh-keygen -t rsa
shell> ssh-copy-id -i ~/.ssh/id_rsa.pub tidb@10.186.65.118
shell> ssh-copy-id -i ~/.ssh/id_rsa.pub tidb@10.186.65.83
# Install tiup under tidb user
shell> curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
shell> source .bash_profile
shell> which tiup
~/.tiup/bin/tiup
# Install dmctl
shell> tiup install dm dmctl:v2.0.7
component dm version v1.10.2 is already installed
download https://tiup-mirrors.pingcap.com/dmctl-v2.0.7-linux-amd64.tar.gz 26.92 MiB / 26.92 MiB 100.00% 10.01 MiB/s
(2) Install dm-cluster
# Generate configuration template
shell> tiup dm template > dm_topology.yaml
# The modified configuration file is as follows:
shell> cat dm_topology.yaml |grep -v ^[#-] |grep -v ^$
global:
user: "tidb"
ssh_port: 22
deploy_dir: "/home/tidb/dm/deploy"
data_dir: "/home/tidb/dm/data"
master_servers:
- host: 10.186.65.83
worker_servers:
- host: 10.186.65.118
monitoring_servers:
- host: 10.186.65.83
grafana_servers:
- host: 10.186.65.83
alertmanager_servers:
- host: 10.186.65.83
# Install and start the DM cluster
shell> tiup dm deploy dm-test v2.0.7 ./dm_topology.yaml --user tidb -i /home/tidb/.ssh/id_rsa
shell> tiup dm start dm-test
# View DM cluster
[tidb@10_186_65_83 ~]$ tiup dm display dm-test
tiup is checking updates for component dm ...
Starting component `dm`: /home/tidb/.tiup/components/dm/v1.10.2/tiup-dm display dm-test
Cluster type: dm
Cluster name: dm-test
Cluster version: v2.0.7
Deploy user: tidb
SSH type: builtin
Grafana URL: http://10.186.65.83:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.186.65.83:9093 alertmanager 10.186.65.83 9093/9094 linux/x86_64 Up /home/tidb/dm/data/alertmanager-9093 /home/tidb/dm/deploy/alertmanager-9093
10.186.65.83:8261 dm-master 10.186.65.83 8261/8291 linux/x86_64 Healthy|L /home/tidb/dm/data/dm-master-8261 /home/tidb/dm/deploy/dm-master-8261
10.186.65.118:8262 dm-worker 10.186.65.118 8262 linux/x86_64 Free /home/tidb/dm/data/dm-worker-8262 /home/tidb/dm/deploy/dm-worker-8262
10.186.65.83:3000 grafana 10.186.65.83 3000 linux/x86_64 Up - /home/tidb/dm/deploy/grafana-3000
10.186.65.83:9090 prometheus 10.186.65.83 9090 linux/x86_64 Up /home/tidb/dm/data/prometheus-9090 /home/tidb/dm/deploy/prometheus-9090
Total nodes: 5
IV. DBLE Configuration
(1) db.xml
<?xml version="1.0"?>
<dble:db xmlns:dble="http://dble.cloud/">
<dbGroup name="dbGroup1" rwSplitMode="0" delayThreshold="-1">
<heartbeat>select 1</heartbeat>
<dbInstance name="host_1" url="10.186.65.61:4408" user="sgy" password="QfnHoIeIYL7ZT+EdJMYNuiLw6glbx2hEyrqxK+uxFPU8vja8vZHCLEKvKBOt1vOJdXCCgW7wNidJaTYWORDaUg==" maxCon="2000" minCon="50" primary="true" usingDecrypt="true"></dbInstance>
</dbGroup>
<dbGroup name="dbGroup2" rwSplitMode="0" delayThreshold="-1">
<heartbeat>select 1</heartbeat>
<dbInstance name="host_2" url="10.186.65.65:4408" user="sgy" password="d+rG/82+4h21ARS8D6Gu5MIFQ2UBC0h+ZS0EI7bvWAtTJUBsKuFY8+AVZtW1pZk+qtISoI2WpVt29Z9eqMRP5A==" maxCon="2000" minCon="50" primary="true" usingDecrypt="true"></dbInstance>
</dbGroup>
</dble:db>
# Note: User passwords are encrypted strings
(2) sharding.xml
<?xml version="1.0"?>
<dble:sharding xmlns:dble="http://dble.cloud/" >
<schema name="dm_meta" sqlMaxLimit="-1">
<singleTable name="mysql_sync_to_dble_loader_checkpoint" shardingNode="dm_meta" />
<singleTable name="mysql_sync_to_dble_syncer_checkpoint" shardingNode="dm_meta" />
</schema>
<schema name="sbtest" sqlMaxLimit="-1">
<shardingTable name="sbtest1" shardingNode="dn1,dn2,dn3,dn4" sqlMaxLimit="-1" shardingColumn="id" function="pro_func_jumpHash"></shardingTable>
<shardingTable name="sbtest2" shardingNode="dn1,dn2,dn3,dn4" sqlMaxLimit="-1" shardingColumn="id" function="pro_func_jumpHash"></shardingTable>
<shardingTable name="t1" shardingNode="dn1,dn2,dn3,dn4" sqlMaxLimit="-1" shardingColumn="c1" function="pro_func_jumpHash"></shardingTable>
<shardingTable name="t2" shardingNode="dn1,dn2,dn3,dn4" sqlMaxLimit="-1" shardingColumn="c1" function="pro_func_jumpHash"></shardingTable>
</schema>
<shardingNode name="dn1" dbGroup="dbGroup1" database="dh_dn1"></shardingNode>
<shardingNode name="dn2" dbGroup="dbGroup1" database="dh_dn2"></shardingNode>
<shardingNode name="dn3" dbGroup="dbGroup2" database="dh_dn3"></shardingNode>
<shardingNode name="dn4" dbGroup="dbGroup2" database="dh_dn4"></shardingNode>
<shardingNode name="dm_meta" dbGroup="dbGroup1" database="dm_meta"/>
<function name="pro_func_jumpHash" class="jumpStringHash">
<property name="partitionCount">4</property>
<property name="hashSlice">0:0</property>
</function>
</dble:sharding>
Note: You need to pre-configure the schema and two tables for DM synchronization, the table names are
DM synchronization task name + _loader_checkpoint, i.e., table mysql_sync_to_dble_loader_checkpoint
DM synchronization task name + _syncer_checkpoint, i.e., table mysql_sync_to_dble_syncer_checkpoint
(3) user.xml
<?xml version="1.0"?>
<dble:user xmlns:dble="http://dble.cloud/">
<managerUser name="root" password="RYQdYYnzbcZlDuhV4LhJxFTM8kbU3d0y183LU+FgRWzNscvvvFPcdmeEKMdswMyzIaA+kObcozliHEYAlT0AjA==" usingDecrypt="true"></managerUser>
<shardingUser name="sz" schemas="sbtest,dm_meta" password="jyaXmGFU+mdTGbUZIVbdEcVwItLPI+Yjxdq4wkOzhAYRB29WGZJd0/PkTJh3ky/v4E2yYoqgUzJXPzPRPiQF0Q==" usingDecrypt="true" readOnly="false" maxCon="1000"></shardingUser>
</dble:user>
# Note: User passwords are encrypted strings
(4) Log in to the DBLE management terminal and execute the command to create physical databases
shell> /data/mysql/base/5.7.36/bin/mysql -uroot -padmin -h 10.186.65.4 -P 9066
dble> create database @@shardingNode = 'dn$1-4';
Query OK, 1 row affected (0.06 sec)
dble> create database @@shardingNode = 'dm_meta';
Query OK, 1 row affected (0.01 sec)
V. Create Test Data on Source MySQL for Benchmarking
# Execute prepare to initialize 1 million rows of data
/usr/share/sysbench/oltp_insert.lua --mysql-db=sbtest --mysql-host=10.186.65.14 --mysql-port=4490 --mysql-user=sgy --mysql-password=admin --tables=2 --table-size=1000000 --threads=16 --time=60 --report-interval=1 --max-requests=0 --percentile=95 --mysql-ignore-errors=1062 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
# Use sysbench to continuously write data
shell> /usr/share/sysbench/oltp_read_write.lua --mysql-db=sbtest --mysql-host=10.186.65.14 --mysql-port=4490 --mysql-user=sgy --mysql-password=admin --tables=2 --table-size=2000000 --threads=16 --time=6000 --report-interval=1 --max-requests=0 --percentile=95 --mysql-ignore-errors=1062 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 16 tps: 585.44 qps: 11916.49 (r/w/o: 8360.33/1282.63/2273.53) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 744.18 qps: 14819.02 (r/w/o: 10379.16/1609.36/2830.50) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 605.11 qps: 12144.20 (r/w/o: 8500.54/1321.24/2322.42) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 773.91 qps: 15558.16 (r/w/o: 10881.71/1737.79/2938.65) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
VI. Start Data Synchronization Task
(1) Configure Data Source
# Use dmctl tool to generate encrypted password string, each execution will produce a different encrypted string
shell> tiup dmctl encrypt 'admin'
dmXgktpuF18RP1mDN/B2UkA6pDN9PQ==
# MySQL database configuration file is as follows
shell> cat mysql_source_14_4490.yaml
source-id: "mysql_source_14_4490"
from:
host: "10.186.65.14"
port: 4490
user: "sgy"
password: "dmXgktpuF18RP1mDN/B2UkA6pDN9PQ=="
# Create data source
shell> tiup dmctl --master-addr 10.186.65.83:8261 operate-source create ./mysql_source_14_4490.yaml
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.7/dmctl/dmctl --master-addr 10.186.65.83:8261 operate-source create ./mysql_source_14_4490.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql_source_14_4490",
"worker": "dm-10.186.65.118-8262"
}
]
}
# View data source
shell> tiup dmctl --master-addr 10.186.65.83:8261 operate-source show
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql_source_14_4490",
"worker": "dm-10.186.65.118-8262"
}
]
}
(2) Configure Synchronization Task
# Synchronization configuration file is as follows
shell> cat mysql_sync_to_dble.yaml
name: "mysql_sync_to_dble" # Synchronization task name
task-mode: "all" # Full + Binlog real-time synchronization
clean-dump-file: true
timezone: "Asia/Shanghai"
ignore-checking-items: ["auto_increment_ID"]
target-database:
host: "10.186.65.4" # DBLE host IP address
port: 8066 # DBLE traffic port
user: "sz" # User to connect to DBLE
password: "KRfSNtdxe63tehpnCYoCz0ABdUGivg==" # Password for connecting to DBLE user, generated by tiup dmctl encrypt 'password'
mysql-instances:
-
source-id: "mysql_source_14_4490"
block-allow-list: "global"
mydumper-config-name: "global"
loader-config-name: "global"
syncer-config-name: "global"
block
|
username: