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.
- MySQL Shell 8.0.27, or higher or a MySQL Client.
All commands in this task are run using the
SQL
execution mode of MySQL Shell.
Creating a Replication Channel
Use the Console to create a replication channel.
- If you are using an external MySQL source, a correctly configured network. You can use VPN Connect, FastConnect, or OpenVPN Access Server.
- A replication user on the MySQL source server. See Creating a Replication User on a Source Server Using a Command-Line Client.
- The MySQL source server
GTID_MODE
variable set toON
. SeeGTID_MODE
in MySQL Reference Manual - GTID_MODE. - An active DB system to replicate to.
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.
- The source
GTID_MODE
variable set toON
. SeeGTID_MODE
in MySQL Reference Manual - GTID_MODE.
Setting gtid_purged
Set replication coordinates after a clean import.
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.
- MySQL Shell's
updateGtidSet: append
: When importing your data dump from Object Storage to the DB System, using MySQL Shell'sloadDump
utility, specifyupdateGtidSet: append
in theloadDump
parameters. This applies thegtid_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 thegtid_executed
GTID set from the source, as recorded in the dump metadata, to thegtid_purged
GTID set on the replica DB System, after importing your data. Retrieve the gtidSet value from thegtidExecuted
field in the@.json
dump file. For more information on this command, see Populating the DB System Using MySQL Shell.
Managing Channels
Use the Console to manage your MySQL DB system replication channels.
Checking the Channel State
Use the Console to check the state of a channel.
- Open the navigation menu and select Databases. Under MySQL, click Channels.
- Choose your compartment from the List Scope.
- 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:
|
|
Updating | A channel operation is ongoing on. | |
Green | Active | The channel is running and replicating successfully. |
Resetting a Channel
Use the Console to reset a replication channel.
- An inactive channel. You cannot reset an active channel.
- Open the navigation menu and select Databases. Under MySQL, click Channels.
- Choose your compartment from the List Scope.
- 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.
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.
Related Topics
Resuming a Channel
Use the Console to resume a channel that is in the Needs Attention state.
- A channel in the Needs Attention state.
- Open the navigation menu and select Databases. Under MySQL, click Channels.
- Choose your compartment from the List Scope.
- 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.
Editing a Channel
Use the Console to edit the channel configuration such as channel name, description and source connection details.
Related Topics
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.
|
Source and Target | Contains the following sections:
|
Outbound Replication
Outbound replication enables asynchronous replication of your DB system database.
Outbound replication supports the following scenarios:
- DB system to external replica: This replication scenario requires an additional network configuration to bridge the external network and the VCN to which the DB system is attached. You can use VPN Connect, FastConnect, or OpenVPN Access Server.
- DB system to MySQL installed on a compute Instance. See MySQL on the Oracle Cloud Infrastructure Marketplace.
- DB system to DB system.
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
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
- Exporting Source Instance to Object Storage Using MySQL Shell
- Importing from Object Storage to Replica Using MySQL Shell
- Creating a Replication User on a Source Server Using a Command-Line Client
- Configuring Outbound Replication from DB System to External Replica Using a Command-Line Client
- Configuring Outbound Replication from DB System to DB System
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:
gtid-mode=ON
. See gtid_mode.enforce-gtid-consistency
. See enforce_gtid_consistency.partial-revokes=ON
. See partial_revokes.lower_case_table_names
. The replica must run with the same value as the source DB System. See Initialization Variables.
- 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.
- 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.
All commands in this task are run using the
JS
execution
mode.
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.
- 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, setlocal-infile=OFF
, and replace your existing configuration. See Editing a DB System.
All commands in this task are run using the
JS
execution mode.
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.
- MySQL Shell 8.0.27, or higher or a MySQL Client.
All commands in this task are run using the
SQL
execution mode of MySQL Shell.
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.
- You have read and implemented the prerequisites. See Prerequisites.
- A replica with an imported copy of the source DB system users and data. See Importing from Object Storage to Replica Using MySQL Shell.
- A source DB system with a replication user. See Creating a Replication User on a Source Server Using a Command-Line Client.
- A command-line client such as MySQL Client or MySQL Shell.
All commands in this task are run using the
SQL
execution mode of MySQL Shell.
Related Topics
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.
- You have read and implemented the prerequisites. See Prerequisites.
- A source DB system.
- A replication user defined on the source. See Creating a Replication User on a Source Server Using a Command-Line Client.
- A replica DB system.
- A replica that contains the source instance data and users. See Exporting Source Instance to Object Storage Using MySQL Shell and Importing from Object Storage to Replica Using MySQL Shell.
The limitations of this type of outbound replication are identical to those of inbound replication. See Limitations.
- Create a replication channel on DBSystem2 specifying DBSystem1 as the source. See Creating a Replication Channel.