Currently, the only way to import and export data used by radon is go-mydumper.
XeLabs/go-mydumper is developed by golang. It is compatible with maxbube/mydumper in the layout, but go-mydumper is optimized for parallelism, and performance is more remarkable.
Importing data to radon, go-mydumper will batch import data in parallel, very fast.
Exporting data from radon, go-mydumper will export data in batch parallel streaming mode, the resource occupancy rate is low.
$git clone https://github.com/XeLabs/go-mydumper
$cd go-mydumper
$make
$./bin/mydumper --help
Usage: ./bin/mydumper -c config/mydumper.ini.sample
-c string
config file
$./bin/myloader --help
Usage: ./bin/myloader -h [HOST] -P [PORT] -u [USER] -p [PASSWORD] -d [DIR] [-o]
-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
-o Drop tables if they already exist
-p string
User password
-t int
Number of threads to use (default 16)
-u string
Username with privileges to run the loader
Firstly, configure config/mydumper.ini.sample, set the host, port, user, password, database, outdir. Use mydumper to export data from the other MySQL data source.
For example:
$./bin/mydumper -c config/mydumper.ini.sample
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]
In the export directory(such as sbtest.sql), find *-schema.sql(such as sbtest.benchyou0-scehma.sql) :
If the primary key or unique index is not specified in the table (or want to manually specify the partition key), we need add ‘PARTITON BY HASH(ShardKey)’ syntax at the end of the original sentence. We can also specify the table type by adding GLOBAL|SINGLE.
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;Modified (id is ShardKey):
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);Modified (global table):
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 GLOBAL;$./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:04:50.476499 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00001].thread[0]
2017/10/25 13:04:50.667836 loader.go:131: [INFO] restoring.tables[benchyou1].parts[00003].thread[15].done...
2017/10/25 13:04:50.667916 loader.go:115: [INFO] restoring.tables[benchyou0].parts[00013].thread[15]
2017/10/25 13:04:50.702259 loader.go:131: [INFO] restoring.tables[benchyou1].parts[00011].thread[9].done...
2017/10/25 13:04:50.702397 loader.go:115: [INFO] restoring.tables[benchyou1].parts[00005].thread[9]
2017/10/25 13:05:52.286931 loader.go:131: [INFO] restoring.tables[benchyou1].parts[00006].thread[11].done...
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]
1) before export data from data source
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| benchyou0 |
| benchyou1 |
+------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from benchyou0;
+----------+
| count(*) |
+----------+
| 12486842 |
+----------+
1 row in set (6.43 sec)
mysql> select count(*) from benchyou1;
+----------+
| count(*) |
+----------+
| 12484135 |
+----------+
2) after import data to `radon`
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| benchyou0 |
| benchyou1 |
+------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from benchyou0;
+----------+
| count(*) |
+----------+
| 12486842 |
+----------+
1 row in set (1.30 sec)
mysql> select count(*) from benchyou1;
+----------+
| count(*) |
+----------+
| 12484135 |
+----------+
1 row in set (1.25 sec)
Use mydumper to export data from radon, this process is stream acquire (set vars= "set @@SESSION.radon_streaming_fetch='ON'" in config/mydumper.ini.sample) and export, basically does not occupy system memory.
$./bin/mydumper -c config/mydumper.ini.sample
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]
2017/10/25 13:12:58.253219 dumper.go:105: [INFO] dumping.table[sbtest.benchyou1].rows[1266054].bytes[256MB].part[2].thread[2]
2017/10/25 13:13:00.545536 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[1896681].bytes[384MB].part[3].thread[1]
2017/10/25 13:13:00.669499 dumper.go:105: [INFO] dumping.table[sbtest.benchyou1].rows[1896682].bytes[384MB].part[3].thread[2]
2017/10/25 13:13:02.939278 dumper.go:182: [INFO] dumping.allbytes[1024MB].allrows[5054337].time[10.01sec].rates[102.34MB/sec]...
2017/10/25 13:13:03.012645 dumper.go:105: [INFO] dumping.table[sbtest.benchyou0].rows[2527168].bytes[512MB].part[4].thread[1]
... ...
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]