Note:

Migrate On-Premises MySQL Database to Oracle HeatWave MySQL Managed Instance using OCI GoldenGate

Introduction

Oracle Cloud Infrastructure GoldenGate (OCI GoldenGate) is a fully managed service that helps data engineers move data in real-time, at scale, from one or more data management systems to OCI databases. Design, run, orchestrate, and monitor data replication tasks in a single interface without having to allocate or manage any compute environments. OCI GoldenGate supports several sources and targets, including MySQL and Oracle HeatWave MySQL database service.

In this tutorial, we will guide you how to migrate on-premises MySQL database to Oracle HeatWave MySQL managed instance using OCI GoldenGate.

Objectives

Prerequisites

Limitations

Task 1: Create Source and Target MySQL Instances

Task 2: Test Connections between Bastion, On-Premises and Oracle Heatwave MySQL Instances

  1. Install MySQL client and MySQL Shell utility on the OCI Bastion host.

  2. Test connection from OCI Bastion to source on-premises MySQL instance.

    [root@bastion ~]#  mysql --host <sourceIP> -uadmin -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

  3. Test connection from source on-premises MySQL to the Oracle Heatwave MySQL managed instance.

    [root@mysqlci57 ~]# mysql --host <targetIP> -u admin -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 108
Server version: 8.4.4-u5-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+----------------+
| @@version      |
+----------------+
| 8.4.4-u5-cloud |
+----------------+
1 row in set (0.01 sec)

Task 3: Create Users for OCI GoldenGate

Task 4: Set Mandatory Parameters in the Source Database for OCI GoldenGate

It is not mandatory to turn on GTID on source if the target is standalone (not HA). But if target is HA then it is highly recommended to turn on GTID on source. It is mandatory to turn on the binlog mode.

  1. Edit the /etc/my.cnf file and add the following lines.

    server-id=1
log-bin=/var/log/mysql/mysql-bin.log
max_binlog_size=100M
binlog_format=ROW
expire_logs_days=10
-- binlog_row_metadata=FULL <-- this is not supported in Version 5.7.44. So DDL replication will not be possible if source is on v5.7.44.
gtid_mode=ON
enforce_gtid_consistency=ON

  2. Restart MySQL server.

    Systemctl stop mysqld
Systemctl start mysqld
Systemctl status mysqld

    For complete list or parameters, check transaction log settings and requirements. For more information, see Option B: Use your own bastion on OCI Compute.

  3. Run the following command to check that binary logs is enabled now.

    mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'bin%';
+--------------------------------------------+--------------+
| Variable_name                              | Value        |
+--------------------------------------------+--------------+
| bind_address                               | *            |
| binlog_cache_size                          | 32768        |
| binlog_checksum                            | CRC32        |
| binlog_direct_non_transactional_updates    | OFF          |
| binlog_error_action                        | ABORT_SERVER |
| binlog_format                              | ROW          |
| binlog_group_commit_sync_delay             | 0            |
| binlog_group_commit_sync_no_delay_count    | 0            |
| binlog_gtid_simple_recovery                | ON           |
| binlog_max_flush_queue_time                | 0            |
| binlog_order_commits                       | ON           |
| binlog_row_image                           | FULL         |
| binlog_rows_query_log_events               | OFF          |
| binlog_stmt_cache_size                     | 32768        |
| binlog_transaction_dependency_history_size | 25000        |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER |
+--------------------------------------------+--------------+
16 rows in set (0.01 sec)

  4. Run the following command to check that GTID mode (gtid_mode) is on.

    mysql> show variables like 'gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |   <--- it is now showing ON
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
+----------------------------------+-----------+
5 rows in set (0.00 sec)

mysql> select @@gtid_executed, @@gtid_purged\G
*************************** 1. row ***************************
@@gtid_executed:
  @@gtid_purged:
1 row in set, 1 warning (0.00 sec)

