7.4 Channels (troubleshooting)

This tutorial is a continuation of tutorials Deploy MySQL DB System with Terraform and access the system and Endpoints

I strongly advise you to read them before moving on to this one.

In our previous tutorials we deployed one MySQL DB System. To access it, we provisioned one instance with MySQL client, respectively MySQL shell, making it possible to analyze the endpoints options.

What can we do from here on? A logical approach, since we have two instances - one instance (on which we deployed MySQL Client & mysql-shell), and one instance on which is deployed MySQL DB System, would be to perform a replication.

Enter "Channels"

Briefly put, a replication channel is the path of transactions transmitted from a master to a slave.

If you are new to channels, you can think of a "replication channel" as a "networking socket" through which the server listens for a client to make a connection, for transfering the data.

How to perform replication with a channel

The MySQL OCI documentation provides the following information:

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

--documentation source: Replication

What does that mean?

For a MySQL replication, we usually need, at least, one master and one slave.

In this scenario, the master will be a MySQL source we will provision (along with a replication user), while the slave will be the MySQL DB System.

Since these tutorials are supposed to be implemented using as much automation as possible, the provisioning of MySQL & replication user should be done just the same.

In this example, the provisioning of MySQL and replica user will be done with the help of an ansible playbook.

The MySQL source will listen on port 3307.

For example, in below excerpt from playbook "mysql.yml", creating the replication user, changing host permissions and applying the changes:

    - name: create replication user
      mysql_user:
        login_user: root
        login_password: ""
        login_port: 3307
        login_unix_socket: '/var/run/mysqld/mysqld.sock'
        name: replicauser
        password: "{{ mypasswd }}"
        priv: '*.*:REPLICATION SLAVE'
        state: present
      become: yes

    - name: Set access host %
      command: /usr/bin/mysql -uroot -e "UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='replicauser';"
      become: yes

    - name: Flush privileges
      command: /usr/bin/mysql -uroot -e "flush privileges;"
      become: yes

For more details on integrating Terraform with Ansible, check the tutorial

VCN, Terraform and Ansible (Nginx example)

Adapting the Terraform code

From the previous tutorial, Deploy MySQL DB System with Terraform and access the system, we have 13 terraform files that would deploy an instance, a MySQL DB along with the compartment &networking setup:

root@deploymentmachine:/home/channel_replica# tree .
.
├── compartment.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── provider.tf
├── remote.tf
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 13 files

As you might have guessed in this tutorial we need to add the code for creating and configuring the channel.

First thing first, let's start with file remote.tf, where there will be added the firewall rule on the instance for port 3307 (on which MySQL source will listen), and a provisioner "local-exec" for running the ansible-playbook:

root@deploymentmachine:/home/channel_replica# more remote.tf
resource "null_resource" "MySqlOciRemote" {

  depends_on = [oci_core_instance.MySqlOciInstance]

  provisioner "remote-exec" {
    inline = ["echo I am in ",
     
              [ ....snip....]
              
             "sudo firewall-cmd --add-port=3307/tcp --permanent",
             
              [ ....snip....]
             
              "sudo snap install mysql-shell"]

 [ ....snip....]
}
   provisioner "local-exec" {

     command = "ansible-playbook -i '${data.oci_core_vnic.MySqlOciVNICprimary.public_ip_address},' --private-key ${var.private_key_path} ./mysql.yml  -u ubuntu"

   }


}

Since I have mentioned firewall rules on the instance, we need to make the change on the Ingress security lists, for variable "portz" from file variables.tf (by adding 3307 port):

# for security list

variable "portz" {
 default = [22,3306,3307,33060]
}

Channel resources (for this example)

To clarify a couple of terms:

• source is the MySQL which is installed and configured on the instance

• target is the MySQL DB System

Resource oci mysql channel - creates a channel

The required fields of oci_mysql_channel: source, target, compartment and is_enabled

source

hostname - fully qualified domain name of the MySQL source

password - The password for the replication user. The password must be between 8 and 32 characters long, and must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character.

port - the port on which MySQL source listens to

source type - The specific source identifier

ssl_mode - ssl status - enabled or disabled

username - the replication user

--------------------------------------------------------------

Changing the variables.tf, by adding the following:

