wbcopytables is a command line utility included in MySQL Workbench that allows you to copy table data from a supported source database server to MySQL. It is used by the 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 is performed by executing a
statement on the source database, and then
inserts the retrieved rows to the target MySQL server. Several
options allow you to control the data that is copied.
Table 11.1 File Location (Default)
Options for the source connection are:
The syntax of the ODBC connection string uses standard ODBC
syntax. You can also use a ODBC data source name (DSN).
Use for MySQL sources (when doing a MySQL to MySQL
migration/copy). It uses the same syntax as the MySQL Utilities:
For TCP/IP connections:
For local socket connections:
You can pass the connection password by using the
For the target connection, the option is:
You can use the
--passwords-from-stdin option to
pass 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
One or more tables can be specified in the command line for the copy operation. There are two copy types:
Full table copy:
Both table copy types require a set of common arguments:
Source schema: The schema/catalog the table belongs to. 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,
Target table: The name of the MySQL table.
Select expression: The list
of fields to
SELECT. This will be inserted
verbatim into the source
Use caution as this expression is copied directly into the
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 performs a full
SELECT on the source
table, fetches records, and then inserts them into the target
There are no additional arguments required.
--table syntax is as follows:
--table Source_Schema Source_Table Target_Schema Target_Table Select_Expression
This performs a
SELECT copy on the source table
for the specified range. The table must have a numeric
UNIQUE NOT NULL or
KEY that is used to create a
expression for the range.
--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 AND key_column <= range_end
If you specify -1 for Range_End, then the expression will be:
key_column >= range_start
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 perform a
COUNT(*) of the
which would be generated by the
option that was used. The target schema and table can be
omitted in this case.
--truncate-target: Execute 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
Performs the backup and DROP process for all triggers in the
Restores triggers previously backed up to the
--dont-disable-triggers: Bypass the trigger