This chapter provides information about optimizing the command line options and avoiding issues connecting SQL Developer to the MySQL database server. It includes information about:
When you migrate to Oracle, SQL Developer attempts to connect to the MySQL database as MySQL user associated with the current database connection. (SQL Developer does not use the MySQL root user.) SQL Developer can capture only those MySQL database objects that the specified MySQL user has privileges to access.
Therefore, when you create the database connection that you intend to use for MySQL migrations, be sure to specify a MySQL user that has sufficient privileges to access all objects and data that you plan to migrate to Oracle.
If you are having difficulty migrating from MySQL to Oracle, you can check the SQL Developer discussion forum on the Oracle Technology Network.
If you have a support contract, the SQL Developer support team may ask you to provide a dump of the MySQL database. This helps the team track the problem and provide a swift solution. By using the mysqldump method to create a copy of the MySQL database you generate text files that are portable to other systems, even those with different hardware architecture. The SQL Developer support team can regenerate the output into another database.
The following table provides an explanation of the code used to dump the MySQL data and to regenerate the database from the mysqldump output text file:
| Command | Description |
|---|---|
| mysqldump | Allows you to extract the schema and data in a MySQL database to a file. |
| mysql | Loads MySQL so you can carry out the command. |
-u user name |
MySQL user name. |
-p password |
Password for the specified user. |
--opt |
Optimizes table dumping speed and writes a dump file for reloading speed. For a list of definitions of the options enabled by --opt, see "Optimizing Command Line Options". |
database_name |
Name of the database containing the information you want to dump to an output text file. |
> |
Symbol used for re-directing the input in UNIX and NT. |
file_name.sql |
File name containing the MySQL database information. |
To dump the MySQL data, use the following command:
% mysqldump -u user name -ppassword --opt database_name > file_name.sql
To regenerate the database from the mysqldump output text file into a database, use the following command:
% mysql -u user name -ppassword database_name < file_name.sql
You automatically switch on options within the mysqldump command line by using --opt. The following table lists the commands encompassed by the -opt command:
| Command | Description |
|---|---|
--add-drop-table |
Adds a DROP TABLE IF EXISTS statement before each CREATE TABLE statement. |
--all |
Includes all of the MySQL specific create options. |
--extended-insert |
Writes multiple row INSERT statements. |
--quick |
Dumps tables directly to the standard output without buffering the query. If you suspend mysqldump while using this option, you may interfere with other clients because it could cause the server to stop responding. |
--lock-tables |
Locks all tables as read only. |