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)
Option parameters for source and target connections are:
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_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:
For local socket connections:
--source-password: Passes the connection password of the data source.
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
Option parameters for source and target connections that support SSH tunneling to copy data are:
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 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,
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 source
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.
Full Table Copy
This argument performs a full
SELECT on the
source table, fetches records, and then inserts them into the
There are no additional arguments required.
--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
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 is:
key_column >= range_start
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
SELECTgenerated by the
--tableoption 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
Schema_Name: Performs the backup and DROP process for all triggers in the specified schema.
Schema_Name: Restores triggers previously backed up to the
--dont-disable-triggers: Bypasses the trigger disabling step.