root@deploymentmachine:/home/channel_replica# more variables.tf  | grep -A2 -E 'instance_passwd|instance_user|source_port|ssl_status|fqdn'
variable "instance_passwd" {
  default = "Str0nkPa$$wd"
}
--
variable "instance_user" {
  default = "replicauser"
}
variable "source_port" {
  default = "3307"
}
--
variable "ssl_status" {
  default = "DISABLED"
}
--
# for fqdn
# gather data as:
# hostname + mysql dns label + mysql oci vcn + oraclevcn.com
--
variable "fqdn" {
  default = "mysqlshellinstance.mysqloci.mysqlocivcn.oraclevcn.com"
}

target

db_system_id - OCID of the target MySQL DB System.

target_type - The specific target identifier

--------------------------------------------------------------

compartment - the Channel must be deployed in the same compartment as the MySQL DB System (the target)

is_enabled - set to "true", the Channel will be asynchronously started as a result of the create Channel operation.

--------------------------------------------------------------

The new terraform file, for setting up the Channel, create_channel.tf:

root@deploymentmachine:/home/channel_replica#  more create_channel.tf


resource "oci_mysql_channel" "MySqlOciChannel" {

    source {
        hostname = var.fqdn
        password = var.instance_passwd
        source_type = "MYSQL"
        ssl_mode = var.ssl_status
        username = var.instance_user

        port = var.source_port
    }

    target {

        db_system_id = oci_mysql_mysql_db_system.mysql_create.id
        target_type = "DBSYSTEM"

    }


    compartment_id = oci_identity_compartment.MySqlOciCompartment.id

    is_enabled = "true"
}

Discussion about source_type and target_type

The Terraform documentation for MySQL OCI DB could provide a bit more information.

For the "target_type" it is mentioned only:

target_type - (Required) (Updatable) The specific target identifier.

source_type - The specific source identifier.

No other options/specifications

However, as a work around, pass no value to either target_type or source type...

target_type = " "

... and when you perform a terraform plan, the errors should provide following output:

root@deploymentmachine:/home/channel_replica# terraform plan

