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):

First part:

• 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:

Second part :

• creating the channel by using "terraform apply target"

Changes implemented on the code:

1. "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 files

First 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 CLI

Keep 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"
> }
> EOF

b) 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] [e-mail]: 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!