mytablecopy

mytablecopy

A command line tool for copying MySQL tables between databases. Performance is very close to a copy done with mysqldump piped to the mysql command. The problem with that approach this tool aims to solve is renaming without sed and retrying of failed inserts. The primary features are:

• A table can be copied to a different schema destination.
• A table can be renamed.
• The -where clause flag can be used to copy a subset of rows.
• The -ignore flag does not drop the destination table if it exists and uses insert ignore to append rows.

Installation

$ go get -d github.com/joshuaprunier/mytablecopy

$ ./build.sh

Building mytablecopy version
1.0.19-c98a96b-20150810.130749


Building Linux
  mytablecopy - OK

Building Windows
  mytablecopy.exe - OK

Building Darwin
  mytablecopy - OK

Done!

$ls -R bin
bin:
darwin  linux  windows

bin/darwin:
mytablecopy

bin/linux:
mytablecopy

bin/windows:
mytablecopy.exe

Usage

mytablecopy version 1.0.19-c98a96b-20150810.130749

USAGE:
mytablecopy SOURCE_FLAGS TARGET_FLAGS [DEBUG FLAGS]

EXAMPLES:
mytablecopy -srcuser=jprunier -srcpass= -srchost=db1 -srctable=test.mytable -tgthost=db2
mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable -where="1=1 limit 1000" -tgtuser=root -tgtpass=pass123 -tgthost=db2 -tgttable=scratchpad.newtable

SOURCE DATABASE FLAGS
=====================
-srcuser: Source Username (required)
-srcpass: Source Password (interactive prompt if blank)
-srchost: Source Database (localhost assumed if blank)
-srcport: Source MySQL Port (3306 default)
-srcsocket: Source MySQL Socket File
-srctable: Fully Qualified Source Tablename: ex. schema.tablename (required)
-where: Where clause to apply to source table select

TARGET DATABASE FLAGS
=====================
-tgtuser: Target Username (source username used if blank)
-tgtpass: Target Password (source password used if blank)
-tgthost: Target Database (required)
-tgtport: Target MySQL Port (3306 default)
-tgtsocket: Target MySQL Socket File
-tgttable: Fully Qualified Target Tablename: ex. schema.tablename (source tablename used if blank)
-ignore: Do insert ignore's and enable the -append flag (false default)
-append: Don't drop the destination table before copying (false default)

DEBUG FLAGS
===========
-debug_cpu: CPU debugging filename
-debug_mem: Memory debugging filename
-version: Version information
-v: Print more information (false default)

Examples

mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable \
-tgtuser=root -tgtpass=pass123 -tgthost=db2

mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable -tgthost=db2

mytablecopy -srcuser=jprunier -srcpass= -srchost=db1 -srctable=test.mytable -tgthost=db2

mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable \
-tgtuser=root -tgtpass=pass123 -tgthost=db2 -tgttable=scratchpad.newtable

mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable -where="1=1 limit 500000" \
-tgtuser=root -tgtpass=pass123 -tgthost=db2

mytablecopy -srcuser=jprunier -srcpass= -srchost=db1 -srctable=test.mytable1 -tgthost=db2 \
-tgttable=test.new -ignore
mytablecopy -srcuser=jprunier -srcpass= -srchost=db1 -srctable=test.mytable2 -tgthost=db2 \
-tgttable=test.new -ignore
mytablecopy -srcuser=jprunier -srcpass= -srchost=db3 -srctable=test.mytable3 -tgthost=db2 \
-tgttable=test.new -ignore

mytablecopy -srcuser=jprunier -srcpass=mypass -srchost=db1 -srctable=test.mytable -where="1=1 limit 100000" \
-tgtuser=root -tgtpass=pass123 -tgthost=db2
A '.' will be shown for every 10,000 CSV rows written
..........

100000 rows written
Total runtime = 12.994247028s

License

[MIT] (LICENSE)