{"id":4188,"date":"2021-01-26T09:00:00","date_gmt":"2021-01-26T08:00:00","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=4188"},"modified":"2021-01-25T21:42:54","modified_gmt":"2021-01-25T20:42:54","slug":"replicate-from-mysql-5-7-to-mysql-database-service","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2021\/01\/26\/replicate-from-mysql-5-7-to-mysql-database-service\/","title":{"rendered":"Replicate from MySQL 5.7 to MySQL Database Service"},"content":{"rendered":"\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/cloud_from_above_by_Olivier_Dasini_1632x918.JPG\" alt=\"Cloud from above by Olivier DASINI\"\/><\/figure>\n\n\n\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Replication\">MySQL Replication<\/a> is a very common topology, widely used in various architecture.<br>People use it, among others, for High Availability, Read Scalability or Geographic Redundancy.<\/p>\n\n\n\n<p>Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture.<br>Let&rsquo;s say you are running MySQL 5.7 then you can easily setup a <span style=\"text-decoration: underline;\">8.0 instance as a replica of your 5.7<\/span>.<\/p>\n\n\n\n<p>Extending this idea it is also possible to replicate your MySQL 5.7 (or 8.0 obviously) to a <a href=\"https:\/\/www.oracle.com\/mysql\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Database Service\">MySQL Database Service<\/a> (MDS) instance, the <em>true<\/em> MySQL PaaS on <a href=\"https:\/\/www.oracle.com\/cloud\/#:~:text=Oracle%20Cloud%20Infrastructure%20(OCI)%20is,integrated%20security%2C%20and%20serverless%20compute.\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Oracle Cloud Infrastructure\">Oracle Cloud Infrastructure<\/a> (OCI).<\/p>\n\n\n\n<p>This is that story you are about to read now \ud83d\ude42<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Architecture<\/h2>\n\n\n\n<p>To make things clear, this is what the (final) architecture looks like:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>An OCI Compute instance where <a href=\"https:\/\/downloads.mysql.com\/archives\/community\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Product Archives\">MySQL 5.7<\/a>.33 &amp; <a href=\"https:\/\/dev.mysql.com\/downloads\/shell\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Download MySQL Shell\">MySQL Shell<\/a> 8.0.23 are installed <ul><li><span style=\"text-decoration: underline;\">Public  IP<\/span>  : 123.45.678.90 <\/li><li><span style=\"text-decoration: underline;\">Private IP<\/span> : 10.0.0.9<\/li><\/ul><\/li><li>A MDS instance<ul><li><span style=\"text-decoration: underline;\">DB name<\/span>  : MDS2_FRA<\/li><li><span style=\"text-decoration: underline;\">Private IP<\/span> : 10.0.1.23 <\/li><\/ul><\/li><li>A replication Channel<ul><li><span style=\"text-decoration: underline;\">Channel name<\/span> : mysqlchannel_MDS2_FRA<\/li><\/ul><\/li><\/ul>\n\n\n\n\n\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MEM_MySQL5.7-MDS_Replication_topology.png\" alt=\"MySQL Replication topology from MySQL Enterprise Monitor\"\/><figcaption>MySQL Replication topology from MySQL Enterprise Monitor<\/figcaption><\/figure>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Plan<\/h2>\n\n\n\n<p>How to proceed?<\/p>\n\n\n\n<p>Let&rsquo;s split the problem in sub-parts:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Create a compute &amp; a MDS instances<\/li><li>Create on the source a dedicated user to the replication<\/li><li>Dump the MySQL instance running on the OCI compute for being migrated to MDS<\/li><li>Load the dump in MDS<\/li><li>Create a replication channel on MDS (from OCI to MDS)<\/li><\/ol>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Requirement<\/h2>\n\n\n\n<p>Before starting, please verify that your source follows the requirements.<\/p>\n\n\n\n<p><span style=\"text-decoration: underline;\">Current limitations of MySQL Database Service Inbound Replication<\/span><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Only Row-based replication supported<\/li><li>Only GTID-based replication is supported<\/li><li>Multi-source replication is not supported<\/li><li>Replication filters are not supported<\/li><li>Changes to the mysql schema are not replicated and cause replication to stop<\/li><li>Source and Replica must run with the same <em>lower_case_table_names<\/em> to avoid data transfer problems arising from letter case of database or table names in cross-platform topology<\/li><li>The inbound applier runs under the privileges of the DB System&rsquo;s admin user<\/li><\/ul>\n\n\n\n<p>The full &amp; up to date list is available <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/replication.html#GUID-94149D15-BE97-483E-99F7-82F347AB9D0E\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MDS Inbound Replication\">here<\/a>.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Create a compute &amp; a MDS instances<\/h2>\n\n\n\n<p>The creation of thee 2 objects is out of the scope of this article. <br>You&rsquo;ll find the relevant resources following these above links:<\/p>\n\n\n\n<p>You can start using <strong>OCI for free<\/strong> : <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/FreeTier\/freetier.htm#Oracle_Cloud_Infrastructure_Free_Tier\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"OCI Free Tier\">Oracle Cloud Infrastructure Free Tier<\/a> <\/p>\n\n\n\n<p>Create a <strong>MySQL DB<\/strong> System is very easy : <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/getting-started.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Getting Started with MDS\">Getting Started with MySQL Database Service<\/a> <\/p>\n\n\n\n<p>You also need to have a running <strong>compute<\/strong> instance attached to a public subnet on the same VCN as the MySQL DB System : <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/Compute\/Tasks\/launchinginstance.htm#Creating_an_Instance\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Creating an OCI Compute  Instance\">Creating a Compute  Instance<\/a><\/p>\n\n\n\n<p>Indeed MySQL 5.7 and MySQL Shell 8.0 are installed on this compute instance : <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-install-linux-quick.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Installing MySQL Shell on Linux\">Installing MySQL Shell on Linux<\/a>. (obviously it works also if you are on Windows).<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Create on the source a dedicated user to the replication<\/h2>\n\n\n\n<p>Assuming the prerequisites above are fine and the <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/networking-setup-mysql-db-systems.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Networking security\">security lists<\/a> are updated, we can now proceed.<\/p>\n\n\n\n<p>Connect by SSH, to the compute instance where MySQL 5.7 is installed:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ssh -i id_rsa  ubuntu@123.45.678.90<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p><span style=\"text-decoration: underline;\"><em>Note<\/em><\/span>:<br>If you are using Ubuntu (the case here) the user is <em><strong>ubuntu<\/strong><\/em>.  <br>For others GNU Linux systems, the user should be <strong><em>opc<\/em><\/strong>.<\/p><\/blockquote>\n\n\n\n\n\n<p>Connect to the MySQL 5.7 instance using MySQL Shell 8.0<strong>.23<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqlsh --version\nmysqlsh   Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqlsh root@localhost:3306 --sql<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT version();\n+------------+\n| version()  |\n+------------+\n| 5.7.33-log |\n+------------+<\/code><\/pre>\n\n\n\n\n\n<p>Then create the replication user with his relevant privileges:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; CREATE USER rplAdmin@'10.0.1.%' IDENTIFIED WITH 'mysql_native_password' BY 'Repl1234c@' REQUIRE SSL; \n\n\nSQL&gt; GRANT REPLICATION SLAVE on *.* to rplAdmin@'10.0.1.%';<\/code><\/pre>\n\n\n\n\n\n<p>We can check the user:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT user, host, plugin FROM mysql.user WHERE user = 'rplAdmin';\n+-----------+----------+-----------------------+\n| user      | host     | plugin                |\n+-----------+----------+-----------------------+\n| rplAdmin  | 10.0.1.% | mysql_native_password |\n+-----------+----------+-----------------------+\n\n\nSQL&gt; SHOW GRANTS FOR rplAdmin@'10.0.1.%';\n+---------------------------------------------------------+\n| Grants for rplAdmin@10.0.1.%                            |\n+---------------------------------------------------------+\n| GRANT REPLICATION SLAVE ON *.* TO 'rplAdmin'@'10.0.1.%' |\n+---------------------------------------------------------+<\/code><\/pre>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Dump the MySQL instance running on OCI for being migrated to MDS<\/h2>\n\n\n\n<p>Now we want to put the current MySQL 5.7 data on the MDS instance.<\/p>\n\n\n\n<p>In this context the best tools to do this job are <strong>MySQL Shell utilities<\/strong>.<br>We will use the <strong><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Instance Dump Utility, Schema Dump Utility, and Table Dump Utility\">instance dump utility<\/a><\/strong>.<\/p>\n\n\n\n<p>The backup output can be stored on a <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/Object\/Concepts\/objectstarthere.htm\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Object Storage\">Object Storage<\/a> Bucket &#8211; <em>I will write an article on that<\/em>.<br>But today, let&rsquo;s keep it simple. We&rsquo;ll store the backup locally on the compute instance.<\/p>\n\n\n\n<p>This strategy makes sense if you have enough free disk space on the compute. Also it will be faster to restore the data on MDS with this strategy than having the dump on the object storage.<\/p>\n\n\n\n\n\n<p>A good practice when attempting to import data from MySQL 5.7 into MDS is to use MySQL Shell&rsquo;s upgrade checker utility <strong><em>util.checkForServerUpgrade()<\/em><\/strong> to check the schemas and if needed proactively fix any compatibility issues identified.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; \\JS\n\nJS&gt; util.checkForServerUpgrade()<\/code><\/pre>\n\n\n\n\n\n<p>We&rsquo;re good to go, so let&rsquo;s create the backup directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mkdir -p \/home\/ubuntu\/backup\/<\/code><\/pre>\n\n\n\n\n\n<p>Then dump our MySQL 5.7 instance using <strong>util.dumpInstance()<\/strong> :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JS&gt; util.dumpInstance(\"\/home\/ubuntu\/backup\/20210117\", {ocimds: true, threads: 8, showProgress: true, compatibility: &#91;\"force_innodb\", \"strip_definers\", \"strip_restricted_grants\", \"strip_tablespaces\"]})<\/code><\/pre>\n\n\n\n<p>Please note <strong><em>ocimds<\/em><\/strong> &amp; <strong><em>compatibility<\/em><\/strong> options. <br>Details are available in the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-utilities-dump-instance-schema.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Instance Dump Utility, Schema Dump Utility, and Table Dump Utility\">documentation<\/a>.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Load the dump in MDS<\/h2>\n\n\n\n<p>Now we must restore our dump. <br>We will use the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-utilities-load-dump.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Dump Loading Utility\">Dump Loading Utility<\/a>.<\/p>\n\n\n\n<p>From the compute instance :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ ssh -i id_rsa  ubuntu@123.45.678.90<\/code><\/pre>\n\n\n\n\n\n<p>connect to the MDS instance (10.0.1.23):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqlsh mdsAdmin@10.0.1.23<\/code><\/pre>\n\n\n\n\n\n<p>Then load the MySQL 5.7 instance&rsquo;s dump using <strong>util.loadDump()<\/strong> :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JS&gt; util.loadDump(\"\/home\/ubuntu\/backup\/20210117\",{ignoreVersion: true, updateGtidSet: \"replace\", threads: 8, waitDumpTimeout: 1800})<\/code><\/pre>\n\n\n\n<p>Please note the <strong><em>ignoreVersion<\/em><\/strong> &amp; <strong><em>updateGtidSet<\/em><\/strong> options.<br>Documentation is available <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-utilities-load-dump.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Dump Loading Utility\">here<\/a>.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Create a replication channel on MDS <\/h2>\n\n\n\n<p>Here is the easiest part.<br>Most of the work will be done using the OCI console \\o\/<\/p>\n\n\n\n<p>The feature we are looking for is named <em><strong>Channel<\/strong><\/em>.<br>You can find it by clicking on \u00ab\u00a0<em><span style=\"text-decoration: underline;\">MySQL<\/span><\/em>\u00a0\u00bb then \u00ab\u00a0<em><span style=\"text-decoration: underline;\">Channel<\/span><\/em>\u00a0\u00bb and finally push the \u00ab\u00a0<em><span style=\"text-decoration: underline;\">Create Channel<\/span><\/em>\u00a0\u00bb button<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MDS_create_channel.png\" alt=\"Create Inbound MySQL Replication in MySQL Database Service in OCI\"\/><\/figure>\n\n\n\n\n\n<p>The following is pretty straightforward. <br>Below the main information:<\/p>\n\n\n\n<p><em><span style=\"text-decoration: underline;\">Create in Compartment<\/span><\/em>: Choose the right Compartment<\/p>\n\n\n\n<p><em><span style=\"text-decoration: underline;\">Name (optional)<\/span><\/em>: ex. mysqlchannel_MDS2_FRA<\/p>\n\n\n\n<p>Source Connection &#8211; Configure Connection to the MySQL Source<br><em><span style=\"text-decoration: underline;\">Hostname<\/span><\/em>: 10.0.0.9<\/p>\n\n\n\n<p><em><span style=\"text-decoration: underline;\">Username<\/span><\/em>: rplAdmin  <br><em>(from the replication user created on the source)<\/em><\/p>\n\n\n\n<p><em><span style=\"text-decoration: underline;\">Password<\/span><\/em>: Repl1234c@  <br><em>(from the replication user created on the source)<\/em><\/p>\n\n\n\n<p><em><span style=\"text-decoration: underline;\">SSL Mode<\/span><\/em>: &#8211; set when create the replication user &#8211;<br>Required (REQUIRED)<br>Establish an encrypted connection.<\/p>\n\n\n\n<p>Target &#8211; Configure the DB System target<br><em><span style=\"text-decoration: underline;\">Select a DB System<\/span><\/em>: MDS2_FRA<\/p>\n\n\n\n\n\n<p>Finally, push the <em><span style=\"text-decoration: underline;\">Create Channel<\/span><\/em> button<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MDS_create_channel_2.png\" alt=\"Create Inbound MySQL Replication in MySQL Database Service in OCI\"\/><\/figure>\n\n\n\n<p>et voil\u00e0!<\/p>\n\n\n\n<p>Wait for the Channel icon become green (ACTIVE)\u2026 <\/p>\n\n\n\n\n\n<figure class=\"wp-block-image\"><img data-recalc-dims=\"1\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MEM_MySQL5.7-MDS_Replication_status.png\" alt=\"MySQL Replication status from MySQL Enterprise Monitor\"\/><figcaption>MySQL Replication status from MySQL Enterprise Monitor<\/figcaption><\/figure>\n\n\n\n\n\n\n\n<p>In the mean time, if you have the soul of a DBA, you can use some commands, on the MDS instance.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\"><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/show-replica-status.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"SHOW REPLICA STATUS\">SHOW REPLICA STATUS<\/a><\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL &gt; SHOW REPLICA STATUS\\G\n\n*************************** 1. row ***************************\n             Replica_IO_State: Waiting for master to send event\n                  Source_Host: 10.0.0.9\n                  Source_User: rplAdmin\n                  Source_Port: 3306\n                           ...\n           Replica_IO_Running: Yes\n          Replica_SQL_Running: Yes \n                           ...\n           Source_SSL_Allowed: Yes\n                           ...\n                Auto_Position: 1\n         Replicate_Rewrite_DB: \n                 Channel_Name: replication_channel\n           Source_TLS_Version: TLSv1.2,TLSv1.3\n                           ...<\/code><\/pre>\n\n\n\n\n\n<p> Using the replication channel name (default: <em>replication_channel<\/em>) you can also use this query:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n   SERVICE_STATE, \n   HOST, \n   USER, \n   PORT,\n   CHANNEL_NAME\nFROM performance_schema.replication_connection_configuration \n   INNER JOIN performance_schema.replication_applier_status \n      USING (CHANNEL_NAME) \nWHERE CHANNEL_NAME = 'replication_channel'\\G\n\n*************************** 1. row ***************************\nSERVICE_STATE: ON\n         HOST: 10.0.0.9\n         USER: rplAdmin\n         PORT: 3306\n CHANNEL_NAME: replication_channel<\/code><\/pre>\n\n\n\n\n\n<p>Last but not least, there is <a href=\"https:\/\/lefred.be\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"lefred's blog\">Lefred&rsquo;s<\/a> favorite replication query (<a href=\"https:\/\/frenchetc.org\/2016\/12\/17\/c-est-cadeau-french-expression\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Freebie\">c&rsquo;est cadeau<\/a>):<\/p>\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;<\/code><\/pre>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">P\u00e9roraison<\/h2>\n\n\n\n<p>MySQL replication has always been easy to setup.<br>This is still true with MySQL 8.0.<br>This is even more true with MySQL Database Service \ud83d\ude42<\/p>\n\n\n\n<p>Easy, flexible and powerful. This is exactly what your application need.<\/p>\n\n\n\n\n\n\n\n<h2 class=\"wp-block-heading\">References<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/www.oracle.com\/mysql\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Database Service\">MySQL Database Service<\/a><\/li><li><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/getting-started.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Getting Started with MySQL Database Service\">Getting Started with MySQL Database Service<\/a><ul><li><a href=\"https:\/\/www.mortensi.com\/2020\/12\/options-to-run-mysql-server-in-oci-and-use-mysql-analytics-service\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Options to Run MySQL Server in OCI (and use MySQL Analytics Service)\">Options to Run MySQL Server in OCI (and use MySQL Analytics Service)<\/a><\/li><li><a href=\"https:\/\/blogs.oracle.com\/mysql\/migrate-from-on-premise-mysql-to-mysql-database-service\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Migrate from on premise MySQL to MySQL Database Service\">Migrate from on premise MySQL to MySQL Database Service<\/a><\/li><\/ul><\/li><li><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/mysql-database\/doc\/replication.html#GUID-94149D15-BE97-483E-99F7-82F347AB9D0E\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Database Service - Inbound Replication\">MySQL Database Service &#8211; Inbound Replication<\/a><\/li><li><a href=\"https:\/\/dev.mysql.com\/doc\/mysql-shell\/8.0\/en\/mysql-shell-utilities.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Shell Utilities\">MySQL Shell Utilities<\/a><\/li><li><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/FreeTier\/freetier.htm#Oracle_Cloud_Infrastructure_Free_Tier\">Oracle Cloud <\/a><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/FreeTier\/freetier.htm#Oracle_Cloud_Infrastructure_Free_Tier\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Oracle Cloud Infrastructure Free Tier\">Infrastructure<\/a><a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/Content\/FreeTier\/freetier.htm#Oracle_Cloud_Infrastructure_Free_Tier\"> Free Tier<\/a><\/li><li><a href=\"https:\/\/www.oracle.com\/cloud\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Oracle Cloud Infrastructure (OCI)\">Oracle Cloud Infrastructure (OCI)<\/a><\/li><li><a href=\"https:\/\/docs.cloud.oracle.com\/en-us\/iaas\/Content\/Object\/Concepts\/objectstorageoverview.htm\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"Overview of Object Storage\">Overview of Object Storage<\/a><\/li><li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"MySQL Replication\">MySQL Replication<\/a><\/li><\/ul>\n\n\n\n\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>MySQL Replication is a very common topology, widely used in various architecture.<br \/>\nPeople use it, among others, for High Availability, Read Scalability or Geographic Redundancy.<\/p>\n<p>Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture.<br \/>\nLet\u2019s say you are running MySQL 5.7 then you can easily setup a 8.0 instance as a replica of your 5.7.<\/p>\n<p>Extending this idea it is also possible to replicate your MySQL 5.7 (or 8.0 obviously) to a MySQL Database Service instance, the true MySQL PaaS on Oracle Cloud Infrastructure.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[203],"tags":[306,1639,66],"class_list":["post-4188","post","type-post","status-publish","format-standard","hentry","category-mysql-en","tag-cloud","tag-mds","tag-replication"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-15y","jetpack-related-posts":[{"id":4462,"url":"https:\/\/dasini.net\/blog\/2021\/04\/13\/heatwave-a-mysql-cloud-feature-to-speed-up-your-queries\/","url_meta":{"origin":4188,"position":0},"title":"HeatWave &#8211; A MySQL cloud feature to speed up your queries","author":"Olivier DASINI","date":"13 avril 2021","format":false,"excerpt":"If you have (too) long running select queries it is probably because of lack of relevant indexes, problematic schema that lead to poor queries or inadequate hardware. That said, sometime even if you doing it right, the query execution time could be too long regarding of what the application or\u2026","rel":"","context":"Dans &quot;Cloud&quot;","block_context":{"text":"Cloud","link":"https:\/\/dasini.net\/blog\/category\/cloud\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/04\/MySQL-Database-Service-HeatWave-Replication.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":6650,"url":"https:\/\/dasini.net\/blog\/2024\/07\/16\/get-your-heatwave-mysql-data-on-premises-with-replication\/","url_meta":{"origin":4188,"position":1},"title":"Get Your HeatWave MySQL Data On-Premises with Replication","author":"Olivier DASINI","date":"16 juillet 2024","format":false,"excerpt":"This article guides you through setting up replication between a HeatWave MySQL instance (source) and an on-premise standalone MySQL instance (replica). It highlights key steps like creating a replication user, securing the connection, and using MySQL Shell utilities for data transfer.","rel":"","context":"Dans &quot;HeatWave&quot;","block_context":{"text":"HeatWave","link":"https:\/\/dasini.net\/blog\/category\/heatwave-en\/"},"img":{"alt_text":"","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=350%2C200&ssl=1","width":350,"height":200,"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=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2024\/07\/Get-Your-HeatWave-MySQL-Data-On-Premises-with-Replication-1.png?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":3086,"url":"https:\/\/dasini.net\/blog\/2019\/05\/21\/mysql-innodb-cluster-howto-2-validate-an-instance\/","url_meta":{"origin":4188,"position":2},"title":"MySQL InnoDB Cluster &#8211; HowTo #2 &#8211; Validate an instance","author":"Olivier DASINI","date":"21 mai 2019","format":false,"excerpt":"Q: Validate an instance for MySQL InnoDB Cluster usage? A: Use check_instance_configuration()","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4151,"url":"https:\/\/dasini.net\/blog\/2020\/12\/17\/mysql-shell-the-new-era\/","url_meta":{"origin":4188,"position":3},"title":"MySQL SHELL &#8211; The new era","author":"Anastasia Papachristopoulou","date":"17 d\u00e9cembre 2020","format":false,"excerpt":"In one of our previous articles - Setting up Replication with various methods for MySQL 8 - we reviewed how to create a replica with multiple tools. Now, it is time to perform the same action but with MySQL Shell.","rel":"","context":"Dans &quot;Backup \/ Restore&quot;","block_context":{"text":"Backup \/ Restore","link":"https:\/\/dasini.net\/blog\/category\/backup-restore\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3567,"url":"https:\/\/dasini.net\/blog\/2020\/05\/19\/mysql-security-dual-password-support\/","url_meta":{"origin":4188,"position":4},"title":"MySQL Security &#8211; Dual Password Support","author":"Olivier DASINI","date":"19 mai 2020","format":false,"excerpt":"Dual-password capability makes it possible to seamlessly perform credential changes without downtime.","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql-en\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2090,"url":"https:\/\/dasini.net\/blog\/2018\/01\/09\/setting-up-proxysql-1-4-with-mysql-5-7-group-replication\/","url_meta":{"origin":4188,"position":5},"title":"Setting up ProxySQL 1.4 with MySQL 5.7 Group Replication","author":"Olivier DASINI","date":"9 janvier 2018","format":false,"excerpt":"There are 3\u00a0pillars for a database architecture: Monitoring, Backup \/ Restore process, High Availability This blog post is about database High Availability; more precisely about one of the best combo of the moment : MySQL 5.7 Group Replication\u00a0: the only native HA solution for MySQL, it's a\u00a0Single\/Multi-master update everywhere replication\u2026","rel":"","context":"Dans &quot;Group Replication&quot;","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication-en\/"},"img":{"alt_text":"MySQL","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/mysql_300x161.jpg?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4188","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=4188"}],"version-history":[{"count":63,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4188\/revisions"}],"predecessor-version":[{"id":4342,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/4188\/revisions\/4342"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=4188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=4188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=4188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}