Error Occurred While Exporting TiDB Database Using mydumper

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

Original topic: mydumper导出tidb数据库报错

| username: TiDBer_OB4kHrS7

[TiDB Usage Environment] Production Environment
[TiDB Version] V5.3.3
[Reproduction Path] Use mydumper to export a database under the TiDB database
[Encountered Problem: Problem Phenomenon and Impact]
Using mydumper to export a database under the TiDB database, the error message is as follows:

cat mydumper_oms_online.sh

#!/bin/sh
YOUR_USER=XXXXXX
YOUR_PASSWD=XXXXXX
/usr/bin/mydumper -u $YOUR_USER -p $YOUR_PASSWD -P 4000 -h 10.8.123.5 -l 7200 --kill-long-queries --lock-all-tables -B oms_online --max-rows 10000 -z -o /data/oms_online/
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
cat mydumper_oms_online.log

** (mydumper:25456): WARNING **: Killed a query that was running for 505284s

** (mydumper:25456): WARNING **: Killed a query that was running for 500434s

** (mydumper:25456): WARNING **: No table found to lock

** (mydumper:25456): CRITICAL **: Error showing tables on: /data/oms_online/ - Could not execute query: No database selected

** (mydumper:25456): CRITICAL **: Error dumping create database (/data/oms_online/): Unknown database ‘/data/oms_online/’

– metadata.partial.0 0

– metadata 228

Started dump at: 2023-11-03 10:46:23

[master]

Channel_Name = ‘’ # It can be used to set up replication FOR CHANNEL

File = tidb-binlog
Position = 445377303943577629
Executed_Gtid_Set =

Finished dump at: 2023-11-03 10:46:23

** (mydumper:25456): CRITICAL **: Backup directory not removed: export-20231103-104622

| username: TiDBer_小阿飞 | Original post link

Warning

The Mydumper tool previously maintained by PingCAP was forked from the mydumper project and optimized for TiDB features. Mydumper is no longer being developed for new features, and most of its functionalities have been replaced by Dumpling. The Dumpling tool is written in Go and supports more optimizations specific to TiDB. It is strongly recommended to switch to Dumpling.
https://docs.pingcap.com/zh/tidb/v4.0/mydumper-overview

| username: TiDBer_OB4kHrS7 | Original post link

Dumpling can only be imported into MySQL using loader, but loader is no longer maintained and there are many issues during the import process. So I tried using mydumper to export and myloader to import, but this didn’t work either.

| username: TiDBer_小阿飞 | Original post link

What should we do then? This tool is no longer maintained, so we can only check the parameters again or post a request on the official forum.

| username: TiDBer_小阿飞 | Original post link

Does this directory exist?

| username: TiDBer_OB4kHrS7 | Original post link

Checked several times, didn’t see any issues.

| username: TiDBer_OB4kHrS7 | Original post link

Yes, both the database and the export directory are available.

| username: 啦啦啦啦啦 | Original post link

Try removing the -z.

| username: TiDBer_OB4kHrS7 | Original post link

Removing the -z works, why is that?

| username: TiDBer_小阿飞 | Original post link

When using --tidb-snapshot for export, an error occurs. How to handle it?

You need to add a parameter --skip-tz-utc when executing the command. If not set, Mydumper will pre-set the UTC time zone, and then when setting tidb-snapshot, a time zone conversion will be performed, which will cause issues.

| username: TiDBer_OB4kHrS7 | Original post link

So if you remove the -z parameter, will there still be issues when importing into MySQL?

| username: TiDBer_小阿飞 | Original post link

Mydumper and myloader are a pair anyway. There is no official documentation about myloader, so I’m not sure if any issues will arise. You can test by exporting one or two tables on a small scale.

| username: Fly-bird | Original post link

TiDB is Dumpling, right?

| username: TiDBer_OB4kHrS7 | Original post link

TiDB uses Dumpling, but there were errors when importing into MySQL with Loader, so I switched to using Mydumper for export and Myloader for import to try.

| username: TiDBer_OB4kHrS7 | Original post link

I can’t export data from TiDB using mydumper; it reports a bunch of errors.

| username: Miracle | Original post link

Dumpling generates SQL files, which can be directly imported using MySQL source. If there are many files, you can write a script. If the data volume is not very large, you can use Navicat to migrate to MySQL.

| username: TiDBer_OB4kHrS7 | Original post link

Using a script to execute the SQL file exported by Dumpling in MySQL, after execution, the statistical results on both sides do not match. The data volume is several hundred GB, which is difficult to handle.

| username: Miracle | Original post link

Are there any data changes on the TiDB side after using dumpling?

| username: TiDBer_OB4kHrS7 | Original post link

No, the source TiDB is a historical database with no data updates.

| username: zxgaa | Original post link

I always use mysqldump.