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]>
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).
