Connecting to a DB System

Connecting to the MySQL DB System with SSH and MySQL Shell

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use MySQL Shell to connect to your MySQL DB System's Endpoint.

This task requires the following:
  • A running VCN
  • A running MySQL DB System attached to the VCN. Make note of the IP address of the MySQL Endpoint in the DB System, and the administration user and password. To find the IP Address, see Endpoints
  • A running Compute instance (Oracle Linux is used in the following example) attached to a public subnet on the same VCN as the MySQL DB System. Make note of the public IP address of the compute instance . The SSH port, 22, is added to the public subnet by default. For more information, see Network Setup. It is strongly recommended you add an Ingress Rule for port 33060, which is the default port forMySQL Shell. If that port is not available, MySQL Shell connects to port 3306. If you have configured alternative ports, you must include the port number in your connection string.
  1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.
    ssh opc@computeInstancePublicIP
  2. Install MySQL Shell on the Compute instance using the following commands:
    shell> sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
    shell> sudo yum install mysql-shell
  3. Start MySQL Shell and connect to the MySQL DB System's endpoint using the following command:
    
    shell> mysqlsh Username@IPAddressOfMySQLDBSystemEndpoint 
    Please provide the password for 'Username@IPAddressOfMySQLDBSystemEndpoint':
    Save password for 'Username@IPAddressOfMySQLDBSystemEndpoint'? 
    [Y]es/[N]o/Ne[v]er (default No): 
    
    This command starts a global session. MySQL Shell attempts to connect to port 33060 by default and, if that port is not available, falls back to port 3306.
The connection is made and the following message displayed:
MySQL Shell 8.0.21-commercial

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'Username@IPAddressOfMySQLDBSystemEndpoint'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 15
Server version: 8.0.21-u2-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
MySQL  IPAddressOfMySQLDBSystemEndpoint:PortNumber ssl  JS > 

Connecting to the MySQL DB System with SSH and the MySQL Client

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use the mysql command line client to connect to your MySQL DB System's Endpoint.

This task requires the following:
  • A running VCN
  • A running MySQL DB System attached to the VCN. Make note of the IP address of the MySQL Endpoint in the DB System, and the administration user and password. To find the IP Address, see Endpoints
  • A running Compute instance (Oracle Linux is used in the following example) attached to a public subnet on the same VCN as the MySQL DB System. Make note of the public IP address of the compute instance . The SSH port, 22, is added to the public subnet by default. For more information, see Network Setup.
  1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.
    ssh opc@computeInstancePublicIP
  2. Install the MySQL client on the Compute instance using the following commands:
    shell> sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
    shell> sudo yum install mysql
  3. Connect to your DB System using the MySQL client.
    shell> mysql --host IPAddressOfMySQLDBSystemEndpoint -u Username -p 

Connecting from MySQL Workbench

This topic describes how to connect to MySQL Database Service using MySQL Workbench.

This task assumes the following:
  • You have created a Linux Compute Instance to connect to. It is not possible to connect directly from a remote IP to the MySQL DB System endpoint. You must connect to a Compute Instance, and from the Compute Instance to the MySQL DB System.
  • An SSH key-pair.
    Note

    MySQL Workbench does not support PuTTy-generated keys. If you used PuTTy to generate your SSH key pair, you must use PuTTygen's Conversion utility to convert your private key to the OpenSSH format for use with MySQL Workbench.
    Note

    MySQL Workbench does not currently support the key-pair generated by the Create Compute Instance task. When creating a Compute instance, you must use SSH keys you generated yourself.
  • You have configured your network as described in Configuring the Network
For information on MySQL Workbench, see MySQL Workbench User Guide
To connect to MySQL using Workbench:
  1. See TCP/IP over SSH Connection.
  2. Enter the following:
    • SSH Hostname: the public IP address of the Compute Instance.
    • SSH Username: opc
    • SSH Keyfile: path to your SSH private key.
    • MySQL Hostname: the IP address of the MySQL Endpoint.
    • MySQL Server Port: the port the MySQL Endpoint is listening on.
    • Username/Password: credentials you defined when creating the DB System.
  3. Click Test Connection to confirm the connection details are valid.