# 6.3 Access MySQL DB System

As already mentioned in tutorial [3.2 The basics - OCI CLI](https://isaac-exe.gitbook.io/various-tutorials/tutorials/3.-mysql/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.`*&#x20;

&#x20;

***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`

### &#x20;**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.`*&#x20;

*`You need your instance to communicate with the DB instance.`*&#x20;

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

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MSPkRFZQbY-9f9duJMa%2F-MSPl2jL66EgPw3PdxPD%2F16.png?alt=media\&token=76bee4d0-78d8-4e9e-a2d5-b6eddeee9044)

***`1.3.2 Select security list that corresponds to your root compartment:`***

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MSPlFaDadXNNv1zH_St%2F-MSPlr9FNxlDdSOqT2fb%2F17.png?alt=media\&token=250a01cd-03e1-4f26-a6e4-6cf42b6ccd46)

***`1.3.3 And add ingress rules:`***

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MSPlvD3C4z3IIe3kM4n%2F-MSPmBnWTQiCSV3W6i7k%2F18.png?alt=media\&token=ddb94046-5de5-4dcf-88cd-cd56d85a18f2)

![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MSPlvD3C4z3IIe3kM4n%2F-MSPmhGfnXga1DsYUAv6%2F19.png?alt=media\&token=79dc7569-67c5-4e14-a1da-d3063fb017a7)

### 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?**&#x20;

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"&#x20;

```
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).`&#x20;

> *References:*
>
> [*https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-linux.html*](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*](https://docs.oracle.com/cd/E17952_01/mysql-shell-8.0-en/mysql-shell-8.0-en.pdf)&#x20;
