7.3 Endpoints

MySQL Client

In previous tutorials (7.1 & 7.2), we have seen how to use mysql-shell for connecting to MySQL System DB.

Let's access the MDS the same way again, and create a "test" database:

root@mysqlshellinstance:/home# mysqlsh  root@10.0.1.4
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
Please provide the password for 'root@10.0.1.4': ***********
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 'root@10.0.1.4'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 48 (X protocol)
Server version: 8.0.23-u2-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
mysql-py []> \sql
Switching to SQL mode... Commands end with ;
mysql-sql []> create database test;
Query OK, 1 row affected (0.0053 sec)
mysql-sql []> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.0012 sec)
mysql-sql []>
mysql-sql []> \exit
Bye!

Another way of accessing the MDS can be done with the help of MySQL Client:

root@mysqlshellinstance:/home# apt update
root@mysqlshellinstance:/home# apt install mysql-server -y

need to modify "remote-exec" provisioner for installing mysql-server

...and not access the MDS, by using the Private IP from the Endpoint section:

root@mysqlshellinstance:/home/test_db# mysql --host 10.0.1.4 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql>

Importing datasets

For this example, the follwing repository will be used:

https://github.com/datacharmer/test_db

Clone the repository on the instance from which you can access the MDS:

root@mysqlshellinstance:/home# git clone  https://github.com/datacharmer/test_db
Cloning into 'test_db'...
remote: Enumerating objects: 11, done.
remote: Counting objects: 100% (11/11), done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 116 (delta 5), reused 8 (delta 4), pack-reused 105
Receiving objects: 100% (116/116), 74.27 MiB | 19.59 MiB/s, done.
Resolving deltas: 100% (59/59), done.

Go under folder test_db, and log in again to MDS via MySQL Client:

root@mysqlshellinstance:/home# cd test_db
root@mysqlshellinstance:/home/test_db# ls -ltr
total 168348
-rw-r--r-- 1 root root     7948 Mar 15 13:03 employees_partitioned_5.1.sql
-rw-r--r-- 1 root root     6276 Mar 15 13:03 employees_partitioned.sql
-rw-r--r-- 1 root root     4193 Mar 15 13:03 employees.sql
-rw-r--r-- 1 root root     4325 Mar 15 13:03 README.md
-rw-r--r-- 1 root root      964 Mar 15 13:03 Changelog
-rw-r--r-- 1 root root      250 Mar 15 13:03 load_departments.dump
drwxr-xr-x 2 root root     4096 Mar 15 13:03 images
-rw-r--r-- 1 root root     1090 Mar 15 13:03 load_dept_manager.dump
-rw-r--r-- 1 root root 14159880 Mar 15 13:03 load_dept_emp.dump
-rw-r--r-- 1 root root 17722832 Mar 15 13:03 load_employees.dump
-rw-r--r-- 1 root root 39806034 Mar 15 13:03 load_salaries1.dump
-rw-r--r-- 1 root root 39805981 Mar 15 13:03 load_salaries2.dump
-rw-r--r-- 1 root root 39080916 Mar 15 13:03 load_salaries3.dump
-rw-r--r-- 1 root root     4568 Mar 15 13:03 objects.sql
-rw-r--r-- 1 root root 21708736 Mar 15 13:03 load_titles.dump
-rwxr-xr-x 1 root root     2013 Mar 15 13:03 test_versions.sh
-rw-r--r-- 1 root root     4715 Mar 15 13:03 test_employees_sha.sql
-rw-r--r-- 1 root root     4711 Mar 15 13:03 test_employees_md5.sql
-rwxr-xr-x 1 root root     1800 Mar 15 13:03 sql_test.sh
-rw-r--r-- 1 root root      272 Mar 15 13:03 show_elapsed.sql
drwxr-xr-x 2 root root     4096 Mar 15 13:03 sakila

root@mysqlshellinstance:/home/test_db# 
root@mysqlshellinstance:/home/test_db#  
root@mysqlshellinstance:/home/test_db# mysql --host 10.0.1.4 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49

[...]

Make sure you are under the exact folder (of the cloned repository), and then proceed with importing the employees.sql file:

mysql>\! pwd
/home/test_db
mysql>
mysql> source employees.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 6 warnings (0.01 sec)

[ ... snip ... ]

Query OK, 24919 rows affected (0.57 sec)
Records: 24919  Duplicates: 0  Warnings: 0

Query OK, 7671 rows affected (0.21 sec)
Records: 7671  Duplicates: 0  Warnings: 0

+---------------------+
| data_load_time_diff |
+---------------------+
| 00:01:49            |
+---------------------+
1 row in set (0.00 sec)

mysql>

Check if new database has been successfully imported:

mysql> use employees ;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql>