【MVS】使用TIDB dumpling从mysql导出数据
- 0关注
- 0收藏 76浏览
问题描述
介绍使用TIDB dumpling从mysql导出数据方法
解决方法
一. 安装dumpling
1.Dumpling 工具集成在 tidb-toolkit 中,先进行下载:
$ wget https://download,***.***/tidb-community-toolkit-v6.1.0-linux-amd64.tar.gz
手动下载：
访问 TiDB 社区版页面, 找到 TiDB-community-toolkit 软件包，点击立即下载。
***.***/zh/tidb/stable/download-ecosystem-tools
2.解压缩下载软件包 tidb-community-toolkit-v6.1.0-linux-amd64.tar.gz:
$ tar xvf tidb-community-toolkit-v6.1.0-linux-amd64.tar.gz
3.解压 tidb-community-toolkit-v6.1.0-inux-amd64 文件中 dumpling-v6.1.0-linux-amd64.tar.gz
cd tidb-community-toolkit-v6.1.0-linux-amd64
tar xvf dumpling-v6.1.0-linux-amd64.tar.gz
4.进入目录,确认安装完毕。也可以将目录加入环境变量,方便后续执行:
# cd
# vi .bash profile
PATH=$PATH:$H0ME/bin:/usr/local/mysql/bin:/root/tidb-community-toolkit-v6.1.0
二。 使用 Dumpling 从 MySQL 数据库中导出数据
1.连接mysql V5.7.40库
$ mysql -uroot -pAbcd_1234 -h192.168.169.121 -P3306
> show databases;
> use tpcc;
> show tables;
2.进入 bin 文件内使用 Dumpling 工具将 3306 端口的 MySQL 数据库的 emp 库导出:
$tidb-community-toolkit-v6.1.0-linux-amd64
$ ./dumpling -h192.168.169.121 -P3307 -uroot -pAbcd_1234 --filetype sql -t8 -o/tmp/mysql_dmpling_tpcc -r 12345 -F 256MB -B tpcc
3. 查看导出文件:
[root@tidb30 mysql_dmpling_tpcc]# ll -ltrh
total 301M
-rw-r--r-- 1 root root 94 Mar 17 15:58 tpcc-schema-create.sql
-rw-r--r-- 1 root root 1.1K Mar 17 15:58 tpcc.customer-schema.sql
-rw-r--r-- 1 root root 572 Mar 17 15:58 tpcc.district-schema.sql
-rw-r--r-- 1 root root 4.5K Mar 17 15:58 tpcc.district.0000000000000.sql
-rw-r--r-- 1 root root 288 Mar 17 15:58 tpcc.item-schema.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000000000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000020000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000010000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000030000.sql
-rw-r--r-- 1 root root 287 Mar 17 15:58 tpcc.new_order-schema.sql
-rw-r--r-- 1 root root 215K Mar 17 15:58 tpcc.new_order.0000000000000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000040000.sql
-rw-r--r-- 1 root root 563 Mar 17 15:58 tpcc.order_line-schema.sql
-rw-r--r-- 1 root root 218K Mar 17 15:58 tpcc.new_order.0000000010000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000050000.sql
-rw-r--r-- 1 root root 1.2M Mar 17 15:58 tpcc.item.0000000060000.sql
-rw-r--r-- 1 root root 510 Mar 17 15:58 tpcc.orders-schema.sql
-rw-r--r-- 1 root root 18M Mar 17 15:58 tpcc.customer.0000000030000.sql
-rw-r--r-- 1 root root 18M Mar 17 15:58 tpcc.customer.0000000020000.sql
-rw-r--r-- 1 root root 18M Mar 17 15:58 tpcc.customer.0000000010000.sql
-rw-r--r-- 1 root root 18M Mar 17 15:58 tpcc.customer.0000000000000.sql
-rw-r--r-- 1 root root 791 Mar 17 15:58 tpcc.stock-schema.sql
-rw-r--r-- 1 root root 1.5M Mar 17 15:58 tpcc.orders.0000000000000.sql
-rw-r--r-- 1 root root 1.5M Mar 17 15:58 tpcc.orders.0000000030000.sql
-rw-r--r-- 1 root root 1.5M Mar 17 15:58 tpcc.orders.0000000020000.sql
-rw-r--r-- 1 root root 1.5M Mar 17 15:58 tpcc.orders.0000000010000.sql
-rw-r--r-- 1 root root 23M Mar 17 15:58 tpcc.order_line.0000000000000.sql
-rw-r--r-- 1 root root 495 Mar 17 15:58 tpcc.warehouse-schema.sql
-rw-r--r-- 1 root root 489 Mar 17 15:58 tpcc.warehouse.0000000000000.sql
-rw-r--r-- 1 root root 32M Mar 17 15:58 tpcc.stock.0000000010000.sql
-rw-r--r-- 1 root root 32M Mar 17 15:58 tpcc.stock.0000000020000.sql
-rw-r--r-- 1 root root 32M Mar 17 15:58 tpcc.stock.0000000030000.sql
-rw-r--r-- 1 root root 23M Mar 17 15:58 tpcc.order_line.0000000020000.sql
-rw-r--r-- 1 root root 32M Mar 17 15:58 tpcc.stock.0000000000000.sql
-rw-r--r-- 1 root root 23M Mar 17 15:58 tpcc.order_line.0000000010000.sql
-rw-r--r-- 1 root root 23M Mar 17 15:58 tpcc.order_line.0000000030000.sql
-rw-r--r-- 1 root root 183 Mar 17 15:58 metadata
备注导出过程：
[root@tidb30 tmp]# dumpling -h192.168.169.121 -P3307 -uroot -pAbcd_1234 --filetype sql -t8 -o/tmp/mysql_dmpling_tpcc -r 12345 -F 256MB -B tpcc
Release version: v6.1.5
Git commit hash: 73d82e330b02a39f74073d98daefbadd7deab9b9
Git branch: heads/refs/tags/v6.1.5
Build timestamp: 2023-02-22 06:14:32Z
Go version: go version go1.19.5 linux/amd64
[2025/03/17 15:58:03.960 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v6.1.5] ["Git Commit Hash"=73d82e330b02a39f74073d98daefbadd7deab9b9] ["Git Branch"=heads/refs/tags/v6.1.5] ["Build timestamp"="2023-02-22 06:14:32"] ["Go Version"="go version go1.19.5 linux/amd64"]
[2025/03/17 15:58:03.963 +08:00] [WARN] [version.go:264] ["select tidb_version() failed, will fallback to 'select version();'"] [error="Error 1046: No database selected"]
[2025/03/17 15:58:03.963 +08:00] [INFO] [version.go:362] ["detect server version"] [type=MySQL] [version=5.7.40-log]
[2025/03/17 15:58:03.966 +08:00] [INFO] [dump.go:117] ["begin to run Dump"] [cOnf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoSequences\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.169.121\",\"Port\":3307,\"Threads\":8,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/mysql_dmpling_tpcc\",\"StatusAddr\":\":8281\",\"Snapshot\":\"\",\"Consistency\":\"flush\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"tpcc\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":1,\"ServerVersion\":\"5.7.40-log\",\"HasTiKV\":false},\"Rows\":12345,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":268435456,\"StatementSize\":1000000,\"SessionParams\":{},\"Tables\":{},\"CollationCompatible\":\"loose\"}"]
[2025/03/17 15:58:04.032 +08:00] [INFO] [dump.go:238] ["All the dumping transactions have started. Start to unlock tables"]
[2025/03/17 15:58:04.040 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=customer] [estimateCount=112739]
[2025/03/17 15:58:04.069 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=district] [estimateCount=40]
[2025/03/17 15:58:04.069 +08:00] [INFO] [dump.go:720] ["fallback to sequential dump due to estimate count < rows. This won't influence the whole dump process"] ["estimate count"=40] [conf.rows=12345] [database=tpcc] [table=district]
[2025/03/17 15:58:04.094 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=item] [estimateCount=98406]
[2025/03/17 15:58:04.181 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=new_order] [estimateCount=36456]
[2025/03/17 15:58:04.263 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=order_line] [estimateCount=1249819]
[2025/03/17 15:58:04.361 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=orders] [estimateCount=115531]
[2025/03/17 15:58:04.522 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=stock] [estimateCount=387580]
[2025/03/17 15:58:04.728 +08:00] [INFO] [dump.go:714] ["get estimated rows count"] [database=tpcc] [table=warehouse] [estimateCount=1]
[2025/03/17 15:58:04.728 +08:00] [INFO] [dump.go:720] ["fallback to sequential dump due to estimate count < rows. This won't influence the whole dump process"] ["estimate count"=1] [conf.rows=12345] [database=tpcc] [table=warehouse]
[2025/03/17 15:58:06.728 +08:00] [INFO] [collector.go:239] ["backup success summary"] [total-ranges=36] [ranges-succeed=36] [ranges-failed=0] [total-take=2.695951069s] [total-kv-size=314.5MB] [average-speed=116.7MB/s] [total-rows=2076058]
[2025/03/17 15:58:06.729 +08:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"]
[root@tidb30 tmp]# ll
total 8
该案例暂时没有网友评论
编辑评论
✖
案例意见反馈
亲~登录后才可以操作哦!
确定你的邮箱还未认证，请认证邮箱或绑定手机后进行当前操作