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: yesFor 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 filesAs 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:
sourceis the MySQL which is installed and configured on the instancetargetis 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 awork 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 filesObviously, 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/python3the 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
mysqlschema are not replicated and cause replication to stop.Source must run with the same
lower_case_table_namesvalue, 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 filesCode 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 initterraform planterraform 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 sourcethe slave - the MySQL DB System targe
1. Investigations on the Target (MySQL DB System)
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)
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 = 6Check 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,
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 asRELOAD,FILE,SUPER,BINLOG_ADMIN, andSET_USER_ID. It is not possible to create users granting these privileges. This option strips these privileges from dumpedGRANTstatements.
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
dumpInstanceutility.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
Last updated