Oracle Cloud Infrastructure Documentation

Replication

This section describes Replication in MySQL Database Service.

The following replication type is described:

Inbound Replication

Enables asynchronous replication from a MySQL source to a DB System.

The following inbound replication scenarios are supported:

  • On-premise MySQL instance to DB System.
    Note

    This replication scenario requires an additional network configuration to bridge the local network and the VCN to which the DB System is attached. VPN Connect, FastConnect, or OpenVPN can be used with Inbound Replication.
    Note

    The minimum supported version of MySQL for a replication source is 5.7.9.
  • MySQL installed on Compute Instance (such as MySQL on the Oracle Cloud Infrastructure Marketplace) to DB System.

Inbound Replication requires a replication channel configured in MySQL Database Service, connecting a correctly configured MySQL Source to a DB System target.

For more information on MySQL Replication, see MySQL Reference Manual - Replication.

The following topics are described:

Limitations

Current limitations of MySQL Database Service Inbound Replication:
  • Only Row-based replication supported.
  • Only GTID-based replication is supported.
  • Multi-source replication is not supported.
  • Replication filters are not supported.
  • Changes to the mysql schema are not replicated and cause replication to stop.
  • Source must run with the same lower_case_table_names value, as the DB System. This value is 0. It is not currently possible to change it.
  • The inbound applier runs under the privileges of the DB System's admin user. See Default MySQL Privileges for more information.
Note

See Compatibility Between On-Premise MySQL and MySQL Database Service for more information.

Source Configuration

Inbound Replication requires your MySQL source to be properly configured. The following are required:
Creating the Replication User on the MySQL Source

A replication channel requires a correctly configured user, with the appropriate MySQL privileges, on the MySQL source server. This task creates a replication user named rpluser001 with the password Rpl001#!.

This task assumes some knowledge of MySQL Replication. For more information, see MySQL Reference Manual - Replication
To add a replication user to the source server, do the following:
  1. Open the SQL client of your choice (mysql client or MySQL Shell, for example) and connect to your source MySQL server.
  2. The following command creates a replication user named rpluser001 with the password Rpl001#!, and requires the server to permit only encrypted connections for all accounts named by the statement.
    mysql> CREATE USER rpluser001@'%' IDENTIFIED BY 'Rpl001#!' REQUIRE SSL;
    Note

    The password must be between 8 and 32 characters and contain at least one numeric character, one special character, one uppercase, and one lowercase character.
  3. To grant the REPLICATION SLAVE privilege to the new replication user, run the following command:
    mysql> GRANT REPLICATION SLAVE on *.* to rpluser001@'%';
The replication user is created and granted the required privileges.

Creating a Replication Channel

