SQLines Data is a scalable, high performance data transfer, schema conversion and validation tool for Oracle to MySQL migration.
The tool requires connections to Oracle (OCI) and MySQL (MySQL C API) databases. To convert standalone SQL statements and SQL scripts, use SQLines SQL Converter tool.
SQLines Data benefits:
Migration Features
You can use SQLines SQL Converter tool to convert stored procedures, functions, triggers, views and other objects.
Advanced Database Schema Conversion and Data Transfer Features
Migration Validation
Scalability and High-Performance
Designed for DBAs and Enterprise-Class Migrations
Logging and Statistics
You can use SQLines Data tool in command line. Just launch sqldata.exe on Windows or ./sqldata on Linux with the specified options.
For information how to set up Oracle and MySQL connections, see SQLines Data Connection String Formats.
Examples:
| sqldata -t=emp -sd=oracle, user/pwd@host/sid -td=mysql, user/pwd@host,db_name |
-t option defines the table name, -sd and -td options (source and target databases) specify the connection strings for Oracle and MySQL, respectively.
This command transfers table emp from Oracle database to MySQL db_name database located on host.
For list of options, see Command Line Reference.
Troubleshooting SQLines Data for Oracle to MySQL migration:
There are two main sources that can help you troubleshoot SQLines Data:
sqldata.log file contains the detailed information about MySQL to Oracle migration process.
By default, sqldata.log file is located in the current working directory. You can use -log command line option to change its location and file name.
sqldata_ddl.sql file contains information about all DDL statements executed in MySQL during migration.
In this file you can see the full SQL statements, the execution result (success or failure) and the execution time. In case of an error, you can find the error message returned by MySQL.
By default, sqldata_ddl.sql file is located in the current working directory. You can use -out command line option to change its location.
If there are any failed SQL statements, you can find them in sqldata_failed.sql file.
Sometimes you can receive Loading local data is disabled; this must be enabled on both the client and server sides during the data transfer:
USERS - Data transfer failed
Level: Error; Code: 3948; Message: Loading local data is disabled;
this must be enabled on both the client and server sides
Rows read: 17 (274 rows/sec)
Rows written: 0 (0 rows/sec, 0 bytes, 0 bytes/sec)
Transfer time: 812 ms (62 ms read, 0 ms write)
The tool uses in-memory LOAD DATA LOCAL INFILE command, and possible reason is that it is not allowed by your MySQL server configuration.
Edit my.cnf (or my.ini on Windows, for example, at C:\ProgramData\MySQL\MySQL Server 8.0) and set local-infile=1 in [mysqld] section:
[mysqld] local-infile=1
You have to restart the MySQL server for the change to take effect.
When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. See this article for details.