TiDB-从0到1-数据导出导入
TiDB从0到1系列
- TiDB-从0到1-体系结构
- TiDB-从0到1-分布式存储
- TiDB-从0到1-分布式事务
- TiDB-从0到1-MVCC
- TiDB-从0到1-部署篇
- TiDB-从0到1-配置篇
- TiDB-从0到1-集群扩缩容
一、数据导出
TiDB中通过Dumpling来实现数据导出，与MySQL中的mysqldump类似，其属于逻辑备份，备份出的文件格式为SQL或CSV。
同样与MySQL中的mysqldump应用场景类似，Dumping最好使用于对小规模的数据备份（导出）
二、Dumpling实操
1、下载安装
wget https://download.pingcap.org/tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz
------
tar -xvf tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz
2、解压需要的工具包
tar xvf dumpling-v7.5.1-linux-amd64.tar.gz
2、权限控制
使用dumpling的用户需要有对应的权限
- select
- reload
- lock tables
- replication client
- process
3、参数
./dumpling --help
-----------------
Flags:--allow-cleartext-passwords Allow passwords to be sent in cleartext (warning: don't use without TLS)--azblob.access-tier string Specify the storage class for azblob--azblob.account-key string Specify the account key for azblob--azblob.account-name string Specify the account name for azblob--azblob.encryption-key string Specify the server side encryption customer provided key--azblob.encryption-scope string Specify the server side encryption scope--azblob.endpoint string (experimental) Set the Azblob endpoint URL--azblob.sas-token string Specify the SAS (shared access signatures) for azblob--ca string The path name to the certificate authority file for TLS connection--case-sensitive whether the filter should be case-sensitive--cert string The path name to the client certificate file for TLS connection--complete-insert Use complete INSERT statements that include column names-c, --compress string Compress output file type, support 'gzip', 'snappy', 'zstd', 'no-compression' now--consistency string Consistency level during dumping: {auto|none|flush|lock|snapshot} (default "auto")--csv-delimiter string The delimiter for values in csv files, default '"' (default "\"")--csv-line-terminator string The line terminator for csv files, default '\r\n' (default "\r\n")--csv-null-value string The null value used when export to csv (default "\\N")--csv-separator string The separator for csv files, default ',' (default ",")-B, --database strings Databases to dump--dump-empty-database whether to dump empty database (default true)--escape-backslash use backslash to escape special characters (default true)-F, --filesize string The approximate size of output file--filetype string The type of export file (sql/csv)-f, --filter strings filter to select which tables to dump (default [*.*,!/^(mysql|sys|INFORMATION_SCHEMA|PERFORMANCE_SCHEMA|METRICS_SCHEMA|INSPECTION_SCHEMA)$/.*])--gcs.credentials-file string (experimental) Set the GCS credentials file path--gcs.endpoint string (experimental) Set the GCS endpoint URL--gcs.predefined-acl string (experimental) Specify the GCS predefined acl for objects--gcs.storage-class string (experimental) Specify the GCS storage class for objects--help Print help message and quit-h, --host string The host to connect to (default "127.0.0.1")--key string The path name to the client private key file for TLS connection-L, --logfile path Log file path, leave empty to write to console--logfmt format Log format: {text|json} (default "text")--loglevel string Log level: {debug|info|warn|error|dpanic|panic|fatal} (default "info")-d, --no-data Do not dump table data--no-header whether not to dump CSV table header-m, --no-schemas Do not dump table schemas with the data--no-sequences Do not dump sequences (default true)-W, --no-views Do not dump views (default true)--order-by-primary-key Sort dump results by primary key through order by sql (default true)-o, --output string Output directory (default "./export-2024-06-26T11:19:24+08:00")--output-filename-template string The output filename template (without file extension)--params stringToString Extra session variables used while dumping, accepted format: --params "character_set_client=latin1,character_set_connection=latin1" (default [])-p, --password string User password-P, --port int TCP/IP port to connect to (default 4000)-r, --rows uint If specified, dumpling will split table into chunks and concurrently dump them to different files to improve efficiency. For TiDB v3.0+, specify this will make dumpling split table with each file one TiDB region(no matter how many rows is).If not specified, dumpling will dump table without inner-concurrency which could be relatively slow. default unlimited--s3.acl string (experimental) Set the S3 canned ACLs, e.g. authenticated-read--s3.endpoint string (experimental) Set the S3 endpoint URL, please specify the http or https scheme explicitly--s3.external-id string (experimental) Set the external ID when assuming the role to access AWS S3--s3.provider string (experimental) Set the S3 provider, e.g. aws, alibaba, ceph--s3.region string (experimental) Set the S3 region, e.g. us-east-1--s3.role-arn string (experimental) Set the ARN of the IAM role to assume when accessing AWS S3--s3.sse string Set S3 server-side encryption, e.g. aws:kms--s3.sse-kms-key-id string KMS CMK key id to use with S3 server-side encryption.Leave empty to use S3 owned key.--s3.storage-class string (experimental) Set the S3 storage class, e.g. STANDARD--snapshot string Snapshot position (uint64 or MySQL style string timestamp). Valid only when consistency=snapshot-s, --statement-size uint Attempted size of INSERT statement in bytes (default 1000000)--status-addr string dumpling API server and pprof addr (default ":8281")-T, --tables-list strings Comma delimited table list to dump; must be qualified table names-t, --threads int Number of goroutines to use, default 4 (default 4)--tidb-mem-quota-query uint The maximum memory limit for a single SQL statement, in bytes.-u, --user string Username with privileges to run the dump (default "root")-V, --version Print Dumpling version--where string Dump only selected records
4、导出数据
导出test库下的所有数据，同时指定导出文件为sql，导出目录为/tmp/test，导出线程2，每10w行数据切换一次文件，每200MB切换一次文件
./dumpling -h192.168.14.121 -P4000 -uroot -p123456 --filetype sql -t 2 -o /tmp/test -r 100000 -F200MiB -B test
(备份成功)
查看导出的内容
其中
- metadata：数据导出时的时间，binlog位置点
- xxx.schema.sql：建库建表语句
- xxx.000000100.sql：数据
导出test库下t1的表中id>10的数据，同时指定导出文件为CSV，导出目录为/tmp/t1，导出线程2，每100行数据切换一次文件，每100MB切换一次文件
./dumpling -h192.168.14.121 -P4000 -uroot -p123456 --filetype csv -t 2 -o /tmp/t1 -r 100 -F100MiB -T test.t1 --where "id>10"
(备份成功)
查看导出的内容
建库\建表语句依旧是SQL文件
不过数据为CSV格式
同时Dumpling默认也是一致性备份，通过MVCC机制备份出某个时间点的快照数据
三、数据导入
TiDB中提供了一种叫TiDB Lightning（Physical Import Mode模式）的数据导入方式，因为其导入过程TiDB是不能对外提供服务的，而且数据是从本地直接导入到TiKV，所以应用场景更适合TiDB集群初始化。
整个Lightning原理如下
- 将集群切换为导入模式
- 创建对应库表
- 分割导入数据源
- 读取数据源文件
- 将源数据文件写入本地临时文件
- 导入临时文件到TiKV集群
- 检验与分析
- 将集群切换回正常模式
四、Lightning实操
1、下载安装
wget https://download.pingcap.org/tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz
------
tar -xvf tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz
2、解压需要的工具包
tar xvf tidb-lightning-v7.5.1-linux-amd64.tar.gz
3、准备配置文件
vim tidb-lighning.toml
-----------------
[lightning]
#逻辑cpu数量
#region-concurrency =
#日志
level = "info"
file = "tidb-lighning.log"[tikv-importer]
#开启并行导入
incremental-import = true
#设置为local模式
backend = "local"
#设置本地临时存储路径
sorted-kv-dir = "/tmp/sorted-kv-dir"[mydumper]
#源数据目录
data-source-dir = "/tmp/test"[tidb]
#tidb-server监听地址
host = "192.168.14.121"
port = 4000
user = "root"
password = ""
#表架构信息
status-port = 10080
#pd地址
pd-addr = "192.168.14.122:2379"
4、导入数据
我这里就将原集群清空，然后将上面-B导出的test库恢复回去
./tidb-lightning --config /opt/tidb-lighning.toml
（导入成功）
5、进入数据库校验
（验证无误）
彩蛋
TiDB-Lightning功能强大，可以通过配置文件过滤导入指定的表，同时也支持将MySQL中分库分表数据导入到TiDB中合并为一张表，还有断点续传等功能。
具体可以参考官方文档
