登录 / 注册
IT168技术开发频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

RadonDB用户使用手册——数据导入导出

2018-05-09 17:04    it168网站原创  作者: 厂商投稿 编辑: 田晓旭

  【IT168 技术】数据导入和导出

  RadonDB 目前只支持 go-mydumper 方式的数据导入和导出。

  XeLabs/go-mydumper 是一个使用 go 语言开发的开源工具,与 maxbube/mydumper 格式完全兼容,但是对并行处理进行了优化,性能更加卓越。该工具不仅限于 RadonDB 使用,MySQL 也可以使用。

  导入数据到 RadonDB,go-mydumper 会批量并行式导入,非常快捷。

  从 RadonDB 导出数据时,go-mydumper 会批量并行流式导出,资源占用率较低。

  1. 安装 go-mydumper

  $ git clone https://github.com/XeLabs/go-mydumper

  $ cd go-mydumper

  $ make

  $ ./bin/mydumper --help

  Usage: ./bin/mydumper -h [HOST] -P [PORT] -u [USER] -p [PASSWORD] -db [DATABASE] -o [OUTDIR]

  -F int

  Split tables into chunks of this output file size. This value is in MB (default 128)

  -P int

  TCP/IP port to connect to (default 3306)

  -db string

  Database to dump

  -h string

  The host to connect to

  -o string

  Directory to output files to

  -p string

  User password

  -s int

  Attempted size of INSERT statement in bytes (default 1000000)

  -t int

  Number of threads to use (default 16)

  -table string

  Table to dump

  -u string

  Username with privileges to run the dump

  $ ./bin/myloader --help

  Usage: ./bin/myloader -h [HOST] -P [PORT] -u [USER] -p [PASSWORD] -d [DIR]

  -P int

  TCP/IP port to connect to (default 3306)

  -d string

  Directory of the dump to import

  -h string

  The host to connect to

  -p string

  User password

  -t int

  Number of threads to use (default 16)

  -u string

  Username with privileges to run the loader

  2. 如何导入数据到 RadonDB

  2.1 从数据源导出数据

  首先使用 mydumper 从别的 MySQL 数据源导出数据,比如:

  $ ./bin/mydumper -h 192.168.0.2 -P 3306 -u test -p test -db sbtest -o sbtest.sql

  2017/10/25 13:12:52.933391 dumper.go:35: [INFO] dumping.database[sbtest].schema...

  2017/10/25 13:12:52.937743 dumper.go:45: [INFO] dumping.table[sbtest.benchyou0].schema...

  2017/10/25 13:12:52.937791 dumper.go:168: [INFO] dumping.table[sbtest.benchyou0].datas.thread[1]...

  2017/10/25 13:12:52.939008 dumper.go:45: [INFO] dumping.table[sbtest.benchyou1].schema...

  2017/10/25 13:12:52.939055 dumper.go:168: [INFO] dumping.table[sbtest.benchyou1].datas.thread[2]...

  2017/10/25 13:12:55.611905 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[633987].bytes[128MB].part[1].thread[1]

  2017/10/25 13:12:55.765127 dumper.go:105: [INFO] dumping.table[sbtest.benchyou1].rows[633987].bytes[128MB].part[1].thread[2]

  2017/10/25 13:12:58.146093 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[1266050].bytes[256MB].part[2].thread[1]

  ...snip...

  2017/10/25 13:13:37.627178 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[11974624].bytes[2432MB].part[19].thread[1]

  2017/10/25 13:13:37.753966 dumper.go:105: [INFO] dumping.table[sbtest.benchyou1].rows[11974630].bytes[2432MB].part[19].thread[2]

  2017/10/25 13:13:39.453430 dumper.go:122: [INFO] dumping.table[sbtest.benchyou0].done.allrows[12486842].allbytes[2536MB].thread[1]...

  2017/10/25 13:13:39.453462 dumper.go:170: [INFO] dumping.table[sbtest.benchyou0].datas.thread[1].done...

  2017/10/25 13:13:39.622390 dumper.go:122: [INFO] dumping.table[sbtest.benchyou1].done.allrows[12484135].allbytes[2535MB].thread[2]...

  2017/10/25 13:13:39.622423 dumper.go:170: [INFO] dumping.table[sbtest.benchyou1].datas.thread[2].done...

  2017/10/25 13:13:39.622454 dumper.go:188: [INFO] dumping.all.done.cost[46.69sec].allrows[24970977].allbytes[5318557708].rate[108.63MB/s]

  2.2 修改 schema

  在导出目录 (比如 sbtest.sql) 里找到*-schema.sql (比如 sbtest.benchyou0-scehma.sql):

  对原语句最后增加 ‘PARTITION BY HASH (分区键)’的语法:

  sbtest.benchyou0-schema.sql:

  CREATE TABLE `benchyou0` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `k` bigint(20) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k_1` (`k`)

  ) ENGINE=InnoDB;

  修改为(这里是以 id 为分区键):

  CREATE TABLE `benchyou0` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `k` bigint(20) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k_1` (`k`)

  ) ENGINE=InnoDB PARTITION BY HASH(id);

  2.3 导入数据到 RadonDB

  $ ./bin/myloader -h 192.168.0.2 -P 3306 -u radondb -p radondb -d sbtest.sql

  2017/10/25 13:04:17.396002 loader.go:75: [INFO] restoring.database[sbtest]

  2017/10/25 13:04:17.458076 loader.go:99: [INFO] restoring.schema[sbtest.benchyou0]

  2017/10/25 13:04:17.516236 loader.go:99: [INFO] restoring.schema[sbtest.benchyou1]

  2017/10/25 13:04:17.516389 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00015].thread[1]

  2017/10/25 13:04:17.516456 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00005].thread[2]

  2017/10/25 13:04:17.516486 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00020].thread[3]

  2017/10/25 13:04:17.516523 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00009].thread[4]

  2017/10/25 13:04:17.516550 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00018].thread[5]

  2017/10/25 13:04:17.516572 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00020].thread[6]

  2017/10/25 13:04:17.516606 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00019].thread[7]

  2017/10/25 13:04:17.516655 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00002].thread[8]

  2017/10/25 13:04:17.516692 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00011].thread[9]

  2017/10/25 13:04:17.516718 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00009].thread[10]

  2017/10/25 13:04:17.516739 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00017].thread[11]

  2017/10/25 13:04:17.516772 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00010].thread[12]

  2017/10/25 13:04:17.516797 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00008].thread[13]

  2017/10/25 13:04:17.516818 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00002].thread[14]

  2017/10/25 13:04:50.476413 loader.go:131: [INFO] restoring.tables[benchyou1].parts[00013].thread[0].done...

  ...snip...

  2017/10/25 13:05:52.602444 loader.go:131: [INFO] restoring.tables[benchyou0].parts[00019].thread[8].done...

  2017/10/25 13:05:52.602573 loader.go:187: [INFO] restoring.all.done.cost[95.09sec].allbytes[5120.00MB].rate[53.85MB/s]

  3. 如何导出 RadonDB 数据

  可以使用 mydumper 导出 RadonDB 数据,此过程是流式获取 (select 语句加 ‘/*backup*/’ hint) 并导出,基本不占用系统内存

  $ ./bin/mydumper -h 192.168.0.2 -P 3306 -u radondb -p radondb -db sbtest -o sbtest.sql

  2017/10/25 13:12:52.933391 dumper.go:35: [INFO] dumping.database[sbtest].schema...

  2017/10/25 13:12:52.937743 dumper.go:45: [INFO] dumping.table[sbtest.benchyou0].schema...

  2017/10/25 13:12:52.937791 dumper.go:168: [INFO] dumping.table[sbtest.benchyou0].datas.thread[1]...

  2017/10/25 13:12:52.939008 dumper.go:45: [INFO] dumping.table[sbtest.benchyou1].schema...

  2017/10/25 13:12:52.939055 dumper.go:168: [INFO] dumping.table[sbtest.benchyou1].datas.thread[2]...

  2017/10/25 13:12:55.611905 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[633987].bytes[128MB].part[1].thread[1]

  2017/10/25 13:12:55.765127 dumper.go:105: [INFO] dumping.table[sbtest.benchyou1].rows[633987].bytes[128MB].part[1].thread[2]

  ... ...

  2017/10/25 13:13:39.622423 dumper.go:170: [INFO] dumping.table[sbtest.benchyou1].datas.thread[2].done...

  2017/10/25 13:13:39.622454 dumper.go:188: [INFO] dumping.all.done.cost[46.69sec].allrows[24970977].allbytes[5318557708].rate[108.63MB/s]

标签: 数据库 , 青云
相关文章
  • IT168企业级IT168企业级
  • IT168文库IT168文库

扫码送文库金币

编辑推荐
系统架构师大会
系统架构师大会
点击或扫描关注
IT168企业级微信关注送礼
IT168企业级微信关注送礼
扫描关注
首页 评论 返回顶部