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,
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
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@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:
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:
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