mysql> select * from mysql.gtid_executed;
Empty set (0.00 sec)   <--- this is empty because we just turned it ON. As transactions occur this will get populated

Note: It is common for the first query to show NULL values for GTID. This is because in MySQL versions 5.7 the GTID value is stored only in the mysql.gtid_executed table.

Task 5: Create the airportdb Schema (Metadata only) in the Target Database

  1. Extract the schema metadata from the source database.

    [root@bastion airport-db]# mysqldump --host <SourceIP> -u admin -p --no-data --routines --events airportdb > airportdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- Warning: column statistics not supported by the server.

  2. Import the schema metadata into the target database.

    [root@bastion opc]#  mysql --host <TargetIP> -u admin -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 581
Server version: 8.4.4-u5-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database airportdb;
Query OK, 1 row affected (0.01 sec)

mysql> use airportdb;
Database changed
mysql> source airportdb.sql

Task 6: Set up OCI GoldenGate Deployment and Add Connections

There are several blogs, tutorials on how to set up OCI GoldenGate deployments, so here we are going to show a few steps.

  1. Log in to the OCI Console and select GoldenGate Deployments.

  2. Click Create deployment and enter the required information as shown in the following image.

    Image showing how to create golden gate deployment

  3. Add source and target connection details on the Create deployment page.

    Image showing how to create golden gate connection for source

    Image showing how to create golden gate connection for target

    Image showing both connections added in GG

  4. Assign the connections to the OCI GoldenGate deployment. Click the connection name and then Assign deployment.

    Image showing how to assign deployment to a connection

    Image showing source connection assigned to deployment

    Image showing target connection assigned to deployment

  5. Using OCI Bastion service, set up port forwarding to port 443 and then log in to the OCI GoldenGate console.

    Image showing goldengate home page URL

  6. Validate the database connections from the OCI GoldenGate console.

    1. Click the Hamburger menu and you will see the two connections added to the deployment in Task 6.4.

    2. Click Connect to validate the connections. If connections are successful you will see the option to create checkpoint table by clicking Checkpoint +.

      It is highly recommended to add the connections using OCI GoldenGate deployment in OCI as it will automatically append the DNS to the IP addresses, without these DNS the test connections will fail.

      Image showing goldengate connections and testing connections

Task 7: Create Extract and Replicat Processes

  1. Before you create the Extract process make sure that gtid_mode is ON in the source database, especially if the target is HA.

    There are two methods to add Extract:

    • Method 1: You can add Extract after initial load (copy or dump instance) is complete and use precise instantiation method. For more information, see Precise Instantiation for MySQL to MySQL Using MySQL Shell Utilities and Oracle GoldenGate.

      To add Extract and start it to do data capture from that particular GTID or binlog as shown in the following image. Oracle recommends to use this method.

      Image showing goldengate Extract setup

      Note: Here the GTID set is taken from the JSON file of the dumpInstance or output of the copyInstance commands.

    • Method 2: You can add Extract at the very beginning before you even start initial data load and then later alter Replicat to start with a particular gtidexecuted/binlog# and binlog position that is displayed in the JSON file of the dumpInstance() or output of copyInstance().

      Use HANDLECOLLISION parameter with this method to avoid any duplicate data issue.

      Note: For this POC, Method 2 was used.

      Edit the extract parameter file:

      EXTRACT ext1
USERIDALIAS  MySQLCI57, DOMAIN OracleGoldenGate
EXTTRAIL e1
--DDL INCLUDE MAPPED  (needed for DDL replication, also need to set  binlog_row_metadata to FULL in mysql config file on source db). But not supported for MySQL V5.7
TRANLOGOPTIONS FETCHPARTIALJSON       (for JSON replication, also need to set binlog_row_value_options to empty string in the mysql config)
TABLE airportdb.*;

      Note: If you add DDL INCLUDE MAPPED to Extract parameter file then you will get error that DDL replication is not supported for MySQL version 5.7.4.

  2. Start the Extract process.

    Image showing goldengate Extract running

  3. Extract is running and generating the trail file, now create Replicat.

    1. Create checkpoint table first. Go to the Configuration section, select the target database and click Checkpoint + to create checkpoint table.

      Image showing goldengate checkpoint table creation

    2. Create Replicat process. Since this is unidirectional replication we can go with parallel Replicat for better performance. For bidirectional replication only classic Replicat is supported.

      Do not start the Replicat process, just create it. We will start it after the data is imported.

      Image showing goldengate replicat creation

    3. Edit the parameter file. You may also add PARALLEL parameters or the default parallelism will be used.

      REPLICAT rep1
