MySQL Workbench
wbcopytables is a command-line utility included in MySQL Workbench that enables you to copy table data from a supported source database server to MySQL. It is used by the MySQL Workbench Migration Wizard to copy data after the schema is migrated and created in the target MySQL server.
wbcopytables can connect to the source database using either ODBC, the Python DBAPI, or the native MySQL client library.
The copy executes a SELECT
statement on the
source database and then INSERT
to insert the
retrieved rows into the target MySQL server.
Table 6.2 File Location (Default)
Operating System | Location |
---|---|
Linux | /usr/bin/wbcopytables |
macOS | /Applications/MySQLWorkbench.app/Contents/MacOS/wbcopytables |
Windows | C:\Program Files\MySQL\MySQL Workbench
8.0\wbcopytables.exe |
Option parameters for source and target connections are:
--odbc-source=
ODBC_connection_string
:
The syntax of the ODBC connection string uses standard ODBC
syntax. You can also use a ODBC data source name (DSN).
--mysql-source=
MySQL_connection_string
:
Use for MySQL sources (when doing a MySQL to MySQL migration
or copy). It uses the same syntax as the MySQL Utilities:
For TCP/IP connections:
username[:password]
@host:port
For local socket connections:
username[:password]
@::socket_path
--source-password
: Passes the connection
password of the data source.
--target=
MySQL_connection_string
:
Specifies the target connection.
--passwords-from-stdin
: Passes a passwords
through STDIN. Source and target passwords must be separated
by a tab character.
You can use ODBC specific data source options from the source
RDBMS to specify the number of rows to fetch at a time for the
source SELECT
statement.
Option parameters for source and target connections that support SSH tunneling to copy data are:
--source-ssh-port=
ssh
port
--source-ssh-host=
ssh
host
--source-ssh-user=
ssh
user
--source-ssh-password=
ssh
password
--target-ssh-port=
ssh
port
--target-ssh-host=
ssh
host
--target-ssh-user=
ssh
user
--target-ssh-password=
ssh
password
One or more tables can be specified in the command line for the copy operation. There are two copy types:
Full table copy: --table
Range copy: --table-range
Both table copy types require a set of common arguments:
Source schema: The schema or catalog to which the table belongs. If quoting is required, it must be done using the syntax from the source RDBMS. For example, SQL Server uses [square_brackets].
Source table: The table to copy. If the source RDBMS uses a schema name in addition to a catalog, both schema and table must be specified here and separated by a dot. For example, [dbo].[mytable].
Target schema: The name of
the MySQL schema. If quoting is needed, it must use the MySQL
backtick syntax. For example,
`
sakila`
.
Target table: The name of the MySQL table.
Select expression: The list
of fields to SELECT
. This will be inserted
verbatim into the source SELECT
statement.
Use caution as this expression is copied directly into the
source SELECT
statement.
For the select expression, if both the source and target tables
have the same fields in the same order, and use compatible types,
you can simply pass *
here, which will build a
query like "SELECT * FROM [dbo].[mytable]".
If not, you can specify the fields as you would in the
SELECT
statement, which are comma (,) separated
and with proper escaping/quoting specific to the source RDBMS. You
can also specify typecasts and/or data conversions that the source
RDBMS supports. For example:
[client_id
], [name
], [address
], AsText([location
])
Because each option must be interpreted as a single option by the wbcopytables command, you must perform OS shell specific quoting whenever necessary. Usually, quoting your parameter values with 'single' or "double" quotes is enough. This is in addition to any database specific quoting you use.
This argument performs a full SELECT
on the
source table, fetches records, and then inserts them into the
target table.
There are no additional arguments required.
The --table
syntax is as follows:
--table Source_Schema Source_Table Target_Schema Target_Table Select_Expression
This argument performs a SELECT
copy on the
source table for the specified range. The table must have a
numeric UNIQUE NOT NULL
or PRIMARY
KEY
that is used to create a WHERE
expression for the range.
The --table-range
syntax is as follows:
--table-range Source_Schema Source_Table Target_Schema Target_Table Select_Expression Source_Key Range_Start Range_End
The generated expression is:
key_column
>=range_start
ANDkey_column
<=range_end
If you specify -1 for Range_End, then the expression is:
key_column
>=range_start
--thread-count
=Number
:
If you are copying more than one table, you can use this
option to divide the tables across several threads. There is
no support for dividing a single table across many threads.
--count-only
: Only performs a
COUNT(*)
of the SELECT
generated by the --table
option that was
used. The target schema and table can be omitted in this case.
--truncate-target
: Executes a TRUNCATE TABLE
command on each target table that is copied.
Because there is no way to temporarily disable triggers in MySQL
and they can affect the copy process, MySQL Workbench will backup and
drop all triggers from the target MySQL database before the copy
process starts, and then these triggers are restored after the
copy finishes. The triggers are backed up in the target schema
under a table named wb_tmp_triggers
.
--disable-triggers-on
=Schema_Name
:
Performs the backup and DROP process for all triggers in the
specified schema.
--reenable-triggers-on
=Schema_Name
:
Restores triggers previously backed up to the
wb_tmp_triggers
table.
--dont-disable-triggers
: Bypasses the trigger
disabling step.