Ensure the following:
To create a replication channel, do the following:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Click Create Channel.
    The Create Channel dialog is displayed.
  3. On the Create Channel dialog, provide the following details:
    • Create in Compartment: If you want to create the channel in a compartment other than the current compartment, select the required Compartment from the list. If you do not select a different compartment, the current compartment is used.
      Note

      The channel must be created in the same compartment as the target DB System.
    • Name: (Optional) User-friendly display-name for the channel. This name is not related to the Channel Name defined in the Target definition. It is used solely to label the channel. If you do not define a name, one is generated using the format mysqlchannelYYYYMMDDHHMMSS.
    • Enabled Automatically Upon Creation: (selected by default) specifies whether the channel starts automatically on successful creation. If not selected, you must enable this channel manually.
    • Description: (Optional) User-friendly description of the channel and its purpose.
    • Source: Configure the MySQL Source the data is replicated from.
      • Hostname: the hostname of the MySQL source. This can be an IP address or fully qualified domain name.
      • Port: The port number the MySQL source listens on. Default is 3306.
      • Username: Replication user defined on the MySQL source. These are the credentials used by the Replication Channel to connect to the source.
      • Password: Password of the replication user. See Creating the Replication User on the MySQL Source for more information.
      • SSL Mode: Select the required SSL configuration. The selected parameter is used to populate the SSL-specific values of the connection to the MySQL Source. Possible values are:
        • Disabled (DISABLED): no encryption required between source and target.
        • Required (REQUIRED): (default) establishes an encrypted connection if the server supports encrypted connections. The connection attempt fails if an encrypted connection cannot be established.
        • Verify Certificate Authority (VERIFY_CA): Like Required, but additionally verify the CA certificate configured on the source against the Certificate Authority (CA) certificate (X509 PEM file). This option requires you to upload your Certificate Authority's X509 certificate.
        • Verify Identity (VERIFY_IDENTITY): Like Verify Certificate Authority, but additionally verify the source's hostname, defined in the source's SSL certificate, against the hostname defined in the Hostname field. This option requires you to upload your Certificate Authority's X509 certificate.
        • Certificate Authority's X509 certificate (PEM): Displayed only if Verify Certificate Authority or Verify Identity are selected. Upload the source's CA X509 certificate in PEM format. This file is used to verify the source's certificate.
    • Target: Configure the DB System target the data is replicated to.
      • Applier Username: (Optional) User the Replication Applier runs as. If you do not define a username, the DB System Administrator user's credentials are used.
      • Channel Name: (Optional) Replication channel name. This is the replication channel used by the target MySQL DB System for the communication with the MySQL Source. If you do not define a name, replication_channel is used.
      • DB System: Select the DB System to use as the replication target.

Populating the DB System with MySQL Shell

Import data from your on-premise database to your DB System before starting your Inbound Replication channel.

Ensure the following:
To populate your DB System using MySQL Shell, do the following:
  1. Export your source data to Object Storage following these instructions: Exporting Data to Object Storage with MySQL Shell
  2. Import the source data to your DB System following these instructions:: Importing to MySQL DB System from Object Storage Using MySQL Shell.
  3. The dump loading utility does not automatically apply the gtid_executed GTID set from the source MySQL instance on the target DB System. The GTID set is included in the dump metadata from MySQL Shell's instance dump utility, schema dump utility, or table dump utility, as the gtidExecuted field in the @.json dump file. Retrieve the value of gtidExecuted from the dump in Object Storage.
  4. Connect to the DB System using MySQL Shell and set the value of gtidPurged in the DB System, using the following command:
    SQL> call sys.set_gtid_purged("+gtidSet")

    where gtidSet is the value in the gtidExecuted field in the @.json dump file.

  5. Create and start your channel, following these instructions: Creating a Replication Channel

Managing Channels

This topic describes how to manage your MySQL DB System Replication Channels. The following topics are described:
Channel Details

Channel Details page enables you to view and manage your replication channels.

Table 15-1 Elements of the Channel Details page

Name Description
Channel Information Basic information on the channel and its configuration.
  • OCID: the unique identifier of the Channel.
  • Description: user-defined description of the channel
  • Enabled: displays the status of the channel. Yes for enabled, No for disabled.
  • State : displays the current lifecycle state of the channel. Select the State Details tooltip to see more detail on the current state of the channel.
  • Compartment: the compartment the channel was created in.
  • Created: time and date the channel was created.
  • Last Updated: time and date the channel was last updated.
Source and Target Contains the following sections:
  • Source:
    • Hostname: the IP address or hostname of the source MySQL server.
    • Port: the port number the source is communicating on.
    • Username: the username of the replication user.
    • SSL Mode: defines the security state of the connection to the source. Possible values are
      • Disabled (DISABLED): no encryption required between source and target.
      • Required (REQUIRED): (default) establishes an encrypted connection if the server supports encrypted connections. The connection attempt fails if an encrypted connection cannot be established.
      • Verify Certificate Authority (VERIFY_CA): like Required, but additionally verify the CA certificate configured on the source against the Certificate Authority (CA) certificate (X509 PEM file).
      • Verify Identity (VERIFY_IDENTITY): like Verify Certificate Authority, but additionally verify the source's hostname, defined in the source's SSL certificate, against the hostname defined in the Hostname field.
    • Certificate: displayed only if either Verify Certificate Authority or Verify Identity SSL Modes were selected. Displays the contents of the uploaded CA X509 certificate.
  • Target:
    • DB System: the display name of the target DB System and a link to it.
    • Applier Username: the username of the replication applier. For more information, see MySQL Reference Manual - REPLICATION_APPLIER
    • Channel Name: replication channel name. This is the replication channel used by the target MySQL DB System for the communication with the MySQL Source.