USERIDALIAS  MySQLGG1, DOMAIN OracleGoldenGate
MAP airportdb.*, TARGET airportdb.*;

Task 8: Copy Data from Source to Target using the copyInstance()

  1. Perform a dry run using dryRun:"true" option of copyInstance().

    Type '\help' or '\?' for help; '\quit' to exit.
MySQL  SQL > \connect admin@<Source IP>
Creating a session to 'admin@<Source IP>'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 37
Server version: 5.7.44-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  <Source IP>:3306 ssl  SQL > \js
Switching to JavaScript mode...
 MySQL  <Source IP>:3306 ssl  JS > util.copyInstance('mysql://admin@<Target IP>', {"compatibility":["skip_invalid_accounts","strip_definers","strip_restricted_grants","strip_tablespaces","ignore_wildcard_grants","strip_invalid_grants","create_invisible_pks"], users:"true", threads:2, dryRun:"true"});
Please provide the password for 'admin@<target IP': *******************
Save password for 'admin@<target IP>'? [Y]es/[N]o/Ne[v]er (default No):

  2. If there are no errors, then remove the dryRun option and re-run to perform the data load. The final output should look like this: No errors reported.

    SRC: Starting data dump
100% (59.50M rows / ~59.36M rows), 142.25K rows/s, 9.68 MB/s
SRC: Dump duration: 00:07:50s
SRC: Total duration: 00:07:50s
SRC: Schemas dumped: 2
SRC: Tables dumped: 15
SRC: Data size: 2.03 GB
SRC: Rows written: 59502422
SRC: Bytes written: 2.03 GB
SRC: Average throughput: 4.33 MB/s
1 thds indexing \ 100% (2.03 GB / 2.03 GB), 7.47 MB/s (161.06K rows/s), 15 / 15 tables done
Building indexes - done
Executing common postamble SQL - done
TGT: 53 chunks (59.50M rows, 2.03 GB) for 15 tables in 2 schemas were loaded in 7 min 51 sec (avg throughput 4.32 MB/s, 126.29K rows/s)
TGT: 17 DDL files were executed in 0 sec.
TGT: 0 accounts were loaded, 3 accounts failed to load due to unsupported authentication plugin errors
TGT: Data load duration: 7 min 51 sec
TGT: 1 indexes were built in 0 sec.
TGT: Total duration: 7 min 51 sec
TGT: 0 warnings were reported during the load.
..
..
Dump_metadata:
Binlog_file: mysql-bin.000006
Binlog_position: 626
Executed_GTID_set: 7ee61c32-16eb-11f0-b3fc-02001702dcb5:1-3

Note: Make a note of the GTID executed shown in the output of copyInstance() and use this to alter the Replicat.

Task 9: Alter Replicat and Start

Use the logfile number which is 000006 and log position number which is 626 as shown in the output of the copyInstance() in Task 8. This logfile and log position should be used in the format to generate CSN 000006:000000000000626.

Alter Replicat and click Start to start. Image showing how to alter replicat

Replicat status showing as Running.

Image showing replicat status

Note: If you had used Method 1 for adding Extract as shown in Task 7, the altering Replicat will not be needed. In this POC, we used Method 2 to add Extract. Either methods work fine.

Task 10: Run DML Tests

Test the replication by performing DML activity on source.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.