Replication

Use the Console to create inbound or outbound replication channels. Inbound replication enables asynchronous replication between a MySQL source and a DB system. Outbound replication enables asynchronous replication of a DB system database to a MySQL replica.

Inbound Replication

Inbound replication enables asynchronous replication to your DB system.

Inbound replication supports the following scenarios:

  • On-premise MySQL instance to DB system: This replication scenario requires an additional network configuration to bridge the local network and the VCN to which the DB system is attached. You can use VPN Connect, FastConnect, or OpenVPN Access Server.
    Note

    The minimum supported version of MySQL for a replication source is 5.7.9.
  • MySQL installed on a compute instance to DB system. See MySQL on the OCI Marketplace.
  • DB system to DB system.

Inbound replication requires a replication channel configured in MySQL Database Service, connecting a correctly configured MySQL source to a DB system.

Related Topics

Limitations

There are certain limitations of inbound replication of the MySQL Database Service.

  • Inbound replication is not a managed functionality. You are responsible for configuring and maintaining the channel, and for ensuring that the traffic between source and replica is properly configured.
  • Only row-based and GTID-based replication is supported.
  • Multi-source replication and replication filters are not supported.
  • Changes to the mysql schema are not replicated and causes replication to stop.
  • Source and replica must run with the same lower_case_table_names value. See Initialization Variables.
  • The inbound applier runs under the privileges of the admin user of the DB system. See Default MySQL Privileges.
  • To configure an inbound replication channel on a highly available DB system, import your data to the DB system before enabling high availability, then enable your inbound replication channel.
  • The channel is suspended when the highly available DB system is upgraded. The channel is resumed when the upgrade process is complete.

Source Configuration

Inbound replication requires you to properly configure your MySQL source.

You require the following:

  • A properly configured replication user on the source server. See Creating a Replication User on a Source Server Using a Command-Line Client.
  • If you intend to encrypt the communication between source and replica, a properly configured source to use SSL. See Server-side Configuration for Encrypted Connections.
  • A properly configured egress seclist, VPN, or on-premise firewalls of your MySQL source to allow traffic from the entire subnet your DB system is present in and not just the IP address of your DB system. This is because the IP address of the replication channel can change in some scenarios such as during upgrades.
Creating a Replication User on a Source Server Using a Command-Line Client

Use a command-line client such as MySQL Shell or MySQL Client to create a correctly configured replication user with appropriate privileges on the source server.

This task requires the following:
  • MySQL Shell 8.0.27, or higher or a MySQL Client.
Do the following to add a replication user to the source server:
Note

All commands in this task are run using the SQL execution mode of MySQL Shell.
  1. Open MySQL Shell and connect to your source server.
  2. Run the following command to create a replication user, rpluser001, with the password Rpl001#!, and to permit only encrypted connections for all accounts named by the statement:
    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. Run the following command to grant the REPLICATION SLAVE privilege to the new replication user:
    GRANT REPLICATION SLAVE on *.* to rpluser001@'%';
The replication user is created and granted the required privileges.

Creating a Replication Channel

Use the Console to create a replication channel.

