Precise Instantiation for MySQL to MySQL Using MySQL Shell Utilities and Oracle GoldenGate

The precise instantiation method allows for the initial load of data from a source database to a target database while the source database remains online for application updates. This method ensures precise positioning of the change data capture and delivery processes without having duplicate data in the target database, and without the need to use HANDLECOLLISIONS in the Replicat.

Precise instantiation for Oracle GoldenGate for MySQL uses the MySQL Shell dump and dump loading utilities and is only supported between a MySQL source and a MySQL target database.

This method requires that the MySQL Shell be installed. Installation steps are available at the following link:

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html

For detailed information and limitations of the MySQL Shell dump and dump loading utilities, refer to the following links:

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html

You will use the MySQL Shell dump utility to export either the MySQL instance, schema, or tables, depending on your requirements. You will then use the MySQL dump loading utility to load the objects into the target database, and afterwards configure a GoldenGate Extract and Replicat to replicate continual change data from the source to the target database.

The MySQL Shell dump utility creates multiple export files, one of which is a @.json file. This file is needed to determine the positioning of Extract when adding it to the source database.

The following is a sample precise instantiation method using a schema dump from the source instance, then loaded to the target instance, followed by configuration of Oracle GoldenGate for change data replication:

Connect to the MySQL Shell. For details about supported connection options with MySQL Shell, refer to this link: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connections.html
```
mysqlsh –mysqlx -u username -h hostname -P port
```
After connecting to the MySQL Shell, ensure that you are in JavaScript mode. You should see a JS > prompt. If in SQL mode, switch to JavaScript mode using the \js switch:
```
MySQL sourcehost:33060+ ssl SQL > \js
```
Verify that the Global Variable local_infile is set to ON.
```
MySQL sourcehost:33060+ ssl JS > \sql SHOW GLOBAL VARIABLES LIKE
    ‘local_infile’;
```
If the local_infile variable is set to OFF, turn it on with the following command:
```
MySQL sourcehost:33060+ ssl JS > \sql SET GLOBAL local_infile = ON;
```
Next, use one of the following functions to export either the instance, schema, or tables that you want to export.
```
util.dumpInstance(outputUrl[, options]) 
util.dumpSchemas(schemas, outputUrl[,  options])
util.dumpTables(schema, tables, outputUrl[, options])
```
In this example, we will dump the hr schema, into the ~/dumps/hr-schema-src folder:
```
MySQL sourcehost:33060+ ssl JS >util.dumpSchemas([“hr”], “~/dumps/hr-schema-src”)
```
After the export completes, use the util.loadDump utility to import the schema to the target instance.

Note:
If your target instance is on a different system than the source, you’ll need to copy the exported files to the target system.
```
MySQL targethost:33060+ ssl JS >util.loadDump(“~/dumps/hr-schema-tgt”, {schema: “tgt-hr”})
```
When the data loading utility completes, open the @.json file in the source dump folder. This file contains the last committed transaction contained in the dump and a change data Extract will need to be configured using that transaction value. Following is an example of the transaction information in the @.json file:
```
"binlogFile": "binlog.000005",
"binlogPosition": 1289,
      "gtidExecuted": "1174b383-3441-11e8-b90a-c80aa9429920:1-9,\n1174b383-3441-11e8-b90a-c80aa9429921:1-9"
```
Add an Extract to the source database using either the gtidExecuted value, or the binlogFile and binlogPosition values, based on your database configuration.
1. If the source database is configured with gtid_mode set to ON, use the gtidExecuted value from the @.json file to add the Extract.
  An example using GTIDSET positioning, based on the gtidExecuted value:
```
ADD EXTRACT extsrc, TRANLOG GTIDSET "1174b383-3441-11e8-b90a-c80aa9429920:1-9,\n1174b383-3441-11e8-b90a-c80aa9429921:1-9"
```
2. If the source database has gtid_mode set to OFF, use the binlogFile and binlogPosition values of the @.json file when adding the Extract.
  An example using LOGNUM and LOGPOS positioning, based on the binlogFile and binlogPosition values:
```
ADD EXTRACT extsrc, TRANLOG, LOGNUM 5 LOGPOS 1289
```
Finally, complete the Oracle GoldenGate configuration by adding the following processes:
1. Corresponding trail for the Extract
2. A distribution path if needed
3. A Replicat to deliver to the target database
Start the Oracle GoldenGate processes and monitor the Extract and Replicat to confirm that they are running correctly, and that lag is reduced to near real-time.