帝国做网站是选择静态还是伪静态,网站开发需求规格说明书,珠海网站设计费用,有免费网站推荐吗TiDB从0到1系列
TiDB-从0到1-体系结构TiDB-从0到1-分布式存储TiDB-从0到1-分布式事务TiDB-从0到1-MVCCTiDB-从0到1-部署篇TiDB-从0到1-配置篇TiDB-从0到1-集群扩缩容
一、数据导出
TiDB中通过Dumpling来实现数据导出#xff0c;与MySQL中的mysqldump类似#xff0c;其属于…
TiDB从0到1系列
TiDB-从0到1-体系结构TiDB-从0到1-分布式存储TiDB-从0到1-分布式事务TiDB-从0到1-MVCCTiDB-从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.gz2、解压需要的工具包
tar xvf dumpling-v7.5.1-linux-amd64.tar.gz2、权限控制 使用dumpling的用户需要有对应的权限
selectreloadlock tablesreplication clientprocess
3、参数
./dumpling --help
-----------------
Flags:--allow-cleartext-passwords Allow passwords to be sent in cleartext (warning: dont 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:2408: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_clientlatin1,character_set_connectionlatin1 (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 consistencysnapshot-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 records4、导出数据 导出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的表中id10的数据同时指定导出文件为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 id10(备份成功)
查看导出的内容 建库\建表语句依旧是SQL文件 不过数据为CSV格式
同时Dumpling默认也是一致性备份通过MVCC机制备份出某个时间点的快照数据
三、数据导入
TiDB中提供了一种叫TiDB LightningPhysical 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.gz2、解压需要的工具包
tar xvf tidb-lightning-v7.5.1-linux-amd64.tar.gz3、准备配置文件
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:23794、导入数据 我这里就将原集群清空然后将上面-B导出的test库恢复回去
./tidb-lightning --config /opt/tidb-lighning.toml导入成功
5、进入数据库校验 验证无误
彩蛋
TiDB-Lightning功能强大可以通过配置文件过滤导入指定的表同时也支持将MySQL中分库分表数据导入到TiDB中合并为一张表还有断点续传等功能。 具体可以参考官方文档