This task requires the following:
Do the following to create a replication channel:
  1. Open the navigation menu and select Databases. Under MySQL do one of the following:
    • Click Channels and click Create Channel.
    • Click DB Systems and from the list of DB systems, click the name of your DB system. Click More Actions, and select Create Channel.
    • Click the Actions menu present on the same line as your DB system and select Create Channel.
  2. On the Create channel panel, 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

      Create the channel in the same compartment as the target DB system.
    • Name: (Optional) Specify a user-friendly display-name for the channel. This name is not related to the Channel Name defined in the Target DB System definition. It is used solely to label the channel. If you do not specify a name, one is generated for you in the format, mysqlchannelYYYYMMDDHHMMSS.
    • Enabled Automatically Upon Creation: Specify whether the channel starts automatically on successful creation. It is enabled by default. If you disable this option, enable the channel manually.
    • Description: (Optional) Specify a user-friendly description of the channel and its purpose.
    • Source Connection: Configure the MySQL source from where you want to replicate the data:
      • Hostname: Specify the hostname of the MySQL source. You can either specify an IP address or a fully qualified domain name.
      • Port: (Optional) Specify the port number the MySQL source listens on. The default value is 3306.
      • Username: Specify the replication username defined on the MySQL source. The replication channel uses these credentials to connect to the source.
      • Password: Specify the password of the replication user. See Creating a Replication User on a Source Server Using a Command-Line Client.
      • SSL Mode: Select the required SSL mode. The selected mode is used to populate the SSL-specific values of the connection to the MySQL Source. Select either of the following SSL modes:
        • Disabled (DISABLED): Establishes an unencrypted connection between the source and target.
        • Required (REQUIRED): (Default) If the server supports encrypted connection, establishes an encrypted connection. The connection attempt fails if an encrypted connection cannot be established.
        • Verify Certificate Authority (VERIFY_CA): Like the Required mode, establishes an encrypted connection if the server supports encrypted connections and additionally verifies the Certificate Authority certificate configured on the source against the Certificate Authority's X509 certificate (PEM). You have to upload your Certificate Authority's X509 certificate.
        • Verify Identity (VERIFY_IDENTITY): Like the Verify Certificate Authority mode, establishes an encrypted connection, verifies the Certificate Authority's certificate, and additionally verifies the source hostname, which you define in the source SSL certificate, against the hostname that you define in the Hostname field. You have to upload your Certificate Authority's X509 certificate.
        • Certificate Authority's X509 certificate (PEM): Enables you to upload the source Certificate Authority's X509 certificate in PEM format. It is displayed only when you select the Verify Certificate Authority or Verify Identity mode. The certificate is used to verify the Certificate Authority's certificate on the source.
    • Target DB System: Configure the DB system to which you want to replicate the data.
      • Applier Username: (Optional) Specify the username of the replication applier on the target DB system. If you do not specify a username, the credentials of the administrator of the DB system is used.
      • Channel Name: (Optional) Specify the replication channel name. The target DB System uses the replication channel to communicate with the MySQL Source. If you do not specify a name, replication_channel is used.
      • Select a DB System: Select the DB system to use as the replication target.
        Note

        If you create the channel from the DB System Details page, or from the Actions menu, the target DB system is automatically selected and you cannot change it. You can view the target DB system at the top of the page.

Populating the DB System Using MySQL Shell

Use MySQL Shell to import data from your source to your replica DB system before starting inbound replication.

This task requires the following:
Do the following to populate your DB system using MySQL Shell:
  1. Export your source data to Object Storage. See Exporting Data Using MySQL Shell.
  2. Import the source data to your replica DB system. See Importing Data Using Object Storage Bucket and MySQL Shell.
  3. Retrieve the value of gtidExecuted from the dump in Object Storage.
    Note

    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 instance dump utility, schema dump utility, or table dump utility, as the gtidExecuted field in the @.json dump file.
  4. Connect to the replica DB system and set the value of gtidPurged using the following command:
    CALL sys.SET_GTID_PURGED("+<gtidSet>")

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

    Note

    This process is for new imports only. If you are attempting to recover from a replication lag, see Resolving Replica and Source Synchronization Issues.
  5. Create and start the channel. See Creating a Replication Channel.
Setting gtid_purged

Set replication coordinates after a clean import.

To begin replication from the source, without re-executing every transaction from the source, apply the gtid_executed set from the source instance to the DB System's MySQL instance.
Note

These methods apply only to new, clean setups. If you are attempting to recover from replication error, apply a gtidSet delta. See Resolving Replica and Source Synchronization Issues.
You can do this in one of the following ways:
  • MySQL Shell's updateGtidSet: append: When importing your data dump from Object Storage to the DB System, using MySQL Shell's loadDump utility, specify updateGtidSet: append in the loadDump parameters. This applies the gtid_executed GTID set from the source, as recorded in the dump metadata, to the gtid_purged GTID set on the replica. For more information, see MySQL Shell Dump Loading Utility.
  • call sys.set_gtid_purged("+gtidSet"): A stored procedure in the DB System's MySQL instance, which enables you to apply the gtid_executed GTID set from the source, as recorded in the dump metadata, to the gtid_purged GTID set on the replica DB System, after importing your data. Retrieve the gtidSet value from the gtidExecuted field in the @.json dump file. For more information on this command, see Populating the DB System Using MySQL Shell.

