Are you thinking about using MySQL HeatWave inbound replication to create a replica of your data in Oracle Cloud Infrastructure (OCI)? Have you read the MySQL HeatWave source configuration documentation and discovered that global transaction identifiers (GTIDs) are recommended for your source MySQL server?
If the source you want to replicate does not have GTIDs enabled, you do not need to enable GTIDs to use inbound replication. We know there are use cases where GTIDs are either not used intentionally and other cases where it is not practical to enable, such as an existing database server with a depth of history. Fortunately, the MySQL HeatWave Service in OCI (MHS) has you covered.
Wait, what are GTIDs?
Global transaction identifiers (GTIDs) represent a unique identifier for each source, which is assigned to each set or group of events, thereby making it possible to know which events have been applied on each replica from a particular source. For more information, see the GTIDs concept section in the online documentation.
Now, let’s see how easy it is to setup inbound replication without GTIDs. But first, let’s discuss a few limitations.
Limitations
To use inbound replication without GTIDs, your replica must be either a standalone MySQL HeatWave DB system or a DB system with HeatWave enabled. Inbound replication without GTIDs enabled on the source does not work with a high-available DB system as the replica.
Figure 1 shows the DB system setup step where you can choose between standalone and high availability options for a new DB system.
Figure 1. Selecting Standalone or High availability (Create DB system)
In addition to these, inbound replication without GTIDs must also satisfy the normal prerequisites and limitations as documented here.
With these in mind, let’s see how to configure inbound replication using a source without GTIDs enabled.
Setting Up Inbound Replication
Rather than go through the entire process to setup inbound replication, you can see a demonstration of how to properly setup inbound replication from an on-premises MySQL server to a MHS DB system in this blog. That process, except for the notations above, is the same.
Also, the process for creating a channel is the same except that you must change the replication positioning option for the source and provide additional details. You can do so on the replication positioning section of the create channel dialog as shown in Figure 2.
Figure 2. Configure Source Replication Positioning (Create Channel)
Notice that we have selected Source cannot use GTID auto-positioning. This option indicates that the source server does not create GTIDs and the gtid_mode is set to either OFF, OFF_PERMISSIVE, or ON_PREMISSIVE. When you click this option, the dialog will change prompting you for more additional information. For more information about the gtid_mode variable, see the online MySQL server documentation.
The first thing we need to decide is whether we want to specify a UUID for the source server or use the same UUID as the target DB system. It is recommended to choose the manual option if you have transactions that are generated from another DB system or an on-premises MySQL server. If you selected the manual option, you will be presented with a generated UUID. Should you not like the one generated, you can click the refresh icon on the right to generate another or manually enter your own GTID.
Finally, we must supply the binary log file name and binary log offset to establish where you want replication to begin. Typically, these values are the latest binary log file and position shown in the output of the SHOW BINARY LOG STATUS command. You can read more about this step in the online MySQL server documentation. An example of the output of the SHOW BINARY LOG STATUS is shown below with the sample values shown in bold. You can use the MySQL Shell to connect to and run the command on the source.
> SHOW BINARY LOG STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 73
Binlog_Do_DB: test
Binlog_Ignore_DB: manual, mysql
1 row in set (0.00 sec)
Once you enter these coordinates in the replication position section, you can continue with creating the channel as demonstrated in the previous blog.
Expectations
When you complete your setup of inbound replication without GTIDs on the source, you may be wondering what you can expect on your replica. The good news is that the replica should perform like any other replica generating GTIDs for the transactions coming from the source.
For example, the following demonstrates inserting data on the source in an inbound replication from an on-premises MySQL server to a standalone MySQL HeatWave DB system. At this stage, inbound replication has been set up and the channel created and active.
We begin by showing that the gtid_mode variable on the source is set to OFF.
> SHOW VARIABLES LIKE '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
9 rows in set (0.0355 sec)
Next, we show the same variable on the replica. Notice that the value is ON, which is required. Notice also that the gtid_executed variable is set to the GTID we used in setting up the channel, as shown in Figure 2 above.
> SHOW VARIABLES LIKE '%gtid%';
+----------------------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| group_replication_gtid_assignment_block_size | 1 |
| gtid_executed | eec87990-07dc-11ef-918a-2dd122fb9050. |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------------------+------------------------------------------+
10 rows in set (0.0171 sec)
Next, we issued an INSERT statement for the test_inbound.t1 table as follows.
> INSERT INTO test_inbound.t1 VALUES (5, 'five');
When we issue the SHOW BINLOG EVENTS command on the replica, we will see the transaction with the correct value for the GTID.
> SHOW BINLOG EVENTS IN 'binary-log.000060'\G
...
*************************** 3. row ***************************
Log_name: binary-log.000060
Pos: 197
Event_type: Gtid
Server_id: 575
End_log_pos: 287
Info: SET @@SESSION.GTID_NEXT= 'eec87990-07dc-11ef-918a-2dd122fb9050:1'
*************************** 4. row ***************************
Log_name: binary-log.000060
Pos: 287
Event_type: Query
Server_id: 575
End_log_pos: 365
Info: BEGIN
*************************** 5. row ***************************
Log_name: binary-log.000060
Pos: 365
Event_type: Table_map
Server_id: 575
End_log_pos: 429
Info: table_id: 116 (test_inbound.t1)
*************************** 6. row ***************************
Log_name: binary-log.000060
Pos: 429
Event_type: Write_rows
Server_id: 575
End_log_pos: 474
Info: table_id: 116 flags: STMT_END_F
...
Conclusion
MySQL HeatWave provides many options to permit you to ingest your data into OCI. One way to do that is with inbound replication and, as you have seen, it supports source servers with or without GTIDs enabled. How cool is that?