6.3 Access MySQL DB System

[continuation of 3.2] [ ... in progress... ]

As already mentioned in tutorial 3.2 The basics - OCI CLI, when successfully providing a MySQL DB instance, it will have a private IP only attached to it.

For security reasons, this seems plausible - you do not want public access to your databases.

How can we login to our databases in this scenario?

A work around for this scenario would be accessing the database through mysql-shell (among other tools)

If you want to connect from an instance to your private MySQL instance you would need an intermediary environment, such as MySQL Router

1. Prepare environment

1.1 Update Firewall Rules on the instance from which you want to deploy to your MySQL instance

root@deploymentmachine:/home/tests/mydb# # or use firewall-cmd
root@deploymentmachine:/home/tests/mydb#
root@deploymentmachine:/home/tests/mydb# ufw allow 3306/tcp
Rules updated
Rules updated (v6)
root@deploymentmachine:/home/tests/mydb# ufw allow 33060/tcp
Rules updated
Rules updated (v6)
root@deploymentmachine:/home/tests/mydb# ufw reload

1.2 Install following MySQL tools:

 root@deploymentmachine:/home/tests/mydb# apt install mysql-client-core-8.0

and

root@deploymentmachine:/home/tests/mydb# snap install mysql-shell

1.3 Update Firewall rules in the Cloud (Ingress Rules)

In order to make these changes work, you need to perform a few changes on the OCI.

You need your instance to communicate with the DB instance.

1.3.1 Go to your DB System, and Select Subnet (marked with green):

1.3.2 Select security list that corresponds to your root compartment:

1.3.3 And add ingress rules:

2. Connect to MySQL system

2.1 Connect to your MySQL system using mysql-shell (provide the password and username you have mentioned in the json file, when creating the database system):

root@deploymentmachine:/home/tests/mydb# mysqlsh usertest@10.0.0.16:3306
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
Please provide the password for 'usertest@10.0.0.16:3306': ***********
MySQL Shell 8.0.22

Copyright (c) 2016, 2020, 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 '\?' for help; '\quit' to exit.
Creating a session to 'usertest@10.0.0.16:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 83
Server version: 8.0.23-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
mysql-py []> 

Perform a few tests:

mysql-py []> \sql show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0009 sec)
mysql-py []>
mysql-py []> \system echo Hello all!
Hello all!
mysql-py []> \sql create database hellotest;
Query OK, 1 row affected (0.0044 sec)
mysql-py []> \sql show databases;
+--------------------+
| Database           |
+--------------------+
| hellotest          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
mysql-py []> \sql use hellotest;
Query OK, 0 rows affected (0.0006 sec)
mysql-py [hellotest]> \sql show tables
Empty set (0.0038 sec)
mysql-py [hellotest]> \sql create table example (name varchar(20), firstname varchar(20));
Query OK, 0 rows affected (0.0171 sec)
mysql-py [hellotest]> \sql show tables
+---------------------+
| Tables_in_hellotest |
+---------------------+
| example             |
+---------------------+
1 row in set (0.0018 sec)
mysql-py [hellotest]> \sql insert into example (name, firstname) values ('Foster', 'Zack');
Query OK, 1 row affected (0.0053 sec)
mysql-py [hellotest]> \sql select * from example;
+--------+-----------+
| name   | firstname |
+--------+-----------+
| Foster | Zack      |
+--------+-----------+
1 row in set (0.0007 sec)
mysql-py [hellotest]>

Type Ctrl+D or \exit to leave the mysql-shell:

mysql-py [hellotest]>  \exit
Bye!
root@deploymentmachine:/home/tests/mydb#

2.2 Connect to your MySQL using mysql command

root@deploymentmachine:/home/tests/mydb# mysql -u usertest -p -h 10.0.0.16
Enter password: <enter passwd setup in json file>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 8.0.23-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

... and let's check what we have created so far:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellotest          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hellotest;
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_hellotest |
+---------------------+
| example             |
+---------------------+
1 row in set (0.01 sec)

mysql> select * from example;
+--------+-----------+
| name   | firstname |
+--------+-----------+
| Foster | Zack      |
+--------+-----------+
1 row in set (0.00 sec)

mysql>
mysql> exit;
Bye

What about SSH?

This is a topic that we need to skip here. When you create the json file for creating the database, there is an option of "sshAuthorizedKeysFile"

root@deploymentmachine:/home/tests/mydb#  oci db system launch --generate-full-command-json-input > createdb.json
root@deploymentmachine:/home/tests/mydb# more createdb.json  | grep ssh
  "sshAuthorizedKeysFile": "/path/to/file",

However, even if these are added, when deploying the instance, port 22 is not opened. (ports that are not >1024 are not valid for the json creation file).

References:

https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-linux.html

https://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/mysql-shell-8.0-en.pdf