Managing Channels

Checking the Channel State

Use the Console to check the state of a channel.

  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment from the List Scope.
  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. See Channel States.
Channel States

Check the icon in the State column to view the state of your channel.

Table 16-1 Channel States

Icon Channel State Description
Grey Deleted The channel has been deleted and is no longer available.
Yellow Creating The channel is being created. Provisioning can take several minutes. The channel is not yet replicating data from the source to the target.
Deleting The channel is being deleted.
Inactive The channel is not replicating because the user has disabled it.
Needs Attention This status is displayed for the following reasons:
  • The channel is not replicating due to an error. The Details column on the Channels page explains why the channel needs attention.
  • The target DB system is INACTIVE, that is, the channel is created or updated while the DB system is in INACTIVE state. Pending actions are applied if the DB system is started.
Updating A channel operation is ongoing on.
Green Active The channel is running and replicating successfully.
Enabling or Disabling a Channel

Use the Console to enable or disable a replication channel.

  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment from the List Scope.
  3. In the list of channels, find the channel you want to enable or disable.
  4. To disable an active channel, do one of the following:
    • Select the channel and choose Disable from the Actions menu at the top of the page.
    • Click the Action menu on the same line as your channel and click Disable.
    • Click the name of the channel to open the Channel Details page and click Disable.
    The state of the channel changes to Updating and then to Inactive.
  5. To enable an inactive channel, do the following:
    • Select the channel and choose Enable from the Actions menu at the top of the page.
    • Click the Action menu on the same line as your channel and click Enable.
    • Click the name of the channel to open the Channel Details page and click Enable.
    The state of the channel changes to Updating and then to Active.
Resetting a Channel

Use the Console to reset a replication channel.

This task requires the following:
  • An inactive channel. You cannot reset an active channel.
Do the following to reset a channel:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment from the List Scope.
  3. In the list of channels, find the channel you want to enable or disable, and do one of the following:
    • Select the channel and choose Reset from the Actions menu at the top of the page.
    • Click the Action menu on the same line as your channel and click Reset.
    • Click the name of the channel to open the Channel Details page and click Reset.
Resetting the channel removes all data on the channel, except the channel configuration, and is equivalent to RESET REPLICA ALL FOR CHANNEL. The target DB system drops its position in the source binary log, clears the replication metadata repositories, deletes the relay log files, and starts a new relay log file. You can now enable the replication channel.
Resuming a Channel

Use the Console to resume a channel that is in the Needs Attention state.

This task requires the following:
  • A channel in the Needs Attention state.
Do the following to resume a channel:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment from the List Scope.
  3. In the list of channels, find the channel you want to resume, and do one of the following:
    • Select the channel and choose Resume from the Actions menu at the top of the page.
    • Click the Action menu on the same line as your channel and click Resume.
    • Click the name of the channel to open the Channel Details page and click Resume.
The state of the channel changes to Updating, and if the error is resolved, changes to Active.
Editing a Channel

Use the Console to edit the channel configuration such as channel name, description and source connection details.

Do the following to edit a channel:
  1. Open the navigation menu and select Databases. Under MySQL, click Channels.
  2. Choose your compartment from the List Scope.
  3. In the list of channels, find the channel you want to resume, and do one of the following:
    • Click the Action menu on the same line as your channel and click Edit.
    • Click the name of the channel to open the Channel Details page and click Edit.
  4. In the Edit Channel panel, edit the channel details.
    Note

    You cannot change the compartment and target DB system.
  5. Click Save Changes.
Channel Details

View and manage your replication channels.

Table 16-2 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.

Outbound Replication

Outbound replication enables asynchronous replication of your DB system database.

Outbound replication supports the following scenarios:

Note

The replica must be the same or a higher version of MySQL as the source.

You can use asynchronous replicas for the following:

  • Redistribution of data
  • Disaster recovery
  • Scale-out
  • Custom point-in-time-recovery
