{"id":6650,"date":"2024-07-16T08:45:00","date_gmt":"2024-07-16T07:45:00","guid":{"rendered":"https:\/\/dasini.net\/blog\/?p=6650"},"modified":"2024-08-06T10:29:53","modified_gmt":"2024-08-06T09:29:53","slug":"get-your-heatwave-mysql-data-on-premises-with-replication","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2024\/07\/16\/get-your-heatwave-mysql-data-on-premises-with-replication\/","title":{"rendered":"Get Your HeatWave MySQL Data On-Premises with Replication"},"content":{"rendered":"\n<p>MySQL Replication exist for centuries&#8230; OK, I&rsquo;m exaggerating a little bit \ud83d\ude42 but this feature is as much appreciated as it is useful, because is helping DBAs to easily build scalable and redundant architectures:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>MySQL 3.23.15 and up features support for one-way, asynchronous replication, in which one<br>server acts as the master, while one or more other servers act as slaves<\/p>\n<cite>MySQL 3.23\/4.0\/4.1 Reference Manual<\/cite><\/blockquote>\n\n\n\n<p>At least from MySQL 3.23&#8230; I told you it&rsquo;s been there for a long time: <a href=\"https:\/\/dasini.net\/blog\/2017\/07\/19\/mysql-first-public-releases\/\" target=\"_blank\" rel=\"noopener\" title=\"\">MySQL first Public Releases<\/a><\/p>\n\n\n\n<p>But what does replication do?<br>Quoting the documentation again (<em>the most recent this time<\/em>)<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Replication enables data from one MySQL database server (the  <strong>source<\/strong>) to be copied to one or more MySQL database servers (the <strong>replicas<\/strong>). <br>Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source.<\/p>\n<cite>MySQL 8.4 Reference Manual<\/cite><\/blockquote>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>In this article we will see how to replicate data from <a href=\"https:\/\/www.mysql.com\/products\/heatwave\/\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave\">HeatWave MySQL<\/a> to a on-premise standalone MySQL Instance.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"303\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=800%2C303&#038;ssl=1\" alt=\"Get Your HeatWave MySQL Data On-Premises with Replication\" class=\"wp-image-6657\" srcset=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=800%2C303&amp;ssl=1 800w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=300%2C114&amp;ssl=1 300w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=768%2C291&amp;ssl=1 768w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1536%2C583&amp;ssl=1 1536w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?w=1640&amp;ssl=1 1640w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/figure><\/div>\n\n\n<ul class=\"wp-block-list\">\n<li>Source is HeatWave MySQL 8.4.1\n<ul class=\"wp-block-list\">\n<li>IP: 137.235.181.51<\/li>\n\n\n\n<li>Port: 3306<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Replica is MySQL 8.4.1\n<ul class=\"wp-block-list\">\n<li>IP: 88.65.229.255<\/li>\n\n\n\n<li>Port: 3306<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Replication user\n<ul class=\"wp-block-list\">\n<li>&lsquo;rplUser&rsquo;@&rsquo;88.65.229.%&rsquo;<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>We will use 3 main components:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>HeatWave MySQL as a source<\/li>\n\n\n\n<li>The asynchronous replication capability of MySQL<\/li>\n\n\n\n<li>A MySQL instance as a read replica<\/li>\n<\/ul>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">HeatWave MySQL<\/h2>\n\n\n\n<p>HeatWave is a fully-managed database service, powered by the integrated in-memory query accelerator. It is <strong>the only cloud-native database service that combines transactions, analytics, machine learning and generative AI services into HeatWave<\/strong>, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication. It also includes HeatWave Lakehouse which allows users to query data stored in object storage in a variety of file formats. <br>HeatWave is developed, managed, and supported by the MySQL team in Oracle.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;68a6154d21780&quot;}\" data-wp-interactive=\"core\/image\" class=\"aligncenter size-large wp-lightbox-container\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"415\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on-async--click=\"actions.showLightbox\" data-wp-on-async--load=\"callbacks.setButtonStyles\" data-wp-on-async-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=800%2C415&#038;ssl=1\" alt=\"Data processing with HeatWave\" class=\"wp-image-6723\" srcset=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=800%2C415&amp;ssl=1 800w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=300%2C156&amp;ssl=1 300w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=768%2C399&amp;ssl=1 768w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=1536%2C797&amp;ssl=1 1536w, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/hw_product_image.png?resize=2048%2C1063&amp;ssl=1 2048w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Agrandir\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on-async--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewBox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure><\/div>\n\n\n<p><strong>HeatWave MySQL accelerate MySQL query performance by orders of magnitude and get real-time analytics on your transactional data<\/strong>. HeatWave MySQL is built on <strong>MySQL Enterprise Edition<\/strong>.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>The HeatWave MySQL endpoints are not directly accessible from the internet (i.e. private IP). That said, there are <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/connecting-db-system1.html\" target=\"_blank\" rel=\"noopener\" title=\"Connecting to HeatWave\">different possibilities to connect<\/a> such as &nbsp;a <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/compute-instance.html#GUID-6087DA45-06E0-44AD-9CAB-0FC37423A07A\" target=\"_blank\" rel=\"noopener\" title=\"Connect to HeatWave from a compute Instance\">compute instance<\/a>, <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/vpn-connection.html#GUID-C0E30DCC-A73D-4074-8543-4C361D879185\" target=\"_blank\" rel=\"noopener\" title=\"Connect to HeatWave from a VPN connection\">VPN connection<\/a>, <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/bastion-session.html#GUID-1FF4D0F2-8066-4903-B98B-F63478594DF6\" target=\"_blank\" rel=\"noopener\" title=\"Connect to HeatWave from a Bastion session\">Bastion session<\/a>, or<strong> <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/network-load-balancer.html\" target=\"_blank\" rel=\"noopener\" title=\"OCI Network Load Balancer\">network load balancer<\/a><\/strong>.<br>In this article I&rsquo;ll use the latter, which is present on a public subnet, enables you to connect to HeatWave MySQL over the internet.<br>However please note that <strong>it is not recommended, to make a database accessible over the internet<\/strong>, because it is a security risk.<br>You <strong>must restrict the authorized public IP addresses<\/strong> to a single IP address or a small range of IP addresses, and <strong>use in-transit encryption<\/strong>. <br>It is recommended to use a <strong>VPN connection<\/strong>.<br>You are warned!<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>I assuming your HeatWave MySQL instance is already created. If not, you can use the Console or the command-line interface to create&nbsp;an instance.<br>Using the console is pretty straightforward, you can find the details in the <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/creating-db-system1.html\" target=\"_blank\" rel=\"noopener\" title=\"OCI Documentation - Creating a HeatWave DB system\">OCI documentation<\/a>.<\/p>\n\n\n\n<p>I also assuming that you have already setup your <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/network-load-balancer.html\" target=\"_blank\" rel=\"noopener\" title=\"Network Load Balancer\">Network Load Balancer<\/a>. If not I recommend you this great tutorial from my colleague <a href=\"https:\/\/www.linkedin.com\/in\/scott-stroz\/\" target=\"_blank\" rel=\"noopener\" title=\"Scott Stroz\">Scott<\/a> : <a href=\"https:\/\/blogs.oracle.com\/mysql\/post\/connecting-to-a-mysql-heatwave-database-instance-using-an-oci-network-load-balancer\" target=\"_blank\" rel=\"noopener\" title=\"Connecting to a MySQL HeatWave Database Instance Using an OCI Network Load Balancer\">Connecting to a MySQL HeatWave Database Instance Using an OCI Network Load Balancer<\/a>.<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL Replication setup<\/h2>\n\n\n\n<p>Since MySQL replication is a familiar concept, widely covered over the years, let&rsquo;s get right to the practical details that will help you set up your replication efficiently.<br>You&rsquo;ll find all the juicy details in the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication.html\" target=\"_blank\" rel=\"noopener\" title=\"MySQL 8.4 Reference Manual\">MySQL 8.4 Reference Manual<\/a> and some extra <a href=\"https:\/\/lefred.be\/content\/category\/mysql\/replication\/\" target=\"_blank\" rel=\"noopener\" title=\"Lefred's blog - Category Replication\">here<\/a> and <a href=\"https:\/\/dasini.net\/blog\/?s=replication\" target=\"_blank\" rel=\"noopener\" title=\"Dasini.net - Category Replication\">here<\/a>.<\/p>\n\n\n\n<p>Because the data stream will be over the Internet, we must, for obvious security reason, encrypted the connection. <br>More information on <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication-encrypted-connections.html\" target=\"_blank\" rel=\"noopener\" title=\"Setting Up Replication to Use Encrypted Connections\">setting up Replication to Use Encrypted Connections<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Source configuration<\/h3>\n\n\n\n<p>On the source, create the replication user then grant that user the&nbsp;<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/privileges-provided.html#priv_replication-slave\"><code>REPLICATION SLAVE<\/code><\/a>&nbsp;privilege. <br>If you want to ensure that the source only accepts replicas that connect using encrypted connections (and btw you really want that), use the&nbsp;<code><strong>REQUIRE SSL<\/strong><\/code>&nbsp;option<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- On the Source\n-- Create the replication user\nCREATE USER 'rplUser'@'88.65.229.%' IDENTIFIED BY 'Q{P6@EH$L!YFje^9' REQUIRE SSL;\nGRANT REPLICATION SLAVE ON *.* TO 'rplUser'@'88.65.229.%';<\/code><\/pre>\n\n\n\n<p>If the HeatWave MySQL instance (the source) contains existing data it is necessary to copy this data to the replica. There are different ways to dump the data from the source database, the recommended way in that context is to use <strong>MySQL Shell <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noopener\" title=\"Instance Dump Utility, Schema Dump Utility, and Table Dump Utility\">dump<\/a> and <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-load-dump.html\" target=\"_blank\" rel=\"noopener\" title=\"Dump Loading Utility\">load<\/a> utilities<\/strong>.<\/p>\n\n\n\n<p>You will most likely need <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noopener\" title=\"dumpInstance()\">dumpInstance<\/a> and the relevant options are dependent of your context, the most important is usually <em><strong>compatibility<\/strong><\/em>. <br>MySQL Shell allows you to store your backup into an object store bucket and load from there, this is very convenient. For this article, the size of my dataset allows me to store it locally, and the only relevant option is <em><strong>dryRun<\/strong><\/em>. <br>More details in the documentation: <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noopener\" title=\"Instance Dump Utility, Schema Dump Utility, and Table Dump Utility\">Instance Dump Utility, Schema Dump Utility, and Table Dump Utility<\/a>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqlsh --js admin@137.235.181.51 \n\nJS&gt; util.dumpInstance(\"\/backup\/20240714\", { dryRun: true })\ndryRun enabled, no locks will be acquired and no files will be created.\n...<\/code><\/pre>\n\n\n\n<p>If the result is satisfactory, then disable dry run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JS&gt; util.dumpInstance(\"\/backup\/20240714\")\n...<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>For your record, below an example of a dump that is<strong> stored in an object Storage bucket<\/strong> named MyBck_07 on <strong>Oracle Cloud Infrastructure<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>util.dumpInstance(\"20240714\", {osBucketName: \"MyBck_07\", osNamespace: \"abcdefghijkl\", ocimds: true })<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Restore the dump on replica<\/h3>\n\n\n\n<p>The dump must be restored on the replica:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- On the Replica\n\\sql SET GLOBAL  local_infile=1;\n\nutil.loadDump(\"\/backup\/20240714\", {updateGtidSet:\"replace\", skipBinlog:true})\n\n\\sql SET GLOBAL  local_infile=0;<\/code><\/pre>\n\n\n\n<p>All the option details are available in the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-load-dump.html\" target=\"_blank\" rel=\"noopener\" title=\"MySQL Shell - Dump Loading Utility\">documentation<\/a>. Please note that you can test you command with the <em>dryRun<\/em> option.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Also for your record, if you want to restore a dump for a bucket, your command will look like:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>util.loadDump(\"20240714\", {osBucketName: \"MyBck_07\", osNamespace: \"abcdefghijkl\"})<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Setting the Source Configuration on the Replica<\/h3>\n\n\n\n<p>To set up the replica to communicate with the source for replication, configure the replica with the necessary connection information:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- On the Replica\nCHANGE REPLICATION SOURCE TO SOURCE_HOST = '137.235.181.51', SOURCE_AUTO_POSITION = 1, SOURCE_SSL=1;\n\nSTART REPLICA USER = 'rplUser' PASSWORD = 'Q{P6@EH$L!YFje^9';<\/code><\/pre>\n\n\n\n<p>For a full list of options, see <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/change-replication-source-to.html\" target=\"_blank\" rel=\"noopener\" title=\"CHANGE REPLICATION SOURCE TO Statement\">CHANGE REPLICATION SOURCE TO options<\/a>.<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Administration<\/h2>\n\n\n\n<p>On the replica, you can manage your replication as you normally do:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Checking Replication Status\nSQL &gt; SHOW REPLICA STATUS\\G\n*************************** 1. row ***************************\n             Replica_IO_State: Waiting for source to send event\n                  Source_Host: 137.235.181.51\n                  Source_User: rplUser\n                  Source_Port: 3306\n                Connect_Retry: 60\n              Source_Log_File: binary-log.000088\n          Read_Source_Log_Pos: 378\n               Relay_Log_File: daz-Latitude-7400-relay-bin.000004\n                Relay_Log_Pos: 557\n        Relay_Source_Log_File: binary-log.000088\n           Replica_IO_Running: Yes\n          Replica_SQL_Running: Yes\n...\n          SQL_Remaining_Delay: NULL\n    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates\n...<\/code><\/pre>\n\n\n\n<p>Other useful commands on the replica, using <strong><em>performance_schema<\/em><\/strong> are:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SELECT * FROM performance_schema.replication_applier_status\\G\n*************************** 1. row ***************************\n              CHANNEL_NAME: \n             SERVICE_STATE: ON\n           REMAINING_DELAY: NULL\nCOUNT_TRANSACTIONS_RETRIES: 0<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SELECT * FROM performance_schema.replication_connection_configuration\\G\n*************************** 1. row ***************************\n                   CHANNEL_NAME: \n                           HOST: 137.235.181.51\n                           PORT: 3306\n                           USER: rplUser\n              NETWORK_INTERFACE: \n                  AUTO_POSITION: 1\n                    SSL_ALLOWED: YES\n...<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SELECT * FROM performance_schema.replication_connection_status\\G\n*************************** 1. row ***************************\n               CHANNEL_NAME: \n                 GROUP_NAME: \n                SOURCE_UUID: 22f79ae9-991d-4b3c-82b9-b81bb2366635\n                  THREAD_ID: 73\n              SERVICE_STATE: ON\n  COUNT_RECEIVED_HEARTBEATS: 17\n   LAST_HEARTBEAT_TIMESTAMP: 2024-06-26 10:42:57.615232\n   RECEIVED_TRANSACTION_SET: 22f79ae9-991d-4b3c-82b9-b81bb2366635:56\n          LAST_ERROR_NUMBER: 0\n         LAST_ERROR_MESSAGE: \n       LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000\n    LAST_QUEUED_TRANSACTION: 22f79ae9-991d-4b3c-82b9-b81bb2366635:56\n...<\/code><\/pre>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>On the source, you can check the status of connected replicas:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Check the status of connected replicas\nSQL &gt; SHOW PROCESSLIST \\G\n*************************** 6. row ***************************\n     Id: 547\n   User: rplUser\n   Host: nlb-mhs-paris-dba.sub03221692542.vcnparisdba.oraclevcn.com:1234\n     db: NULL\nCommand: Binlog Dump GTID\n   Time: 428\n  State: Source has sent all binlog to replica; waiting for more updates\n   Info: NULL<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Basic information about the replicas that were started with the&nbsp;<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication-options-replica.html#sysvar_report_host\" target=\"_blank\" rel=\"noopener\" title=\"report-host option\"><code>--report-host<\/code><\/a>&nbsp;option and are connected to the source:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SHOW REPLICAS \\G\n*************************** 1. row ***************************\n   Server_Id: 1\n        Host: Replica-8865229255\n        Port: 3306\n   Source_Id: 3640816068\nReplica_UUID: 46c6de0c-2ff7-4b14-a80a-5dffea08864a<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p>Other useful commands on the source, using <strong><em>performance_schema<\/em><\/strong> are:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SELECT \n   SERVICE_STATE, \n   HOST, \n   USER, \n   PORT\nFROM performance_schema.replication_connection_configuration \n   INNER JOIN performance_schema.replication_applier_status \n      USING (CHANNEL_NAME) \\G\n*************************** 1. row ***************************\nSERVICE_STATE: ON\n         HOST: 137.235.181.51\n         USER: rplUser\n         PORT: 3306<\/code><\/pre>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n    concat(conn_status.channel_name, ' (', worker_id,')') AS channel,\n    conn_status.service_state AS io_state,\n    applier_status.service_state AS sql_state,\n    format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = \"\",\"0\",\n    abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,\n    timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,\n    format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -\n    LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,\n    format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -\n    LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,\n    format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -\n    LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,\n    conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,\n    applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction\nFROM\n    performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status \n        ON applier_status.channel_name = conn_status.channel_name\n\\G\n*************************** 1. row ***************************\n                 channel:  (1)\n                io_state: ON\n               sql_state: ON\n                 latency:   0 ps\n          transport_time:   0 ps\n       time_to_relay_log:   0 ps\n              apply_time:   0 ps\n last_queued_transaction: \nlast_applied_transaction: \n...<\/code><\/pre>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Peroration<\/h2>\n\n\n\n<p>This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). <br>It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer. <br>While a Network Load Balancer can be used for the connection, a VPN is recommended for better security.<\/p>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Resources<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/index.html\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave\">HeatWave<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/outbound-replication.html\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave MySQL - Outbound Replication\">HeatWave MySQL &#8211; Outbound Replication<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication.html\" target=\"_blank\" rel=\"noopener\" title=\"MySQL Replication\">MySQL Replication<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/change-replication-source-to.html\" target=\"_blank\" rel=\"noopener\" title=\"CHANGE REPLICATION SOURCE TO Statement\">CHANGE REPLICATION SOURCE TO Statement<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication-encrypted-connections.html\" target=\"_blank\" rel=\"noopener\" title=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication-encrypted-connections.html\">Setting Up Replication to Use Encrypted Connections<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/replication-administration-status.html\" target=\"_blank\" rel=\"noopener\" title=\"Checking Replication Status\">Checking Replication Status<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dasini.net\/blog\/2021\/04\/13\/heatwave-a-mysql-cloud-feature-to-speed-up-your-queries\/\" target=\"_blank\" rel=\"noopener\" title=\"HeatWave \u2013 A MySQL cloud feature to speed up your queries\">HeatWave \u2013 A MySQL cloud feature to speed up your queries<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dasini.net\/blog\/2021\/01\/26\/replicate-from-mysql-5-7-to-mysql-database-service\/\" target=\"_blank\" rel=\"noopener\" title=\"Replicate from MySQL 5.7 to HeatWave MySQL\">Replicate from MySQL 5.7 to HeatWave MySQL<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noopener\" title=\"MySQL Shell - Instance Dump Utility, Schema Dump Utility, and Table Dump Utility\">MySQL Shell &#8211; Instance Dump Utility, Schema Dump Utility, and Table Dump Utility<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.4\/en\/mysql-shell-utilities-load-dump.html\" target=\"_blank\" rel=\"noopener\" title=\"MySQL Shell - Dump Loading Utility\">MySQL Shell &#8211; Dump Loading Utility<\/a><\/li>\n<\/ul>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p><a href=\"https:\/\/www.linkedin.com\/groups\/12524512\/\" target=\"_blank\" rel=\"noopener\" title=\"Olivier DASINI on Linkedin\">Follow me on Linkedin<\/a><\/p>\n\n\n\n<p>Watch my videos on my <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier's MySQL Channel\">YouTube channel<\/a> and <a href=\"https:\/\/www.youtube.com\/channel\/UC12TulyJsJZHoCmby3Nm3WQ\/?sub_confirmation=1\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Subscribe\">subscribe<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/www.slideshare.net\/freshdaz\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Slideshare\">Slideshare account<\/a>.<\/p>\n\n\n\n<p>My <a href=\"https:\/\/speakerdeck.com\/freshdaz\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Olivier DASINI on Speaker Deck\">Speaker Deck account<\/a>.<\/p>\n\n\n\n<div style=\"height:25px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-vivid-red-color has-text-color\"><strong>Thanks for using HeatWave &amp; MySQL!<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica).<br \/>\nIt highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer. <\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[1694,203,339],"tags":[1697],"class_list":["post-6650","post","type-post","status-publish","format-standard","hentry","category-heatwave-en","category-mysql-en","category-tuto-en","tag-heatwave-fr-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-1Jg","jetpack-related-posts":[],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/6650","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=6650"}],"version-history":[{"count":91,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/6650\/revisions"}],"predecessor-version":[{"id":6796,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/6650\/revisions\/6796"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=6650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=6650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=6650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}