Checking the State of a Channel
To check the status of a Channel:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your Compartment.
    A list of Channels is displayed.
  3. In the list of Channels, find yours and check the icon in the State column. The color of the icon, and the associated text, indicate the status of the Channel.

    • CREATING: Yellow icon. The Channel is being created. Provisioning can take several minutes. The channel is not yet replicating data from the source to the target.

    • ACTIVE: Green icon. The Channel was successfully enabled.

    • UPDATING: Yellow icon. A Channel operation is ongoing.

    • NEEDS_ATTENTION: Yellow icon. This status is displayed for the following reasons:
      • The Channel is not replicating due to an error. The Details column on the Channels page lists why the channel needs attention.
      • The target DB System is INACTIVE. This can also occur if the channel was created or updated while the DB System was in INACTIVE state. Pending actions are applied if the DB System is started.

    • INACTIVE: Yellow icon. The Channel is not replicating because the user disabled it.

    • DELETING: Yellow icon. The Channel is being deleted.

    • DELETED: Grey icon. The Channel has been deleted and is no longer available.

Enabling or Disabling a Channel

  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your Compartment.
    A list of Channels is displayed.
  3. In the list of Channels, find the Channels you want to enable or disable. To disable the Channel, do one of the following:
    • On the Channels list page, select the Channel and choose Disable from the Actions menu at the top of the page or from the Action icon (three dots) on the same line as your Channel.
    • Click the name of the Channel to open the Channel Details page. Click Disable to disable the channel.
    The Channel's state changes to Updating and then to Inactive.
  4. To enable an Inactive Channel, do the following:
    • On the Channels list page, select the inactive Channel and choose Enable from the Actions menu at the top of the page or from the Action icon (three dots) on the same line as your Channel.
    • Click the name of the Channel to open the Channel Details page. Click Enable to enable the channel.
    The Channel's state changes to Updating and then to Active.
Resetting a Channel

A channel reset removes all data on the channel, except the channel's configuration, and is equivalent to RESET REPLICA ALL FOR CHANNEL. The target DB System drops its position in the source's binary log, clears the replication metadata repositories, deletes the relay log files, and starts a new relay log file.

The following is assumed for this task:
  • The channel is disabled. Reset is not possible on channels which are enabled.
To reset a channel, do the following:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment
    The Channels in page is displayed.
  3. Select the channel, or multiple channels, you want to reset and select Reset from the Actions menu or from the Action (three dots) menu.
    The Channel status changes to Updating, then back to Inactive when the reset completes.
  4. Click Enable to restart replication.
Resuming a Channel

Resuming a channel is used to start replication on a Channel which became inactive due to an error.

The following is assumed for this task:
  • The channel is in the NEEDS_ATTENTION state.
To resume a channel, do the following:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment
    The Channels page is displayed.
  3. Select the channel, or multiple channels, you want to resume and select Resume from the Actions menu or from the Action (three dots) menu.
    The Channel status changes to Updating, then to Active, if the error was corrected and the action is successful.
Editing a Channel

Enables editing channel's configuration, except compartment and target DB System. This enables you to update the channel if the location or credentials of the source change, for example. Or edit the channel's name.

To edit the channel:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment
    The Channels page is displayed.
  3. Open the channel you want to edit and click Edit.
    The Edit Channel dialog is displayed..
  4. You can edit all channel details except the compartment and target DB System. For more information, see Creating a Replication Channel.
  5. Click Save Changes.