7.5 Channels (code)
About
This tutorial presents the code implementation for subchapter "Fixed 'MySQL source - MDS' replication"
Code example
Example code at github repository: https://github.com/MuchTest/MySqlDbSystemExample3
The code will implement (in two parts):
creating a VCN and compartment
deploying an OCI instance and provisioning MySQL service
creating a MySQL DB System
creating an object storage(bucket)
After setting up the /root/.oci/config for object storage, and performing the GTID purging on MDS target, you can create the channel:
creating the channel by using "terraform apply target"
Changes implemented on the code:
"remote.tf" - MySQL-shell snapd installation has been replaced by installing MySQL-Shell from official repository for enabling Javascript mode (apparently,
root@deploymentmachine:/home# diff channel_replica/remote.tf bucket_test/remote.tf
18,19c18,21
< "sudo apt install -y snapd",
< "sudo snap install mysql-shell"]
---
> "sudo wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.23-1ubuntu20.04_amd64.deb",
> "sudo dpkg -i mysql-shell_8.0.23-1ubuntu20.04_amd64.deb",
> "sudo apt update"
> ]
34d35
<
root@deploymentmachine:/home#
}2. "mysql.yml" - provided root password
root@deploymentmachine:/home# diff channel_replica/mysql.yml bucket_test/mysql.yml
72a73,76
> - name: Password for root login
> command: /usr/bin/mysql -uroot -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123!';"
> become: yes
>
74c78
< command: /usr/bin/mysql -uroot -e "flush privileges;"
---
> command: /usr/bin/mysql -uroot -pabc123! -e "flush privileges;"
root@deploymentmachine:/home# 3. "channel.tf" will not be called when deploying the environment. The creation of channel will happen when the dumping/loading data and GTID purging are correctly implemented.
4. "variables.tf" - variables added for configuring the object storage (bucket):
root@deploymentmachine:/home/bucket_test# more variables.tf | grep -A2 bucket
## for bucket
variable "name_bucket"{
default = "mdsbucket"
}
variable "bucket_namespace" {
default = " " ## add here your namespace
}
n.b: You need to find the ID of the bucket and add it to the variable bucket_namespace
You can find the namespace with command "oci os ns get"
root@deploymentmachine:/home# export OCI_CLI_SUPPRESS_FILE_PERMISSIONS_WARNING=True
root@deploymentmachine:/home# oci os ns get
{
"data": "some info here"
}
and of course, create_bucket.tf for creation of the bucket
root@deploymentmachine:/home/bucket_test# more create_bucket.tf
resource "oci_objectstorage_bucket" "mds_bucket" {
compartment_id = oci_identity_compartment.MySqlOciCompartment.id
name = var.name_bucket
namespace = var.bucket_namespace
storage_tier = "Standard"
}
# resource "oci_objectstorage_object" "test_object" {
# bucket = oci_objectstorage_bucket.mds_bucket.name
# namespace = var.bucket_namespace
# object = var.bucket_object
# storage_tier = "Standard"
# }Folder's content:
root@deploymentmachine:/home/bucket_test# tree .
.
├── ansible.cfg
├── channel
│  └── create_channel.tf
├── compartment.tf
├── create_bucket.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── my.cnf
├── mysql.yml
├── output.tf
├── provider.tf
├── remote.tf
├── replication.yml
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf
1 directory, 20 filesFirst Part
Perform the usual terraform commands: terraform init, terraform plan, terraform apply
After the "terraform apply" you will have created/launched following:
a VCN and a compartment
an OCI instance on which we provisioned MySQL service (mysqlshellinstance)
a MySQL DB System
an object storage(bucket)
The created Object storage in OCI UI:
Setting up the /root/.oci/config for object storage
You can find the tutorial, step-by-step, at:Install and Configure OCI CLIKeep in mind, that now you make the changes for the compartment where the MDS, MySQL source and Object bucket are located. This configuration file /root/.oci/config must exist on the instance that can access the MDS (in our case, it's the instance "MySqlShellInsance"
Performing the GTID purging
a) Dumping data from MySQL source into Bucket
Log in to mysqlshellinstance, and create a database:
root@deploymentmachine:/home/bucket_test# instanceip=`terraform output MySqlSourceIP | sed 's/"//g'`
root@deploymentmachine:/home/bucket_test#
root@deploymentmachine:/home/bucket_test# 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:~# cd /home
root@mysqlshellinstance:/home# mysql -uroot -pabc123!
[.... snip ....]
mysql> create database hehe;
Query OK, 1 row affected (0.00 sec)
mysql> create table hehe.hehehe (name varchar(20), firstname varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into hehe.hehehe (name, firstname) values ('Foster', 'Zack');
Query OK, 1 row affected (0.01 sec)
mysql> select * from hehe.hehehe;
+--------+-----------+
| name | firstname |
+--------+-----------+
| Foster | Zack |
+--------+-----------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hehe |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>Dump data into bucket from mysql-shell:
root@mysqlshellinstance:/home# mysqlsh -uroot -pabc123!
Please provide the password for 'root@localhost': *******
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): N
MySQL Shell 8.0.23
[... snip.... ]
MySQL localhost:33060+ ssl JS > util.dumpInstance("mdsobject", {osBucketName: "mdsbucket", osNamespace: "add here namespace", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: User 'debian-sys-maint'@'localhost' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'root'@'localhost' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, PROXY, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: Database hehe had unsupported ENCRYPTION option commented out
Compatibility issues with MySQL Database Service 8.0.23 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Writing DDL for schema `hehe`
Writing DDL for table `hehe`.`hehehe`
Preparing data dump for table `hehe`.`hehehe`
NOTE: Could not select a column to be used as an index for table `hehe`.`hehehe`. Chunking has been disabled for this table, data will be dumped to a single file.
Data dump for table `hehe`.`hehehe` will be written to 1 file
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
1 thds dumping - ?% (1 rows / ?), 0.00 rows/s, 10.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 12 bytes
Compressed data size: 0 bytes
Compression ratio: 12.0
Rows written: 1
Bytes written: 0 bytes
Average uncompressed throughput: 10.90 B/s
Average compressed throughput: 0.00 B/s
MySQL localhost:33060+ ssl JS > \q
Bye!
root@mysqlshellinstance:/home#Now, the bucket has the following "mdsobject" object with contents:
Save file @.json on the mysqlshellinstance:
root@mysqlshellinstance:/home# cat > @.json << EOF
> {
> "dumper": "mysqlsh Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))",
> "version": "1.0.2",
> "origin": "dumpInstance",
> "schemas": [
> "hehe"
> ],
> "basenames": {
> "hehe": "hehe"
> },
> "users": [
> "'debian-sys-maint'@'localhost'",
> "'replicauser'@'%'",
> "'root'@'localhost'"
> ],
> "defaultCharacterSet": "utf8mb4",
> "tzUtc": true,
> "bytesPerChunk": 64000000,
> "user": "root",
> "hostname": "mysqlshellinstance",
> "server": "mysqlshellinstance",
> "serverVersion": "8.0.23-0ubuntu0.20.04.1",
> "gtidExecuted": "1beaf71c-8b06-11eb-898a-00001702dfe1:1-10",
> "gtidExecutedInconsistent": false,
> "consistent": true,
> "mdsCompatibility": true,
> "begin": "2021-03-22 12:30:22"
> }
> EOFb) Loading data from Bucket into MySQL DB system
root@mysqlshellinstance:/home# mysqlsh -uusermds -h10.0.1.4 -pABCabc123$%
MySQL Shell 8.0.23
[... snip ... ]
MySQL 10.0.1.4:33060+ ssl JS >
MySQL 10.0.1.4:33060+ ssl JS > util.loadDump("mdsobject", {osBucketName: "mdsbucket", osNamespace: "add here namespace", threads: 4})
Loading DDL and Data from OCI ObjectStorage bucket=mdsbucket, prefix='mdsobject' using 4 threads.
Opening dump...
Target is MySQL 8.0.23-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-0ubuntu0.20.04.1
Fetching dump data from remote location...
Fetching 1 table metadata files for schema `hehe`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `hehe`
[Worker002] Executing DDL script for `hehe`.`hehehe`
[Worker001] hehe@hehehe.tsv.zst: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Executing common postamble SQL
1 chunks (1 rows, 12 bytes) for 1 tables in 1 schemas were loaded in 1 sec (avg throughput 12.00 B/s)
0 warnings were reported during the load.
MySQL 10.0.1.4:33060+ ssl JS >Check if data is loaded, and perform the GTID purging:
root@mysqlshellinstance:/home# mysql -uusermds -h10.0.1.4 -pABCabc123$%
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hehe |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql>
mysql> call sys.set_gtid_purged("1beaf71c-8b06-11eb-898a-00001702dfe1:1-10")
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> show global variables like 'GTID%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------------------------------+
| gtid_executed | 1beaf71c-8b06-11eb-898a-00001702dfe1:1-10,76a2b021-8b06-11eb-839e-020017069921:1-3 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 1beaf71c-8b06-11eb-898a-00001702dfe1:1-10 |
+----------------------------------+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> exit
Bye
root@mysqlshellinstance:/home#
root@mysqlshellinstance:/home# exit
logout
ubuntu@mysqlshellinstance:~$ exit
logout
Connection to 130.61.186.185 closed.
root@deploymentmachine:/home/bucket_test#Second part
Time to deploy the channel:
root@deploymentmachine:/home/bucket_test#
root@deploymentmachine:/home/bucket_test# cp channel/create_channel.tf .
root@deploymentmachine:/home/bucket_test#
root@deploymentmachine:/home/bucket_test# terraform plan -target oci_mysql_channel.MySqlOciChannel
[.... snip .....]
An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
+ create
Terraform will perform the following actions:
# oci_mysql_channel.MySqlOciChannel will be created
+ resource "oci_mysql_channel" "MySqlOciChannel" {
+ compartment_id = "ocid1.compartment.oc1..a"
+ defined_tags = (known after apply)
+ description = (known after apply)
+ display_name = (known after apply)
+ freeform_tags = (known after apply)
+ id = (known after apply)
+ is_enabled = true
+ lifecycle_details = (known after apply)
+ state = (known after apply)
+ time_created = (known after apply)
+ time_updated = (known after apply)
+ source {
+ hostname = "mysqlshellinstance.mysqloci.mysqlocivcn.oraclevcn.com"
+ password = (sensitive value)
+ port = 3307
+ source_type = "MYSQL"
+ ssl_mode = "DISABLED"
+ username = "replicauser"
+ ssl_ca_certificate {
+ certificate_type = (known after apply)
+ contents = (known after apply)
}
}
+ target {
+ applier_username = (known after apply)
+ channel_name = (known after apply)
+ db_system_id = "ocid1.mysqldbsystem.oc1.eu-frankfurt-1.aaa"
+ target_type = "DBSYSTEM"
}
}If no errors, apply changes:
root@deploymentmachine:/home/bucket_test# terraform apply -target oci_mysql_channel.MySqlOciChannel
[ ... snip ... ]
Do you want to perform these actions?
Terraform will perform the actions described above.
Only 'yes' will be accepted to approve.
Enter a value: yes
oci_mysql_channel.MySqlOciChannel: Creating...
[ ... snip ... ]Check from UI, if channel is active
Go back to mysqlshellinstance, and check status of the slave, via MySQL Client:
root@mysqlshellinstance:~# mysql -uusermds -h10.0.1.4 -pABCabc123$%
[ .... snip .... ]
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: 2991
Relay_Log_File: relay-log-replication_channel.000002
Relay_Log_Pos: 409
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[ .... snip .... ]
Last_Errno: 0
Last_Error:
Skip_Counter: 0
[ .... snip .... ]
Master_UUID: 1beaf71c-8b06-11eb-898a-00001702dfe1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 0
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
[ .... snip .... ]
Executed_Gtid_Set: 1beaf71c-8b06-11eb-898a-00001702dfe1:1-10,
76a2b021-8b06-11eb-839e-020017069921:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: replication_channel
Master_TLS_Version: TLSv1.2,TLSv1.3
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace: mysql
1 row in set, 1 warning (0.00 sec)
Check replication
Now, access MySQL source via MySQL client, and create another database:
root@mysqlshellinstance:~# mysql -uroot -pabc123!
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 21
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
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> create database testingcode;
Query OK, 1 row affected (0.01 sec)
mysql> create table testingcode.hehehe (name varchar(20), firstname varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into testingcode.hehehe (name, firstname) values ('Foster', 'Zack');
Query OK, 1 row affected (0.01 sec)
mysql>... and access once again MDS to see if replication was possible:
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 32
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> show databases;
+--------------------+
| Database |
+--------------------+
| hehe |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testingcode |
+--------------------+
6 rows in set (0.00 sec)
mysql> use testingcode;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_testingcode |
+-----------------------+
| hehehe |
+-----------------------+
1 row in set (0.00 sec)
mysql> select * from testingcode.hehehe;
+--------+-----------+
| name | firstname |
+--------+-----------+
| Foster | Zack |
+--------+-----------+
1 row in set (0.00 sec)
mysql>Destroy resources
Before destroying the resources, you need to empty the bucket, otherwise you may end up with this kind of error:
Error: 409-BucketNotEmpty
Service: ObjectStorageBucket
Error Message: Bucket named 'mdsbucket' is not empty. Delete all object versions first.
OPC request ID: ////
Suggestion: The resource is in a conflicted state. Please retry again or contact support for help with service: ObjectStorageBucket
Delete pre-authenticated requests:
For deleting pre-authenticated requests from mdsbucket by using the OCI CLI, use the command line:
for idz in `oci os preauth-request list -bn mdsbucket | grep id | awk '{print $2}' | sed 's/",//g' | sed 's/"//g'`; \
do echo y | oci os preauth-request delete -bn mdsbucket --par-id $idz ; \
done... and delete the object's contents:
Now, you can apply "terraform destroy"
Code for an architecture with Instance in a public subnet, and MDS in a private subnet, at following github repository: https://github.com/MuchTest/ExampleMySqlDbAndInstance
Fini!
Last updated