Note

Outbound replication is not a managed functionality. You are responsible for configuring and maintaining the channel, and for ensuring that the traffic between source and replica is properly configured.

Prerequisites

To use outbound replication of MySQL Database Service, you require certain configurations.

  • The replica must apply the source transactions before the source binary log is purged. The default value of binlog_expire_logs_seconds is 3600 seconds (1 hour). If you require a longer value, create a new configuration with the required expiration value and create a new source DB system using the new configuration.
  • Your VCN and local network must be configured to permit replication traffic between the source DB system and external replica.
  • The external replicas must run with the following parameters:
  • The replica must contain the same timezone definitions as the DB system. The default timezone of MySQL Database Service is UTC. See Populating the Timezone Tables.
  • Outbound replication from a DB system to an external replica, or another DB system, requires you to create a dump of the source DB system using MySQL Shell dumpInstance utility and import that dump on the replica, including users, before starting replication. See Exporting and Importing Overview.
  • Users on the source must be present on the replica. Partial revokes requires that the roles and users creating grants are present on the replica. The users on the source are exported as part of the dump and must be imported to the replica.
  • On external replicas, the user must have the necessary privileges to load users and data.
  • The server_id value must be defined on your external replica and be unique in your replication topology. The server_uuid value must also be unique.

Exporting Source Instance to Object Storage Using MySQL Shell

Use MySQL Shell to export the DB system source instance to Object Storage.

This task requires the following:
  • MySQL Shell 8.0.27 or higher version.
    Note

    It is recommended to use the latest version of MySQL Shell. Any earlier versions of MySQL Shell cannot import the exports created by MySQL Shell 8.0.27 or higher version.
  • Access to Object Storage and an existing bucket.
  • A valid configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it or create a configuration file manually. See SDK and CLI Configuration File.
  • At least one database schema in your source instance. MySQL Shell's dumpInstance command fails if there are no database schemas present in the instance.
Do the following to export your source DB system to Object Storage:
Note

All commands in this task are run using the JS execution mode.
  1. Connect to your DB system using MySQL Shell. See Connecting to a DB System Using SSH and MySQL Client and Exporting and Importing Overview.
  2. In the JS execution mode, run the following command:
    util.dumpInstance("prefix", {osBucketName: "bucket", ocimds: true, threads: n})
    • util.dumpInstance: Export all data in the MySQL instance.
    • prefix: (Optional) Add a prefix to the files uploaded to the bucket. See Exporting and Importing Overview.
    • ocimds: (Optional) Check your data for compatibility with MySQL Database Service. Specify this option only if you intend to import the dump into another DB system. If you are exporting dump to an external replica, do not specify this option.
    • osBucketName: Specify the case-sensitive name of the Object Storage bucket to export to.
    • threads: Specify the number of processing threads to use for this task. The default value is 4.
Your instance is exported to the Object Storage bucket.

Importing from Object Storage to Replica Using MySQL Shell

Use MySQL Shell to import the source data and users from an Object Storage bucket to a replica.

This task requires the following:
  • You have read and implemented the prerequisites. See Prerequisites.
  • MySQL Shell 8.0.27 or higher version.
    Note

    It is recommended to use the latest version of MySQL Shell. Any earlier versions of MySQL Shell cannot import the exports created by MySQL Shell 8.0.27 or higher version.
  • Access to Object Storage and an existing bucket that contains the exported files.
  • A valid configuration file. If you have installed and configured the CLI in the default location, you have a valid configuration file. If you have not installed and configured the CLI, you must either install it or create a configuration file manually. See SDK and CLI Configuration File.
  • Your replica has enough storage space for the import.
  • A replica running with local-infile=ON. You require the parameter for importing the source dump. You can disable the parameter after the import. If you disable the parameter, restart the replica.See local_infile.
    Note

    If you are importing to a DB system replica, this parameter is enabled by default. To disable it, create a copy of your configuration, set local-infile=OFF, and replace your existing configuration. See Editing a DB System.
Do the following to import the source data and users to the replica:
Note