Error: expected source.0.source_type to be one of [MYSQL], got

  on createchannel.tf line 1, in resource "oci_mysql_channel" "MySqlOciChannel":
   1: resource "oci_mysql_channel" "MySqlOciChannel" {



Error: expected target.0.target_type to be one of [DBSYSTEM], got

  on createchannel.tf line 1, in resource "oci_mysql_channel" "MySqlOciChannel":
   1: resource "oci_mysql_channel" "MySqlOciChannel" {

Data sources (for this example)

Data source oci mysql channel

Setting up the data.tf, by adding the following:

root@deploymentmachine:/home/channel_replica# more data.tf  | grep -A3 "channel"
## for MDS channel

 data "oci_mysql_channel" "MySqlOciChannel" {
    channel_id = oci_mysql_channel.MySqlOciChannel.id
}

Output

And just in case, let's add an output file, output.tf, to extract the Public IP of the instance:

root@deploymentmachine:/home/channel_replica#  more output.tf

output "MySqlSourceIP" {
  value = data.oci_core_vnic.MySqlOciVNICprimary.public_ip_address
}

My folder now has 15 terraform files that will implement the creation of an instance, the deployment of a MDS, and the configuration of Channel (along with compartment and networking configurations):

root@deploymentmachine:/home/channel_replica#  tree .
.
├── compartment.tf
├── create_channel.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── output.tf
├── provider.tf
├── remote.tf
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 15 files

Obviously, we are not done yet... as mentioned in the beginning we need to automatically configure the MySQL source and the replication user.

Integrate Terraform with Ansible

A step-by-step implementation for Terraform-Ansible integration can be found at tutorial VCN, Terraform and Ansible (Nginx example), and at (a bit advanced) MySQL Replication (Compute Instances)

This tutorial will focus on Ansible files for MySQL service only, and not explaining the entire process of Terraform-Ansible integration

In the same folder, where the Terraform files exist, you will have to add:

• ansible.cfg file (for setting up the default ansible configuration):

root@deploymentmachine:/home/channel_replica# more ansible.cfg
[defaults]

host_key_checking = False
interpreter_python = /usr/bin/python3

• the mysql.yml playbook, which will make possible the installing and configuring of MySQL service on the instance, along with creating the replication user

• the my.cnf configuration file for MySQL service (the source)

root@deploymentmachine:/home/channel_replica# more  my.cnf

[mysqld]

server-id = 2
port = 3307
gtid-mode=ON
enforce-gtid-consistency

binlog_format = row
lower_case_table_names = 0

Discussion for my.cnf configuration file

Configurations implemented by following the recommendations/limitations of the Official Documentation:

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.

-- documentation source: Replication

Now, my working directory contains 18 files in total:

root@deploymentmachine:/home/channel_replica#  tree .
.
├── ansible.cfg
├── compartment.tf
├── create_channel.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── my.cnf
├── mysql.yml
├── output.tf
├── provider.tf
├── remote.tf
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 18 files

Code example 1

The code example is at Github repository: https://github.com/MuchTest/MySqlDbSystemExample2

Comments are added for guidance - you will need to provide certain info if you want the environment to work (tenancy ocid, user ocid ... etc). Find a "how to" guide at this tutorial from Chapter 1.

Checking the deployed environment in Oracle Cloud UI

Deploy the environment by using the main terraform commands:

• terraform init

• terraform plan

• terraform apply

root@deploymentmachine:/home/channel_replica# terraform apply
[....snip .... ]

data.oci_mysql_channel.MySqlOciChannel: Reading...
data.oci_mysql_channel.MySqlOciChannel: Read complete after 1s [id=ocid1.mysqlchannel.oc1.eu-frankfurt-1.aa]

Apply complete! Resources: 11 added, 0 changed, 0 destroyed.

Outputs:

MySqlSourceIP = "130.61.49.205"

Let's check the status of our MySQL source (MySQL service installed on instance), that runs on port 3307:

root@deploymentmachine:/home/channel_replica# instanceip=`terraform output MySqlSourceIP | sed 's/"//g'`
root@deploymentmachine:/home/channel_replica#
root@deploymentmachine:/home/channel_replica# ssh ubuntu@$instanceip
Welcome to Ubuntu 20.04.1 LTS (GNU/Linux 5.4.0-1035-oracle x86_64)
[.... snip ....]
ubuntu@mysqlshellinstance:~$ sudo -i
root@mysqlshellinstance:~# lsof -i :3307
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  11013 mysql   34u  IPv6  57622      0t0  TCP *:3307 (LISTEN)
mysqld  11013 mysql   37u  IPv6  57902      0t0  TCP mysqlshellinstance.mysqloci.mysqlocivcn.oraclevcn.com:3307->10.0.1.3:55860 (ESTABLISHED)
root@mysqlshellinstance:~#

• Check the MySQL DB System from OCI UI:

Notice the Inbound replication Channel state

• Check the Channel

With message error:

The Channel is not applying transactions due to errors (MY-1227, Fri Mar 19 12:11:32 GMT 2021). A Resume action may be needed to continue.

Check from UI, just in case, if the configuration has been setup properly for target and source:

What we have so far: a created Channel that presents MY-1227 error.

Troubleshooting

As a reminder, we have two database systems that communicate through the Channel:

• the master - the MySQL source

• the slave - the MySQL DB System targe

1. Investigations on the Target (MySQL DB System)

Let's check the slave's status on MySQL DB System

we have already discussed how to connect to MySQL DB System at tutorial Endpoints

root@mysqlshellinstance:~# mysql -uusermds -h10.0.1.4 -pABCabc123$%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.23-u2-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Check what errors appear in the status of the slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysqlshellinstance.mysqloci.mysqlocivcn.oraclevcn.com
                  Master_User: replicauser
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 1846
               Relay_Log_File: relay-log-replication_channel.000002
                Relay_Log_Pos: 365
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1227
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'c585489e-88ab-11eb-802b-000017024f83:3' at master log binlog.000004, end_log_pos 885.
                   
                  [...snip...]

Check the transactions status:

mysql> select * from  performance_schema.replication_applier_status_by_worker \G;
*************************** 1. row ***************************
                                           CHANNEL_NAME: replication_channel
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1227
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'c585489e-88ab-11eb-802b-000017024f83:3' at master log binlog.000004, end_log_pos 885; Error 'Access denied; you need (at least one of) the RELOAD privilege(s) for this operation' on query. Default database: ''. Query: 'flush privileges'
                                   LAST_ERROR_TIMESTAMP: 2021-03-19 12:11:32.181589
                               LAST_APPLIED_TRANSACTION: c585489e-88ab-11eb-802b-000017024f83:2
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2021-03-19 12:10:42.191049
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2021-03-19 12:10:42.191049
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2021-03-19 12:11:32.174749
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2021-03-19 12:11:32.181391
                                   APPLYING_TRANSACTION: c585489e-88ab-11eb-802b-000017024f83:3
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2021-03-19 12:10:42.836870
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2021-03-19 12:10:42.836870
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2021-03-19 12:11:32.181440
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************

2. Investigations on the Source (MySQL service installed on OCI Instance)

mysql>  show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       179 | No        |
| binlog.000002 |       403 | No        |
| binlog.000003 |       179 | No        |
| binlog.000004 |      1846 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

As already suggested in the target logs, we need to check the binlog.000004 for more information:

mysql> show binlog events in 'binlog.000004';
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                  |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000004 |    4 | Format_desc    |         2 |         125 | Server ver: 8.0.23-0ubuntu0.20.04.1, Binlog ver: 4                                                                                                                    |
| binlog.000004 |  125 | Previous_gtids |         2 |         156 |                                                                                                                                                                       |
| binlog.000004 |  156 | Gtid           |         2 |         235 | SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:1'                                                                                                     |
| binlog.000004 |  235 | Query          |         2 |         483 | CREATE USER 'replicauser'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$;h%        F>ypPcb[&sCX%vzQtsm38sgjfxPVfrZEMGEF.f8xWKNTyg5kAWBbr.T5' /* xid=5 */ |
| binlog.000004 |  483 | Gtid           |         2 |         560 | SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:2'                                                                                                     |
| binlog.000004 |  560 | Query          |         2 |         718 | GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'localhost' /* xid=6 */                                                                                               |
| binlog.000004 |  718 | Gtid           |         2 |         795 | SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:3'                                                                                                     |
| binlog.000004 |  795 | Query          |         2 |         885 | flush privileges                                                                                                                                                      |
| binlog.000004 |  885 | Gtid           |         2 |         964 | SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:4'                                                                                                     |
| binlog.000004 |  964 | Query          |         2 |        1044 | BEGIN                                                                                                                                                                 |
| binlog.000004 | 1044 | Table_map      |         2 |        1242 | table_id: 60 (mysql.user)                                                                                                                                             |
| binlog.000004 | 1242 | Update_rows    |         2 |        1648 | table_id: 60 flags: STMT_END_F                                                                                                                                        |
| binlog.000004 | 1648 | Xid            |         2 |        1679 | COMMIT /* xid=11 */                                                                                                                                                   |
| binlog.000004 | 1679 | Gtid           |         2 |        1756 | SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:5'                                                                                                     |
| binlog.000004 | 1756 | Query          |         2 |        1846 | flush privileges                                                                                                                                                      |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set (0.00 sec)

Check transactionc585489e-88ab-11eb-802b-000017024f83:2

mysql> exit;
root@mysqlshellinstance:~#
root@mysqlshellinstance:~# mysqlbinlog  --base64-output=decode-rows /var/lib/mysql/binlog.000004  --include-gtids=c585489e-88ab-11eb-802b-000017024f83:2  --hexdump -vv
[...snip...]
SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:2'/*!*/;
# at 560
#210319 12:10:42 server id 2  end_log_pos 718 CRC32 0xce1f03e7
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 00000230 c2 94 54 60   02   02 00 00 00   9e 00 00 00   ce 02 00 00   00 00
# 00000243 08 00 00 00 00 00 00 00  00 00 00 3e 00 00 00 00 |................|
# 00000253 00 00 01 20 00 a0 45 00  00 00 00 06 03 73 74 64 |......E......std|
# 00000263 04 2d 00 2d 00 ff 00 0b  04 72 6f 6f 74 09 6c 6f |.........root.lo|
# 00000273 63 61 6c 68 6f 73 74 0c  01 6d 79 73 71 6c 00 11 |calhost..mysql..|
# 00000283 06 00 00 00 00 00 00 00  12 ff 00 00 47 52 41 4e |............GRAN|
# 00000293 54 20 52 45 50 4c 49 43  41 54 49 4f 4e 20 53 4c |T.REPLICATION.SL|
# 000002a3 41 56 45 20 4f 4e 20 2a  2e 2a 20 54 4f 20 27 72 |AVE.ON.....TO..r|
# 000002b3 65 70 6c 69 63 61 75 73  65 72 27 40 27 6c 6f 63 |eplicauser...loc|
# 000002c3 61 6c 68 6f 73 74 27 e7  03 1f ce                |alhost.....|
#       Query   thread_id=8     exec_time=0     error_code=0    Xid = 6

Check transaction c585489e-88ab-11eb-802b-000017024f83:3

root@mysqlshellinstance:~# mysqlbinlog  --base64-output=decode-rows /var/lib/mysql/binlog.000004  --include-gtids=c585489e-88ab-11eb-802b-000017024f83:3  --hexdump -vv
[...snip...]
#210319 12:10:42 server id 2  end_log_pos 795 CRC32 0x589861cc
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 
000002ce c2 94 54 60   21   02 00 00 00   4d 00 00 00   1b 03 00 00   00 00
# 000002e1 01 c5 85 48 9e 88 ab 11  eb 80 2b 00 00 17 02 4f |...H...........O|
# 000002f1 83 03 00 00 00 00 00 00  00 02 02 00 00 00 00 00 |................|
# 00000301 00 00 03 00 00 00 00 00  00 00 86 f9 e9 9a e2 bd |................|
# 00000311 05 a7 97 38 01 00 cc 61  98 58                   |...8...a.X|
#       GTID    last_committed=2        sequence_number=3       rbr_only=no     original_committed_timestamp=1616155842836870   immediate_commit_timestamp=1616155842836870     transaction_length=167
# original_commit_timestamp=1616155842836870 (2021-03-19 12:10:42.836870 UTC)
# immediate_commit_timestamp=1616155842836870 (2021-03-19 12:10:42.836870 UTC)
/*!80001 SET @@session.original_commit_timestamp=1616155842836870*//*!*/;
/*!80014 SET @@session.original_server_version=80023*//*!*/;
/*!80014 SET @@session.immediate_server_version=80023*//*!*/;
SET @@SESSION.GTID_NEXT= 'c585489e-88ab-11eb-802b-000017024f83:3'/*!*/;
# at 795
#210319 12:10:42 server id 2  end_log_pos 885 CRC32 0xb8ff20d5
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 0000031b c2 94 54 60   02   02 00 00 00   5a 00 00 00   75 03 00 00   00 00
# 0000032e 09 00 00 00 00 00 00 00  00 00 00 25 00 00 00 00 |................|
# 0000033e 00 00 01 20 00 a0 45 00  00 00 00 06 03 73 74 64 |......E......std|
# 0000034e 04 ff 00 ff 00 ff 00 05  06 53 59 53 54 45 4d 12 |.........SYSTEM.|
# 0000035e ff 00 00 66 6c 75 73 68  20 70 72 69 76 69 6c 65 |...flush.privile|
# 0000036e 67 65 73 d5 20 ff b8                             |ges....|
#       Query   thread_id=9     exec_time=0     error_code=0
S
[..... snip ......]

Check transaction c585489e-88ab-11eb-802b-000017024f83:4

root@mysqlshellinstance:~# mysqlbinlog  --base64-output=decode-rows /var/lib/mysql/binlog.000004  --include-gtids=c585489e-88ab-11eb-802b-000017024f83:4  --hexdump -vv 
# 000004b7 f7 01 00 f7 01 f7 01 f7  01 f7 01 04 00 00 00 00 |................|
# 000004c7 00 8d 07 01 01 fe 03 07  0b 53 3f 3f 3f 53 53 0b |.........S...SS.|
# 000004d7 80 64 64                                         |.dd|
#       Table_map: `mysql`.`user` mapped to number 60
# at 1242
#210319 12:10:43 server id 2  end_log_pos 1648 CRC32 0x04cfa80f
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 000004da c3 94 54 60   1f   02 00 00 00   96 01 00 00   70 06 00 00   00 00
# 000004ed 3c 00 00 00 00 00 01 00  02 00 33 ff ff ff ff ff |..........3.....|
# 000004fd ff ff ff ff ff ff ff ff  ff 00 00 00 00 00 88 07 |................|
# 0000050d 09 6c 6f 63 61 6c 68 6f  73 74 0b 72 65 70 6c 69 |.localhost.repli|
# 0000051d 63 61 75 73 65 72 01 01  01 01 01 01 01 01 01 01 |causer..........|
# 0000052d 01 01 01 01 01 01 01 01  01 02 01 01 01 01 01 01 |................|
# 0000053d 01 01 01 01 00 00 00 00  00 00 00 00 00 00 00 00 |................|
# 0000054d 00 00 00 00 00 00 00 00  00 00 15 63 61 63 68 69 |...........cachi|
# 0000055d 6e 67 5f 73 68 61 32 5f  70 61 73 73 77 6f 72 64 |ng.sha2.password|
# 0000056d 46 00 24 41 24 30 30 35  24 3b 68 25 09 07 46 3e |F..A.005..h...F.|
# 0000057d 79 70 50 63 62 5b 26 73  17 04 43 58 25 76 7a 51 |ypPcb..s..CX.vzQ|
# 0000058d 74 73 6d 33 38 73 67 6a  66 78 50 56 66 72 5a 45 |tsm38sgjfxPVfrZE|
# 0000059d 4d 47 45 46 2e 66 38 78  57 4b 4e 54 79 67 35 6b |MGEF.f8xWKNTyg5k|
# 000005ad 41 57 42 62 72 2e 54 35  01 60 54 94 c2 01 01 01 |AWBbr.T5..T.....|
# 000005bd 00 00 00 00 00 88 07 01  25 0b 72 65 70 6c 69 63 |..........replic|
# 000005cd 61 75 73 65 72 01 01 01  01 01 01 01 01 01 01 01 |auser...........|
# 000005dd 01 01 01 01 01 01 01 01  02 01 01 01 01 01 01 01 |................|
# 000005ed 01 01 01 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|
# 000005fd 00 00 00 00 00 00 00 00  00 15 63 61 63 68 69 6e |..........cachin|
# 0000060d 67 5f 73 68 61 32 5f 70  61 73 73 77 6f 72 64 46 |g.sha2.passwordF|
# 0000061d 00 24 41 24 30 30 35 24  3b 68 25 09 07 46 3e 79 |..A.005..h...F.y|
# 0000062d 70 50 63 62 5b 26 73 17  04 43 58 25 76 7a 51 74 |pPcb..s..CX.vzQt|
# 0000063d 73 6d 33 38 73 67 6a 66  78 50 56 66 72 5a 45 4d |sm38sgjfxPVfrZEM|
# 0000064d 47 45 46 2e 66 38 78 57  4b 4e 54 79 67 35 6b 41 |GEF.f8xWKNTyg5kA|
# 0000065d 57 42 62 72 2e 54 35 01  60 54 94 c2 01 01 01 0f |WBbr.T5..T......|
# 0000066d a8 cf 04                                         |...|
#       Update_rows: table id 60 flags: STMT_END_F
### UPDATE `mysql`.`user`
### WHERE
###   @1='localhost' /* STRING(255) meta=65279 nullable=0 is_null=0 */
###   @2='replicauser' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @5=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

Confused much...

3 ... and let's go back to target MySQL DB System,

...and run a basic query:

root@mysqlshellinstance:~# mysql -uusermds -h10.0.1.4 -pABCabc123$%
[...]
mysql>
mysql> flush privileges;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
mysql>
mysql>

So, far this error appears to be caused by my user's lack of super privileges.

Also, as mentioned in the documentation: "Replication filters are not supported." So, you can't make your way around.

However, how can one grant these privileges for a user on a MySQL OCI System?

There's a documentation about Configuring DB System (still, not sure if this can provide me more control over the user's privileges).

Edit: at next section ("...more Troubleshooting") you will find the right documentation; I still suggest you do read the Configuring DB System, too. You never know when it comes in handy for you.

... more Troubleshooting

I have mentioned at the ending of the previous "Troubleshooting" section that I needed to provide higher privileges to my user.

The information I was looking for (or at least, something related to) was found in the documentation Import and Exporting Databases (Compatibility Between On-Premise MySQL and MySQL Database Service section) and I quote:

strip_restricted_grants: certain privileges are restricted in the MySQL Database Service. Privileges such as RELOAD, FILE, SUPER, BINLOG_ADMIN, and SET_USER_ID. It is not possible to create users granting these privileges. This option strips these privileges from dumped GRANT statements.

That "RELOAD" privilege sounds familiar, eh?

Things to take in consideration at this step

The documentation Import and Exporting Databases mentions the following:

This task describes how to export data from a supported MySQL Server source to an Object Storage bucket using the MySQL Shell dumpInstance utility.This task requires the following:

• MySQL Shell 8.0.21, or higher.

• MySQL Server 5.7.9, or higher.

• Access to Object Storage and an existing bucket.

[.... snip ... ]

-- source Exporting Data to Object Storage with MySQL Shell (Import and Exporting Databases)

Continuation at Fixed "MySQL source - MDS" replication