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:
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:
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:
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
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.
The Terraform documentation for MySQL OCI DB could provide a bit more information.
For the "target_type" it is mentioned only:
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)
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):
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
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.
Now, my working directory contains 18 files in total:
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
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:
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?
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.
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
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 ... ]
(Nginx example)
From the previous tutorial,, we have 13 terraform files that would deploy an instance, a MySQL DB along with the compartment &networking setup:
Resource - creates a channel
- (Required) (Updatable) The specific target identifier.
- The specific source identifier.
Data source
A step-by-step implementation for Terraform-Ansible integration can be found at tutorial , and at (a bit advanced)
-- documentation source:
The code example is at Github repository:
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
we have already discussed how to connect to MySQL DB System at tutorial
There's a documentation about (still, not sure if this can provide me more control over the user's privileges).
The information I was looking for (or at least, something related to) was found in the documentation (Compatibility Between On-Premise MySQL and MySQL Database Service section) and I quote:
The documentation mentions the following:
-- source Exporting Data to Object Storage with MySQL Shell ()
