CODEWITHSUNDEEP
migrationmysql-workbench
Hugo Simonin
Hugo Simonin

Reputation: 37

mysql workbench migration select data

I use MySQL Workbench to copy a table MS SQL to MYSQL server. It's possible to select the data between 2 date ?

Today this export lasts 3h with more than 150K rows and I would like to speed up the treatment.

Thanks

Upvotes: 0

Views: 1905

Answers (1)

Miłosz Bodzek
Miłosz Bodzek

Reputation: 1369

You must run migration wizard once again, but in 'Data Transfer Setup' step choose 'Create a shell script to copy the data from outside of Workbench' option. After that Workbench generate shell script for you, which may look similar to this:

#!/bin/sh
# Workbench Table Data copy script
# Workbench Version: 6.3.10
#
# Execute this to copy table data from a source RDBMS to MySQL.
# Edit the options below to customize it. You will need to provide passwords, at least.
#
# Source DB: Mysql@localhost:8000 (MySQL)
# Target DB: Mysql@localhost:8000


# Source and target DB passwords
arg_source_password=
arg_target_password=

if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
    echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
arg_worker_count=2
# Uncomment the following options according to your needs

# Whether target tables should be truncated before copy
# arg_truncate_target=--truncate-target
# Enable debugging output
# arg_debug_output=--log-level=debug3

/home/milosz/Projects/Oracle/workbench/master/wb_run/usr/local/bin/wbcopytables \
 --mysql-source="root@localhost:8000" \
 --target="root@localhost:8000" \
 --source-password="$arg_source_password" \
 --target-password="$arg_target_password" \
 --thread-count=$arg_worker_count \
 $arg_truncate_target \
 $arg_debug_output \
 --table '`test`' '`t1`' '`test_target`' '`t1`' '`id`' '`id`' '`id`, `name`, `date`'

First of all you need to put your password for source and target databases. Then change last argument of wbcopytables command from --table to --table-where and add condition to the end of line. Side note: you can run wbcopytables command with --help argument to see all options.

After all you should get script that looks like similar to:

#<...>
# Source and target DB passwords
arg_source_password=your_sorce_password
arg_target_password=your_target_password

if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
    echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
arg_worker_count=2
# Uncomment the following options according to your needs

# Whether target tables should be truncated before copy
# arg_truncate_target=--truncate-target
# Enable debugging output
# arg_debug_output=--log-level=debug3

/home/milosz/Projects/Oracle/workbench/master/wb_run/usr/local/bin/wbcopytables \
 --mysql-source="root@localhost:8000" \
 --target="root@localhost:8000" \
 --source-password="$arg_source_password" \
 --target-password="$arg_target_password" \
 --thread-count=$arg_worker_count \
 $arg_truncate_target \
 $arg_debug_output \
--table-where '`test`' '`t1`' '`test_target`' '`t1`' '`id`' '`id`' '`id`, `name`, `date`' '`date` >= "2017-01-02" and `date` <= "2017-01-03"'

I hope that is helpful for you.

Upvotes: 2

Related Questions