All commands in this task are run using the JS execution mode.
  1. In MySQL Shell, connect to your replica, and run the following command:
    util.loadDump("prefix", {osBucketName: "bucket", threads: n, 
        loadUsers: true, updateGtidSet: "append"})
    • util.loadDump: Import data from the specified Object Storage bucket to MySQL DB system.
    • prefix: (Optional) If the data is uploaded to Object Storage with a prefix, specify that prefix in the import command. See Exporting and Importing Overview.
    • osBucketName: Specify the name of the Object Storage bucket to import from.
    • threads: Specify the number of processing threads to use for this task. The default value is 4. It is recommended to set this parameter to the number of CPU cores available on the local database server or twice the number of OCPUs used by the target MySQL DB system.
    • loadUsers: true: Load the users defined in the source instance.
    • updateGtidSet: "append": Apply the gtid_executed GTID set from the source, as recorded in the dump metadata, to the gtid_purged GTID set on the replica. See MySQL Shell Dump Loading Utility.
The source data is imported to the replica.

Creating a Replication User on a Source Server Using a Command-Line Client

Use a command-line client such as MySQL Shell or MySQL Client to create a correctly configured replication user with appropriate privileges on the source server.

This task requires the following:
  • MySQL Shell 8.0.27, or higher or a MySQL Client.
Do the following to add a replication user to the source server:
Note

All commands in this task are run using the SQL execution mode of MySQL Shell.
  1. Open MySQL Shell and connect to your source server.
  2. Run the following command to create a replication user, rpluser001, with the password Rpl001#!, and to permit only encrypted connections for all accounts named by the statement:
    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. Run the following command to grant the REPLICATION SLAVE privilege to the new replication user:
    GRANT REPLICATION SLAVE on *.* to rpluser001@'%';
The replication user is created and granted the required privileges.

Configuring Outbound Replication from DB System to External Replica Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to configure outbound replication from a DB system to an external replica.

This task requires the following:
Do the following to configure outbound replication from a source DB system to a replica:
Note

All commands in this task are run using the SQL execution mode of MySQL Shell.
  1. Open MySQL Shell and connect to your replica.
  2. On the replica, specify the replication source by running the following command:
    CHANGE REPLICATION SOURCE TO SOURCE_HOST='<DBSystemIPAddress>', 
    SOURCE_PORT=3306, SOURCE_USER='<SourceUser>', SOURCE_PASSWORD='<SourcePassword>', SOURCE_SSL=1, 
    SOURCE_AUTO_POSITION=1 FOR CHANNEL '<ChannelName>';
    • SOURCE_HOST: Specify the IP address of the source DB system.
    • SOURCE_PORT: Specify the port the source is configured to use. The default port is 3306.
    • SOURCE_USER: Specify the username of the replication user created on the source DB system.
    • SOURCE_PASSWORD: Specify the password of the source replication user.
    • SOURCE_SSL=1: Specify an SSL-encrypted connection.
    • SOURCE_AUTO_POSITION=1: Specify that the replica connects to the source using the autopositioning feature of GTID-based replication, rather than a binary log file based position.
    • CHANNEL: Specify a replication channel. This either uses an existing replication channel with the supplied channel name or, if the channel does not exist, creates one using the supplied name. This channel is defined on the replica server, it does not require a channel defined in MySQL Database Service Channels.
  3. To start replication, run the following command on the replica:
    START REPLICA FOR CHANNEL '<ChannelName>';

    <ChannelName>: Specify the name of the channel.

  4. To view the replication status, run any of the following command:
    • On the replica:
      SHOW REPLICA STATUS \G;
      SHOW PROCESSLIST;
    • On the source DB system:
      SHOW REPLICAS;
      SHOW PROCESSLIST;

Configuring Outbound Replication from DB System to DB System

Use the Console to configure outbound replication from a source DB system to a replica DB system.

This task requires the following:
Note

The limitations of this type of outbound replication are identical to those of inbound replication. See Limitations.
Do the following to setup outbound replication between DBSystem1 (source) and DBSystem2 (replica):
  1. Create a replication channel on DBSystem2 specifying DBSystem1 as the source. See Creating a Replication Channel.